0

I have SQL Server 2012 running in an Active Directory Domain environment. I set up a Managed Service Account for the SQL services to run under, as per this document. Since my domain functional level is 2008, it's a regular MSA and not a gMSA (group). So far, so good. The problem is that I want to back up the databases to a UNC share. This wouldn't be a problem if the SQL service were running under a regular domain account, but the Managed Service Account cannot write to a shared directory. I explicitly gave permission in the security settings for that share, but SQL still throw an error when trying to make a backup. Specifically, the error message says:

System.Data.SqlClient.SqlError: Cannot open backup device '\remoteserver\Backupshare\SQLbkup.bak'. Operating system error 1808(The account used is a computer account. Use your global user account or or local user account to access this server.). (Microsoft.SqlServer.Smo)

[Actual backup path changed for redaction purposes]

Searches on the error message have only turned up non-relevant results. Some discussions on technet indicate that it should be possible to give the MSA permission to write in a remote directory. Any idea what I'm missing?

26 April 2018 Edit:

In my original post I neglected to mention that the specific share I want to write to is a CIFS share on a Netapp device. I didn't mention it because I didn't think it was relevant. However, as I have continued to research this and do more testing, it seems that it may indeed be a Netapp issue. As a test, I made a share on a regular Windows 7 machine, and attempted to write my SQL backup there. It worked as long as I gave the MSA permission on the target directory. When I looked in the security log on the Windows 7 machine, I saw that the incoming connection was using the MSA credentials, regardless of whether I was using a proxy in in the SQL Agent or not.

So on the SQL end, it seems that even if the job is getting launched as domain administrator, the actual write operation for the bak file is taking place as the Managed Service Account. If the target is a Windows machine on the domain, it can accept that incoming connection. A Netapp, however, cannot - at least with the version of Data ONTAP we have. So it would seem we're at an impasse. Thanks though to Katherine for your response, which helped me learn a lot. :)

Charles Burge
  • 768
  • 6
  • 16

1 Answers1

0

Your backup job is running as the SQL Agent user. I assume that the SQL Agent is running as your MSA?

I ran into this myself in a test environment and the backups succeeded when I granted the server's computer account access to the share. Since this was only a test environment I decided that was good enough. I don't know if that would work for you, or if you'd want to chase it down further.

I imagine the issue might be not assigning the MSA SeAssignPrimaryTokenPrivilege. The necessary privileges for SQL Agent Proxies are:

  1. Permission to bypass traverse checking (SeChangeNotifyPrivilege)
  2. Permission to replace a process-level token (SeAssignPrimaryTokenPrivilege)
  3. Permission to adjust memory quotas for a process (SeIncreaseQuotaPrivilege)
  4. Permission to access this computer from the network (SeNetworkLogonRight)

Honestly, I'd expect not assigning the MSA these privileges to cause the agent jobs to fall back to the MSA user account rather than the computer account, but your error message and my test environment suggest otherwise.

Katherine Villyard
  • 18,550
  • 4
  • 37
  • 59
  • Thanks for the links. I used the red-gate link to set up a proxy, and I can see that the job is now running with my domain administrator credentials. However, it's still failing. Regarding the necessary privileges for the MSA, I've been searching in vain **how** to check what privileges it has now, and how to change them if necessary. Any pointers? – Charles Burge Apr 25 '18 at 18:03
  • Is it the same `Operating system error 1808(The account used is a computer account. Use your global user account or or local user account to access this server.).` error? – Katherine Villyard Apr 25 '18 at 19:08
  • 1
    Yes, that is the exact error message. The job history in SQL Management Studio says that the job was invoked by the domain administrator, so it seems my proxy is working properly - at least to begin with. By the way, I did figure out how to assign the privileges above to the MSA, using the Carbon module for Powershell. (http://get-carbon.org/index.html). – Charles Burge Apr 25 '18 at 21:14
  • Ah, I was changing them in local security policy -> user rights assignment. – Katherine Villyard Apr 25 '18 at 21:16