Home / server / Questions / 17182
Accepted
gillonba
Asked: 2009-06-02 12:42:27 +0800 CST

MS SQL 2005 -- backing up to a shared directory

  • 772

I have a SQL 2005 virtual machine running Windows 2003 on a 2008 server under hyper-v and I need to start backing up my databases to a shared folder. I have created similar backup jobs in other environments with no problems. The difference is that in every other case, the SQL server was part of a domain and in this case there is no domain. Even when I set the shared folder to give "Full Control" permissions to "everyone", SQL still refuses to run the backup job:

Operating system error 1326(Logon failure: unknown user name or bad password.). BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What do I have to do? Is there no solution other than to just back up to a local drive and find another way to copy the files over?

edit: added bold for emphasis

sql-server sql-server-2005 backup active-directory workgroup

3 Answers

  1. The SQL Server service account must have access to the network share. If you running your SQL Server service using the Localsystem account: this account has no access to shares on the network, because it is not an authenticated network account, making it impossible to backup to a normal network share.

    So, if you want to backup to a network share you can do two different things:

    Change the account which runs SQL Server service to a domain account (DOMAIN\USERNAME) which can access the network share and has the needed permissions.

    or (not recommended in a production environment):

    Add the following registry value on the server with the network share and add the sharename you want to backup to - the share does not then authenticate who is coming in and so the Localsystem account will have access. The server must be re-started before the change takes effect. Note that this effectively removes all security on that share, so you're letting anyone have access. 

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters\NullSessionShares
    • 4
  2. Best Answer

    If you do not have a domain, you can set up the same user (name) on all machines, and then use the local user to run SQL Server.

    For example, on ServerA (which has SQL Server running), I can set up the user MySQLServerUser as a local user. Use Configuration Manager to set this as the account to run SQL Server.

    Then on ServerB, which is where I want to send the backups, I set up the same account (MySQLServerUser) with the same password.

    Since Windows will pass this through as the first user/password, it should authenticate.

    Note that backups across the network are not recommended. The SQL backup process is intolerant of network delays. Anything that hiccups will fail the backup. And it will likely happen when your disks fail. Back up locally, copy to the remote machine. Copies will retry themselves automatically.

    • 3

  3. Make sure you're running the SQL Server service under the context of a domain account that has network access privileges.

    Or is this a folder local to the SQL Server box and shared with others?

    **EDIT:**bah, @splattne's right, I was on auto-pilot. SQL Server service, not the SQL Agent.

    • 1