Thursday, April 03, 2008

SQL Machine in a workgroup; unable to access network resources!

In order for SQL server to access network resrources(eg. taking backups etc on network paths) with in a domain, we are aware that SQL Startup Agent account should be a domain user with appropriate rights within the LAN.
Howerver, What if SQL Server machine is not part of Domain and you need to BCP out a file to your webserver which is in the LAN but both machines are in a workgroup?
Here's a solution:
  1. Create a share folder on Destination Network(webserver).
  2. MAP a drive on your SQL machine to the shared folder you created.
  3. Run your SQL command (eg backup or bcp) with the security context of a Windows user account, not SQL user. The actual problem lies here; using SA won't work, it will give you error that network path not accessible. You have to use windows user account in SQL to acess the network resources in case machine running SQL Server is not a part of DOMAIN.

Note: You can ommit the step 2 and use UNC naming convention to access the path directly in the code ( eg. backup database mydb to disk = '\\webserver\shared\mydb.bak'), but it's better to use MAP-Drives for robust coding.

No comments: