woensdag 22 december 2010

BULK Admin (Operating system error code 5 (Access is denied))

Today I was supporting a 2 node SQL 2008 Cluster with 3 instances:

SQL01\Test, SQL01\Acceptance and SQL01\Production

The customer wanted to use BULKINSERTS and was granted Server Role BULKADMIN on all instances. The below SQL Statement was executed on the SQL01\Test Instance.

image

The result was as expected, data was inserted in tblSomeTable.

Then they tried to execute the same statement on the SQL01\Acceptance and Production instance what resulted in the error:
msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file “\\SomeShare\SomeFolder\SomeFile.txt” could not be opened. Operating system error code 5(Access is denied).

The first thing I checked has the user all needed permissions on the share, it was working on Test using the same SQL Active Directory based principal and share so that could not be the issue.

The second check if principal has the bulkadmin server role granted on all instances:

image

I hope you trust me when I say that this is correctly granted on all instances.

So I needed to go a bit deeper. To understand what was needed first I need to show you a little image of how the environment is working:

image

On the client they started SSMS 2008, and executed the given BULK INSERT command. The image explains that there are 2 hops or so cold “Double Hop” needed to get to the files. This means that the SQL01 instances need to be able to forward the user security token to the FILE1 server file share. This requires KERBEROS authentication and delegation!

So the first thing I checked was what protocol is used for each connection.

image

This resulted in:
- SQL01\Test TCP KERBEROS
- SQL01\Acceptance TCP NTLM
- SQL01\Production TCP NTLM

So there is the reason why it is not working, NLM does not support the authentication pass through. But what is causing this? You can not configure this as a setting direct in SQL Server. Lucky I have some experience with Kerberos configuring for SharePoint environments.

The next thing I did was looking for the SPN (Server Principal Names) that are created for the SQL Server Service accounts for each Instance. SPN’s needed to be configured for each Service Account to make sure Kerberos delegation is supported.

You need the Windows resource kit to be able to check SPN’s with the setspn tool.

I executed setspn –L <service account> for each instance. The result was displaying the following:

SQL01\Test
- MSSQLSvc/SQL01.corp.local:Test
- MSSQLSvc/SQL01.corp.local:2612

For the other to instances there were no SPN’s configured!!! I created the below SPN’s with the setspn tool:
setspn –A MSSQLSvc/SQL01.corp.local:<port> <service account>
setspn –A MSSQLSvc/SQL01.corp.local:<port> <instancename> <service account>

SQL01\Acceptance
- MSSQLSvc/SQL01.corp.local:Acceptance
- MSSQLSvc/SQL01.corp.local:2611

SQL01\Production
- MSSQLSvc/SQL01.corp.local:Production
- MSSQLSvc/SQL01.corp.local:2610

When I now execute the query

image

All instance connections are configured for using TCP KERBEROS

Testing the BULK INSERT again and all work fine on each instance!

When in your situation it is still not working don’t forget to check or the SQL Services domain accounts <service account> are configured for delegation. In AD Users & Groups look for the Service Accounts, select the Delegation Tab is should be configured as “Trust this user for delegation to any service (Kerberos only)”

Geen opmerkingen: