Home / server / Questions / 294705
Accepted
marc esher
Asked: 2011-07-28 09:45:24 +0800 CST

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

  • 772

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!

sql-server sql-server-2008 permissions

2 Answers

  1. Best Answer

    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.

    • 3

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

    • 0