Automate configuration of Active Directory authentication for Microsoft SQL Server running on Red Hat Enterprise Linux

Last year, the microsoft.sql.server Ansible role was introduced in Red Hat Enterprise Linux (RHEL). With the release of RHEL 8.8 and 9.2, this role receives a new update with several new features:

• Configure authentication of SQL Server with Active Directory server.
• Expanded management capabilities for Always On availability groups, including support for configuring asynchronous availability replicas and for configuring read-scale always on clusters.
• Configure and manage SQL Server 2022 on RHEL 8 and RHEL 9.

This article covers integration with Active Directory (AD), and guides you through the whole process:

1. Ensure that you meet the requirements to configure AD Authentication for SQL Server
2. Prepare an Ansible inventory file
3. Explore the variables you must set, and prepare an Ansible Playbook file using the role to automatically configure authentication with Active Directory
4. Run the role
5. Finish the configuration
6. Verify that the authentication works as expected

Prerequisites

Before you begin, you must ensure that you meet the prerequisites for the role.

1. You must have an Active Directory server running and accessible by your RHEL machine. You can read the overview of the prerequisites in Microsoft documentation in Join SQL Server on a Linux host to an Active Directory domain. Should you have trouble meeting the prerequisites, refer to Troubleshooting Active Directory.
2. Ensure that DNS lookup works. You must be able to successfully ping the Active Directory server by its IP address and its short and full DNS names from your RHEL machine.
3. Ensure that reverse DNS lookup works. Executing the nslookup command for the IP address of the Active Directory server must resolve to the correct DNS name, and the DNS name must in turn resolve to the correct IP address.
4. Ensure that you have access and credentials to an Active Directory user with permission to create accounts and Service Principal Names (SPNs) on the domain.

Prepare the Ansible inventory

The inventory must only contain RHEL servers that you wish to configure for Active Directory authentication. This example uses the inventory file inventory.yml, containing a single RHEL node rhel-sql-server.example.com:

$ cat inventory.yml
---
all:
hosts:
rhel-sql-server.example.com

Use the role

Next, you need a playbook to configure authentication with Active Directory. This is easiest to do after looking at an example playbook first, adjusting variables and parameters as needed, and using it as a foundation for your own.

Note that the role requires some passwords to be set with variables. Examples in this article provide these variables in plain text, but in production you must use Ansible vault to encrypt passwords.

Example playbook

Here's an example playbook saved as playbook.yml. It configures authentication with Active Directory:

---
- name: Configure with AD server authentication
hosts: all
vars:
# General variables
mssql_accept_microsoft_odbc_driver_17_for_sql_server_eula: true
mssql_accept_microsoft_cli_utilities_for_sql_server_eula: true
mssql_accept_microsoft_sql_server_standard_eula: true
mssql_version: 2022
mssql_password: "p@55w0rD"
mssql_edition: Evaluation
mssql_manage_firewall: true
# AD Integration required variables
mssql_ad_configure: true
mssql_ad_sql_user_name: sqluser
mssql_ad_sql_password: "p@55w0rD1"
ad_integration_realm: domain.com
ad_integration_user: Administrator
ad_integration_password: Secret123
# AD Integration optional variables
mssql_ad_sql_user_dn: "CN=sqluser,CN=Users,DC=DOMAIN,DC=COM"
mssql_ad_netbios_name: domain

The variables in the example playbook are divided into three sections: general, required, and optional variables.

General variables

Variables that accept the End User License Agreement (EULA) must be set to true to confirm that you agree to the terms of the mssql-server package. These include:

• mssql_accept_microsoft_odbc_driver_17_for_sql_server_eula
• mssql_accept_microsoft_cli_utilities_for_sql_server_eula
• mssql_accept_microsoft_sql_server_standard_eula

Other variables:

• mssql_version defines the version (2017, 2019, or 2022) of the SQL Server you want to manage.
• mssql_password sets the password for the sa user
• mssql_edition defines the edition to configure for SQL Server
• mssql_manage_firewall enables the firewall and opens the required ports. This is an optional variable. Use it only if you wish the role to manage the firewall for you.

Required variables for AD Integration

These variables are required:

• mssql_ad_configure: Whether to configure Active Directory authentication.
• mssql_ad_sql_user_name: Name of the user to be created in Active Directory server to enable authentication for the SQL Server. The role uses adutil, which requires the user to be created with permission to access it as a privileged user.
• mssql_ad_sql_password: Password for the user being created.
• ad_integration_realm: Active Directory realm name.
• ad_integration_user: User name of the privileged user in Active Directory. This user must already exist in the domain.
• ad_integration_password: Password for the user.

Optional variables for AD Integration

These variables are optional:

• mssql_ad_sql_user_dn: You must set this variable if your AD server stores user accounts in a custom Organizational Unit (OU) rather than in the default Users OU. By default, the role builds the distinguished name based on the values provided for variables mssql_ad_sql_user_name and ad_integration_realm and uses the Users OU (for example, CN=sqluser,CN=Users,DC=DOMAIN,DC=COM). You can overwrite this by providing a distinguished name.
• mssql_ad_netbios_name: You must set this variable when NetBIOS domain name of your AD server is not equal to the first subdomain of the domain name you provide with the ad_integration_realm variable (for example, you've set ad_integration_realm to domain.contoso.com, but your NetBIOS domain name is not domain). This value is used to create the {{ mssql_ad_netbios_name }}\{ad_integration_user }} login in SQL Server.

Run the Ansible role

After you ensure that you meet all prerequisites, and you have your Ansible inventory and playbook ready, you can run the playbook.

$ ansible-playbook -i inventory.yml playbook.yml

Add domain users to SQL Server

Before a user can log in, you must add it to SQL Server. To do this, run the following T-SQL command for each AD domain user:

CREATE LOGIN \[<domain>**<username>*\] FROM WINDOWS;

Enabling additional encryption types

After you execute the role to configure Active Directory authentication, you must add AES128 and AES256 Kerberos encryption types to the user defined with the mssql_ad_sql_user_name variable in Active Directory. You can do this using the Active Directory web UI, or you can use PowerShell.

Web UI

Open the web UI of your AD Server and log in.

1. Navigate to Tools > Active Directory Users and Computers > [domain] > Users > [sqluser] > Account
2. In the Account options list, select This account supports Kerberos AES 128 bit encryption and This account supports Kerberos AES 256 bit encryption
3. Click Apply

PowerShell

In PowerShell, use the Set-ADUser command, replacing <sqluser> with the username that you set as the mssql_ad_sql_user_name variable:

Set-ADUser -Identity <sqluser> -KerberosEncryptionType AES128,AES256

Verify authentication with Active Directory

Now that you've executed the role to configure AD Server authentication, and you've added the required encryption types to your <sqluser> user, you can authenticate using one of the following methods:

• Windows or RHEL: Azure Data Studio (ADS)
• Windows: SQL Server Management Studio (SSMS)
• RHEL: Linux terminal

Azure Data Studio (ADS)

If you don't already have ADS installed, follow Microsoft's documentation to download and install Azure Data Studio.

Once it's installed, launch ADS.

1. Click Create a connection
2. From the Authentication type list, select Windows Authentication
3. Fill in other fields and click Connect

You can now use SQL Server.

SQL Server Management Studio (SSMS)

First, log in to Windows as the user that you want to use to log in to SQL Server, and then launch SSMS.

1. In the Connect to Server pane, select Database Engine from the Server type list
2. In the Server name field, enter the fully-qualified domain name (FQDN) of the RHEL instance you ran the role for
3. From the Authentication list, select Windows Authentication, and fill in these fields:
   • Server type: Database Engine
   • Server name: Enter your server name
   • Authentication: Windows Authentication
4. Click Connect

Linux terminal

To verify authentication using a Linux terminal, use SSH to log in to the server as the user created by the role.

$ ssh -l <sqluser>@<domain.com> <client.domain.com>

Obtain a Kerberos ticket for the Active Directory user you want to authenticate:

$ kinit <AD_user_name>@<DOMAIN.COM>

Use sqlcmd to log in to SQL Server and, for example, run a query to get the current user:

/opt/mssql-tools/bin/sqlcmd -S. -Q 'SELECT SYSTEM_USER'

Conclusion

The microsoft.sql.server role can help you automate integration with Active Directory for user authentication. The role uses the redhat.rhel_system_roles.ad_integration role to connect to Active Directory, and the redhat.rhel_system_roles.firewall role to configure the firewall, and it does this in a consistent manner. Red Hat offers more system roles to manage different services and tools on RHEL. If you're interested in automating your workflows, look at the list of available RHEL System Roles and read the documentation or related blog posts on the topic. There's a lot available!

If you're interested in what else the microsoft.sql.server role can help you with, take a look at its official documentation.