Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
That's what I feared. Thanks. –  Andrew J. Brehm Mar 13 '12 at 8:00
add comment

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

share|improve this answer
    
I don't see what this would achieve. –  Andrew J. Brehm Mar 13 '12 at 7:59
add comment

Run it as a job from the sql server agent

share|improve this answer
    
That solves a different problem. As I described, I have a tool running on server A. –  Andrew J. Brehm Mar 12 '12 at 19:58
    
The tool can trigger the job running under a different user though. Need to change any variables? then base the job on tables your tool and the agent account both have access to. –  JamesRyan Mar 12 '12 at 23:21
    
I'm not sure I can make the tool do that. I'd have to sync with the developers. –  Andrew J. Brehm Mar 13 '12 at 7:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.