How To: SQL Server Bulk Insert with Constrained Delegation (Access is Denied)
Posted by Sudarshan Narasimhan on December 30, 2011
Well folks, I’ve not been able to blog for sometime as I was caught up with work. But fear not, these past 2 months have given me enough subject material for multiple blog posts. Let me start off with one regarding setting up SQL Server for bulk inserts from a network file share when constrained delegation is setup in Active Directory.
For SQL Server folks out there, I don’t expect you to know about Delegation let alone Constrained vs. Unconstrained.
Q&A
Q: What is Delegation?
A: A client connected to an instance of SQL Server can connect to another instance of SQL Server or another machine by forwarding the credentials of an authenticated Windows user.E.g. Domain\User1 on Client1 connects to –> SQL Server SQL1 and accesses a remote file on –> Server2, using his own credentials i.e. Domain\User1
It is the role of the SQL Server machine SQL1 to impersonate/delegate that user when communicating with Server2. For this to happen, the SQL Server has to be configured to allow delegation (in Active Directory).
Q: What is Constrained Delegation?
A: This feature allows administrators to specify exactly which services a server or a domain account can access when using an impersonated user’s security context. More on this later. If there is no such restriction configured, then it is called as unconstrained delegation, where every service for that user can perform impersonation aka delegation. It is a security best practice to perform constrained delegation as it reduces the surface area of any attacks.
Environment
Here is a brief layout of the environment that I am going to use in this post as a reference.
This is a basic 3 machine architecture that is very common. Here are the various parties involved:-
- Client Computer – Windows PC/Workstation/Application Server: machine name TRINITY1, Client account Domain\appadmin
- SQL Server – Windows Server 2003+ running SQL Server 2005/2008/2008 R2 standalone instance: machine name NEOSQL, SQLSvc account Domain\sqladmin
- File Share on another Windows Server (basically a shared folder): machine name MORPHEUS1, where user Domain\appadmin has Full Control on the shared folder.
Scenario/Requirement
In an ideal scenario, here is how I want things to work. When a client application runs the SQL BULK INSERT command logged in as Domain\appadmin account to SQL Server, I want the same appadmin account to access the remote file share and read the data.
1. If a user uses a SQL Server login to connect to SQL, then the SQL Service account credentials are used to access the remote file share.
2. If a user uses a Windows account, then his own account is used to access the file share and for this to work successfully, delegation has to be configured.
BULK INSERT appdb.dbo.bulktbl
FROM ‘\\morpheus1\share\data.txt’
If not configured correctly then you will get this error when running the above bulk insert command.
Msg 4861, Level 16, State 1, Line 3
Cannot bulk load because the file "\\morpheus1\share\data.txt " could not be opened. Operating system error code 5(Access is denied.).
Configuring Unconstrained Delegation
1. Configuring permissions on the shared folder on Morpheus1. As you can see the appadmin windows account has Full Control and the sqladmin account has Read/Write permission (if any sql login is going to be used, this is not mandatory).
2. I am assuming that the SQL Server is running under the service account Domain\sqladmin. Login to the Domain Controller with Domain Admin rights and open up Active Directory Users and Computers MMC snap-in.
a. Make sure that the following is NOT checked.
b. The SQL Service account needs SPN’s (Service Principal Names) to be created before it can be configured for delegation. You can use the SetSPN.exe tool that is available with Windows SDK or sysinternals toolkit to create the SPN’s. This tool is also available bundled along with Windows Server 2008.
We need to create 4 SPN’s for the account running SQL Server service as shown below. (2 with netbios names and 2 with FQDN). Note: The command below is for a standalone default instance of SQL Server running on default port 1433. Please modify as applicable to your environment.
Setspn -A MSSQLSvc/neosql thematrix\sqladmin
Setspn -A MSSQLSvc/neosql:1433 thematrix\sqladmin
Setspn -A MSSQLSvc/neosql.thematrix.sudarn.com thematrix\sqladmin
Setspn -A MSSQLSvc/neosql.thematrix.sudarn.com:1433 thematrix\sqladminOnce done you can query the SPN’s using setspn.exe and it should list you these 4 SPN’s.
c. Switch to the Delegation Tab and select the radio button by Trust this computer for delegation to any service (Kerberos only). We are setting up unconstrained delegation if this option is chosen. If you do not see the Delegation tab available, then there was some issue with creating the SPN’s in step (b) listed above.
d. Next find the actual machine account in Active Directory for the SQL Server machine NEOSQL and set the computer account to be trusted for delegation, as we did above.
e. Next, we need to check the file server where the file we want to import is located, i.e. MORPHEUS1. We need to verify that this machine has the normal 2 HOST SPN’s registered.
This is required to use Kerberos for authentication. Delegation will not work without this, and you will receive an error otherwise. By default each machine should have 2 HOST SPN’s created for it.
3. On the SQL Server machine NEOSQL, open up the Local Security Policy by going to secpol.msc –> Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment, and add the sql account Domain\sqladmin to the following policies:
- Act as part of the operating system
- Impersonate a client after authentication
So far the configuration given above is for unconstrained delegation. We need to make sure that this setup works before we can configure constrained delegation. Once you are done till there, reboot the SQL Server machine (NEOSQL) and the file share machine (Morpheus1), so that all the changes we made in Active Directory are reflected when the machine starts up.
Testing
Make a test connection from the client machine (TRINITY1) using sqlcmd or SSMS. Open up another connection from SSMS and run the following query to find out if the connection from the client machine is using Kerberos authentication.
select b.spid, b.hostname, b.program_name, a.auth_scheme
from sys.dm_exec_connections a
inner join sys.sysprocesses b
on a.session_id = b.spid
The connection from the client machine should return KERBEROS. This is required for delegation to work. If it did, then you are good to run the bulk insert statement and it should work.
Configuring Constrained Delegation
So far so good. Now comes the part that is the crux of this blog post. If you were successful in getting things to work, then read on….
1. Open up Active Directory Users and Computers MMC snap-in with a Domain Admin account as before. Open up the Domain\sqladmin account and switch to the Delegation Tab.
2. Choose the option “Trust this computer for delegation to specified services only”, select “Use any authentication protocol” and click on the “Add…” button.
In the dialog to select the sqladmin account (or your account which runs the SQL service).
This should list the 2 SPN’s we have previously created for this account. Select both of them and click OK.
Click on the “Add…” button again and enter the SQL Server machine name (NEOSQL) and choose the HOST service.
Click on the “Add…” button again and enter the File Share machine name (MORPHEUS1) and choose the HOST Service. We are actually interested only in the CIFS and Protected Storage service. Choosing HOST will automatically choose these 2 services.
After adding all of these, this is how your final configuration for the sqladmin account will look like.
We need the SQLSvc account (Domain\sqladmin)and file share server to have CIFS service enabled for delegation because we are accessing a remote file share and it is the role of the CIFS Service (Common Internet File Share) to perform this. If we do not have CIFS (which comes when we added the HOST Service), then your account (Domain\appadmin) from the client machine (TRINITY1) will reach the file share server (MORPHEUS1) as NT AUTHORITY\ANONYMOUS LOGON and this will not have access and fail with 0x5 (Access is Denied).
I tested this by enabling auditing on the shared folder and saw this. This is a easy test to perform to check if delegation is working or not
When the bulk insert fails with access denied we will see this in the security event log of the file server (MORPHEUS1),
Log Name: Security
Source: Microsoft-Windows-Security-Auditing
Date: 12/20/2011 11:17:33 PM
Event ID: 4624
Task Category: Logon
Level: Information
Keywords: Audit Success
User: N/A
Computer: MORPHEUS1.THEMATRIX.sudarn.com
Description:
An account was successfully logged on.Subject:
Security ID: NULL SID
Account Name: –
Account Domain: –
Logon ID: 0x0Logon Type: 3
New Logon:
Security ID: ANONYMOUS LOGON
Account Name: ANONYMOUS LOGON
Account Domain: NT AUTHORITY
That’s all folks! Keep in mind that this configuration requires a good bit of handling with Active Directory and/or policies. Please work with your Domain Administrator while making these changes. As always, stay tuned for more… Cheers and a happy new 2012 to y’all!
The SQL Dude! 
Gaurav said
I Think when we create SPN for both the machines there is no need to re start the SQL server machine i.e. NEOSQL and the file share machine (Morpheus2). We have to wait for 20-30 mins until the change reflects i.e. whatever the changes that we made in AD.
Sudarshan Narasimhan said
Gaurav – That is true, but for that you need to know the AD replication policy that has been implemented by your Domain Administrator (which in your case could be 20-30 mins). Since these could get complicated depending on the number of forests/OU’s, and for the sake of testing and to be 100% sure, we reboot since the policies are always applied on machine restart.
Gaurav said
Okay. Thx for clarification.
Jacqui Mosho said
You really make it seem so easy with your presentation but I find this topic to be actually something which I think I would never understand. It seems too complex and extremely broad for me. I am looking forward for your next post, I will try to get the hang of it!
Sudarshan Narasimhan said
Thanks Jacqui. I know this topic was a little outside the realms of SQL Server. But I come across this often when working with people. Imagine if someone wanted to have a monthly job to pull some data from a remote server into the database. They will have to go through this if BULK INSERT needs to be used. And if someone is particular about security, then these steps are required for safety reasons. Stay tuned for more posts this week…
Agueda Littell said
Very efficiently written story. It will be supportive to anyone who utilizes it, as well as yours truly :). Keep up the good work – looking forward to more posts.
Sudarshan Narasimhan said
Thank you, appreciate the feedback. More is coming this week 🙂
Gonzalo Lestronge said
I went over this site and I believe you have a lot of good info, saved to bookmarks (:.
Otto Criger said
Great post, you have pointed out some superb details , I as well believe this s a very superb website.
Alex Tetreault said
Hi,
Doesn’t this leave the machine open to delegation to any service while the service account is using constrained delegation?
Alex
Sudarshan Narasimhan said
Hi Alex,
Good point. Constrained delegation was introduced to specifically grant delegation privileges to just those services that needed it & were trustworthy. Now there are 2 possible scenarios here depending on what account the service runs under. If the service is using a computer account like [NT Authority\System], then the “Trust this computer for delegation” is required. Whereas if its running as a Domain account then “Trust this user for delegation” is required.
I haven’t yet had time to test turning off machine delegation in this bulk insert scenario. I will post back my results once I can do that.
Nicky said
What can you test if you’ve implemented the above steps and it still doesn’t work? I’m still getting the error in the event log.
Sudarshan Narasimhan said
Well you don’t provide any information at all apart from “its not working”. Are you using constrained or un-constrained delegation. It is mandatory to have CIFS enabled for the SQL Server machine account. Please refer http://blogs.msdn.com/b/psssql/archive/2012/09/07/bulk-insert-and-kerberos.aspx and see if you can follow this to implement your delegation.
Park said
Very thanks for your good post. I’ve spent more than a half day for this problem and you solved my pain. This is one of the nicest article on the web and detailed documentation. A few good man!!!
Sudarshan Narasimhan said
Thank you Park, really appreciate your comments.. Stay subscribed for more SQL stuff on this site. Cheers!
Julia said
Hi, I’m a SSIS developer. I’m working on a project on which the Server\Machine setup is as same as what you descripted here except our SQL Server service is running under local system. I got Access Denied error when executing the Bulk Insert on the Client machine. I was using Windows Authentication to for all SQL Server logins and my ID has Admin access to SQL Server and Read\Write access to the Shared file fold. Could you please explain how the delegation would setup differently for SQL Server service is running under local system? Thanks in advance.
Sudarshan Narasimhan said
Hi Julia,
I must’ve missed this comment earlier. Regarding your question on Delegation when SQL is running under a built-in account like Local System, please check if Delegation has been setup as described in my post.
When SQL Server is running under [NT AUTHORITY\SYSTEM], you need to enable delegation for the machine account, which in this case would be the SQL Server machine. The machine account e.g. DOMAIN\SQLMACHINE$ will be used to pass on your windows credentials when accessing the share. For LocalSystem SQL Server self-registers SPN during startup. Check the errorlog to see if this happening or SPN Registration is throwing errors.
Find the Computer Object for the SQL Server machine in AD and set the option “Trust this computer for delegation”. Also, the file share/file server where the file is located, you need to check if the 2 HOST SPN’s are present. E.g. If the machine name where the file is located is FSHARE1, then you should see 2 spn’s called HOST/FSHARE1 and HOST/FSHARE1.corp.domain.com. The HOST SPN’s are required because CIFS is covered under this.
Note: All of the above is assuming you are setting up unconstrained delegation (to any service).
SQL server BULK INSERT fails, with access denied error said
[…] Found this query raised earlier and this post, but both of them talks about the case were Windows authentication is used (which is not the case here). DBA here strictly does not allow permission for SQL service account for the shared folder citing security violations […]
eddy said
The scenario/requirement here is:
‘In an ideal scenario, here is how I want things to work. When a client application runs the SQL BULK INSERT command logged in as Domain\appadmin account to SQL Server, I want the same appadmin account to access the remote file share and read the data.
1. If a user uses a SQL Server login to connect to SQL, then the SQL Service account credentials are used to access the remote file share.
2. If a user uses a Windows account, then his own account is used to access the file share and for this to work successfully, delegation has to be configured.’
If the requirement is:
1. the application uses a specific Domain\SQL_ACCT_ID to access SQL2008/R2
2. the application uses a specific Domain\Windows_ACCT_ID through impersonation to read/write files in File Share through Bulk Insert command.
Can we use the same delegation setting to make it work?
If not, what are the alterations?
Thanks.
me7hos said
In the testing section, shouldn’t the screenshot show the connection coming from TRINITY1 instead of MORPHEUS1 since it was stated that the connection should be made from the client machine?
Sqltimes said
Absolutely great article. It helped me during my visit to our customer site. I’ll blog my experience of implementing it on my blog (sqltimes.wordpress.com)
Thanks again,
_Sqltimes
How To: SQL Server Bulk Insert with Constrained Delegation (Access is Denied) | 阿志的分享空間 said
[…] 參考https://thesqldude.com/2011/12/30/how-to-sql-server-bulk-insert-with-constrained-delegation-access-is… […]
Bulk Insert Through Network | XL-UAT said
[…] the problem you need to enable constrained delegation for the SQL Server service account. This blog How To: SQL Server Bulk Insert with Constrained Delegation (Access is Denied) has an example of how to do it, and I really do hope that the step on how to ‘enable […]
pranshulgupta said
Instead of using delegation with windows authentication, why shouldn’t we use sql server authentication as its less complex to configure so as easy to troubleshoot in case.
Sudarshan Narasimhan said
Less complex & less secure. SQL authentication is password string authenticated by SQL Server itself & as such is inherently insecure.
Charles Goodwin said
This article is the best out there for constrained delegation but it has one flaw.
Under the Configuring Constrained Delegation, step 1 asks you to open up the Delegation tab for the Domain\sqladmin account.
And then in step 2 asks you to select ‘Trust this computer . . .
rather than Trust this service . .
After that, I start to lose the trail.
Is there anyway you can clarify so that I can complete the constrained delegation?
Alan Costa de Souza said
I was having problem with bulk insert on this 3 machine architecture. Change the connection protocol from named pipe to tcp/ip resolved the issue. Altough i was using a sql authentication, not Windows authentication.
From the client machine, the application was using named pipe and the management studio was using a tcp/ip.
The client configuration for 32bit was the cause.
I changed the windows register HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\SuperSocketNetLib and add the key ProtocolOrder REG_MULTI_SZ with value tcp.
I’dint hace access do the Active Directory Users and Computers MMC snap-in.
How To Enable Bulk Insert In Sql Server 2005 | People Life said
[…] How To: SQL Server Bulk Insert with Constrained Delegation … – How To: SQL Server Bulk Insert with Constrained Delegation (Access is Denied) Posted by Sudarshan Narasimhan on December 30, 2011 […]
Steve-0 said
It’s July 2016, and doggone if this article didn’t just save our butts! THANK YOU SO MUCH for typing this up. We were having problem with bulk loads from a share, and this did the trick. AND, I learned something! WORKS in SQL 2016
Sudarshan Narasimhan said
Glad to hear it helped. SQL 2016 compatibility check passed 🙂
Cara said
I’m stuck, too. Not getting the same error that you’re saying I might get (in the event log, near the end of your writeup). Got as far as your TESTING section and, like comment #12, above, I’m not sure that your screen shot is accurate, is it? I think I’ve followed all steps for UNconstrained delegation, and still cannot open the file on the file server from the client running SSMS through the SQL server.
exxian said
I posted a comment, but am not sure that it “took.” Sorry if I duplicate.
I agree with comment #12 that the screen shot after your TESTING header seems off — like it’s showing the wrong server name? Or are we wrong?
I still can’t get this working. A simple ‘exec master..xp_cmdshell dir uncfoldername’ returns ‘the specified path is invalid,’ though I think I’ve followed all your steps. My error log doesn’t show anything like yours does.
Are reboots of the SQL and FILE server really necessary? I cannot do that — too many other users dependent on them.
exxian said
Wow. I just retested — this time, starting SSMS “as administrator” from my desktop, and it seems to work!
Why does running “as administrator” make a difference?
Sudarshan Narasimhan said
@exxian – Well, RunAs Administrator does make a difference especially if you are accessing remote resources (like a fileshare or UNC path). This goes back to Windows Vista days, because starting with Win 2008 UAC was implemented, so you need to launch programs specifically as Administrator to “elevate” to the administrator rights. For SSMS, I always runAs admin since I am anyways local administrator.
More info here – https://technet.microsoft.com/en-us/library/cc709691(v=ws.10).aspx#BKMK_WhatIs