SQL Server Performance Forum – Threads Archive
Remote bulk insert and backups??
We attempted to do a bulk insert from a remote machine yesterday after testing in house and even though the remote server user has rights the bulk insert failed with access denied. After some research we determined the issue is that the MSSQL Service needs to be the user with rights on the remote server. My question is what harm if any can we cause to the server by changing the service user from Local System to an admin with rights to post the bulk insert? Also will changing this user enable me to backup to UNC paths which fails now. Our test environment is in the office and that SQL server allows the remote bulk insert and the backups to remote servers via UNC paths. That SQL service is also set to LOCAL System but it is part of a AD Domain and is actually a DC as well as a SQL server (I assume that is far from a good thing but developers do what they want when they own the company). Thanks DougHi Doug, LocalSystem implies that SQLServer can’t use the network. Using a domain account you can grant rights to the UNC paths allow SQLServer to use them. If you use Enterprise Manager to make the change then all the rights are set up for you, otherwise there are a whole suite of rights that you need to grant the domain account. (so best to use EM) Don’t make the user a domain admin though, unless you’re ok with someone intentionally or accidentally doing some damage to your domain. yep running SQL on a DC is not a very clever idea for security, but as you say developers will be developers (I was one too in a previous life…) The easiest thing if you have multiple SQLServers is to keep the service accounts the same across them (within the same environment, prod/dev/test) Also keeping the SQLServer and SQLAgent accounts the same can be a convenient thing Cheers
Twan
I would recommend creating a user account with privilages on the remote drives and starting SQL Server in this account. Apart from security concers of haning SQL Server as DC, there is definatly performance degradation. Starting system in some user account rather than localsystem account shouldn’t be an issue. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
]]>