The SQL Dude!

Pages that teach you the SQL way of life!

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.

image

This is a basic 3 machine architecture that is very common. Here are the various parties involved:-

  1. Client Computer – Windows PC/Workstation/Application Server: machine name TRINITY1, Client account Domain\appadmin
  2. SQL Server – Windows Server 2003+ running SQL Server 2005/2008/2008 R2 standalone instance: machine name NEOSQL, SQLSvc account Domain\sqladmin
  3. 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).

image

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.

image

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\sqladmin

Once done you can query the SPN’s using setspn.exe and it should list you these 4 SPN’s.

image

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.

image

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.

image

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.

image

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

image

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.

image

In the dialog to select the sqladmin account (or your account which runs the SQL service).

image

 

This should list the 2 SPN’s we have previously created for this account. Select both of them and click OK.

image

Click on the “Add…” button again and enter the SQL Server machine name (NEOSQL) and choose the HOST service.

image

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.

 

image

 

After adding all of these, this is how your final configuration for the sqladmin account will look like.

image

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 0×5 (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:        0×0

Logon 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!

 

About these ads

20 Responses to “How To: SQL Server Bulk Insert with Constrained Delegation (Access is Denied)”

  1. 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.

  2. 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…

  3. 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.

  4. I went over this site and I believe you have a lot of good info, saved to bookmarks (:.

  5. Great post, you have pointed out some superb details , I as well believe this s a very superb website.

  6. 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.

  7. 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.

  8. 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!

  9. 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.

    • 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).

  10. [...] 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 [...]

  11. 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.

  12. 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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 112 other followers

%d bloggers like this: