How to enable a SQL Server 2008 login to access a directory on another server

Question

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!

Solution

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.

Comments

[marc esher]

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?

[squillman]

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.