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.

  • A dedicated AD domain user account with one or more registered Service Principal Names which the SQL Server will use as its Kerberos identity and to perform group refresh. This account is commonly referred to as privileged Active Directory account for SQL Server in Microsoft’s documentation.

    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

    Note that while the SQL Server documentation does not explicitly state that the Kerberos identity and the group refresh account must be the same, in practice we’ve run into multiple problems when trying to use different accounts. For this reason, when running SQL Server on DSM this guide recommends to use the same account for both purposes.

  • 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

An identity account, a keytab, and DNS records are necessary for each AD-integrated SQL Server cluster running on DSM. You can create all the required records and perform all configuration manually. However, DSM also support automating some of the steps:

  • Automated creation of Service Principal Names : you still need to create the SQL Server’s identity account in Active Directory, but instead of creating SPNs and a keytab, you need to 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.

  • Automated creation of DNS records: if you are using the Active Directory built-in DNS implementation, you can provide DSM with the credentials of a user which can create records in one or more DNS zones; then, DSM itself will create A and PTR records for the DNS names and the IP address of the cluster respectively.

Note that the automation support is not all-or-nothing: you can let DSM only create SPNs, or only write DNS records for you, and perform the remainder manually.

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.

Fully manual mode

This section describes how to create all Active Directory configuration manually and provide it to DSM. You don’t need to provide any plaintext password to DSM, only the Kerberos service keytab.

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:

    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

Automated creation of Service Principal Names

You need to manually create the server identity account for SQL Server. DSM will register Service Principal Names and will create a keytab from the credentials you provided.

Multiple SQL Server clusters can share the same identity account for testing purposes, but this is not a secure practice and is strongly discouraged for production deployments.

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

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='<password>'
    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='<password>'

    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-----
---
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:
    # These credentials are required and will be used to create SPNs under
    # the SQL Server's identity account.
    name: config-credentials

  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

    # If no keytab is provided, DSM will create Service Principal Names in
    # Active Directory under the sql01user, and create a keytab for the same.
    # The referenced secret must have both "username" and "password" fields.
    sqlServerAccount:
      name: sql01user

  4. Create DNS records, or configure the cluster with “writeDNSNames” (see bellow.)

Automated creation of DNS records

If you are using the Active Directory’s integrated DNS server, you can configure DSM to create the necessary DNS records for you. This can be set for each SQL Server cluster individually and regardless whether you configure the cluster with manually-provided keytab.

Instead of Active Directory, you may use any DNS implementation which supports RFC 2136 dynamic DNS updates with Kerberos authentication (RFC 3645), but please note that this hasn’t been extensively tested.

  1. Provide the credentials of a user which can create and update DNS records to DSM

    KUBECONFIG=dsm-api-kubeconfig.yaml kubectl create secret generic config-credentials --from-literal=username=my-admin --from-literal=password='<password>'

  2. Create an ActiveDirectoryDomain object for your Active Directory domain using the “config-credentials” Secret created in the previous step as privilegedAccountCredentials. Follow the guidelines from the “Automated creation of Service Principal Names” section to configure the nameserver and trust bundle.

       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
       - 10.20.30.50
     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:
       # These credentials are required and will be used to create DNS records.
       # Note that it's the same credentials used to write SPNs as well; if
       # both modes of automation are enabled on a cluster, the respective user
       # simply needs to have both sets of privileges.
       name: config-credentials

     # If writeDNSNames is specified for the cluster (see bellow), DSM will
     # execute the updates against this server. If the property is not specified,
     # DSM will execute the updates against the first nameserver i.e. 10.20.30.40
     # in this example.
     primaryNameserver: 10.20.30.50

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

    Example:

    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

    # You MAY provide keytab, or let DSM create the SPNs and the keytab in
    # addition to the DNS records.
    keytab:
      name: sql01user-keytab

    # This instructs DSM to create A record for "sql01.dsmsqlserver.com"
    # pointing to the cluster's IP address, and a PTR record for the
    # cluster's IP address pointing back to "sql01.dsmsqlserver.com".
    # The update is run against the domain's primaryNameserver (see above.)
    writeDNSNames: ForwardAndReverse

How is the target DNS zone chosen?

For each DNS name that it needs to write, DSM will choose the “narrowest” parent zone of the name for which the domain’s primary nameserver is authoritative.

For example, consider a cluster with dnsNames sql01.mydbs.myorg.dsmsqlserver.com.

  1. First, DSM will ask the primary nameserver whether it has authority (SOA) over mydbs.myorg.dsmsqlserver.com
  2. If the primary nameserver response with authoritative SOA answer for that zone, DSM will create A record there. End.
  3. Otherwise, DSM will “strip” one more component from the name and repeat the process.

When writing PTR records, DSM follows the same procedure with the PTR name. For example, a PTR record for IP address 10.20.30.40 has name 40.30.20.10.in-addr.arpa. . DSM will ask, in order, about 30.20.10.in-addr.arpa, 20.10.in-addr.arpa, and 10.in-addr.arpa.

Assigning the correct privileges to the ActiveDirectoryDomain’s privileged account

Required privileges for automatic creation of SPNs

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-admin: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 sqlserver-user) /G "my-admin: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"

Required privileges for automatic creation of DNS records

Note: the information in this section is specific to the Active Directory DNS server implementation.

Like with SPNs, DSM uses the privilegedAccountCredentials from the ActiveDirectoryDomain object to write DNS records for the SQL Server’s DNS names and IP address. DSM requires the following privileges:

  • Create child objects privilege of type “dnsNode” on the zone(s) where the A and PTR records for SQL Server clusters will be created.
  • Optional but recommended: Change owner privilege for all child objects of the zone(s) where A and PTR records will be created. This will allow DSM to overwrite existing DNS records, including if you switch from one privileged account to another.

The required privileges can be assigned by:

  • Using Active Directory’s DNS server management UI.
  • Using the dsacls command line utility to modify the permissions on the LDAP subtree which the Active Directory’s DNS implementation uses for storage.

In the examples bellow, replace name of the zone and the default naming context with the names from your own domain.

Example 1: grant privileges to my-admin over the Forward Zone “mydnszone.com”

dsacls DC=mydnszone.com,CN=MicrosoftDNS,DC=DomainDNSZones,DC=dsmsqlserver,DC=com /G "my-admin:CC;dnsNode"

dsacls DC=mydnszone.com,CN=MicrosoftDNS,DC=DomainDNSZones,DC=dsmsqlserver,DC=com /G "my-admin:WO"

Example 2: grant privileges to my-admin over the Reverse Zone for the 10.20.0.0/16 network.

dsacls DC=20.10.in-addr.arpa,CN=MicrosoftDNS,DC=DomainDNSZones,DC=dsmsqlserver,DC=com /G "my-admin:CC;dnsNode"

dsacls DC=20.10.in-addr.arpa,CN=MicrosoftDNS,DC=DomainDNSZones,DC=dsmsqlserver,DC=com /G "my-admin:WO"

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.

Implementation limitations

The current Technical Preview implementation has some limitations w.r.t. the cleanup of the created Active Directory objects.

If you change the cluster’s spec.dnsNames, the objects created for the removed entries will be cleaned from Active Directory as long as they’ve been created by DSM to begin with.

However, in the following scenarios the objects will NOT be deleted and will have to cleaned up manually:

  • If you delete the SQLServerCluster
  • If you remove the SQLServerCluster’s Active Directory integration configuration i.e. remove the spec.activeDirectory property.
  • The Service Principal Names will not be removed from the old account if you change the cluster’s server identity account i.e. if you change spec.activeDirectory.sqlServerAccount to identify another user.