Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 0 down vote favorite

I need a sql server login (not a windows login) to be able to perform bulk inserts using a file on another server.

using bulk insert \\path\to\server\c$\path\to\dir does not work because of permissions.

My question is: how can I configure the sql server user to access that file on another server?

I've tried mapping a drive, but that mapping isn't "visible" to the sql server login (it's only visible if I log in as a windows user)

Thanks for helping!

share|improve this question

2 Answers

up vote 3 down vote accepted

Grant access to the share to the Windows ID that the SQL Server Service is running as. When you log into SQL Server using a SQL Login, SQL will use the context of the account it is running as to access network resources.

That is assuming you're running the bulk insert interactively or via an app using a SQL Login in the connection string. If you're doing the bulk insert in a SQL Agent job then you'll either need to grant access to the share to the account under which the SQL Agent service is running or set up a credential and a proxy and set the job step to run under that proxy. The credential would need to be for an account that has access to the share.

share|improve this answer
Thanks squillman. Unfortunately, I do not believe I can do that. These machines are in the same workgroup, but there's no "domain" to speak of so I'm not sure how to give that service account permissions to the directory on the other machine. Is that possible? – marc esher Jul 27 '11 at 18:22
You should be able to do it by creating an account on the machine hosting the share using the same username and password as the account SQL Server is running as. That will of course mean changing the SQL Server service account if you're using LocalSystem. – squillman Jul 27 '11 at 18:26
up vote 0 down vote

How big are the files? Use the windows scheduler to run a periodic xcopy from the remote machine to the machine that hosts sql server, then import from local folder.

(Also, I wouldn't recommend granting any additional permissions to the SQL Server service.)

share|improve this answer

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.