Hi

So the documentation (https://technet.microsoft.com/en-us/library/jj128431.aspx) for creating gMSAs says that the parameter "-PrincipalsAllowedToRetrieveManagedPassword" should restrict the ability of using the gMSA to the machines that are part of the security groups given in the parameter. E.g.

New-ADServiceAccount -name dev-service -DNSHostName dev-service -PrincipalsAllowedToRetrieveManagedPassword gMSA-dev-service-allowed-hosts

should, as I understand it,  allow only the machines that are part of the security group "gMSA-dev-service-allowed-hosts" to access the password of the the account dev-service thereby limiting the machines that can use the account.

My problem is that I can not get it to work that way. Even on a machine that is not a member of "gMSA-dev-service-allowed-hosts", the account can be used without problem.

Did I misunderstand the meaning of -PrincipalsAllowedToRetrieveManagedPassword ?

Thanks

Best,

Deniz

No, you did not. It is correct. The following PowerShell command should create a gMSA (Group Managed Service) Account, and make sure the gMSA's password can only be retreived by the Security Group "SQL Servers".

New-ADServiceAccount -Name sql_gmsa -DNSHostName sql_gmsa.yourdomain.local -PrincipalsAllowedToRetrieveManagedPassword "SQL Servers"

Note you can also use Computer Accounts instead of Security Groups. And the -DNSHostName value should be a FQDN. I see you used single NetBIOS name. Correct it, just to make sure.

Hi,

How is it going? I agree with Boudewijn. If you need further help regarding the question, please don't hesitate to let us know.

Best regards,

Frank Shen

Hey thanks for the interest in my problem.

I decided to do some experiments. I created a small test service that simply accesses a local sql server db and retrieves the content of a small table and writes it to a file.

I created a security group called testgroup. The server is a member of this group.

I then created 4 gMSAs with the following command

New-ADServiceAccount name testuser1  -DNSHostName testuser1.domain.local

New-ADServiceAccount name testuser2  -DNSHostName testuser2.domain.local PrincipalsAllowedToRetrieveManagedPassword testgroup

New-ADServiceAccount name testuser3  -DNSHostName testuser3

New-ADServiceAccount name testuser4  -DNSHostName testuser4.domain.local

Only when setting the service logon to testuser2 does the service run. With testuser1, testuser3 and testuser4  I get the following error message while trying to start the service: Windows could not start the Test Service service on Local Computer. Error 1069: The service did not start due to a login failure

Is it normal expected behavior that only testuser2 should work? Is a gMSA without the -PrincipalsAllowedToRetrieveManagedPassword argument useless?

Another issue I have is creating a gMSA where the PrincipalsAllowedToRetrieveManagedPassword is a single computer account:

New-ADServiceAccount -name testuser5 -DNSHostName testuser5.domain.local -PrincipalsAllowedToRetr

ieveManagedPassword pr-server-s14-01

gives

New-ADServiceAccount : Identity info provided in the extended attribute: 'PrincipalsAllowedToRetrieveManagedPassword' could not be resolved. Reason: 'Cannot find an object with identity: 'pr-server-s14-01' under: 'DC=domain,DC=local'.'.

At line:1 char:1

+ New-ADServiceAccount -name testuser5 -DNSHostName testuser5.domain.local -Princip ...

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : InvalidData: (CN=testuser5,CN...=domain,DC=local:String) [New-ADServiceAccount], ADIdentityResolutionException

    + FullyQualifiedErrorId : ActiveDirectoryCmdlet:Microsoft.ActiveDirectory.Management.ADIdentityResolutionException

   ,Microsoft.ActiveDirectory.Management.Commands.NewADServiceAccount

 

What am I missing here?

• Edited by deniz_ Monday, May 25, 2015 1:25 PM

Hi,

Before doing that just read the below information.

1. GMSA are not yet available, are not yet supported for SQL Server.  gMSA exist and are available and supported in Windows Server 2012 and higher.  SQL does not support them , but from an OS perspective, they exist and are supported.    

3. MSA are supported from Windows 2008 on for specific applications and for specific purposes.

4. MSA are supported from SQL 2012 on for use running SQL service accounts (all SQL Services) where they are confined to a SINGLE machine.  This means that this account can NOT be used across multiple machines.

http://blogs.msdn.com/b/sqlosteam/archive/2014/02/19/msa-accounts-used-with-sql.aspx

https://social.technet.microsoft.com/Forums/en-US/de881f57-28a4-4af6-99a8-9fe5157a50e0/installadserviceaccount-an-unspecified-error-has-occurred?forum=winserverDS

Hey,

I am using SQL Server 2014 Standalone. The server is installed with local accounts.  I only use gMSA for logins. It works for one of my test cases (testuser2).

Do you have any specific references for gMSA's and SQL Server 2014? SQL Server 2014 was released after one of the post you linked to, and the other is vague about specific support.

Answer by Sofiya Li

Thanks.

• Edited by deniz_ Monday, May 25, 2015 2:50 PM

hi,

Thanks for replying I guess this is still under review no official document yet for this. But there is some information available here.

In SQL Server 2014 official document, we can configure the accounts to run as a built-in account, such as Local System, Local Service, or Network Service, or a non-domain account, you must use certificates for endpoint authentication. If your service accounts are using domain accounts in the same domain, you can choose to grant CONNECT access for each service account on all the replica locations or you can use certificates. It is not introduced Group Managed Service Accounts in AlwaysOn Availability Groups.

https://social.technet.microsoft.com/Forums/sqlserver/en-US/9d8dc0eb-7932-4fe8-93e0-d9051f1da8f5/does-sql-server-2014-support-group-managed-service-accounts-for-an-alwayson-availability-group

Hi,

Hope your query is answered.

Hello,

Hope your query is answered?

Hello Deniz,

Hope your query is answered.