Sql-server – SQL Server log shipping: How to give SQL Agent the needed rights to remote share

sql server

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?

Best Answer

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

Related Question