AD(Active Directory) authentication for SQL Containers on Azure Kubernetes Service (AKS)

In this blog today, let's configure AD (Active Directory) authentication for SQL Server containers running on Azure Kubernetes Service (AKS). Same steps can be followed for SQL Server containers deployed on other kubernetes environments as well.

Pre-requisites:

1. Create an Azure Vnet and add a virtual machine to the network as a domain controller.
2. We will use another machine (in this case, an Ubuntu machine) that is added to the above-mentioned network and joined to the domain to create the required keytab file for SQL Server container.
3. Then, create an Azure kubernetes service cluster with network plugin set to Azure CNI and connected to the Azure vnet created in the preceding steps.

Current Environment details & Setup:

In this environment:-

1. I've created an Azure Vnet (aksvnet2) and a VM (kadvm.contoso.com) as the domain controller with domain named: CONTOSO.COM.
2. I then created an Azure kubernetes service cluster (akscluster2) with the network type plugin set to Azure CNI, resulting in the cluster being part of the Azure Vnet(aksvnet2). To create an Azure kubernetes service using the Azure CNI plugin, refer to this document. Once the cluster is created you would see the network plugin showing as Azure CNI as shown below, please note that the cluster is not part of the domain but only connected to the network.
3. I also added another Azure VM Ubuntu 20.04 machine (Ubu11.contoso.com) to the network and joined it to the domain, this machine will be used to generate the keytabs.

Now, we log in to our Azure VM machine (ubu11.contoso.com), which was previously created and added to the domain. If you need assistance creating the VM and adding it to the domain refer to this article. We'll use this machine to generate the service keytab, which we'll then copy to the container required for AD authentication setup.

Preparing for the deployment:

Before we can create the keytab we must first plan the container hostname, add the dns host entry for the container hostname and specify the internal load balancer IP address for the DNS host entry. The following are the configuration details for this blog:

1. The container hostname will be: sqlinst1-0 ( I am going to deploy SQL container as statefulset).
2. For the internal load balancer we will use the IP address that is available on the network, in this case the IP address from the vnet that I will be using is: 10.1.0.230.
3. The DNS host entry that I added to the domain controller before deploying the SQL Server container is shown below:
4. The AD account that I will use in the keytab is : "sqluser".

With the above information, we are now ready to create the keytab on the Ubuntu machine. I download & install the adutil client on the Ubu11.contoso.com server and then run following commands:

1. We first proceed to create the user "sqluser" with the command:

   adutil user create --name sqluser --distname CN=sqluser,CN=Users,DC=CONTOSO,DC=COM --password 'P@ssw0rd!'​

2. I also enable the AES protocol for the sqluser, which is needed when you don't want to use older protocols like RC4.
3. Run the following command to create the spns:

   adutil spn addauto -n sqluser -s MSSQLSvc -H sqlinst1-0.contoso.com -p 1433​​

4. We are now ready to create the keytab using the commands listed below:

   adutil keytab createauto -k ~/container/mssql.keytab -p 1433 -H sqlinst1-0.contoso.com --password 'P@ssw0rd!' -s MSSQLSvc
   adutil keytab create -k ~/container/mssql.keytab -p sqluser --password 'P@ssw0rd!'

With this, the keytab is ready and here is how it looks, note for this example I used RC4 protocol but for production environment you should not use RC4.

Time to deploy SQL Server container:

With all of the preliminary work completed, we are now ready to deploy the SQL Server container. I've included all of the deployment files below, along with explanations of the various parameters used:

1. First, we create the krb5.conf configmap that will be mounted on the container and contains the relevant realm details so that the container can communicate with the domain. In my case, the domain is CONTOSO.COM, but the details will vary depending on your environment. The file is saved as krb5conf.yaml, so when I deploy this configmap, I run the following command:

   kind: ConfigMap
   apiVersion: v1
   metadata:
     name: krb5-conf
   data:
     krb5.conf: |
       [libdefaults]
       default_realm = CONTOSO.COM
   
       [realms]
       CONTOSO.COM = {
         kdc = kadvm.contoso.com
         admin_server = kadvm.contoso.com
         default_domain = CONTOSO.COM
       }
   
       [domain_realm]
       contoso.com = CONTOSO.COM
       .contoso.com = CONTOSO.COM​

    kubectl apply -f "path to file>/krb5conf.yaml"

2. We then create the mssql.conf configmap and like the name suggests this has the mssql.conf details that needs to be mounted. The file is saved as mssql.yaml and the command I run to create the configmap is :

   kind: ConfigMap
   apiVersion: v1
   metadata:
     name: mssql
   data:
     mssql.conf: |
       [EULA]
       accepteula = Y
       accepteulaml = Y
   
       [coredump]
       captureminiandfull = true
       coredumptype = full
   
       [language]
       lcid = 1033
   
       [filelocation]
       defaultdatadir = /var/opt/mssql/userdata
       defaultlogdir = /var/opt/mssql/userlog
   
       [network]
       kerberoskeytabfile = /var/opt/mssql/secrets/mssql.keytab
       privilegedadaccount = sqluser​

   kubectl apply -f "<path to file>/mssql.yaml"

Please note:I am adding the network related configuration details like the keytab location on the container and the privilagedadaccount to be used for SQL Server.

1. Lets create the secret to store SA password and the command I use is shown below, please ensure that you change this to the SA password that you want to use

    kubectl create secret generic mssql --from-literal=SA_PASSWORD="MyC0m9l&xP@ssw0rd"​

2. Using the yaml file below, we can now deploy SQL Server containers:

   kind: StorageClass
   apiVersion: storage.k8s.io/v1
   metadata:
        name: azure-disk
   provisioner: kubernetes.io/azure-disk
   parameters:
     storageaccounttype: Standard_LRS
     kind: Managed
   ---
   apiVersion: apps/v1
   kind: StatefulSet
   metadata:
    name: sqlinst1
    labels:
     app: sqlinst1
   spec:
    serviceName: "sqlinst1"
    replicas: 1
    selector:
     matchLabels:
      app: sqlinst1
    template:
     metadata:
      labels:
       app: sqlinst1
     spec:
      securityContext:
        fsGroup: 10001
      containers:
       - name: sqlinst1
         command:
         - /bin/bash
         - -c
         - cp /var/opt/config/mssql.conf /var/opt/mssql/mssql.conf && /opt/mssql/bin/sqlservr
         image: mcr.microsoft.com/mssql/server:2019-latest
         env:
         - name: ACCEPT_EULA
           value: "Y" 
         - name: MSSQL_ENABLE_HADR
           value: "1"
         - name: SA_PASSWORD
           valueFrom:
             secretKeyRef:
               name: mssql
               key: SA_PASSWORD
         volumeMounts:
         - name: mssql
           mountPath: "/var/opt/mssql"
         - name: tempdb
           mountPath: "/var/opt/mssql/tempdb"
         - name: data
           mountPath: "/var/opt/mssql/userdata"
         - name: log
           mountPath: "/var/opt/mssql/userlog"
         - name: mssql-config-volume
           mountPath: /var/opt/config
         - name: krb5-conf
           mountPath: /etc/krb5.conf
           subPath: krb5.conf
      hostAliases:
       - ip: "10.1.0.226"
         hostnames:
          - "kadvm.contoso.com"
          - "contoso.com"
          - "contoso"
      dnsPolicy: "None"
      dnsConfig:
        nameservers:
          - 10.1.0.226
        searches:
          - "contoso.com"
          - "com"
      volumes:        
        - name: mssql-config-volume
          configMap:
           name: mssql
        - name: krb5-conf
          configMap:
           name: krb5-conf
    volumeClaimTemplates:
     - metadata:
         name: mssql
       spec:
         accessModes:
           - ReadWriteOnce
         resources:
           requests:
             storage: 8Gi
     - metadata:
         name: tempdb
       spec:
         accessModes:
           - ReadWriteOnce
         resources:
           requests:
             storage: 8Gi
     - metadata:
         name: data
       spec:
         accessModes:
           - ReadWriteOnce
         resources:
           requests:
             storage: 8Gi
     - metadata:
         name: log
       spec:
         accessModes:
           - ReadWriteOnce
         resources:
           requests:
             storage: 8Gi
   ---
   apiVersion: v1
   kind: Service
   metadata:
     name: sqlinst1-0
     annotations:
       service.beta.kubernetes.io/azure-load-balancer-internal: "true"
   spec:
     type: LoadBalancer
     loadBalancerIP: 10.1.0.230
     ports:
     - name: sql
       protocol: TCP
       port: 1433
       targetPort: 1433
     selector:
       statefulset.kubernetes.io/pod-name: sqlinst1-0​

We are deploying the following Kubernetes objects in this yaml file:

1. Creating a statefulset deployment called sqlinst1
2. Deploying the Azure disk storage class for persistent data storage and deploying SQL Server container.
3. After that, creating an internal load balancer service with static IP (so the IP remains the same even after restart) called sqlinst1-0 pointing to sqlinst1-0 container.

Because my kubernetes service host nodes are not joined to the domain, I must provide domain-related information to the containers during deployment, which is why you see me using parameters like hostAliases, dnsPolicy, and dnsConfig. The IP address that is used in hostAliases.ip refers to the domain controller's IP address, while hostname refers to the domain controller's hostname. This adds the necessary entries to the container's /etc/hosts file. Similarly the dnsConfig settings add the dnssearch and dns nameserver entries of the domain and the domain controller respectively in /etc/resolv.conf file of the container.

During the container deployment, I load the mssql.conf and krb5.conf configuration files as configmaps. With this in place, we can now copy the keytab file from the Ubuntu server to the local client from which we are running kubectl, and then copy it from the local client to the container and restart the SQL Server container. To copy the mssql.keytab file from the client to the container, use the following command:

kubectl cp "certificate\mssql.keytab" sqlinst1-0:/var/opt/mssql/secrets/mssql.keytab

Where certificate is the name of a folder in my current working directory.

Note: To troubleshoot AD authentication issues, you will also need logger.ini, which can be copied to the container in the same way you copied mssql.keytab. The command to copy the file is: 

kubectl cp "certificate\logger.ini" sqlinst1-0:/var/opt/mssql/logger.ini

The logger.ini file consists of the following details :

To restart the container post the copy you can run the command: kubectl delete pod sqlinst1-0. This will delete and recreate the SQL Server container.

AD authentication in action:

After restarting the SQL Server container, you can now connect to it using the FQDN name of the container, which is the DNS host entry that was added in this case: "sqlinst1-0.contoso.com." After connecting, open a query window and run the following command to add a domain user:

create login [contoso\amvin] from windows.

Log in with this AD user now, and the AD authentication should work as shown below:

I hope this helps, and please let me know if you have any further questions! Happy Monday !!