none
BULK INSERT not working despite Kerberos and Delegation

    Pregunta

  • Hi all,

    I've been banging my head around this for quiet a while now and still can't get this working. Domain is in 2003 functional level, SQL server R2 is running on Windows Server 2008 R2 and using a domain account (BA_AG\sql1service) and a named instance (SQL1).

    When running:
    BULK INSERT "dbo"."T_WM_GATTUNG_SECURITY_IN_PRE" FROM "\\SV2451\CADINS_IMPORT\WM_GAT.txt" WITH (DATAFILETYPE = 'widechar', CODEPAGE=1200, BATCHSIZE = 750000, FIELDTERMINATOR = '|', MAXERRORS = 20, ROWTERMINATOR = '\n', TABLOCK)
    from a remote machine (XP SP3) I receive a SYSTEM ERROR 5 (Access Denied) due to the double hop.

    SQL Domain Account (BA_AG\sql1service) as well as SQL Server account (SV2534) are trusted for delegation (all services - Kerberos only), select auth_scheme from sys.dm_exec_connections where session_id=@@spid returns KERBEROS. SETSPN -L BA_AG\sql1service shows:
        MSSQLSvc/SV2534.baag:1433
        MSSQLSvc/sv2534.MUC.BAAG:1433
        MSSQLSvc/sv2534:1433

    Permissions on the FileShare are everybody full and authenticated full (just to make sure).

    So, Kerberos and delegation seem to be set up fine but it's still not working.

    Can anyone bring light into this?????

    Best Regards,
    Matthias.

    lunes, 14 de febrero de 2011 18:13

Respuestas

  • Hi Chunsong,

    the user has Bulk Admin and DBO rights on the database. Both the SQL Service account and the Domain Account of the user have full access rights on the fileshare and file.

    In the Security Eventlog on the Fileserver I see the SQL Server trying to authenticate as ANONYMOUS which seems to indicate that impersonation somehow does not work if using Windows Authentication on the SQL session despite the session being in Kerberos mode.

    Meanwhile I double checked all accounts (computer objects, service accounts etc.) involved are indeed trusted for delegation. I followed http://blogs.technet.com/b/askds/archive/2009/04/30/sql-bulk-insert-access-is-denied.aspx to the letter as it seems to be a pretty thorough howto regarding the setup of kerberos delegation for SQL, but still nothing.

    Best Regards,
    Matthias.

    martes, 15 de febrero de 2011 12:07

Todas las respuestas

  • Hello Matthias,

    Probably you are using a built-in system account to start SQL Server service. Please change it to a Domain Windows Account with local administrative privileges on the SQL Server server.

    Configure TCP/IP for remote connections.

    See the following link also:

    http://msdn.microsoft.com/en-us/library/ms188365.aspx  Security Account Delegation (Impersonation)

    Hope this helps.   
     
    Regards,

    Alberto Morillo
    SQLCoffee.com

    lunes, 14 de febrero de 2011 19:32
  • Hi Alberto,

    as stated in my first post, SQL server is running with a domain account and TCP/IP is the only protocol enabled on the server. Kerberos seems to be working as I can see by issuing the following SQL statement run from a remote management studio (logged in with a different domain account and using Windows Authentication:

    select auth_scheme from sys.dm_exec_connections where session_id=@@spid ---> returns KERBEROS

    But still I am getting a system error 5 when trying to do a BULK INSERT from a remote fileshare.

    I really went thru all the knowledge base articles and documentation there is about setting up SQL account delegation... any help is highly appreciated.

    Kind Regards,

    Matthias.

    lunes, 14 de febrero de 2011 21:01
  • Hi,

    It looks like a permission issue, could you please provide the following information:

    1. The domain account BA_AG\sql1service is the service account of SQL Server, right?
    2. SQL Server account (SV2534) is the user that you use to connect to SQL Sever, is it SQL Server login or domain account (BA_AG\SV2534)?
    3. Which tool are you using to run BULK INSERT statement, Management Studio, sqlcmd, or osql?

    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    martes, 15 de febrero de 2011 10:15
  • Hi Chunsong,

    1.) correct
    2.) no. SV2534 is the machine that SQL is running on, Fileserver is SV2451
    3.) SQL Management Studio from my Workstation (WS3730)

    Additionally I installed kerbtray on my workstation and do see a kerberos ticket named "MSSQLSvc/sv2534.muc.baag:1433@BAAG" with delegation enabled - so I'm assuming that Kerberos works indeed. Question is though, why I can't access the fileshare via bulk insert.

    Best Regards,
    Matthias.

    martes, 15 de febrero de 2011 11:10
  • Hi,

    Thanks for your update.

    What kind of account you use to connect to SQL Server, domain account (using Windows Authentication)or SQL login (using SQL Server Authentication)?


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    martes, 15 de febrero de 2011 11:37
  • I'm using a domain account, hence the problem. If I switch to SQL authentication then the SQL Service account will be used to access the file which works.

    For security reasons using SQL authentication is no solution.

    martes, 15 de febrero de 2011 11:47
  • Hi,

    Thanks for your update.

    Here are differences:

    1. If you are using Windows Authentication, the Database Engine will not use service account to access the file but impersonate the Windows user you connect to SQL Server. So that, you need to grant this login user read permission on the shared file but not service account.
    2. If you are using SQL Server Authentication, the Database Engine will use service account to access the file. So that, you need to grant SQL Server service account read permission on the shared file.

    In both cases, the user connecting to SQL Server requires INSERT and ADMINISTER BULK OPERATIONS permissions while executing in SQL Server Management Studio. Please refer to Permissions section at BULK INSERT on Books Online.

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    martes, 15 de febrero de 2011 11:57
  • what's your fileshare server? native windows or others?have you allow ed delegation to that box?

    ha


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.

    Sevengiants.com
    martes, 15 de febrero de 2011 12:04
  • Hi Chunsong,

    the user has Bulk Admin and DBO rights on the database. Both the SQL Service account and the Domain Account of the user have full access rights on the fileshare and file.

    In the Security Eventlog on the Fileserver I see the SQL Server trying to authenticate as ANONYMOUS which seems to indicate that impersonation somehow does not work if using Windows Authentication on the SQL session despite the session being in Kerberos mode.

    Meanwhile I double checked all accounts (computer objects, service accounts etc.) involved are indeed trusted for delegation. I followed http://blogs.technet.com/b/askds/archive/2009/04/30/sql-bulk-insert-access-is-denied.aspx to the letter as it seems to be a pretty thorough howto regarding the setup of kerberos delegation for SQL, but still nothing.

    Best Regards,
    Matthias.

    martes, 15 de febrero de 2011 12:07
  • File server is a Windows Server 2003 x64 but will be a NetApp Filer in Production. Delegation is enabled for the computer object of the file server as well. I also see a kerberos ticket named host/sv2451.baag@BAAG (the fileserver) with delegation enabled.
    martes, 15 de febrero de 2011 12:25
  • On the SQL server, have you done the local policy (group policy) part in the article you followed?

    Also on SQLServer, check whether you got any security related errors?


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.

    Sevengiants.com
    martes, 15 de febrero de 2011 12:29
  • Yes this is set via GPO for all our SQL servers/clusters.
    martes, 15 de febrero de 2011 12:30
  • GOOD NEWS!

    Everything is working now as expected. Seems there were old Kerberos tickets cached somewhere as nothing else has been changed for the last 12 hours...

    Thanks for your help to everyone!

    martes, 15 de febrero de 2011 13:35