Windows Docker Containers using GMSA to connect to SQL Server – Part 1

Windows Containers do not ship with Active Directory support and due to their nature can’t (yet) act as a full-fledged domain joined objects, but a certain level of Active Directory functionality can be supported through the use of Globally Managed Service Accounts (GMSA).

Windows Containers cannot be domain-joined, they can also take advantage of Active Directory domain identities similar to when a device is realm-joined. With Windows Server 2012 R2 domain controllers, we introduced a new domain account called a group Managed Service Account (GMSA) which was designed to be shared by services.

https://blogs.technet.microsoft.com/askpfeplat/2012/12/16/windows-server-2012-group-managed-service-accounts/

https://technet.microsoft.com/en-us/library/hh831782(v=ws.11).aspx

We can authenticate to Active Directory resources from Windows container which is not part of your domain. For this to work certain prerequisites needs to be met.

For once your container hosts shall be part of Active Directory and you shall be able to utilize Group Managed Service Accounts.
https://technet.microsoft.com/en-us/library/hh831782%28v=ws.11%29.aspx?f=255&MSPPError=-2147217396

The following steps needed for communicate Windows container with on premise SQL server using GMSA.
Environments are used and described for this post.

1. Active directory Domain Controller installed on server CloudIQDC1.
   • OS – Windows Server 2012/2016.
   • The domain name is cloudiq.local
2. Below are the Domain members (Computers) joined in DC
   • CIQ-2012R2-DEV
   • CIQSQL2012
   • CIQ-WIN2016-DKR
   • cloud-2016
3. SQL server installed on CIQSQL2012. This will be used for GMSA testing.
   • OS – Windows 2012
4. cloud-2016 will be used to test GSMA connection.
   • This is the container host we are using to connect on premise SQL server using GMSA account.

   

5. The GMSA account name is “container_gsma”. We will create this and configure it.

Step 1: Create the KDS Root Key

1. We can generate this only once per domain.
2. This is used by the KDS service on DCs (along with other information) to generate passwords.
3. Login to domain controller.
4. Open PowerShell and execute the below.

                           Import-module ActiveDirectory
       Add-KdsRootKey –EffectiveTime ((get-date).addhours(-10));5.
        

   

5. Verify your key using the below command.

           Get-KdsRootKey
        

Step 2: Create GMSA account

1. Create GSMA account using the below command.

           
       New-ADServiceAccount -Name container_gmsa -DNSHostName cloudiq.local 
       -PrincipalsAllowedToRetrieveManagedPassword "Domain Controllers", "domain admins", 
       "CN=Container Hosts,CN=Builtin, DC=cloudiq, DC=local" -KerberosEncryptionType RC4, AES128, AES256
        

   

2. Use below command to verify the created GMSA account.

           Get-ADServiceAccount -Identity container_gmsa 

3. If everything works as expected, you’ll notice a new gMSA object in your domain’s Managed Service Account.
   

   

Step 3: Add GMSA account to Servers where we are going to use.

1. Open the Active directory Admin Center.
2. Select the container_gmsa account and click on properties.
   

   

3. Select the security and click on add.
   

   

4. Select only Computers
   

   

5. Select Computers you want to use GMSA. In our case we need to add CIQSQL2012 and cloud-2016.

   

   

6. Reboot Domain controller first to these changes take effect.
   

   

7. Reboot the computers who will be using GMSA. In our case we need to reboot CIQSQL2012 and cloud-2016.
8. After reboots, login to Domain controller. Execute the below command.

           
       Set-ADServiceAccount -Identity container_gmsa -PrincipalsAllowedToRetrieveManagedPassword 
       CloudIQDC1$,cloud-2016$, CIQSQL2012$
        

   

Step 4: Install GMSA Account on Servers

1. Login to the system where the GMSA account which will use it. In our case login to cloud-2016. This is the container host we are using to connect on premise SQL server using GMSA account.
2. Execute the below command if AD features are not available.

           
       Enable-WindowsOptionalFeature -FeatureName ActiveDirectory-Powershell -online -all
        

3. Execute the below commands

           Get-ADServiceAccount -Identity container_gmsa
       Install-ADServiceAccount -Identity container_gmsa
       Test-AdServiceAccount -Identity container_gmsa 

   

4. If everything is working as expected then you need to create credential spec file which need passed to docker during container creation to utilize this service account. Run the below commands to downloads module which will create this file from Microsoft github account and will create a JSON file containing required data.

           
       Invoke-WebRequest "https://raw.githubusercontent.com/Microsoft/Virtualization-Documentation/live/windows-server-container-tools/ServiceAccounts/CredentialSpec.psm1" 
       -UseBasicParsing -OutFile $env:TEMP\cred.psm1
       Import-Module $env:temp\cred.psm1
       New-CredentialSpec -Name Gmsa -AccountName container_gmsa
       #This will return location and name of JSON file
       Get-CredentialSpec 

   

Step 5: SQL Server Configuration to allow GMSA

1. On SQL server create login for GMSA account and add it to “Sysadmin” role. Based on your on premise DB access, you can add suitable roles.

           CREATE LOGIN [cloudiq\container_gmsa$] FROM WINDOWS
       sp_addsrvRolemember "cloudiq\container_gmsa$", "sysadmin"