I’ve started to setup a SLQ2019 Availability group on my PC at home. You see I upgraded it last year specifically for this purpose, and up to now I’ve been spinning up various NoSQL databases in Docker containers (Elastic Search, Cassandra, Kafka). I went as far as setting up a 5 node Cassandra cluster in Hyper-V amongst other various VM’s for testing with distributed database tech (I once thought 64gb of memory was a lot but now I keep an eye on it if I don’t stop previous tests).
Here, for this test I’ve got the following:
- A Windows Server 2019 running Active Directory and various AD services (DNS and GPO)
- Two Windows Server 2019 VMs running MSSQL2019
All VM’s have 2 virtual cores, the SQL boxes have 8gb ram whilst the DC has 2gb. Storage is provided by a 2tb M.2 drive delivering around 28k read/21k write IOPS.
I did follow a few different guides for this, so if you want the (much better) instructions the links can be found at the end. I wrote this because there were a few other things not included, the typical PowerShell things that throw errors if you’re just getting to grips with it.
Make sure your domain is running
It’s been a coupleof years since I last administered Active Directory, DNS and GPO, so it took me half a day to figure out some of the basics again. I’ll list out the sort of stuff I configured:
- Install AD, DNS and AD Web Services on my DC using default settings through out.
- Created a domain during the AD set-up process, using a .local suffix to not interfere with my business website of the same name (this VM environment is only ever going to be an internal playground
- In AD Users and Computers, created a new OU called People so we don’t pollute the Users OU
- Created a new user that I can use for my Domain Admin. I will get round to adding a noddy account, but right now I’m doing this as quick as I can
- Created a new Virtual Switch on an Internal network. I don’t need internet access for my servers. Configured my VM’s to use this switch.
- Back in the VM’s, hard configured all the IPV4 addresses in the network adaptor to:
- Used a 172.24.101.1-255 address range (255.255.255.0 subnet)
- Updated the Default Gateway to point to my DC
- Updated primary DNS to my DC and secondary to a random Google one (8.8.8.8)
- Rebooted the VM’s to register the new IP in Hyper-V, though this may just be a display issue on the networking tab but in either case it takes but a minute to do.
- Join the two servers to my Domain in System Properties. Always a pain to find, but it’s in
- Control Panel
- System and Security
- System
- Advanced system settings
- Computer Name tab
- Hit Change then change the Member of field. I took this opportunity to change my server names to something a bit more memorial (server1 and server2)
- You’ll need to reboot them again
- Once back up you can use the domain account created earlier to log in. You may want to prefix it with your domains NETBIOS name for the first time to ensure you login using the domain account.
You should now be able to ping the servers from the DC. You may want to go into the DNS manager and just make sure the Forward Lookup Zones for your domain shows your servers with the correct IP’s you set. You can also run in Powershell to test the domain connection:
|
1 |
Test-ComputerSecureChannel -Verbose |
This tests the communication between the server you’re in on the DC. So long as you get True then you’re good to go and can proceed with generating the gMSA’s.
Generating the gMSA
We’ve a few things to run in Powershell. These commands form part of my PS script I’ve got in Github, but I’ll paste them below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
# test communication between server and the DC: Test-ComputerSecureChannel -Verbose # you need this to continue with these commands Install-WindowsFeature RSAT-AD-PowerShell # This is on the target node (MSSQL in this case) # Import the AD tools into the current session Import-Module ActiveDirectory; # test to make sure we can get the domain name. This should return whatever you called your domain. # after this we can move on and fill out our config for the new gMSA (Get-ADDomain).DNSRoot; |
This next block is a Powershell script to generate the gMSAs. It goes hand in hand with a JSON file (just underneath) that acts as our config file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
Import-module activedirectory # change this if this isn't where your MSAs are held. # I've also run it without that at all and it still worked and put the accounts in the # correct OU #$ou = $("CN=Managed Service Accounts,") + (Get-ADDomain).DistinguishedName # need this for when we create the account $domain = (Get-ADDomain).DNSRoot # load up our config, edit this file to add in your servers/account $json = Get-Content 'config.json' | Out-String | ConvertFrom-Json $accountObj = $json | Select-Object -expand account $serverObj = $json | Select-Object -expand server # identify any gMSA accounts in the config file. $service_gMSA = $accountObj | Select-Object -expand service | Where-Object {$_.type -match "gMSA"} if ($serverObj) { $data = $serverObj $serverDetailsObj = $data.ForEach{ return (Get-ADComputer $_.name) } if ($service_gMSA) { $sub_data = $service_gMSA foreach ($sub_key in $sub_data) { "Checking for " + $sub_key.username + "..." Try { "...account already exists! See details below:" Get-ADServiceAccount -Identity $sub_key.username } Catch [Microsoft.ActiveDirectory.Management.ADIdentityNotFoundException] { "Creating new gMSA acount:" + $sub_key.username If($ou) { New-ADServiceAccount -Name $sub_key.username -Path "$ou" -DNSHostName "$sub_key.username.$domain" -PrincipalsAllowedToRetrieveManagedPassword $serverDetailsObj -TrustedForDelegation $true } else { New-ADServiceAccount -Name $sub_key.username -DNSHostName "$sub_key.username.$domain" -PrincipalsAllowedToRetrieveManagedPassword $serverDetailsObj -TrustedForDelegation $true } } } } } |
And now the config file:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
{ "account": { "service": [ { "name": "MSSQL-server", "type": "gMSA", "username": "MSSQL-server", "password": null }, { "name": "MSSQL-agent", "type": "gMSA", "username": "MSSQL-agent", "password": null } ] }, "server": [ { "name": "server1", "instance": "MSSQLSERVER" }, { "name": "server2", "instance": "MSSQLSERVER" } ] } |
This should create you as many accounts tied to as many servers as you add to the config file.
Now I didn’t have any issues with this. However, I was running these scripts from my SQL boxes as a domain admin. I had to do something back in the day to Enable Delegation that would allow certain boxes on my domain to create MSA accounts and configure SPNs for me. Essentially, there were two options to do this:
- Create the accounts from the DC, enabling the advanced view and doing something with the setSPN command (this was when I used normal MSA’s instead of group – I haven’t read into the differences with these but I think these are a bit easier to get going)
- Enable Delegation so that the server can ‘register’ a MSA with the DC. This requires elevated permissions, but these permissions can be revoked after you’ve generated the account.
There are really good guide out there if you Google ‘SQL and setSPN, MSA’ or words to that effect.
https://www.derekseaman.com/2018/09/sql-2017-always-on-ag-pt-3-service-accounts.html
https://medium.com/@jibinpb/create-group-managed-service-account-gmsa-using-powershell-626f8a7a4aa0
https://www.altaro.com/hyper-v/virtual-networking-configuration-best-practices/