3

This is an old topic, and somehow I have not been able to find a clear answer.

Setup: Two stand alone SQL Servers. They have full network visibility to each other. They are NOT in a domain. There is NO domain available. (I am using SQL Server 2014, but this exact issue applies at least back to SQL 2005.)

Everything "works" ... the only piece missing is that the sql job that tries to copy the log backup fails with this error:

Error: Cannot open backup device '\\sql2\sqllogs\NORTHWND_20150209053800.trn'. 
Operating system error 5(Access is denied.).

SQL Agent is running as the "NT Service\SQLSERVERAGENT" account. On SQL2, I have given this user full rights to \sql2\sqllogs\ share and folder.

The SQLSERVERAGENT account on both systems has the same password, and the agent has been restarted after setting that password.

I have seen some folks solve this by making SQLSERVERAGENT a member of the administrators group on the OS, but that does not sound like a safe path.

What is the correct way to solve this?

  • In logshipping copy job is created on Secondary server. Did you made sure you gave full access to SQL Server agent account FROM SECONDARY server. Reason being since both are different servers it would make difference if you add account running sql agent on secondary server to have read write permission on the file share please try this – Shanky Feb 9 '15 at 5:52
  • @Shanky Fair call, but it is the same issue in either direction.... backup log across unc path to remote, or copy from unc to local.... either way, same rights issues apply I believe. In ex above, we are trying to back up log directly to secondary. – Jonesome Reinstate Monica Feb 9 '15 at 6:17
  • Access denied error means its not able to access so there is of course some issue. Have you made sure you have set permission correctly ? – Shanky Feb 9 '15 at 6:23
  • @shanky this post is a request for a pointer to steps or docs that explain this aspect. The docs i have found so far do not address this aspect head on. – Jonesome Reinstate Monica Feb 9 '15 at 14:45
3

Create a service account in your Active Directory or as a local account on the primary server. Add that account to the folder's security permissions with change and read permissions.

http://www.mssqltips.com/sqlservertip/2562/sql-server-log-shipping-to-a-different-domain-or-workgroup/

The service startup account defines the Microsoft Windows account in which SQL Server Agent runs and its network permissions. SQL Server Agent runs as a specified user account. You select an account for the SQL Server Agent service by using SQL Server Configuration Manager.

https://msdn.microsoft.com/en-us/library/ms191543.aspx

To set the Service Startup Account for SQL Server Agent:

  1. In Registered Servers, click the plus sign to expand Database Engine.
  2. Click the plus sign to expand the Local Server Groups folder.
  3. Right-click the server instance where you want set up the Service Startup Account, and select SQL Server Configuration Manager….
  4. In the User Account Control dialog box, click Yes.
  5. In SQL Server Configuration Manager, in the console pane, select SQL Server Services.
  6. In the details pane, right-click SQL Server Agent (server_name), where server_name is the name of the SQL Server Agent instance for which you want to change the service startup account, and select Properties.
  7. In the SQL Server Agent (server_name)Properties dialog box, in the Log On tab, select one of the following options underLog on as.

https://msdn.microsoft.com/en-us/library/ms186264.aspx#SSMSProcedure


You must grant read permissions on this network share to the proxy account under which the copy job will run at the secondary server instance.

By default, this is the SQLServerAgent service account of the secondary server instance, but an administrator can choose another proxy account for the job.

Network path to the download folder

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.