Hey all, Mark from DS again. I have found that numerous cases have been opened where Microsoft customers are upgrading from SQL 2000 to SQL 2005. After the upgrade they were attempting to run a bulk insert statement either in the Enterprise Manager or the Management Studio application and getting an “Access is denied” error message. Here is what my customer was running:
DROP TABLE #In_File
CREATE TABLE #In_File(In_Data varchar(2000))
BULK INSERT #In_File FROM ‘\\contoso.com\DFSRoot\share\subfolder\log.txt’ WITH (FIELDTERMINATOR=’\0′, ROWTERMINATOR=”)
This is the error message they were getting (sound familiar anyone?):
Msg 4861, Level 16, State 1, Line 3
Cannot bulk load because the file “\\contoso.com\ DFSRoot\share\subfolder\log.txt ” could not be opened. Operating system error code 5(Access is denied.).
This customer had used the same command in SQL 2000 without issues. Only after the upgrade to SQL 2005 did it start failing. Per the SQL 2005 Online Books this behavior has changed in SQL 2005:
This customer had SQL running on a two node cluster (active\passive) and was attempting to import the file that happened to be on another 2 node file cluster (active\passive). So here is how to set this up properly after upgrading to SQL 2005. I will start off with a cluster configuration but I will make notes regarding a single server setup as well.
First I want to identify all the names that I will be using. With a cluster you have a SQL Virtual network resource that I will call SQL1; there are also cluster nodes called Node1 and Node2. The SQL service is set to use a domain user account called SQL-Service-Acct. The domain name is contoso.com.
1) The Virtual Name (SQL1) needs to be set for Enable for Kerberos Authentication. To do this open the Cluster Administrator MMC and locate the SQL1 network name resource, right click it and select Take Offline. You have to take the resource offline to be able to enable the Kerberos authentication. Once it is offline right click the resource again and select Properties. On the Parameters tab check the box for Enable Kerberos Authentication then click Ok. Right click the resource and select Bring Online. This will create a computer account in Active Directory so make sure the account that you use to perform this action above also has the user right to Add workstations to the domain. The computer account will be created in the “Computers” container. We will need to configure Kerberos delegation on the SQL service account so proceed to the next step as this depends on what the domain functional mode is set to in your environment.
Note: If you are not running SQL on a cluster you can skip step #1 of course.
2) Before going any further we need to talk about configuring Kerberos delegation on either the SQL service account or any computer accounts. If the domain is running in Windows 2000 (mixed or native mode) Domain Functional mode you will see differences on how to set the delegation vs. in a Windows 2003 Domain Functional mode. If the domain is in 2000 mode then on a computer account you will need to go to the properties of the computer account and simply check the box for the Trust computer for delegation on the General tab. If the domain is in 2003 mode then you will see a Delegation tab on the computer properties. In this case you need to select the radio button by Trust this computer for delegation to any service (Kerberos only).
3) Now we will talk about the SQL service account, SQL-Service-Acct. It needs SPN’s (Service Principal Name) registered on the account and also needs to be set for delegation. If the domain functional mode is set to Windows 2000 then find the account in Active Directory Users and Computer MMC, right click select Properties and go to the Account tab. In the Account options scroll down and check the box for Account is trusted for delegation. If you do not see the Delegation tab that means that you have not added the SPN on the account. This must be done first before you will see the delegation tab. Make sure the box for Account is sensitive and cannot be delegated is NOT checked which is found on the Account tab under account options. The GUI will allow you to check both. If your domain is running in 2003 mode then you will have to go to the Delegation tab and make the same change as a computer account to enable the delegation but you first have to register the SPN’s on the SQL service account before the delegation tab will be available! You will need to go ahead and go to the next step then come back to enable the delegation.
4) To register the SPN’s and the SPN’s to register on the SQL service account you will need to use the utility called setspn.exe. It can be downloaded here or by installing the Resource Kit tools. The SPN’s needed to be registered are as follows:
Setspn –A MSSQLSvc/NetbiosNameOfSQLVirtualName SQLServiceAccountName
Setspn –A MSSQLSvc/NetbiosNameOfSQLVirtualName:1433 SQLServiceAccountName
Setspn –A MSSQLSvc/FullDNSNameofSQLVirtualName SQLServiceAccountName
Setspn –A MSSQLSvc/ FullDNSNameofSQLVirtualName:1433 SQLServiceAccountName
NOTE: The SPN’s that have the trailing port number are absolutely required. The SPN’s without the port number are optional. Also please note that if the instance is running on a different port number than the default 1433 then simply change the port number in the syntax.
So in my case my SQL Virtual network name is SQL1 and the service account is SQL-Service-Acct so here is what I would run:
Setspn –A MSSQLSvc/SQL1. contoso.com SQL-Service-Acct
Setspn –A MSSQLSvc/SQL1. contoso.com:1433 SQL-Service-Acct
Setspn –A MSSQLSvc/SQL1 SQL-Service-Acct
Setspn –A MSSQLSvc/SQL1:1433 SQL-Service-Acct
To verify that you have entered them correctly you can run the following command to list out what SPN’s are registered on the account:
Setspn –L SQL-Service-Acct
If you are not using the default port that SQL uses of 1433 then you need to change the syntax to reflect the port that you are using when adding the SPN’s.
Note: If you are not running a cluster then you need to add the SPN’s using the server name that SQL is running on instead of the SQL Virtual name. Example:
Setspn –A MSSQLSvc/SQLServer.contoso.com CONTOSO\SQL-Service-Acct
5) Okay, moving right along now we should have the SQL Virtual name enabled for Kerberos and trusted for delegation, the SPN’s registered on the SQL service account and it is set for delegation. The next thing we need to do is make sure the SQL service account has a couple of userrights in the local security policy. Open gpedit.msc from the Start – Run and drill down to the following:
Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment.
Add the SQL-Service-Acct to the following policies:
- Act as part of the operating system
- Impersonate a client after authentication
No need to run gpupdate as we are going to reboot here in a minute. Remember to modify the user rights assignments on both of the cluster nodes!
6) Next find the actual machine accounts in Active Directory for the cluster nodes, node1 and node2. Set the computer accounts to be trusted for delegation as we did in step #2 for the SQL Virtual name. Once that is done reboot one of the nodes then once it is back up reboot the other node.
Note: When not running a cluster simply set the delegation on the machine account that is running the SQL service and reboot the server.
7) We are almost done now. We need to check the file server where the file is located that we want to import. If it is on another cluster make sure that cluster virtual resource name is enabled for Kerberos authentication as we did in step #1. If we are on a non-clustered file server then make sure we have the normal 2 HOST SPN’s registered. You can run the following command to check this:
Setspn –L fileserver.contoso.com
You should receive the two default HOST SPN’s back such as these:
This is so we can continue to use Kerberos for authentication. Once this is setup you have to be able use Kerberos for authentication end to end or you will receive an error. If the file server is set up on a cluster as my customer then make sure the virtual name that is being accessed is set to use Kerberos authentication as we did in step #1. No need to set the virtual name here for the trusted for delegation as it is not needed.
Ok, we should have things setup now. Let’s review what we have done:
- We enabled the SQL Virtual Network resource for Kerberos authentication.
We should have the following set to be trusted for delegation: SQL Virtual Network resource name, both of the cluster nodes and the SQL Service Account.
- We set the proper user rights in the local security policy.
- We have rebooted both of the cluster nodes.
Now we can test the Kerberos authentication. Open either the Enterprise Manager or the SQL Management Studio application and run the following command:
select auth_scheme from sys.dm_exec_connections where session_id=@@spid
It should return Kerberos, if it does then we are successfully authenticating to SQL with Kerberos. Try your insert statement now and it should be able to import if you have the appropriate rights in SQL, the syntax of your command is correct and the format of the file you are importing are correct. If you should need any assistance with this then please contact the SQL Team Blogs which can be found here.
956378 FIX: You may be unable to start the SQL Server agent if you configure a SQL Server 2005 failover cluster to use Kerberos constrained delegation for a domain user account
909801 How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005
– Mark ‘DS? No, DB!’ Ramey