VMware Data Services Manager Kubernetes API

Integrate DSM SQL Server cluster with Active Directory using Windows tooling

Overview

The following document describes how to integrate a DSM SQL Server cluster with an Active Directory (AD) domain. This enables domain users to connect to this DSM SQL Server instance using Windows Authentication, i.e. without providing a password in the connection parameters.

In order to integrate a given DSM SQL Server instance with Active Directory, the server needs:

  • A dedicated AD domain user account with one or more registered Service Principal Names which the SQL Server will use as its Kerberos identity.

  • An AD domain user account, commonly referred to as privileged Active Directory account for SQL Server in Microsoft’s documentation, which the server will use for group refresh. The SQL Server identity account can optionally be used for this purpose as well.

    For more information see: https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-ad-auth-understanding?view=sql-server-ver16#sql-server-group-refresh

  • A keytab file with the credentials of both accounts.

  • A resolvable and reverse-resolvable DNS name. While not strictly required, it is needed for compatibility with most client tools. For example, the Windows authentication with Microsoft SQL Server Management Studio will not work without DNS name.

The steps outlined in this document must be executed on a domain-joined Windows machine with a domain account that has sufficient privileges to create other domain accounts and register/configure SPNs in AD to generate a .keytab file. Alternatively, these steps can be performed using adutil tool on a domain-joined Ubuntu machine.

Modes of operation

DSM supports two ways to provide a managed SQL Server cluster with the required configuration:

  • Manual: In this mode, you need to manually create the necessary accounts, register their desired Service Principal Names, create the keytab, and create DNS records. In addition to the Active Directory domain name, only need to provide the created keytab to DSM. DSM will not try to authenticate to nor will call any APIs of your Active Directory.

  • Semi-automated: you still need to create DNS records and the SQL Server’s identity account in Active Directory, but instead of creating SPNs and a keytab, you will provide to DSM the credentials of user who can write to the servicePrincipalName property; then, DSM itself will register the required SPNs and generate a keytab file.

Prerequisites

  • A Windows Domain Controller running Active Directory Domain Services must be available and discoverable through DNS in the network used by DSM. Тhe DNS must be able to resolve all names in the Active Directory domain required for SQL Server Windows Authentication, e.g. domain controller, KDC, SQL Server hostname etc., including reverse lookup zones and PTR records as per Microsoft’s official documentation.
  • A privileged domain account that can create other domain accounts and register/configure SPNs in AD to generate a .keytab file.

Manual mode

In manual mode, you will create all Active Directory configuration manually and provide it to DSM. You don’t need to provide any password to DSM.

Create AD user and Service Principal Names (SPN) for a given DSM SQL Server instance

  1. Create SQLServerCluster instance through DSM and obtain the static IP address allocated by DSM from the cluster’s status, i.e. status.connection.host

  2. Choose an FQDN (e.g. sql01.dsmsqlserver.com) for the cluster and create a forwarding host (A) entry mapping it to the above IP address. The same name must be used in the following SPN records for that cluster. Additionally, create associated PTR record in a reverse lookup zone.

  3. On your domain controller, use PowerShell’s New-ADUser command and setspn.exe tool to create a new AD user and set SPNs for the target DSM SQL Server instance as described here: https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-ver16#create-active-directory-user-for-sql-server-and-set-spn

    For example:

    Create new Active Directory user:

    Import-Module ActiveDirectory

New-ADUser sql01user -AccountPassword (Read-Host -AsSecureString "<password>") -PasswordNeverExpires $true -Enabled $true

    Create Service Principal Names:

    setspn -A MSSQLSvc/sql01.dsmsqlserver.com:1433 sql01user

setspn -A MSSQLSvc/sql01:1433 sql01user

  4. Additionally, create the keytab file for the target DSM SQL Server instance as described here: https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-ver16#spn-keytab-entries

    For example: TODO revise

    ktpass /princ MSSQLSvc/sql01.dsmsqlserver.com:[email protected] /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser DSMSQLSERVER\sql01user /out mssql.keytab -setpass -setupn /kvno 2 /pass <password>

ktpass /princ MSSQLSvc/sql01.dsmsqlserver.com:[email protected] /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser DSMSQLSERVER\sql01user /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass <password>

ktpass /princ MSSQLSvc/sql01:[email protected] /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser DSMSQLSERVER\sql01user /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass <password>

ktpass /princ MSSQLSvc/sql01:[email protected] /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser DSMSQLSERVER\sql01user /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass <password>

ktpass /princ [email protected] /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser DSMSQLSERVER\sql01user /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass <password>

ktpass /princ [email protected] /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser DSMSQLSERVER\sql01user /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass <password>

Configure the DSM SQL Server instance to integrate with Active Directory

  1. In DSM API create a Secret from the keytab file created in the previous section

    KUBECONFIG=dsm-api-kubeconfig.yaml kubectl create secret generic sql01-keytab-secret --from-file=keytab=/path/to/mssql.keytab

  2. Create an ActiveDirectoryDomain object for your Active Directory domain. If the DNS server responsible for the AD domain is different from the system-wide DNS configured for DSM globally, you need to specify the domain’s DNS explicitly in the ActiveDirectoryDomain’s spec. You don’t need to specify trustBundle and privilegedAccountCredentials in manual mode. Multiple SQLServerClusters can share the same ActiveDirectoryDomain.

    Example:

    apiVersion: infrastructure.dataservices.vmware.com/v1alpha1
kind: ActiveDirectoryDomain
metadata:
  name: my-test-domain
spec:
  name: dsmsqlserver.com
  nameservers:
    # Only needed if the globally configured DNS nameserver for DSM cannot
    # resolve dsmsqlserver.com
    - 10.20.30.40

  3. Update the SQLServerCluster spec with the Active Directory configuration.

    Example:

    apiVersion: v1
kind: Secret
metadata:
  name: sql01user
stringData:
  # You don't need to provide a password when a keytab is provided
  username: "sql01user"
---
apiVersion: databases.dataservices.vmware.com/v1alpha1
kind: SQLServerCluster
metadata:
  name: sqlserver-01
  namespace: default
spec:
  dnsNames:
    - sql01.dsmsqlserver.com
  activeDirectory:
    domain:
      # References the name of the ActiveDirectoryDomain object
      name: my-test-domain
    sqlServerAccount:
      # References the name of the Secret
      name: sql01user
    keytab:
      name: sql01-keytab-secret

Semi-automated mode

In semi-automated mode, you will create the server identity account for SQL Server, and the DNS records. DSM will register Service Principal Names and will create a keytab from the credentials you provided. Although not a security best practice for production environments, multiple SQL Server clusters can share the same identity account.

Create AD user for a given DSM SQL Server instance

On your domain controller, use PowerShell’s New-ADUser command to create an AD user for the target DSM SQL Server instance as described here: https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-ver16#create-active-directory-user-for-sql-server-and-set-spn

You don’t need to create Service Principal Names, DSM will do that automatically.

For example:

Create new Active Directory user: ```powershell Import-Module ActiveDirectory

New-ADUser sql01user -AccountPassword (Read-Host -AsSecureString “”) -PasswordNeverExpires $true -Enabled $true ```

Configure the DSM SQL Server instance to integrate with Active Directory

  1. You’ll provide DSM with two sets of credentials:

    1. The credentials of the SQL Server identity user created in the previous section:
    KUBECONFIG=dsm-api-kubeconfig.yaml kubectl create secret generic sql01-credentials --from-literal=username=sql01user --from-literal=password='<passowrd>'
    1. The credentials of a user who can write to the servicePrincipalName property of the server identity user:
    KUBECONFIG=dsm-api-kubeconfig.yaml kubectl create secret generic config-credentials --from-literal=username=my-admin --from-literal=password='<passowrd>'

    You may optionally use the same user for both purposes; in this case, the user needs to be able to write servicePrincipalName to SELF. See the final paragraph in this section on how to configure a user with the minimal required privileges.

  2. Create an ActiveDirectoryDomain object for your Active Directory domain using the “config-credentials” Secret created in the previous step as privilegedAccountCredentials.

    If the DNS server responsible for the AD domain is different from the system-wide DNS configured for DSM globally, you need to specify domain’s DNS explicitly in the ActiveDirectoryDomain’s spec.

    If the domain’s LDAP servers’ TLS certificates are not issued by a well-known authority, you also need to create a ConfigMap with a single key – tls.crt – which contains the PEM encoded certificate of the LDAP servers’ signing CA.

    Example:

    kind: ConfigMap
apiVersion: v1
metadata:
  name: test-domain-trust-bundle
data:
  tls.crt: |
    -----BEGIN CERTIFICATE-----
    ... The PEM-encoded CA certificate of the domain's LDAP services.
    -----END CERTIFICATE-----
---
kind: Secret
apiVersion: v1
metadata:
  name: test-domain-priv-account
stringData:
  username: "admin-user"
  password: "<admin-user's password>"
---
apiVersion: infrastructure.dataservices.vmware.com/v1alpha1
kind: ActiveDirectoryDomain
metadata:
  name: dsmsqlserver.com
spec:
  name: dsmsqlserver.com
  nameservers:
    # Only needed if the globally configured DNS nameserver for DSM cannot
    # resolve dsmsqlserver.com
    - 10.20.30.40
  trustBundle:
    # If the domain's LDAP servers TLS certificates are issued by a well-known
    # authority like DigiCert or GlobalSign, you may skip this section.
    name: test-domain-trust-bundle
  privilegedAccountCredentials:
    # You MUST specify these credentials in semi-automatic mode
    name: test-domain-priv-account

  3. Update the SQLServerCluster spec with the Active Directory configuration.

    Example:

    apiVersion: v1
kind: Secret
metadata:
  name: sql01user
stringData:
  # We must provide a password in semi-automatic mode so DSM can create a keytab file
  username: "sql01user"
  password: "<sql01user's-password>"
---
apiVersion: databases.dataservices.vmware.com/v1alpha1
kind: SQLServerCluster
metadata:
  name: sqlserver-01
  namespace: default
spec:
  dnsNames:
    - sql01.dsmsqlserver.com
  activeDirectory:
    domain:
      name: dsmsqlserver.com
    sqlServerAccount:
      name: sql01user

  4. Create DNS records

    Obtain the static IP address allocated by DSM from the cluster’s status, i.e. status.connection.host, and create a forwarding host (A) and reverse lookup (PTR) between that IP address and the DNS name of the SQL Server (in the above example, sql01.dsmsqlserver.com).

Assigning the correct privileges to the ActiveDirectoryDomain’s privileged account

If semi-automated mode, DSM uses the privilegedAccountCredentials from the ActiveDirectoryDomain object to write the Service Principal Names under the SQL Server’s identity account. This — being able to write to the servicePrincipalName property — is the only write privilege that DSM actually requires. DSM will not attempt write to other properties, and will not attempt to create users.

The required privilege can be configured using the Microsoft’s dsacls utility.

Example 1: configure the privileged account to be able to write the servicePrincipalName of all users, assuming that “CN=Users,DC=dsmsqlserver,DC=com” is the root of the LDAP subtree where all Active Directory users are located. Change accordingly if your domain uses different structure, or if you want to limit the privileges to a subset of users.

dsacls CN=Users,DC=dsmsqlserver,DC=com /I:S /G "my-config-account:RPWP;servicePrincipalName;user"

Example 2: configure the privileged account to be able to write the servicePrincipalName of a single user (syntax assumes PowerShell):

dsacls $(dsquery user -samid my-admin) /G "sqlserver-user:RPWP;servicePrincipalName"

Example 3: configure the privileged account to be able to write its own servicePrincipalName; this is useful if you prefer to use the same account as a privileged account and as a SQL Server identity account.

dsacls $(dsquery user -samid sqlserver-user) /G "SELF:RPWP;servicePrincipalName"

Create SQL Server logins to enable AD users to login using Windows Authentication

DSM SQL Server automatically restarts and is now ready for you to create Windows logins and enable Active Directory users to login using Windows Authentication.

For example, connect to the SQL Server using the initial local account provisioned by DSM (referenced in status.connection) and run the following commands to create the login, and confirm that it exists:

CREATE LOGIN [DSMSQLSERVER\aduser]
    FROM WINDOWS;

SELECT name
FROM sys.server_principals;

Finally, sign in to a domain-joined Windows machine using aduser’s domain credentials. Open SQL Server Management Studio and connect to your DSM SQL Server instance (e.g. sql01.dsmsqlserver.com) by specifying Windows Authentication in the Connect to Server dialog.