Home / server / Questions / 368933
Accepted
Andrew J. Brehm
Asked: 2012-03-13 11:30:39 +0800 CST

SQL Server bulk import from fileshare. How can I get SQL Server to see the share?

  • 772

I have two servers A and B. B is a database server, A is (here) a client to the database server. A tool running on server A connects to SQL Server on server B and ultimately triggers a bulk import from a share on server A (\\servera\fileshare).

The tool on server A runs as localsystem. domain\servera$ has the required access to the SQL Server on server B.

The problem is SQL Server on server B cannot access \\servera\fileshare despite the SQL service account having all permissions to do so (on server A). I found out that this is because SQL Server tries to delegate the connection to the account that the utility uses to connect to SQL Server and that fails because the SQL Server service account does not have the right to delegate (and that setting cannot be changed by me).

I also found that if I connect using a SQL account (i.e. not an Active Directory account) using SQL authentication (and not Windows authentication), SQL Server connects to the fileshare using the SQL Server service account and everything works. The tool luckily supports using a SQL account. Unfortunately the password is given in plain text as a command line parameter and then appears in the process list among all other parameters. This is not good.

Is there a way to make SQL Server use its own service account when connecting to the fileshare without using a SQL account to connect to SQL Server?

This is SQL Server 2008 on Windows Server 2003.

sql-server active-directory windows-authentication

3 Answers

  1. Best Answer

    Unfortunately, the behaviour you witnessed is by design:

    http://msdn.microsoft.com/en-us/library/ms188365.aspx [Security Account Delegation (Impersonation)] http://msdn.microsoft.com/en-us/library/ms175915.aspx [Security Considerations] http://msdn.microsoft.com/en-us/library/ms161965.aspx

    From the second link:

    The way in which SQL Server 2005 and later versions control access to files addresses a security issue that was present in Microsoft SQL Server 2000 and earlier versions. Formerly, after a user was authenticated, access to external files was based on the security profile of the SQL Server process. When the SQL Server process had read access to a file, for a user that did not have access to the file but was a member of the bulkadmin fixed server role, the user could import the file by using BULK INSERT and access the contents of the file.

    Your only options are either using a SQL Server login or giving SQL Server the needed rights to perform delegation.

    A possible solution could be to run a job to copy the file from ServerA to ServerB (I assume your service account has write access to both of them), and then have SQL Server on ServerB perform the import from its local disk.

    • 2

  2. You can map your windows logins to a credential that has access:

    create credential [...]  with identity='domain\user', secret='domainpassword';
alter login [...] with credential=[...];

    Be careful though so you don't end up duplicating the AD in your SQL credentials store. And you need to devise a way of keeping the SQL Server credentials database in sync with AD (password changes etc).

    • 1

  3. Run it as a job from the sql server agent

    • 0