Not sure if anyone else out there is having this problem but we recently migrated some of our databases to SQL 2005. We have some processes that use BULK INSERT or BCP to import the data. On the new server I required that the files we import are stored and held on the FileStore not the database server.
If the user uses SQL authentication this work fine. However, if the user is using Integrated Authentication it fails saying that it could not find the file or access is denied.
The SQL Server services are using a domain account and they have access to the share where the file resides, otherwise SQL authentication would not work.
After some web searches I found out that in SQL 2000 would automatically impersonate as the SQL Service account and they figured that was a security hole (which it is) so now in 2005 you can't do that without allowing kerberos delegation which my Active Directory guys forbid because it is "evil".
So the only other thing I can think of is to use certificates but how do I go about that? Or is there anything else I can do besides use SQL logins.
Just a side note since I'm talking about BCP what happened in version 9.0? We had to grab version 8.0 in order for the bloody thing to work.
Thanks
Rich
Ok, worked with HP and figured out how to fix it. In case any of you are interested below is the documentation I created for myself:
Process Details and Instructions
1. The issue here is using Integrated Authentication to execute a BULK INSERT command (or BCP) from a client workstation, which will retrieve a file that is located in a different location other than the target SQL Server. This will result in a "double hop" authentication that will not work unless you have certificates or delegation.
A couple of things to make sure that are done before reaching this stage.
ü The SQL Service is running under an Active Directory account
ü The SQL Service account has sufficient rights on the SQL Server (Local Admin)
ü The SQL Service account has rights to the remote file via network share
ü The user executing the command has rights to the remote file via network share.
SQL 2000 - This should not be an issue with SQL 2000 because it will automatically delegate on your behalf that is a considered a security hole.
SQL 2005 - They fixed up the security hole by not allowing delegation unless allowed in Active Directory.
You can reproduce the error by executing something like this against the SQL 2005 server:
CREATE TABLE #In_File(In_Data varchar(2000))
BULK INSERT #In_File FROM '\\share\SQL\DEV\20061106.csv' WITH (FIELDTERMINATOR='\0', ROWTERMINATOR='')
The results should look something like this:
Msg 4861, Level 16, State 1, Line 6
Cannot bulk load because the file "\\share\SQL\DEV\20061106.csv" could not be opened. Operating system error code 5(Access is denied.).
If you were to execute the statement on the SQL Server itself then it will work. If you were to move the file locally to the SQL Server this will work. Both will work because you are no longer doing a double hop to execute and get the file.
2. Now that we have the error we are going to check the Service Principal Name in Active Directory to ensure that the service can be delegated to do this task. To do this we need the utility setspn.exe which is available in the Windows Resource Kit or can be downloaded here (http://www.microsoft.com/downloads/details.aspx?familyid=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en <blocked::http://www.microsoft.com/downloads/details.aspx?familyid=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en> )
After installation on your workstation run the following from command prompt:
C:\Program Files\Support Tools> setspn -L serviceaccount
(eg C:\Program Files\Support Tools> setspn -L SQLAccount)
One result you will get is this:
Registered ServicePrincipalNames for CN=SQLAccount,OU=Users,OU=Vancouver,DC=Domain,DC=corp:
The result you should get is this:
Registered ServicePrincipalNames for CN=SQLAccount,OU=Users,OU=Vancouver,DC=domain,DC=corp:
MSSQLSvc/sqlserver.domain.corp:1433
Notice the extra line MSSQLSvc/sqlserver.domain.com:1433? This "binds" the account to the SQL Service to allow the Service to delegate as the account.
3. Now that we verified that there is no SPN for the MSSQLSvc let's create one. All we do is use SETSPN to add the service by executing the following:
Setspn -A MSSQLSvc/sqlserver.domain.corp:1433 Domain\SQLAccount
4. Now that we created the SPN for the SQL Service we must allow it to delegate. Using Active Directory Users and Computers go to the properties of the account the SQL Service is running under.
There should be a DELEGATION tab there, if there isn't then the SPN was not set correctly (or not done at all) and you need to repeat step 2 and 3.
In the Delegation tab you will want to select "Trust this user for delegation to any service (Kerberos only)
5. Last step is to restart the SQL Service that can be done in the services section on the server. This will re-authenticate the user to the domain and it will get the new delegation changes.
6. Retry the BULK INSERT command from step 1 and it should work now.
Additional Information
Here are the resources used to solve the problem:
Windows 2003 Resource Kit (includes SPN)
http://www.microsoft.com/downloads/details.aspx?familyid=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en <blocked::http://www.microsoft.com/downloads/details.aspx?familyid=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en>
MSDN Article on using SETSPN and the Delegation Issue
http://msdn.microsoft.com/msdnmag/issues/05/09/SecurityBriefs/default.aspx <blocked::http://msdn.microsoft.com/msdnmag/issues/05/09/SecurityBriefs/default.aspx>
TechNet Article on Allowing an User to be Delegated
http://technet2.microsoft.com/WindowsServer/en/library/bef202b0-c8e9-4999-9af7-f56b991a4fd41033.mspx?mfr=true <blocked::http://technet2.microsoft.com/WindowsServer/en/library/bef202b0-c8e9-4999-9af7-f56b991a4fd41033.mspx?mfr=true>
No comments:
Post a Comment