Using gMSA accounts for SQL Clusters

[Mike Leone]

OK. So I have a bunch of SQL clusters (they work better for us than
SQL Always On, I'm told). Anyway, what I want to do is to start using
Group Managed Service Accounts (gMSAs) as the domain account to run
the SQL server, rather than just a regular AD account, which is how
it's done now. (yes, yes, I know, that's why I'm trying to change the
process LOL).

Anyway, there are a lot of resources about this, but they're more than
a bit confusing (shocking, I know ...). Anyway, I hope someone can
provide some pointers, or maybe answers to some questions:

I haven't found (what I consider to be) a clear step-by-step for using
a gMSA for a SQL cluster (or even a standalone SQL server). Anybody
have a good one they know of?

Some questions about the gMSA:

https://www.dbi-services.com/blog/make-sql-server-services-more-secure-with-managed-service-accounts/

"gMSA creation is slightful different as there’s a need to create the
Account and grant the defined computer Objects to use it. Fortunately,
this can als easily be done with a single PS Query:

New-ADServiceAccount -Name msa_exhib2 -DNSHostName DNS Server
-PrincipalsAllowedToRetrieveManagedPassword Server1$, Server2$"

That's fine. But these computer accounts are the nodes of the cluster.
This has to be done before installing SQL Server, because during the
install, I have to tell it what service account to use, right? Do I
then have to come back and somehow add the SQL Server computer object
name to this Service Account, or does the SQL install do that for me?

That website above seems to use some PS module they wrote. I'd prefer
not to have to go adding in more complexity. I haven't seen anything
that shows how to do that without adding in some other PS module.

Even the other website I usually rely on for SQL info seems to like
using that module:
https://www.mssqltips.com/sqlservertip/5340/using-group-managed-service-accounts-with-sql-server/

Anybody using gMSAs for SQL servers (especially SQL clusters)?

Thanks

--

Mike. Leone, <mailto:tur...@mike-leone.com>

PGP Fingerprint: 0AA8 DC47 CB63 AE3F C739 6BF9 9AB4 1EF6 5AA5 BCDF
Photo Gallery: <http://www.flickr.com/photos/mikeleonephotos>

[Mike Leone]

Some progress! (I think)

https://www.sqlshack.com/configure-managed-service-accounts-for-sql-server-always-on-availability-groups/

This seems to indicate that I have to install SQL Server *first* and
make sure it's running properly (I presume using the default built in
NT AUTHORITY accounts? That part isn't clear). THEN change the SQL
Server config to use the new service account. At that point, since
it's defined explicitly as an AD *service* account, you won't be
prompted for a password, since AD handles setting and changing the
password automatically. If I read another site correctly, that
password is something like 120 chars long. LOL

Figures I just recently purged my test cluster, where I could have
tried this. Oh, well. Won't take too long to make a new Windows
cluster, make some shared iSCSI storage, install SQL Cluster.

Then try changing that SQL cluster to use the new Service Account.
I'll just make one with the word "Test" in it, to differentiate it.

Last question - for now .... I have (counting ....) 11 SQL clusters
now. (1 cluster per job function - so 1 exclusively for CRM, 1 for HR,
etc etc etc).
Best practice ... 1 gMSA for each cluster? But have all 11 gMSAs in a
single AD group?

That way, when it comes time to set the folder ACLs for writing
backups, logs, etc, I can just use the 1 AD group name. We do log
shipping, where each SQL server writes a backup of each DB to a single
file server share (each in it's own separate folder, of course). And
that way I can just put that 1 Ad group on the NTFS security ot the
top level share, and then each SQL Server can write it's backups
there.

[Dave Lum]

" Anybody using gMSAs for SQL servers (especially SQL clusters)?"

<Raises hand> Yep. Fought the cluster battle buy I found the delegation part harder to figure out (but makes complete sense once you do it...once)

For gMSAs I create computer groups in AD and point the gMSA to that when I create 'em.

Something like CMP.SQLSERVERS with servers SQ1 and SQL2 in it. (Server reboot required for them to pick up that group token)

Then when you create the gMSA , -PrincipalsAllowedToRetrieveManagedPassword points to CMP.SQLSERVERS

So the command would look like (this creates the gMSA):
New-ADServiceAccount -SAMAccountName SVC.SQLSERVERS -DNSHostName SVC.SQLSERVERS +"youdomain.com" -Name SVC.SQLSERVERS -PrincipalsAllowedToRetrieveManagedPassword CMP.SQLSERVERS

Then on each SQL server: Install-ADServiceAccount -Identity SVC.SQLSERVERS
If that gives you an error, run Add-WindowsFeature RSAT-AD-PowerShell then the Install command again

Depending on what you're doing, this gMSA account may need permissions in SQL itself. For that I created another AD group called SQL.SVCACCTADMIN and in SQL I have DBA's assign permssions to that AD group, and then I add SVC.SQLSERVERS to that AD group. This way you can swap out / change underlying accounts of the AD group and not have to touch SQL perms again.

If this is unclear, feel free t reach out to me directly, I have a lot of very recent experience here.

Dave


-----Original Message-----
From: ntsys...@googlegroups.com <ntsys...@googlegroups.com> On Behalf Of Mike Leone
Sent: Wednesday, May 28, 2025 7:13 AM
To: NTSysAdmin <ntsys...@googlegroups.com>
Subject: [ntsysadmin] Using gMSA accounts for SQL Clusters

CAUTION: This email originated from outside of OCHIN's network

Do not click links or open attachments unless you recognize the sender and know the content is safe. If you suspect this email is phishing or a scam, use the report button in the Outlook toolbar to report it to Desktop Support.

--
You received this message because you are subscribed to the Google Groups "ntsysadmin" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ntsysadmin+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/ntsysadmin/CAHBr%2B%2Bg1oS2Phpeg6tp-M0hrOKdpb5%3DO8BPDxx%3D2%2By6RBnF_Jg%40mail.gmail.com.
Attention: Information contained in this message and or attachments is intended only for the recipient(s) named above and may contain confidential and or privileged material that is protected under State or Federal law. If you are not the intended recipient, any disclosure, copying, distribution or action taken on it is prohibited. If you believe you have received this email in error, please contact the sender with a copy to compl...@ochin.org, delete this email and destroy all copies.

[Dave Lum]

If you need delegation and SPN help with gMSA's, I have experience there as well. SETSPN -l doesn't always return what you expect when dealing with gMSA's.

I bashed my head on this *a lot*, but finally understand it enough to admit I work with gMSA's :)

Dave

To view this discussion visit https://groups.google.com/d/msgid/ntsysadmin/BN6PR17MB30769063C322E9E4CA7A6DA3DD67A%40BN6PR17MB3076.namprd17.prod.outlook.com.

[Mike Leone]

On Wed, May 28, 2025 at 4:56 PM 'Dave Lum' via ntsysadmin
<ntsys...@googlegroups.com> wrote:
>
> " Anybody using gMSAs for SQL servers (especially SQL clusters)?"
>
> <Raises hand> Yep. Fought the cluster battle buy I found the delegation part harder to figure out (but makes complete sense once you do it...once)
>
> For gMSAs I create computer groups in AD and point the gMSA to that when I create 'em.
>
> Something like CMP.SQLSERVERS with servers SQ1 and SQL2 in it. (Server reboot required for them to pick up that group token)
>
> Then when you create the gMSA , -PrincipalsAllowedToRetrieveManagedPassword points to CMP.SQLSERVERS
>
> So the command would look like (this creates the gMSA):
> New-ADServiceAccount -SAMAccountName SVC.SQLSERVERS -DNSHostName SVC.SQLSERVERS +"youdomain.com" -Name SVC.SQLSERVERS -PrincipalsAllowedToRetrieveManagedPassword CMP.SQLSERVERS
>
> Then on each SQL server: Install-ADServiceAccount -Identity SVC.SQLSERVERS
> If that gives you an error, run Add-WindowsFeature RSAT-AD-PowerShell then the Install command again

I got the acct created successfully, at least. LOL About to try
installing SQL to use it. I do have some questions (I don't see your
personal email address in the message headers, so I'm replying here
...)
I'm guessing I should be using separate accounts for the SQL Service
itself, and for the SQL Server Agent service. So I'll create 2
accounts, like that example website shows ...

PS C:\WINDOWS\system32> Install-ADServiceAccount -Identity TST_SQL_SvcAcct
PS C:\WINDOWS\system32> Test-ADServiceAccount -Identity TST_SQL_SvcAcct
True

> Depending on what you're doing, this gMSA account may need permissions in SQL itself. For that I created another AD group called SQL.SVCACCTADMIN and in SQL I have DBA's assign permssions to that AD group, and then I add SVC.SQLSERVERS to that AD group. This way you can swap out / change underlying accounts of the AD group and not have to touch SQL perms again.

Couple SQL-related questions:

1. What permissions are you referring to, in the DB itself? So I can
tell my DBA what to watch out for ...(mind you, sometimes my DBA has
problems spelling "DBA": unless I spot him the D and the B first, but
... LOL)

2. We have a lot of maintenance plans in SQL, where a backup copy of
the DB is written to a whole separate server (\\LogShip). I'm sure I
would need to adjust permissions on those \\LogShip shares, but what
am I adding? The group of hosts (CMP.SQLSERVERS)? Or the group that
contains the actual service acct (SQL.SVCADMIN)?

I'm guessing the latter needs R/W access to the folder the backup copy
is written to, since the job executes (I think) as SQL Server Agent,
which should be using that

Thanks for all your help so far!

[Dave Lum]

Answers below

-----Original Message-----
From: ntsys...@googlegroups.com <ntsys...@googlegroups.com> On Behalf Of Mike Leone

Sent: Thursday, May 29, 2025 11:38 AM
To: ntsys...@googlegroups.com
Subject: Re: [ntsysadmin] Using gMSA accounts for SQL Clusters

>
> Then on each SQL server: Install-ADServiceAccount -Identity
> SVC.SQLSERVERS If that gives you an error, run Add-WindowsFeature
> RSAT-AD-PowerShell then the Install command again

I got the acct created successfully, at least. LOL About to try installing SQL to use it. I do have some questions (I don't see your personal email address in the message headers, so I'm replying here
...)
I'm guessing I should be using separate accounts for the SQL Service itself, and for the SQL Server Agent service. So I'll create 2 accounts, like that example website shows ...

>>> We use the same gMSA for SQL and SQL agent for a given server. We have multiple SQL servers and we create a different gMSA for each server function. So Accounting SQL servers have one gMSA and reporting SQL servers have a different gMSA. (We use gMSA's on our IIS servers too, if you want to go that route at some point).

Couple SQL-related questions:

1. What permissions are you referring to, in the DB itself? So I can tell my DBA what to watch out for ...(mind you, sometimes my DBA has problems spelling "DBA": unless I spot him the D and the B first, but ... LOL)

>>> Our DBAs have some monitoring app (Redgate, I think) that runs a Windows service as an AD account but this acct also needs "sysadmin" permissions in the SQL database itself. Assigning an AD *group* with those permissions means you can just swap out / change AD group membership and not have to log into multiple SQL servers to change the account later.

>>> Also this SQL.SVCACCTADMIN AD group has an alert assigned to it so if membership changes, the Windows team gets an alert (similar to notices for Domain Admin and other privileged groups).

2. We have a lot of maintenance plans in SQL, where a backup copy of the DB is written to a whole separate server (\\LogShip). I'm sure I would need to adjust permissions on those \\LogShip shares, but what am I adding? The group of hosts (CMP.SQLSERVERS)? Or the group that contains the actual service acct (SQL.SVCADMIN)?

>>>> If your in SSMS, you'll be assigning perms to SQL.SVCADMIN. If you're anywhere else (SMB shares) you're using SVC.SQLSERVERS[note1]. The SQL prefix tells me "you're in SQL assigning permissions"

>>>> [note1] For SMB shares I actually have at least a 1:1 ratio of SMB share name = AD Group. So share \\SERVER1\SHARE1 I have an AD group called something like Access_\\SERVER1\SHARE1_RWXD. The gMSA account would just become a member of the RWXD group instead of assigned directly to the share. This has the benefit of letting me see what accounts can get where just by looking at the "Member Of" tab.

Dave

[Mike Leone]

> >>> We use the same gMSA for SQL and SQL agent for a given server. We have multiple SQL servers and we create a different gMSA for each server function. So Accounting SQL servers have one gMSA and reporting SQL servers have a different gMSA. (We use gMSA's on our IIS servers too, if you want to go that route at some point).

I plan to do the same (different gMSAs for CRM, HR, etc). But I think
I will make 1 for SQL Server, and 1 for SQL Agent. So 2 gMSAs for each
function ...

>
> Couple SQL-related questions:
>
> 1. What permissions are you referring to, in the DB itself? So I can tell my DBA what to watch out for ...(mind you, sometimes my DBA has problems spelling "DBA": unless I spot him the D and the B first, but ... LOL)
>
> >>> Our DBAs have some monitoring app (Redgate, I think) that runs a Windows service as an AD account but this acct also needs "sysadmin" permissions in the SQL database itself. Assigning an AD *group* with those permissions means you can just swap out / change AD group membership and not have to log into multiple SQL servers to change the account later.

OK, we don't have that, so I don't think this will apply to us ...

>
> >>> Also this SQL.SVCACCTADMIN AD group has an alert assigned to it so if membership changes, the Windows team gets an alert (similar to notices for Domain Admin and other privileged groups).
>
> 2. We have a lot of maintenance plans in SQL, where a backup copy of the DB is written to a whole separate server (\\LogShip). I'm sure I would need to adjust permissions on those \\LogShip shares, but what am I adding? The group of hosts (CMP.SQLSERVERS)? Or the group that contains the actual service acct (SQL.SVCADMIN)?
>
> >>>> If your in SSMS, you'll be assigning perms to SQL.SVCADMIN. If you're anywhere else (SMB shares) you're using SVC.SQLSERVERS[note1]. The SQL prefix tells me "you're in SQL assigning permissions"

Why would the DBA need to assign rights in SSMS to the gMSA? Not being
a real DBA, I'm not following this part ...

OK. What I've done is create 2 AD groups -
"SQL_Servers_Service_Accts", which will contain all the gMSAs that run
the SQL Servers, and "SQL_Server_Agent_Service_Accts" which will
contain all the gMSAs that run the SQL agent service.
(subtle naming, huh? LOL)

I might make a new group "SQL_Server_Agent_RWXD" (which will be used
only for NTFS security on the log shipping servers, or wherever), and
add the Agents group to it.

I would then add this group to the top level share on the log shipping
servers (I have 3). That way it will inherit to each sub-folder (each
maintenance plan writes out a DB backup into its own separate folder,
so 1 share, many sub-folders inheriting). That way, even if the
maintenance plan changes the location of where it writes its backup
(move log shipping server, etc), it will still be able to write to
where it needs to.
.

> >>>> [note1] For SMB shares I actually have at least a 1:1 ratio of SMB share name = AD Group. So share \\SERVER1\SHARE1 I have an AD group called something like Access_\\SERVER1\SHARE1_RWXD. The gMSA account would just become a member of the RWXD group instead of assigned directly to the share. This has the benefit of letting me see what accounts can get where just by looking at the "Member Of" tab.

Like you, I prefer to have AD groups for NTFS security (RWXD, etc).
And assign only groups to the NTFS security, rather than individual AD
accounts, it's much cleaner and easier to maintain, since all the work
is done in AD, I don't have to touch the shares if anything changes
...

Thanks!

[Mike Leone]

> Why would the DBA need to assign rights in SSMS to the gMSA? Not being
> a real DBA, I'm not following this part ...

AH!

"The SQL Server Agent service account requires sysadmin privileges in
the SQL Server instance that it is associated with. In this tip I have
tried to put forth a solution by running SQL Server agent under group
(SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER). This group has
all the required privileges and is not part of the administrator
group."

https://www.mssqltips.com/sqlservertip/2317/running-sql-server-agent-with-a-least-privilege-service-account/

OK, I guess that explains that ...

[Dave Lum]

"Why would the DBA need to assign rights in SSMS to the gMSA? Not being a real DBA, I'm not following this part ..."

IIRC if service MSSQLSERVER is running under the YOURDOMAIN\GMSAAccount then SQL maintenance jobs also run under that context, so YOURDOMAIN\GMSAAccount (or AD group it's in) needs to be defined in the SQL database with whatever role runs the maintenance tasks.

Your DBA's would know the answer to this one, just ask 'em "hey does this service account need perms in SQL itself?"

Dave

-----Original Message-----
From: ntsys...@googlegroups.com <ntsys...@googlegroups.com> On Behalf Of Mike Leone
Sent: Friday, May 30, 2025 7:45 AM
To: ntsys...@googlegroups.com
Subject: Re: [ntsysadmin] Using gMSA accounts for SQL Clusters

CAUTION: This email originated from outside of OCHIN's network

Do not click links or open attachments unless you recognize the sender and know the content is safe. If you suspect this email is phishing or a scam, use the report button in the Outlook toolbar to report it to Desktop Support.

--
You received this message because you are subscribed to the Google Groups "ntsysadmin" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ntsysadmin+...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/ntsysadmin/CAHBr%2B%2BhPY020fnVCy2hAL0B4Juq2mtyBkNCPodPFJMfPkhEZZw%40mail.gmail.com.

[Michael Kurzdorfer]

Came across this a little over a year ago. The Install-ADServiceAccount command (although it is documented as necessary), is unnecessary when using gMSA (it was only a requirement for MSA).  This means there is no need to install the AD RSAT PowerShell tool to use it. We stopped using the command after reading the Twitter thread. #ThanksMicrosoft.

https://x.com/NathanMcNulty/status/1712619034322337952

https://unrollnow.com/status/1712619034322337952 <- Unrolled link for those that don't want to use X

To view this discussion visit https://groups.google.com/d/msgid/ntsysadmin/BN6PR17MB307645D202182B13EE313EE3DD61A%40BN6PR17MB3076.namprd17.prod.outlook.com.

[Brian Illner]

Is anyone else doing this? If all the MS documentation still says its necessary, it feels like one of those “Failed Successfully” situations that they could make an unannounced correction to at any point.

 

BRIAN ILLNER  |  Senior Systems Administrator 864.250.9227 Office 864.679.2537 Fax Canal Insurance Company 101 N. Main Street, Suite 400 Greenville, SC 29601 WARNING:  As the information in this transmittal (including attachments, if any) may contain confidential, proprietary, or business trade secret information, it should only be reviewed by those who are the intended recipients.  Unless you are an intended recipient, any review, use, disclosure, distribution or copying of this transmittal (or any attachments) is strictly prohibited.   If you have received this transmittal in error, please notify me immediately by reply email and destroy all copies of the transmittal.  While Canal believes this transmittal to be free of virus or other defect, it is the responsibility of the recipient to ensure that it is virus free and no responsibility is accepted by Canal (or its subsidiaries and affiliates) for any loss or damage arising therefrom.

    

From: ntsys...@googlegroups.com <ntsys...@googlegroups.com> On Behalf Of Michael Kurzdorfer
Sent: Saturday, May 31, 2025 10:08 AM
To: ntsys...@googlegroups.com
Subject: Re: [ntsysadmin] Using gMSA accounts for SQL Clusters

 

CAUTION: This message was sent from outside of Canal Insurance. Please do not click links or open attachments unless you recognize the source of this email and know the content is safe. Please report all suspicious emails to "inf...@canal-ins.com" as an attachment.

---

To view this discussion visit https://groups.google.com/d/msgid/ntsysadmin/CA%2BZZigb7RAVGnfvCrCG5qik%3DJtNyzcRcmcO7sxxQYZ4XPcQ0gA%40mail.gmail.com.

[Dave Lum]

Huh.  Yeah I get an error if that Install-ADServiceAccount command isn’t run, so it never occurred for me to try the gMSA without running it. I’ll have to give it a shot the next time I set a server up for it.

But I also know MS is good at “This error message is expected and can safely be ignored” in its logging, too. I once went on a mission to evict 100% of error and warnings from Windows logs on a fresh server and was utterly foiled.  “This error means we’re checking for something that doesn’t apply to this server..”

Dave

To view this discussion visit https://groups.google.com/d/msgid/ntsysadmin/CO6PR13MB5308EBE4A49E8FC2AD2AA1F4C562A%40CO6PR13MB5308.namprd13.prod.outlook.com.

[Mike Leone]

An update:

So I made the 2 gMSAs, used them to install SQL, that all worked fine. So YAY!

But what's not working fine is the SQL Agent, and I'm unsure why ....

So my DBA  apparently makes a maintenance plan, which writes out a BAK to a local drive (in our case, H:).
Then he makes a SQL Agent job that moves said BAK to a separate server (in this case \\DC1_LogShip02).
And that move is failing (this is what he IMed me:

H:\MSSQL16.MSSQLSERVER\MSSQL\Backup\ Access is denied. 0. Process Exit Code 0. The step succeeded.

So I figured "OK, maybe the SQL Agent acct needs to be on the NTFS security of that BACKUP folder". So I have an AD group (SQL_Agent_RWXD) which has all the SQL Agent gMSAs in it - currently just 1, this one I'm testing with.
So I added that group to the BACKUP folder NTFS, and gave it RWXD rights.

He says it's still failing.(I verified that the same AD group is on the NTFS for the target of the move job).

Have I missed something? I don't see anything in Event Logs (not that I expected to). I don't see anything in SQLAGENT.OUT, which is where the SQL Agent has listed as an error log.

The actual SQL Server Agent job step is:

Robocopy "H:\MSSQL16.MSSQLSERVER\MSSQL\Backup" \\dc1_logship02\TEST /mov /NFL /NDL /NJS /NJH /NS /NC

$directory = "H:\MSSQL16.MSSQLSERVER\MSSQL\Backup"
$directoryInfo.count
if($directoryInfo.Count -gt '0')
{
     Throw "One or more files not copied"
}

Thoughts? Is it something simple I've just overlooked, since this is the first time I am trying to use gMSAs?

[Wright, John M]

You mentioned the NTFS permissions but what about the share/SMB permissions?  Is SQL_Agent_RWXD included there?

 

--

John Wright

IT Support Specialist

1800 Old Bluegrass Avenue, Louisville, KY 40215

502.708.9953

Please submit IT requests to Hazelwoo...@bluegrass.org

24 Hour Helpline 1.800.928.8000

  

CONFIDENTIALITY NOTICE: This message contains confidential information and is intended only for the individual(s) addressed in the message. If you are not the named addressee, you should not disseminate, distribute, or copy this e-mail. If you are not the intended recipient, you are notified that disclosing, distributing, or copying this e-mail is strictly prohibited.

 

From: ntsys...@googlegroups.com <ntsys...@googlegroups.com> On Behalf Of Mike Leone

Sent: Tuesday, June 3, 2025 12:51 PM
To: NTSysAdmin <ntsys...@googlegroups.com>

Subject: [ntsysadmin] Re: Using gMSA accounts for SQL Clusters

An update:

So I made the 2 gMSAs, used them to install SQL, that all worked fine. So YAY!

But what's not working fine is the SQL Agent, and I'm unsure why ....

So my DBA  apparently makes a maintenance plan, which writes out a BAK to a local drive (in our case, H:).
Then he makes a SQL Agent job that moves said BAK to a separate server (in this case \\DC1_LogShip02).
And that move is failing (this is what he IMed me:

H:\MSSQL16.MSSQLSERVER\MSSQL\Backup\ Access is denied. 0. Process Exit Code 0. The step succeeded.

So I figured "OK, maybe the SQL Agent acct needs to be on the NTFS security of that BACKUP folder". So I have an AD group (SQL_Agent_RWXD) which has all the SQL Agent gMSAs in it - currently just 1, this one I'm testing with.
So I added that group to the BACKUP folder NTFS, and gave it RWXD rights.

 

He says it's still failing.(I verified that the same AD group is on the NTFS for the target of the move job).

 

Have I missed something? I don't see anything in Event Logs (not that I expected to). I don't see anything in SQLAGENT.OUT, which is where the SQL Agent has listed as an error log.

 

The actual SQL Server Agent job step is:

 

Robocopy "H:\MSSQL16.MSSQLSERVER\MSSQL\Backup" \\dc1_logship02\TEST /mov /NFL /NDL /NJS /NJH /NS /NC

$directory = "H:\MSSQL16.MSSQLSERVER\MSSQL\Backup"
$directoryInfo.count
if($directoryInfo.Count -gt '0')
{
     Throw "One or more files not copied"
}

 

Thoughts? Is it something simple I've just overlooked, since this is the first time I am trying to use gMSAs?

 

--

You received this message because you are subscribed to the Google Groups "ntsysadmin" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ntsysadmin+...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/ntsysadmin/CAHBr%2B%2BhDo0o2iouQKsg8M9JU41eAsfFh6wSdszCrOdNqUxq09Q%40mail.gmail.com.

[Dave Lum]

Did you restart the Agent service after adding it’s gMSA to the AD group?  Needs a restart for the SQL agent account to pick up the new group token.

 

Dave

 

From: ntsys...@googlegroups.com <ntsys...@googlegroups.com> On Behalf Of Mike Leone
Sent: Tuesday, June 3, 2025 9:51 AM
To: NTSysAdmin <ntsys...@googlegroups.com>
Subject: [ntsysadmin] Re: Using gMSA accounts for SQL Clusters

 

CAUTION: This email originated from outside of OCHIN’s network

Do not click links or open attachments unless you recognize the sender and know the content is safe. If you suspect this email is phishing or a scam, use the report button in the Outlook toolbar to report it to Desktop Support.

 

An update:

So I made the 2 gMSAs, used them to install SQL, that all worked fine. So YAY!

But what's not working fine is the SQL Agent, and I'm unsure why ....

So my DBA  apparently makes a maintenance plan, which writes out a BAK to a local drive (in our case, H:).
Then he makes a SQL Agent job that moves said BAK to a separate server (in this case \\DC1_LogShip02).
And that move is failing (this is what he IMed me:

H:\MSSQL16.MSSQLSERVER\MSSQL\Backup\ Access is denied. 0. Process Exit Code 0. The step succeeded.

So I figured "OK, maybe the SQL Agent acct needs to be on the NTFS security of that BACKUP folder". So I have an AD group (SQL_Agent_RWXD) which has all the SQL Agent gMSAs in it - currently just 1, this one I'm testing with.
So I added that group to the BACKUP folder NTFS, and gave it RWXD rights.

 

He says it's still failing.(I verified that the same AD group is on the NTFS for the target of the move job).

 

Have I missed something? I don't see anything in Event Logs (not that I expected to). I don't see anything in SQLAGENT.OUT, which is where the SQL Agent has listed as an error log.

 

The actual SQL Server Agent job step is:

 

Robocopy "H:\MSSQL16.MSSQLSERVER\MSSQL\Backup" \\dc1_logship02\TEST /mov /NFL /NDL /NJS /NJH /NS /NC

$directory = "H:\MSSQL16.MSSQLSERVER\MSSQL\Backup"
$directoryInfo.count
if($directoryInfo.Count -gt '0')
{
     Throw "One or more files not copied"
}

 

Thoughts? Is it something simple I've just overlooked, since this is the first time I am trying to use gMSAs?

 

--

You received this message because you are subscribed to the Google Groups "ntsysadmin" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ntsysadmin+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/ntsysadmin/CAHBr%2B%2BhDo0o2iouQKsg8M9JU41eAsfFh6wSdszCrOdNqUxq09Q%40mail.gmail.com.

Attention: Information contained in this message and or attachments is intended only for the recipient(s) named above and may contain confidential and or privileged material that is protected under State or Federal law. If you are not the intended recipient, any disclosure, copying, distribution or action taken on it is prohibited. If you believe you have received this email in error, please contact the sender with a copy to compl...@ochin.org, delete this email and destroy all copies.

[Mike Leone]

On Tue, Jun 3, 2025 at 1:03 PM Wright, John M <John....@newvista.org> wrote:
>
> You mentioned the NTFS permissions but what about the share/SMB permissions? Is SQL_Agent_RWXD included there?

They were not. I will try that ...

[Mike Leone]

On Tue, Jun 3, 2025 at 1:28 PM 'Dave Lum' via ntsysadmin
<ntsys...@googlegroups.com> wrote:
>
> Did you restart the Agent service after adding it’s gMSA to the AD group? Needs a restart for the SQL agent account to pick up the new group token.

I added it to the Ad group last week. But no, I didn't restart the
service. I will try, too.

Thanks!

[Mike Leone]

This is what he is sending me:

And yet that directory has the SQL Agent AD group (well, that AD group has a group in it. And that nested group has the gMSA of the SQL Agent).

I'll try adding the gMSA directly to that folder. You know what? I don't think it's on the NTFS of the folders above. I'll add the AD group there FIRST, then if that doesn't work, try adding just the acct.

[Mike Leone]

D'OH!

That is the acct for the SQL SERVER gMSA, not the acct for the SQL Agent gMSA!

Now why would it do that? Meanwhile, I added the group the "Tst_SQL_SvcAcct" in in, to the acct "SQL_Agent_RWXD" which is on the security of the folder mentioned above.

I may have to restart SQL ...

[Mike Leone]

OK, so I'm an idiot ...

Notice that I am running the SQL Server using the SQL_Agent gMSA acct. And running the SQL Agent using the SQL Server gMSA acct. I completely have it backwards. LOL

No wonder the SQL Agent couldn't access the source file; the NTFS security AD group didn't contain the gMSA that the Agent was actually running.

[Dave Lum]

Looks like a normal day in Sysadmin land to me! LOL.

 

What’s the thinking behind having a different account for the SQL server and the SQL agent? Seems like unnecessary granularity, but I don’t know your environment. 

My gMSA’s are set up the following ways:

• One gMSA for all similar functions for a given (like SQL or IIS) server, and the gMSA account name would reflect it (svc.<servername>)

-OR- 

• One gMSA for SQL functions for a group of servers, and the service account name would intuitively map to the name of the computer group it is assigned to (svc.accounting pointing to the cmp.accounting AD group that can use this account)

 

As with everything, 742 ways to skin a cat. Biggest limitation on gMSA’s is the 15 character name limit, but hey, it’s only 2025.

 

Dave

 

From: ntsys...@googlegroups.com <ntsys...@googlegroups.com> On Behalf Of Mike Leone

Sent: Tuesday, June 3, 2025 12:39 PM
To: ntsys...@googlegroups.com
Subject: Re: [ntsysadmin] Re: Using gMSA accounts for SQL Clusters

 

CAUTION: This email originated from outside of OCHIN’s network

Do not click links or open attachments unless you recognize the sender and know the content is safe. If you suspect this email is phishing or a scam, use the report button in the Outlook toolbar to report it to Desktop Support.

 

OK, so I'm an idiot ...

 

 

Notice that I am running the SQL Server using the SQL_Agent gMSA acct. And running the SQL Agent using the SQL Server gMSA acct. I completely have it backwards. LOL

 

No wonder the SQL Agent couldn't access the source file; the NTFS security AD group didn't contain the gMSA that the Agent was actually running.

 

 

On Tue, Jun 3, 2025 at 2:43 PM Mike Leone <tur...@mike-leone.com> wrote:

D'OH!

 

 

That is the acct for the SQL SERVER gMSA, not the acct for the SQL Agent gMSA!

 

Now why would it do that? Meanwhile, I added the group the "Tst_SQL_SvcAcct" in in, to the acct "SQL_Agent_RWXD" which is on the security of the folder mentioned above.

 

I may have to restart SQL ...

 

 

 

 

On Tue, Jun 3, 2025 at 2:30 PM Mike Leone <tur...@mike-leone.com> wrote:

This is what he is sending me:

And yet that directory has the SQL Agent AD group (well, that AD group has a group in it. And that nested group has the gMSA of the SQL Agent).

 

I'll try adding the gMSA directly to that folder. You know what? I don't think it's on the NTFS of the folders above. I'll add the AD group there FIRST, then if that doesn't work, try adding just the acct.

 

 

 

On Tue, Jun 3, 2025 at 2:15 PM Mike Leone <tur...@mike-leone.com> wrote:

On Tue, Jun 3, 2025 at 1:03 PM Wright, John M <John....@newvista.org> wrote:
>
> You mentioned the NTFS permissions but what about the share/SMB permissions?  Is SQL_Agent_RWXD included there?

They were not. I will try that ...

 

--

Mike. Leone, <mailto:tur...@mike-leone.com>

PGP Fingerprint: 0AA8 DC47 CB63 AE3F C739 6BF9 9AB4 1EF6 5AA5 BCDF
Photo Gallery: <http://www.flickr.com/photos/mikeleonephotos>

 

--

Mike. Leone, <mailto:tur...@mike-leone.com>

PGP Fingerprint: 0AA8 DC47 CB63 AE3F C739 6BF9 9AB4 1EF6 5AA5 BCDF
Photo Gallery: <http://www.flickr.com/photos/mikeleonephotos>

 

--

Mike. Leone, <mailto:tur...@mike-leone.com>

PGP Fingerprint: 0AA8 DC47 CB63 AE3F C739 6BF9 9AB4 1EF6 5AA5 BCDF
Photo Gallery: <http://www.flickr.com/photos/mikeleonephotos>

--

You received this message because you are subscribed to the Google Groups "ntsysadmin" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ntsysadmin+...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/ntsysadmin/CAHBr%2B%2Bi%3D5G%2BLWrCKNYDRYZzbiDBNmF4_RQobCYQhEHnHo0BDbg%40mail.gmail.com.

[Mike Leone]

On Tue, Jun 3, 2025 at 6:46 PM 'Dave Lum' via ntsysadmin <ntsys...@googlegroups.com> wrote:

Looks like a normal day in Sysadmin land to me! LOL.

 

What’s the thinking behind having a different account for the SQL server and the SQL agent? Seems like unnecessary granularity, but I don’t know your environment. 

Well, this was the first test. I was thinking that each of the SQL servers  (HR, CRM, etc) would have its own Agent service acct, and own SQL Server service acct. 

And then I could keep all the Agent accts in 1 AD group, which I could use in assigning as NTFS permissions on the log shipping servers (we have 3, although quite honestly near as I can tell, 1 is just for archived versions of DBs).  That way, the SQL Server acct that actually runs SQL wouldn't be in use anywhere except on the DB server/cluster. And the Agents would be able to write to any log shipping server, in case they needed to move from one to the other for .. reasons (disk space, etc).

I may change my mind and use just 1 service acct per SQL server, but it's not really that much more complicated to use 2 ....

My gMSA’s are set up the following ways:

• One gMSA for all similar functions for a given (like SQL or IIS) server, and the gMSA account name would reflect it (svc.<servername>)

I'll do something similar - the server name will be indicated in the service account name, and the name of the AD group that holds the host(s) - I've got quite a few SQL clusters, so I will make a group for each clusters computer accts,  necessary when creating the service accts.

[Charles F Sullivan]

We had a DBA here who insisted on having a gMSA for every standalone SQL server. There were at least 40 such servers. When I pressed him for why the standard local accounts created by the SQL install weren't sufficient, he kept saying "for security reasons". All it took was a short meeting with him and our managers and a director to squelch that.

We have several SQL AOAG clusters and I think gMSAs are just about perfect for those, but creating gMSAs isn't exactly trivial. I've made that semi-automated, but it would still be a lot of work for 40+ of them.

--
You received this message because you are subscribed to the Google Groups "ntsysadmin" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ntsysadmin+...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/ntsysadmin/CAHBr%2B%2BibDG177KMuFzNv_2S2TF53n2METXt4BrsUy9AiaR%3D1QQ%40mail.gmail.com.

--

Charlie Sullivan

Principal Windows Systems Administrator

[Mayo, Bill]

I strongly prefer having the standard local accounts when possible. When the SQL Server has to access files on other boxes, we use gMSAs for that and we do generally create them per server, per service. That is because our SQL Servers tend to be department/purpose specific and this allows you to finely control to what it has access. When we have a cluster/farm, we use the same account there (obviously for cluster).

 

From: 'Charles F Sullivan' via ntsysadmin <ntsys...@googlegroups.com>
Sent: Wednesday, June 4, 2025 10:24 AM
To: ntsys...@googlegroups.com
Subject: Re: [ntsysadmin] Re: Using gMSA accounts for SQL Clusters

 

EXTERNAL EMAIL: This email originated from outside of Pitt County Government. Do not click any links or open any attachments unless you trust the sender and know the content is safe.

To view this discussion visit https://groups.google.com/d/msgid/ntsysadmin/CAEuHzzm4Ln4WjKsEr7tMbo6%2B7rs6Fz7LVSySNksLYemg98LBow%40mail.gmail.com.

[Mike Leone]

On Wed, Jun 4, 2025 at 10:31 AM Mayo, Bill <Bill...@pittcountync.gov> wrote:

I strongly prefer having the standard local accounts when possible. When the SQL Server has to access files on other boxes, we use gMSAs for that and we do generally create them per server, per service.

All of our SQL servers use maintenance plans to write  out a BAK copy of each DB  locally(well, all the important ones, I suppose). Then a SQL Agent moves that BAK to a log shipping server, thus freeing up space locally. That's for both PROD and DEV.

So in my case, yeah, pretty much each SQL server has to access files on other hosts (usually *putting* files there). Hence why I am planning on using gMSAs for all the SQL servers, although I might wait until the DB server is scheduled to be upgraded. 

Right now, all the SQL servers (clusters and standalones) use a standard AD account as a service acct. (different accounts for clusters and standalones, obviously, but the point is that they are just regular AD user accounts. I'd prefer them to be gMSAs for security. Yes, it makes for a bunch of new accounts to keep track of. But once you set them up, and get them in the right AD groups, you'll never touch the accounts again. Or the AD groups, except to maybe add or remove gMSAs.

To view this discussion visit https://groups.google.com/d/msgid/ntsysadmin/068f38f355c844cd99a20df4405d6168%40pittcountync.gov.

[Dave Lum]

FYI we just flipped the last chunk of our SQL servers to gMSA’s last week and found (aka rediscovered) a couple of cases the service account needed additional permissions for performance reasons. Our fix was to add them to the AD group that a GPO for this leverages. Probably an edge case for most but figured I’d throw it out there while it’s fresh in my tiny brain.

Local Policies/User Rights Assignment

• Lock pages in memory
• Perform volume maintenance tasks

 

Dave

 

From: ntsys...@googlegroups.com <ntsys...@googlegroups.com> On Behalf Of Mike Leone

Sent: Wednesday, June 4, 2025 8:52 AM
To: ntsys...@googlegroups.com
Subject: Re: [ntsysadmin] Re: Using gMSA accounts for SQL Clusters

 

CAUTION: This email originated from outside of OCHIN’s network

Do not click links or open attachments unless you recognize the sender and know the content is safe. If you suspect this email is phishing or a scam, use the report button in the Outlook toolbar to report it to Desktop Support.

 

On Wed, Jun 4, 2025 at 10:31 AM Mayo, Bill <Bill...@pittcountync.gov> wrote:

To view this discussion visit https://groups.google.com/d/msgid/ntsysadmin/CAHBr%2B%2Biw11j_cFwKVe9VH2jYwBfPUZbS46EDFn7FQvQ2izMh9g%40mail.gmail.com.