OK, Ive read many posts on this problem but have seen no resolution.
Here's the deal. When on the actual SQL box (There's 2 in the cluster) the bulk insert works fine. No errors and the event log on the machine that is hosting the text file shows that the account that SQL runs on has accessed the file. This account is a DOmain account and is in the Local Administrator of the SQL server and the remot box hosting the text file.
Thats great if you want your developers testing and accessing your SQL box as Administrators. We don't work that way. Our developers connect via SQL Management Studio and test ther stuff that way. That's where the problem rears it's ugly head.
When anyone runs a Bulk Insert command that accesses a text file that is on a remote server, they get an "Access Denied". Now, I did a lot of testing and found that when the users executes the Bulk Insert command from the SQL Management studio on their desk top, they connect to the SQL box with their credentials (OK, that's correct right?), SQL then runs the Bulk Isert command which then reaches out to the remote file server but gets the "Access Denied". I check the logs and it shows that "Anonymous" was trying to accesss the file.
Why is Anonymouss coming over as credentials for SQL on a Bulk Insert? I'm no idiot but this sounds like a big crappy bug tha M$ will not fess to. I followed many suggestions, made sure NTFS and Share level permissions were correct (That was the first thing...), made sure the account that was running as SQL Server within the cluster on both nodes in the cluster was the same, that wasn't it, I even created a SPN for SQL to run and automatically register in AD with the credentials that SQL runs as. NOTHING!!!
Has anyone gotten their bulk insert to work when inserting from a file that is NOT local to the SQL box? Any help is appreciated, but putting the text files on the local SQL box IS NOT AN OPTION.
Thanks
What account is the sql server service running under? And when your devs connect, are they using windows accounts or SQL logins? Did you read topic "Importing Bulk Data by Using Bulk Insert or OPENROWSET" http://msdn2.microsoft.com/en-us/library/ms175915.aspx? See the "Security Considerations" section to understand how it works.
|||I am seeing the same thing. I am convinced it is a bug also.It looks like SQL 2005 is not passing the proper windows login back to the other server, however it does locally. I too have tried everything recommended and have had no luck.
|||According to section Security Considerations in the BOL topic I posted above, you should use impersonation/delegation to meet your needs. You guys saying you tried impersonation/delegation but it didn't work, is this correct? If so, post your repro below and we can take a look at it.|||
The account is a Domain account and we are using both Windows and SQL security. Upon reading the site you provided, the following state from the site indicates that the SQL service is the item accessing the remote file...
"BULK INSERT loads data from a data file into a table. This functionality is similar to that provided by the in option of the bcp command; however, the data file is read by the SQL Server process"
That criteria is met in that the SQL account used to run SQL does have FULL rioghts to the file from the share level to the NTFSlevel and has "Administrator" rights to both boxes. To further substantiate it's "Full" access, from any other application on the SQL box it can ope the text file.
Again it is because SQL is actually using "Anonymous" to log on to the server hosying the file.
Let me re0iterate this again. When SQL reaches out to access the text file for a bulk unsert, it get's the Access denied because it is using "Anonymous" to log on to the remote server. The SQL Service is running with domain credentials.
WHY IS SQL USING "ANONYMOUS"?
Sorry for the frustrations here but it looks like a lot of people are having this problem and no one is giving a sulution.
|||Yes, we set delegation on Both nodes of the SQL cluster and the server hosting the files as well as the Domain account tha the SQL instance runs as. We also have a SPN running for the instance of SQL for the domain account SQL runs as.
Now, what is a "repro" and is delegation the same thing as imersonation?
Thank you your help.
|||Sorry, repro is short for "reproduce". Basically give me an example that I can try to duplicate on my machines here. If we can't figure out the problem over the forum, we can try your scenario in our lab to experience first hand what you're doing, but it can only be done if you give us the exact steps. It's the easiest way for us to see and understand the problem you're facing.
|||Delegation is a Windows things. If you want to use Bulk Insert via 2-hop scenario (Person on Computer1 connects to Computer2 which reads file from Computer3 - this looks like what you're describing), you have to enable Windows delegation. If you open Windows Help and search for Delegation, you should come across a topic "Delegation authentication: Security". I believe web scenarios who need to validate with user credentials and have the IIS server on a different box than their SQL Server have to do the same thing.
Cos_SQL, your quote is for a standard 1-hop scenario, but you have a 2-hop scenario. I am guessing this is why you see "Anonymous" access, and why you're probably getting access denied.
The above is what I gathered from reading BOL and the bugs database, so let me know if you hit any snags or are still hitting problems.
|||OK, here's our setup...
Active directory Account: SQL05svc (Administrator to all servers in question.)
SQL 2005 Cluster: 2 Node Active Passive (SQLNodeA & SQLNodeB)
File Server: Fsrv01 (Windows 2003 Server - Fully Patched)
Client WS: Client01 (Windows XP - Fully Patched)
The SQL CLuster runs SQL, SSIS and Reporting Service and runs on the domain account: SQL05svc
Security is set on the Windows file share for "Full" rights to the share as well as the NTFS level for the domain account SQL05svc.
From one of the SQL 2005 server nodes that has all resources assigned to it we log onto it and run a query from QUERY analyzer...
BULK INSERT dbo.CS_Test FROM '\\Fsrv01\Data\YanTest\CS_TEST.txt'
WITH (FIELDTERMINATOR = '","',MAXERRORS=0,TABLOCK,BATCHSIZE=300000,
ERRORFILE='\\Fsrv01\Data\YanTest\CS_TEST_Err.txt',
ROWTERMINATOR ='\"\n\"',
DATAFILETYPE = 'char',
CODEPAGE='1252')
GO
It runs fine. One hop.
Check the Security Log of the Fsrv01 and it should show that "SQL05svc" acessed the server.
From the clients PC, Client01, we connect to the SQL instance "VirtualServer\SQLInstanceName" via SQL 2005's "Microsoft SQL Server Management Studio", open a Query Analyzer session and run the same query...
BULK INSERT dbo.CS_Test FROM '\\Fsrv01\Data\YanTest\CS_TEST.txt'
WITH (FIELDTERMINATOR = '","',MAXERRORS=0,TABLOCK,BATCHSIZE=300000,
ERRORFILE='\\Fsrv01\Data\YanTest\CS_TEST_Err.txt',
ROWTERMINATOR ='\"\n\"',
DATAFILETYPE = 'char',
CODEPAGE='1252')
GO
This is a two hop scenario.
And we get the following error...
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\Fsrv01\Data\YanTest\CS_TEST.txt" could not be opened. Operating system error code 5(Access is denied.).
Now check the security logs in Fsrv01 "SQL05svc" and you will see that "Anonymous" tried to get to Fsrv01.
It's a pretty vanilla setup.
Please let me know your findings.
Again, please remember we have delegation configured for the two SQL nodes, the Fsrv01, and the domain account SQL05svc.
We also have a SPN for the instance of SQL in the Domain for SQL05svc.
Here's a funny part, if you move ALL the resources from the SQL node you are logged into and try the same query, you get the same error. This is because you have now introduced your setup as a two hop scenario. The resources are no longer on the local server!
Thanks again for trying to help us.
I have seen one post where someone changed their process to use osql and said it fised their problem. I have tried it and we get the error "Login Failed for user: blahblahblah."
Does anyone have this working?
|||I have not had time today to replicate the problem. But what you describe is exactly what I am saw in our environment (without the cluster).The thing that worked for me was to move the share to the SQL server (which was easy for my environment). The only other thing that worked was go give "Everyone" read rights to the share, not an option in my situation. My share was set to only the AD User with "Full Access".
Even as a "Domain Admin" with full rights to everything, trying to run the query from a client pc to the SQL server, accessing a file on a different file server, gives me "Access Denied" error. Running 2005 (9.0.2153 32bit) on 2003 R2 on both servers.
|||
Moving the files local to the SQL server will work but in a clustered environment, if the resources fail over to the other node or if the admin decides to move the resources then your back to the 2 hop scenario.
Thanks for your reply though.
|||In fact, I have the same problem since I migrated from a windows 2k server to a windows 2003 server.
We have sql 2000 running on it : it doesn' function.
I tried on sql 2000 running on windows 2003 and it fails : I put all rights to everybody, I changed from local system count to administrator to launch sql server and no change : it only works if sql is running on windows 2000 ?
|||Issue is resolved in SP2 beta.|||
Issue WAS resolved in SP2 CTP (Beta).
BUT it is not resolved in SP2 final release (the same problem as before).
No comments:
Post a Comment