SQL Server – Michał's Blog

gMSA and Docker – Lessons Learnt

In the last two posts (here and here) I have documented how I use gMSAs to connect services running in docker containers on Windows to SQL Server using the domain authentication. In the end it was very simple, but there are things I wish I knew when I started. It would save me a lot of time. Here is an attempt to document the lessons learnt.

Versions are important!

While in the end I was able to make it work on Windows Server 2016, 1803, 2019 and 1809 I wasted some time trying to make it work with docker 17.06. Unsuccessfully. Docker 18.09.1 and 18.09.2 worked every time.

There are some reports of intermittent problems with specific OS updates breaking stuff, like the one here but I wasn’t able to reproduce it. I wonder if the updates changes something else that it causing problems, in other words is it the problem with the update itself or the update process?

The Set-AdServiceAccount

From the beginning I set to try the gMSA authentication on multiple VMs following blog posts which all included some use of the Set-AdServiceAccount powershell command (from `RSAT-AD-PowerShell). I could not make it work on more than one VM at a time. I thought I was going mad! The problem (and the clue) is in the name. Set. It is not add, not modify. So when I was doing something like this

Set-AdServiceAccount -Identity MyService `
  -PrincipalsAllowedToRetrieveManagedPasswords DH2019A$

it was setting the principals allowed to retrieve the managed passwords for MyService to DH2019A VM. As expected. But not as expected removing the privilege from all the other VMs I granted that permission before. With no warnings.

It is probably the most worrying part about using gMSAs for the service authentication in production, as I plan to do it. All it will take is one sysadmin to run a command like that to break all the services, potentially on all docker hosts. To mitigate we have decided to grant the permissions through a domain group to which we will add docker hosts. That way there should be no need to run this command when scaling out.

Misleading Get- and Test-AdServiceAccount

Understanding the above problem with Set-AdServiceAccount was made much worse, by my misunderstanding how Get-AdServiceAccount and `Test-AdServiceAccount work.

If you are a domain admin the Get-AdServiceAccount will always return details of the gMSA if it exists. So it is of no use to check if the specific gMSA can be used on a given host.

If the gMSA was previously installed the Test-AdServiceAccount will return true regardless if the host account has permissions to retrieve the password or not. That permission is necessary for the gMSA authentication to work.

So with that in mind neither command is fit for checking if a specific host has permissions it needs to use a gMSA. I was not able to find anything better than attempting to install it again with Install-AdServiceAccount. It will either install it again, or display error message indicating that the computer has no permissions to retrieve the password.

Remove-AdServiceAccount

This does not remove previously installed gMSA from the local host. It removes the gMSA from the domain!

The SSPI context error.

If you try to use domain authentication from the service running on a docker host which has no permissions to retrieve the gMSA password you will get fairly generic error tell you that the SSPI context couldn’t be created.

There are scores of blog posts and msdn documents explaining how to troubleshoot many SSPI context errors. Not a single one I found mentions any problems with gMSA. I have learnt a lot about SSPI and how it really works, just to eventually realise that everything is fine, and I have to look for problems somewhere else.

There is no localhost

When you run a standalone container you can access it from the same host on the localhost. By default a nat network is used and it allows communication on the hosts IP. When moving to the swarm mode (using docker service create not just docker create) by default the ingress network is used and the localhost is not available. You have to use the public IP address of the docker swarm. There is a lot of blog posts how to define your own overlay or bridge networks. I suppose they all work on Linux, but on windows when using an overlay network you cannot use host IPs (so no localhost) and you cannot create bridge networks at all.

PS C:\> docker network create -d bridge bnet
Error response from daemon: could not find plugin bridge in v1 plugin registry: plugin not found

I was able to make it work when publishing the port directly on the host using --publish published=8001,target=80,mode=host instead of the shorter -p 8001:80 (ports obviously may be different) but I don’t think that’s a configuration I’d be using so… I simply gave up. I use public IP and everything works.

Security considerations

To install a gMSA on a host which has permission to read the gMSA’s password you don’t need to have any extra permissions. It appears that anybody with access to PowerShell on the host can do Install-AdServiceAccount -Identity MyService. There are no restrictions which credential spec file can be sued for which servce either. This means that if somebody has access to the docker host they can create a new service using any gMSA to which the host itself has permissions.

gMSA name lenght limit

The Group Managed Service Account’s name is limited to 15 characters. Not a big deal, but it messed up a carefully agreed naming strategy and in the end I have vowel-less service names.

New-CredentialSpec silently overwrites existing files.

Active Directory, Windows Containers in Swarm Mode and SQL Server

In my previous post I have explained how I was able to connect from windows containers running on docker to a SQL Server cluster on a network using domain authentication (with gMSAs) rather than SA logins and passwords.

gMSAs in docker swarm mode

After I got the containers using Group Managed Service Accounts working on a single Docker host I went on to try the same in the swarm mode. My plan was to simply replace docker run -d part of the command creating the container with docker service create, but it turns out that it is not that simple, especially if you don’t have a lot of experience with swarm mode. It is also worth noting, that I had a lower success rate than when I was experimenting with standalone containers. I was able to make it work on all the same Windows versions (2016, 1803, 2019, 1809) but only when using Docker 18.09 and not on the 17.06 which was on the image I used for 1803 tests).

Demo setup

Similarily to the previous post I have tested this on a range of operating systems and docker versions, but what I want to show here is how it worked on Windows 2019 and Docker 18.09.

To make it a bit more exciting (and because of how Docker Swarm works) this time I will be testing the service from a web browser. To start with it does’t work. That is because there is no service listening on port 8101.

Creating a service

To create a service docker service create command is used. When compared to docker create some parameters are different, for example there is no --security-opt used in the previous post and instead --credential-spec is used. But first things first. Let’s just create a service using michalporeba/sqlgmsatest:1809nano image with minimal configuration and see what happens.

docker service create -p 8101:80 michalporeba/sqlgmsatest:1809nano

The -p 8101:80 makes the service available on the 8101 port using the default ingress network. No errors, the service is running, it is converged, so let’s try to connect to it!

And here is the first surprise. The localhost doesn’t work. That’s a swarm thing and although it is possible to publish ports in host mode it is not how I would be running in production, so I will just open the ports and connect to the service externally using a web browser.

OK, so the api/info call was successful. The service from michalporeba/sqlgmsatest:1809nano image is running and responding. So the next task is to use it to query theTestDBdatabase on my test instanceDB.sqlgmsa.local`.

Adding the gMSA

Not authorized! But who? The NT AUTHORITY\ANONYMOUS LOGON. That is because despite the docker host being member of the sqlgmsa.local domain, the container running the service is not. To fix it, exactly as in the case of standalone container a Group Managed Service Account has to be created, installed and a credential file created.

# Create gMSA
New-AdServiceAccount -Name MyService -DNSHostName sqlgmsa.local `
  -PrincipalsAllowedToRetrieveManagedPassword "Domain Controllers", "Domain Admins", "CN=DockerHosts,CN=Computers,DC=sqlgmsa,DC=local" `
  -KerberosEncryptionType AES128, AES256

# Install it
Install-AdServiceAccount -Identity MyService

# Import the module to manage Credential Specs
Import-Module .\PsModules\CredentialSpec.psm1

# And create a spec file for MyService
New-CredentialSpec -Name MyService -AccountName MyService `
-Domain (Get-AdDomain -Current LocalComputer)

Consistency is everything, isn’t it? -Name, -Identity, -AccountName on those commands above refer to the same thing, the gMSA name and have to match. The -Name parameter on the New-CredentialSpec command is used to control the name of the json file containing the credential spec. The filename can be anything, and it doesn’t need to match the account name, but I find it easier if it does. The existing credential spec files can be found in C:\ProgramData\docker\CredentialSpecs\ or by using Get-CredentialSpec command from the CredentialSpec.psm1 module.

The next step is to use the newly created credential spec file when creating the service. The --security-opt is not supported when created a service and --credential-spec has to be used instead.

docker service create -p 8102:80 `
  --credential-spec file://MyService.json `
  michalporeba/sqlgmsatest:1809nano

The new service now runs on port 8102 and should use the new MyService identity. Let’s see.

Almost there! Login failed for user SQLGMSA\MyService$ That’s good, that means the correct identity has been picked up, so the last thing to do is to create the login on the SQL Server.

And now, as if by magic

The test web service, written in C#, using .net core is hosted in a Docker container running on windows host,and queries a SQL Server database using domain authentication.

Active Directory, Windows Containers and SQL Server

The problem

Everything appears to be in containers nowadays, even the SQL Server. But still there are mixed environments, people and companies wanting to try containers without going all in. So I was wondering how practical would it be to have .net core services on docker, running in Windows containers connecting to an external, old fashioned SQL Server instance? Also, as it is all in a Windows domain, I’d like to use domain authentication so I don’t have to worry about managing passwords.

Simple isn’t it? Well, it turns not that simple as not everything is on the domain. The SQL Server is, the docker hosts are, but the containers are not.

Additionally there are differences depending on whether you want to run as an independent container, or in docker swarm mode. This blog post focuses on standalone containers, and the swarm mode is covered in the follow-up post.

The quick answer

The good news is that it is not an unreasonable requirement and it has been done before. The solution is to use Group Managed Service Accounts (gMSA) and Credential Spec Files. A number of people have already documented their efforts. Some were more successful than others.

My story

My problem was that I wasn’t able to make it work just by following any single write-up. In fact, for a few days, I was not able to get it going at all. But eventually it happened and here is a step by step description of how I made it work on Windows Server 2016, 1803, 1809 and 2019 as the host OS and 2016, 1803 and 1809 in full and nano options as the container base image. Generally, it is very simple once you know what to do, and more importantly what not to do (more about it later).

Test setup

To test it I have set up a virtual lab environment on Azure with 6 VMs

DC – Windows Server 2016 Datacenter acting as a domain controller
DB – Windows Server 2016 Datacenter with SQL Server 2017 Developer edition installed
DH2016A – Windows Server 2016 Datacenter with Containers (Docker version 18.09.2)
DH1803A – Windows Server 1803 with Containers (Docker version 17.06.2-ee-18)
DH1809A – Windows Server 1809 with Containers (Docker version 18.09.0)
DH2019A – Windows Server 2019 with Containers (Docker version 18.09.1)

I have created a sqlgmsa.local domain and joined all the VMs to it. SQL Server was run using SQLGMSA\SqlServer Managed Service Account without any special permissions.

In the domain I have 2 service accounts SQLGMSA\ServiceA and SQLGMSA\ServiceB. Both have logins on the SQL Server instance. I will be setting some of my containers to connect to the SQL Server as ServiceA and some as ServiceB.

Initially I tested the connectivity from containers build with full base images (standard mrc.microsoft.com/windows/servercore) and using dbatools module to run queries from them to the DB.sqlgmsa.local server. To be able to test nano based images I created two test images containing simple .net core WebAPI service written in C# with two public methods. Calling api/info you can check if the service is running, what system is it running on. Calling api/query/ attempts to open connection to the specified database (or master if the db parameter is not provided) and returns information about the database, the original login and the current user. A simple query

select 
     @@version SqlServer
    ,db_name()  [Database]
    ,current_user CurrentUser
    ,original_login() OriginalLogin
for json path

The test images are available on docker hub and the source code here on github.

PowerShell and AD

In the example I am using PowerShell to manage my active directory. If the commands I use don’t work for you you may be missing the AD modules. To install them add the RSAT-AD-PowerShell windows feature by executing this PowerShell command

Add-WindowsFeature RSAT-AD-PowerShell

Group Managed Service Accounts (gMSA)

Managed Service Accounts where introduced some time ago to reduce overhead associated with managing passwords for service accounts. The Group Managed Service Accounts solve the same problem but unlike MSAs gMSAs can be used across multiple computers.

To start using gMSAs on a domain a KDS Root Key has to be created first. It is the key with which passwords shared between the computers on the domain are protected. If your domain has other MSAs already you will not need to do it again.

To create a KDS Root Key Run I used this PowerShell command on the domain controller

Add-KdsRootKey -EffectiveTime (Get-Date).AddHours(-10)

and then to verify that it has been created

Get-KdsRootKey

Now to create the test service accounts I used the following commands. I was doing it on the DC, but with the right permissions it should be possible to do from any computer on the domain.

New-AdServiceAccount -Name ServiceA -DNSHostName sqlgmsa.local `
   -PrincipalsAllowedToRetrieveManagedPassword "Domain Controllers", "Domain Admins", "CN=DockerHosts,CN=Computers,DC=sqlgmsa,DC=local" `
   -KerberosEncryptionType AES128, AES256

New-AdServiceAccount -Name ServiceB -DNSHostName sqlgmsa.local `
   -PrincipalsAllowedToRetrieveManagedPassword "Domain Controllers", "Domain Admins", "CN=DockerHosts,CN=Computers,DC=sqlgmsa,DC=local" `
   -KerberosEncryptionType AES128, AES256

Where ServiceA and ServiceB are the names of the accounts and "CN=DockerHosts,CN=Computers,DC=sqlgmsa,DC=local" is the distinguished name of the group I have created for the docker hosts.

If you don’t know the exact distinguished name running this command can help

Get-AdGroup -filter { name -like "yourgroupname" }

Now on every docker host all the specific service accounts (2 in my test case) have to be installed so that the host OS can access them.

Install-AdServiceAccount -Identity ServiceA
Install-AdServiceAccount -Identity ServiceB

If there is an error message like this, it means the permissions were not set correctly
Install-AdServiceAccount : Cannot install service account. Error Message: ‘{Access Denied}

Credential Spec file

Docker Credential Spec Files have been created specifically to solve the problem of passing gMSA to containers. They are plain json files with information about the service account. It is possible to create the files manually, but there is module for it. It is documented here but here is a short instruction of how to create get and import the module.

This part needs to be done on every docker host.

# 1. Set TLS1.2 support from PowerShell so the module can be downloaded from github. 
PS C:\Tmp> [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# 2. Download the psm1 file using Invoke-WebRequest
PS C:\Tmp> Invoke-WebRequest "https://raw.githubusercontent.com/MicrosoftDocs/Virtualization-Documentation/live/windows-server-container-tools/ServiceAccounts/CredentialSpec.psm1" -OutFile "CredentialSpec.psm1"

# 3. Import the module
PS C:\Tmp> Import-Module .\CredentialSpec.psm1

With CredentialSpec module imported for each gMSA a credential spec file has to be created.

PS C:\> New-CredentialSpec -Name ServiceA`
  -AccountName ServiceA `
  -Domain $(Get-AdDomain -Current LocalComputer)

PS C:\> New-CredentialSpec -Name ServiceB`
  -AccountName ServiceA `
  -Domain $(Get-AdDomain -Current LocalComputer)

The list of existing files can be obtained with

PS C:\> Get-CredentialSpec

Name     Path
----     ----
ServiceA C:\ProgramData\docker\CredentialSpecs\ServiceA.json
ServiceB C:\ProgramData\docker\CredentialSpecs\ServiceB.json

And finally, run the containers passing the credential spec files with the --security-opt parameter. (This is an example from DH2019A using the 1809 nano base image).

docker run -d -it -p 8001:80 `
   --security-opt "credentialspec=file://ServiceA.json" `
   --name ServiceA `
   michalporeba/sqlgmsatest:1809nano

docker run -d -it -p 8002:80 `
   --security-opt "credentialspec=file://ServiceB.json" `
   --name ServiceB `
   michalporeba/sqlgmsatest:1809nano

The proof is in the pudding

After checking both containers are running with docker ps I can start testing. As the test is not focused on anything else but domain authentication I didn’t open any ports to the lab, and all I was doing was to either connect to the container and use dbatools to execute a query on the db server, or from the docker host connecting to the service listening on the published port. Here are the example calls using Invoke-WebRequest on DH2019A.

PS C:\> $env:ComputerName
DH2019A
PS C:\> docker start ServiceA
ServiceA
PS C:\> docker start ServiceB
ServiceB
PS C:\> docker ps
CONTAINER ID        IMAGE                               COMMAND                  CREATED             STATUS              PORTS                           NAMES
c6382bb7d816        michalporeba/sqlgmsatest:1809nano   "dotnet TestService.…"   2 days ago          Up 4 seconds        443/tcp, 0.0.0.0:8002->80/tcp   ServiceB
02ece189cb74        michalporeba/sqlgmsatest:1809nano   "dotnet TestService.…"   2 days ago          Up 8 seconds        443/tcp, 0.0.0.0:8001->80/tcp   ServiceA
PS C:\> # Service A
PS C:\> (Invoke-WebRequest -UseBasicParsing http://localhost:8001/api/info).Content
["OS:  Microsoft Windows 10.0.17763 ","Framework: .NET Core 4.6.27317.07"]
PS C:\> (Invoke-WebRequest -UseBasicParsing http://localhost:8001/api/query/DB.sqlgmsa.local).Content
[{"Database":"master","CurrentUser":"guest","OriginalLogin":"SQLGMSA\\ServiceA$"}]
PS C:\> # Service B
PS C:\> (Invoke-WebRequest -UseBasicParsing http://localhost:8002/api/info).Content
["OS:  Microsoft Windows 10.0.17763 ","Framework: .NET Core 4.6.27317.07"]
PS C:\> (Invoke-WebRequest -UseBasicParsing http://localhost:8002/api/query/DB.sqlgmsa.local).Content
[{"Database":"master","CurrentUser":"guest","OriginalLogin":"SQLGMSA\\ServiceB$"}]
PS C:\>

Conclusions

The above setup really boils down to 5 steps. If you want to use windows authentication from windows containers on docker to a SQL Server instance (or a cluster you have to

Create gMSAs for your services
Create logins for the service accounts on the SQL Server
Install gMSAs on the docker hosts
Create credential spec files
Create containers with --security-opt parameter pointing to the credential spec file.

That’s it. In my case I was able to make it work (for standalone containers, not in swarm mode) on different OS versions (2016, 1803, 1809, 2019) using full and nano base images and using docker 17.06 and 18.09. However, there can be surprises and pulling hair. In the week I spent trying to figure it out I had a number of moments when I thought I’ve got it, just to realise that what worked a moment ago, doesn’t any more.

The biggest lessons where

use AD Groups for managing access to gMSAs rather than individual computer accounts,
be very careful with Set-AdServiceAccount which I have seen in some of the posts out there,
SSPI context errors is not what it seems, and can be very annoying

More details about the lessons learnt can be found in the follow up post.

Trying to do the same but in a service run on Docker in swarm mode is similar, but not exactly the same. I have described it here.

This post is long enough as it is, so I will not go into the details of those lessons learnt here but instead include them in a follow up to which I will link here later.