Doorgaan naar hoofdcontent

Using a gMSA with SQL Server

Prepare AD

First thing to do is adding a KdsRootKey by issuing this cmdlet on the DC:

Add-KdsRootKey –EffectiveTime ((get-date).addhours(-10))

Security Group

Create a new global security group. This group will contain all servers that will use gMSA. After adding the servers, they will require a reboot in order for their tokens to pick up membership in the group. This group will be given specific permissions to its members that will allow the member servers to retreive the gMSA password.

e.g. Create group nl.mgt.SQL2017DE.gs in OU batenict.corp/DTB Desktop/Groups/Security Groups/

gMSA

Now we can create the gMSA:

New-ADServiceAccount -Name nl.SQL2017DE -DNSHostName nl.SQL2017DE.batenict.corp -PrincipalsAllowedToRetrieveManagedPassword nl.mgt.SQL2017DE.gs

After creating the gMSA add some permissions:

Add a new SELF permission with grants on Read msDS-PrincipalName and Write msDS-PrincipalName

Service Dependencies

To prevent auto-start issues, add the following services to the existing DependOnService key: W32Time and NetLogon




Reacties

Populaire posts van deze blog

SQL Server Agent Jobs and Availability Groups

When using Availability Groups (or database mirroring for that matter), all instance level objects like logins, are not automatically distributed across all participating instances. The same goes for SQL Agent jobs. All of these objects need to be replicated somehow. Besides that, SQL Agent jobs must be “Primary or not”-aware. One simple solution to this problem, is to insert a step in each job, which should run first, with the following code. Set this step to continue with the next step on success, and to quit the job reporting success on a failure. IF ( SELECT ARS . role_desc FROM sys . dm_hadr_availability_replica_states AS ARS INNER JOIN sys . availability_groups AS AG ON ARS . group_id = AG . group_id AN D ARS . is_local = 1 ) <> 'PRIMARY' BEGIN --We're on the secondary node, throw an error THROW 50001 , 'Unable to execute job on secondary node' , 1 ; END

How can you drop the witness of a mirroring session when the partners have been dismantled?

The clean way of removing the witness is by issuing this command while connected to the principal: ALTER DATABASE foo SET WITNESS OFF; Sometimes however, both partners have been dismantled. In that case this will work: Start witness SQL Server in single user mode. Connect my instance with an admin connection. Delete orphaned entries in sys.syslogshippers. Restart SQL in multi user mode. Source: https://blogs.msdn.microsoft.com/ialonso/2011/12/13/how-can-you-drop-the-witness-of-a-mirroring-session-when-the-partners-have-been-dismantled/