locked
Security Account Delegation and Bulk Insert RRS feed

  • Question

  • This question has been asked several times, but something is remained hidden, so hopefully as I explain our setup it will jump out at somebody what it is. I apologize for the length of this, but I want to try to expose every detail that we have examined.

    SQL Server is running under a cluster with virtual name SQLCLUSTER. The cluster only has a single member currently, sqlserverPC. The SQL Server instance is named SQLCLUSTER\SQLDATABASE (SQL Server 2012). All SQL server processes are running under the user name sqlServiceAccount. This instance is using port 51878 for communication.

    The files are on the file server FileServer with share name fileShare$.

    The account windowsServiceAccount has full access rights to \\FileServer\fileShare$. It also has been verified that it does NOT have the property "Account is sensitive and cannot be delegated".

    The login windowsServiceAccount has the bulkadmin role on SQLCLUSTER\SQLDATABASE.

    With these settings, what we desire is than any bulk import that is run on SQLCLUSTER to use the credentials of the connecting Windows User (windowsServiceAccount) to determine what files it can or cannot access. This is failing, though. The problem seems to lie with the way that we are configuring security account delegation. We are getting an Access is Denied error when trying to BULK INSERT the various files. However, if we switch to SQL Server authentication, the bulk insert works fine.

    The following links give good information about our problem and what it takes to solve it:
    http://blogs.technet.com/b/askds/archive/2009/04/30/sql-bulk-insert-access-is-denied.aspx
    http://msdn.microsoft.com/en-us/library/ms175915.aspx
    http://www.kendalvandyke.com/2008/11/delegation-what-it-is-and-how-to-set-it.html

    I am going to mention several steps that were mentioned in those articles that have been applied to our specific situation.

    The virtual name SQLCLUSTER has been set with the property "Enable for Kerberos Authentication". This is done with the Cluster Administrator MMC.

    In active directory, the virtual name SQLCLUSTER has been set for delegation, specifically the option "Trust this computer for delegation to any service (Kerberos only)."

    Service Principal Names have been registered on the SQL service account name (sqlServiceAccount). This was done with the following commands:

    Setspn –A MSSQLSvc/SQLCLUSTER.domain.com sqlServiceAccount
    Setspn –A MSSQLSvc/SQLCLUSTER.domain.com:51878 sqlServiceAccount
    Setspn –A MSSQLSvc/sqlserverPC.domain.com sqlServiceAccount
    Setspn –A MSSQLSvc/sqlserverPC.domain.com:51878 sqlServiceAccount

    In retrospect, I only think the first two are necessary, and they did produce a positive result explained below. The other two should not hurt anything.

    In active directory, the account sqlServiceAccount has been set for delegation, but with the setting "Trust this user for delegation to specified services only." In this box, the four SPNs that were created above are listed. We also have the "Use Kerberos only" option selected. We have tried the "Trust this user for delegation to any service (Kerberos only)" option as well.

    The following policies have been added to the account sqlServiceAccount: "Act as part of the operating system", "Impersonate a client after authentication".

    In active directory, the computer name sqlserverPC has been set for delegation, specifically the option "Trust this computer for delegation to any service (Kerberos only)."

    After completing these steps, we are seeing one positive result. The following query when run on SQLCLUSTER when logged in as windowsServiceAccount produces the value of "Kerberos" on the field auth_scheme, which is correct. It was producing NTLM before these changes. We are still getting the "Access is Denied" error.

    select session_id,auth_scheme,net_transport,client_net_address,client_tcp_port,
    c.connection_id
    from sys.dm_exec_connections  c
    where @@SPID = session_id

    Does anyone have any thoughts on what I may still be missing?

    Tuesday, May 13, 2014 10:31 PM

Answers

  • Hi ScottBurl,

    If you are running a SQL Cluster, and want to configure constrained delegation, usually, we recommend that you use SQL Authentication instead of Windows Authentication for avoiding the access denied error. If you use the SQL Service Account to do bulk insert in SQL Server with NTLM authentication, it may fail because of double hop. You need to set up the delegation for your SQL Service Account in Active Directory and use Kerberos authentication so that  this account which is running bulk insert should have access to the shared directory.

    In your cluster environment, if you have a SQL Virtual network resource, cluster nodes, and the SQL service is set to use a domain user account, 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.

    For more information, see:

    http://blogs.technet.com/b/askds/archive/2009/04/30/sql-bulk-insert-access-is-denied.aspx

    http://blogs.msdn.com/b/psssql/archive/2012/09/07/bulk-insert-and-kerberos.aspx

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Thursday, May 22, 2014 1:43 AM
    Friday, May 16, 2014 3:11 AM

All replies

  • I see that this is in the wrong forum. I don't remember selecting that so I apologize that it ended up here. Is there an easy way to move this?
    Tuesday, May 13, 2014 10:34 PM
  • Hi,

    I will help you do the move to the Sql Server forums.

    Best Regards,
    Amy Peng


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, May 15, 2014 6:32 AM
  • Hi ScottBurl,

    If you are running a SQL Cluster, and want to configure constrained delegation, usually, we recommend that you use SQL Authentication instead of Windows Authentication for avoiding the access denied error. If you use the SQL Service Account to do bulk insert in SQL Server with NTLM authentication, it may fail because of double hop. You need to set up the delegation for your SQL Service Account in Active Directory and use Kerberos authentication so that  this account which is running bulk insert should have access to the shared directory.

    In your cluster environment, if you have a SQL Virtual network resource, cluster nodes, and the SQL service is set to use a domain user account, 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.

    For more information, see:

    http://blogs.technet.com/b/askds/archive/2009/04/30/sql-bulk-insert-access-is-denied.aspx

    http://blogs.msdn.com/b/psssql/archive/2012/09/07/bulk-insert-and-kerberos.aspx

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Thursday, May 22, 2014 1:43 AM
    Friday, May 16, 2014 3:11 AM