Monday, September 13, 2021

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.amvin87_0-1631537502525.png

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.amvin87_2-1631537502497.png
  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.amvin87_3-1631537502500.png
  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 createauto -k ~/container/mssql.keytab -p 1433 -H sqlinst1-0.contoso.com --password 'P@ssw0rd!' -s MSSQLSvc​​

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.

amvin87_4-1631537502528.png

 

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.amvin87_5-1631537502503.png

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 :

 

 

amvin87_6-1631537502504.png

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:amvin87_7-1631537502523.png

 

 

 

 

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

 

Posted at https://sl.advdat.com/3hsx95Q