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:
Current Environment details & Setup:
In this environment:-
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.
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:
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:
adutil user create --name sqluser --distname CN=sqluser,CN=Users,DC=CONTOSO,DC=COM --password 'P@ssw0rd!'
adutil spn addauto -n sqluser -s MSSQLSvc -H sqlinst1-0.contoso.com -p 1433
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.
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:
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"
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.
kubectl create secret generic mssql --from-literal=SA_PASSWORD="MyC0m9l&xP@ssw0rd"
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:
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.
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 !!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.