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