I have a WORKGROUP network with 2 SQL Server 2005 servers. I want to configure for log shipping.
When I try and set up the 'Secondary Database Settings', and try and run the process, it gets an error saying it cannot access the backup file (the secondary cannot access the backup file on the primary). The file is there. It is shared. I have created local accounts on both machines called 'SyncAccount', and have given full control over the shared directory (on the primary) and local directory on the secondary. I have chaged the SQL Agent to use the local account.
TITLE: Microsoft SQL Server Management Studio
SQL Server Management Studio restore database 'ISC'
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (SqlManagerUI)
Cannot open backup device '\\FAtBOY\ISC\ISC.bak'. Operating system error 5(Access is denied.).
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476
I have tested access for the created user by doing a "runas" from the command prompt.
I have invested hours in this. I have found several relevent artcles, but have not found the answer.
What is wrong with this thing?
Is this login part of sql sysadmin group? What account is used to start up sqlserver service? Does this account have permission to write to the shared folder? What's about the sqlagent service?
|||It has been a while since I made this post, but I am still having the problem.
Is this login part of sql sysadmin group? I have given admin provlidges to thie user.
What account is used to start up sqlserver service? I created a new "syncaccount" as assiged it to the sql agent. It has admin rights.
Does this account have permission to write to the shared folder? Yes. I tested this by linking to the shared folder using "connect as".
What's about the sqlagent service? Explained above.
I think there is something wrong with the server. Perhaps my configuration is the issue. Two servers (not on a domain) in a workgroup.
|||When you operate in a workgroup, network permission is nothing more than Windows impersonation. For things to work, the account must be created on both workgroup nodes and has the same password. Logshipping is nothing more than a whole bunch of sql jobs that get executed under a security context (i.e. sqlagent or the proxy). If your job is owned by a sysadmin, the job will get executed under sqlagent service account to access network resources. If it's a non-sysadmin, it will be under the proxy account.
Take a look at xp_cmdshell in book online. There is a link to how to setup/configure the proxy account.
To see if the NT accout is valid, try backup a database to the network share. Resolve this before you attempt to solve the logshipping issue.
No comments:
Post a Comment