Chapter 16. Configuring Microsoft SQL Server by using the microsoft.sql.server Ansible role

As an administrator, you can use the microsoft.sql.server Ansible role to install, configure, and start Microsoft SQL Server (SQL Server). The microsoft.sql.server Ansible role optimizes your operating system to improve performance and throughput for the SQL Server. The role simplifies and automates the configuration of your RHEL host with recommended settings to run the SQL Server workloads.

16.1. Installing and configuring SQL server by using the microsoft.sql.server Ansible role with existing certificate files

You can use the microsoft.sql.server Ansible role to install and configure SQL Server version 2019. The playbook in this example also configures the server to use an existing sql_cert certificate and private key files for TLS encryption.

Prerequisites

  • You have prepared the control node and the managed nodes.
  • You are logged in to the control node as a user who can run playbooks on the managed nodes.
  • The account you use to connect to the managed nodes has sudo permissions on them.
  • Minimum 2 GB RAM
  • The ansible-collection-microsoft-sql package is installed on the managed node.

Procedure

  1. Create a playbook file, for example ~/playbook.yml, with the following content: 

    ---
- name: Install and configure SQL Server
  hosts: managed-node-01.example.com
  roles:
    - microsoft.sql.server
  vars:
    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: 2019
    mssql_manage_firewall: true
    mssql_tls_enable: true
    mssql_tls_cert: sql_crt.pem
    mssql_tls_private_key: sql_cert.key
    mssql_tls_version: 1.2
    mssql_tls_force: false
    mssql_password: <password>
    mssql_edition: Developer
    mssql_tcp_port: 1433

  2. Validate the playbook syntax: 

    $ ansible-playbook --syntax-check ~/playbook.yml

    Note that this command only validates the syntax and does not protect against a wrong but valid configuration.

  3. Run the playbook: 

    $ ansible-playbook ~/playbook.yml

Additional resources

  • /usr/share/ansible/roles/microsoft.sql-server/README.md file

16.2. Installing and configuring SQL server by using the microsoft.sql.server Ansible role with the certificate role

You can use the microsoft.sql.server Ansible role to install and configure SQL Server version 2019. The playbook in this example also configures the server to use TLS encryption and creates a self-signed sql_cert certificate file and private key by using the certificate system role.

You do not have to call the certificate system role in the playbook to create the certificate. The microsoft.sql.server Ansible role calls it automatically.

Prerequisites

  • You have prepared the control node and the managed nodes.
  • You are logged in to the control node as a user who can run playbooks on the managed nodes.
  • The account you use to connect to the managed nodes has sudo permissions on them.
  • Minimum 2 GB RAM
  • The ansible-collection-microsoft-sql package is installed on the managed node.
  • The managed nodes are enrolled in a Red Hat Identity Management (IdM) domain.

Procedure

  1. Create a playbook file, for example ~/playbook.yml, with the following content: 

    ---
- name: Install and configure SQL Server
  hosts: managed-node-01.example.com
  roles:
    - microsoft.sql.server
  vars:
    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: 2019
    mssql_manage_firewall: true
    mssql_tls_enable: true
    mssql_tls_certificates:
      - name: sql_cert
        dns: *.example.com
        ca: self-sign
    mssql_password: <password>
    mssql_edition: Developer
    mssql_tcp_port: 1433

  2. Validate the playbook syntax: 

    $ ansible-playbook --syntax-check ~/playbook.yml

    Note that this command only validates the syntax and does not protect against a wrong but valid configuration.

  3. Run the playbook: 

    $ ansible-playbook ~/playbook.yml

Additional resources

16.3. TLS-related variables of the microsoft.sql.server system role

You can use the following variables to configure the Transport Level Security (TLS) protocol.

Table 16.1. TLS role variables

Role variableDescription

mssql_version

Defines which version of SQL server to install. Possible values are 2017, 2019 and 2022.

mssql_tls_enable

This variable enables or disables TLS encryption.

The microsoft.sql.server Ansible role performs following tasks when the variable is set to true:

  • Copies or generates a TLS certificate in /etc/pki/tls/certs/ on the SQL Server
  • Copies or generates a private key in /etc/pki/tls/private/ on the SQL Server
  • Configures the SQL Server to use TLS certificate and private key to encrypt connections

When set to false, the TLS encryption is disabled. The role does not remove the existing certificate and private key files.

mssql_tls_certificates

Generates a certificate and a private key for TLS encryption using the certificate role.

Important

When you set this variable, you must not set mssql_tls_cert and mssql_tls_private_key variables.

mssql_tls_cert

Copies a certificate file from the specified path to SQL Server and uses it for TLS encryption.

mssql_tls_private_key

Copies a private key file from the specified path to SQL Server and uses it for TLS encryption.

mssql_tls_remote_src

Defines if the role searches for mssql_tls_cert and mssql_tls_private_key files remotely or on the control node.

When set to the default false, the role searches for mssql_tls_cert or mssql_tls_private_key files on the Ansible control node.

When set to true, the role searches for mssql_tls_cert or mssql_tls_private_key files on the Ansible managed node.

mssql_tls_version

Defines which TLS version to use.

The default is 1.2.

mssql_tls_force

If set to true, replaces the certificate and private key files on the host. The files must exist under /etc/pki/tls/certs/ and /etc/pki/tls/private/ directories.

The default is false.

Additional resources

  • /usr/share/ansible/roles/microsoft.sql-server/README.md file

16.4. Setting up custom storage paths for data and logs

To store your data or logs in a different directory than the default one, specify the custom storage path using the mssql_datadir, mssql_datadir_mode, mssql_logdir, and mssql_logdir_mode variables in an existing playbook. When you define a custom path, the role creates the provided directory and ensures correct permissions and ownership for it.

Important

If you later decide to remove the variables, the storage paths will not change back to the default ones but will store the data or logs in the latest defined paths.

Prerequisites

  • You have prepared the control node and the managed nodes.
  • You are logged in to the control node as a user who can run playbooks on the managed nodes.
  • The account you use to connect to the managed nodes has sudo permissions on them.
  • Minimum 2 GB RAM
  • The ansible-collection-microsoft-sql package is installed on the managed node.

Procedure

  1. Edit an existing playbook file, for example ~/playbook.yml, and add the storage and log-related variables: 

    ---
- name: Install and configure SQL Server
  hosts: managed-node-01.example.com
  roles:
    - microsoft.sql.server
  vars:
    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: 2019
    mssql_manage_firewall: true
    mssql_tls_enable: true
    mssql_tls_cert: sql_crt.pem
    mssql_tls_private_key: sql_cert.key
    mssql_tls_version: 1.2
    mssql_tls_force: false
    mssql_password: <password>
    mssql_edition: Developer
    mssql_tcp_port: 1433
    mssql_datadir: /var/lib/mssql/
    mssql_datadir_mode: '0700'
    mssql_logdir: /var/log/mssql/
    mssql_logdir_mode: '0700'

    Enter the permission modes in single quotation marks so that Ansible parses it as a string and not as an octal number.

    If you do not specify the mode and the destination directory does not exist, the role uses the default umask on the system when setting the mode. If you do not specify the mode and the destination directory does exist, the role uses the mode of the existing directory.

  2. Validate the playbook syntax: 

    $ ansible-playbook --syntax-check ~/playbook.yml

    Note that this command only validates the syntax and does not protect against a wrong but valid configuration.

  3. Run the playbook: 

    $ ansible-playbook ~/playbook.yml

Additional resources

  • /usr/share/ansible/roles/microsoft.sql-server/README.md file

16.5. Preparing and running a playbook to enable SQL Server authentication with Active Directory

To be able to automatically authenticate your Microsoft SQL server with Active Directory, you need to set up an microsoft.sql.server Ansible playbook with variables according to your use case.

Prerequisites

  • You have prepared the control node and the managed nodes.
  • You are logged in to the control node as a user who can run playbooks on the managed nodes.
  • The account you use to connect to the managed nodes has sudo permissions on them.
  • Minimum 2 GB RAM
  • The ansible-collection-microsoft-sql package is installed on the managed node.

Procedure

  1. Create a playbook file, for example ~/playbook.yml, with the following content: 

    ---
- name: Configure with AD server authentication
  hosts: managed-node-01.example.com
  roles:
    - microsoft.sql.server
  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: "<password>"
    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: "<password>"
    ad_integration_realm: domain.com
    ad_integration_user: Administrator
    ad_integration_password: <password>

  2. Validate the playbook syntax: 

    $ ansible-playbook --syntax-check ~/playbook.yml

    Note that this command only validates the syntax and does not protect against a wrong but valid configuration.

  3. Run the playbook: 

    $ ansible-playbook ~/playbook.yml

Additional resources

  • /usr/share/ansible/roles/microsoft.sql-server/README.md file

16.6. Configuring SQL Server to authenticate with Active Directory (AD) Server

The following procedure shows how to configure SQL Server to authenticate with Active Directory (AD) Server.

Prerequisites

  • An Active Directory domain controller is configured on your network.
  • An applicable RDNS (Reverse DNS) zone exists for both the domain controller and the IP address of the Linux machine that will be running SQL Server.
  • A PTR record that points to your domain controllers exists.
  • The SQL Server host resolves relative domain name, fully qualified domain name, and the IP of the domain controller to the fully qualified domain name of the domain controller.

Procedure

  1. Log in to your AD server through the web UI.
  2. Navigate to Tools > Active Directory Users and Computers > domain.com > Users > sqluser > Account.
  3. In the Account options list, select This account supports Kerberos AES 128 bit encryption and This account supports Kerberos AES 256 bit encryption.
  4. Click Apply

Verification

  1. Use ssh to log in to the client.domain.com machine: 

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

  2. Obtain the Kerberos ticket for the Administrator user: 

    # kinit Administrator@<domain.com>

  3. Use the sqlcmd utility to log in to the SQL Server and, for example, run the following query to view the current user: 

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

16.7. Variables for SQL Server integration with Active Directory Server

You can use the following variables to configure the SQL Server to authenticate with Active Directory (AD) Server.

Table 16.2. Active Directory variables

Role variableDescription

mssql_ad_configure

This variable enables or disables configuration for AD Server authentication. The default value is false. When set to true, the configuration for AD Server authentication is enabled. The role does not remove configuration for AD Server authentication.

mssql_ad_sql_user_name

You can define a username that is going to be created in SQL server and then used for authentication.

mssql_ad_sql_password

You can define a password for a user defined in mssql_ad_sql_user_name that is going to be created in SQL server and then used for authentication.

mssql_ad_sql_user_dn

You have to set the ssql_ad_sql_user_dn variable when your AD server stores user accounts in a custom Organization Unit rather than in the Users Organization Unit.

mssql_ad_netbios_name

You have to set the mssql_ad_netbios_name variable when the NetBIOS domain name of your AD server is not equal to the first subdomain of the domain name that you provide with the ad_integration_realm variable.

16.8. Accepting EULA for MLServices

You must accept all the EULA for the open-source distributions of Python and R packages to install the required SQL Server Machine Learning Services (MLServices).

See /usr/share/doc/mssql-server for the license terms.

Table 16.3. SQL Server Machine Learning Services EULA variables

Role variableDescription

mssql_accept_microsoft_sql_server_standard_eula

This variable determines whether to accept the terms and conditions for installing the mssql-conf package.

To accept the terms and conditions set this variable to true.

The default is false.

Additional resources

  • /usr/share/ansible/roles/microsoft.sql-server/README.md file

16.9. Accepting EULAs for Microsoft ODBC 17

You must accept all the EULAs to install the Microsoft Open Database Connectivity (ODBC) driver.

See /usr/share/doc/msodbcsql17/LICENSE.txt and /usr/share/doc/mssql-tools/LICENSE.txt for the license terms.

Table 16.4. Microsoft ODBC 17 EULA variables

Role variableDescription

mssql_accept_microsoft_odbc_driver_17_for_sql_server_eula

This variable determines whether to accept the terms and conditions for installing the msodbcsql17 package.

To accept the terms and conditions set this variable to true.

The default is false.

mssql_accept_microsoft_cli_utilities_for_sql_server_eula

This variable determines whether to accept the terms and conditions for installing the mssql-tools package.

To accept the terms and conditions set this variable to true.

The default is false.

Additional resources

  • /usr/share/ansible/roles/microsoft.sql-server/README.md file

16.10. High availability variables

You can configure high availability for Microsoft SQL Server with the variables from the table below.

Table 16.5. High availability configuration variables

VariableDescription

mssql_ha_configure

The default value is false.

When it is set to true, performs the following actions:

  • Configures firewall by opening a port from the mssql_ha_listener_port variable and enables the high-availability service in firewall.

  • Configures SQL Server for high availability.

    • Enables Always On Health events.
    • Creates certificate on the primary replica and distributes it to other replicas.
    • Configures endpoint and availability group.
    • Configures the user from the mssql_ha_login variable for Pacemaker.
  • Optional: Includes the system roles ha_cluster role to configure Pacemaker. You must set mssql_ha_cluster_run_role to true and provide all variables that the ha_cluster role requires for a Pacemaker cluster configuration.

mssql_ha_replica_type

This variable specifies which type of replica you can configure on the host. You can set this variable to primary, synchronous, and witness. You must set it to primary only on one host.

mssql_ha_listener_port

The default port is 5022.

The role uses this TCP port to replicate data for an Always On availability group.

mssql_ha_cert_name

You must define the name of the certificate to secure transactions between members of an Always On availability group.

mssql_ha_master_key_password

You must set the password for the master key to use with the certificate.

mssql_ha_private_key_password

You must set the password for the private key to use with the certificate.

mssql_ha_reset_cert

The default value is false.

If it is set to true, resets the certificate which an Always On availability group uses.

mssql_ha_endpoint_name

You must define the name of the endpoint to configure.

mssql_ha_ag_name

You must define the name of the availability group to configure.

mssql_ha_db_names

You can define a list of the databases to replicate, otherwise the role creates a cluster without replicating databases.

mssql_ha_login

The SQL Server Pacemaker resource agent utilizes this user to perform database health checks and manage state transitions from replica to primary server.

mssql_ha_login_password

The password for the mssql_ha_login user in SQL Server.

mssql_ha_cluster_run_role

The default value is false.

This variable defines if this role runs the ha_cluster role.

Note that the ha_cluster role replaces the configuration of the HA cluster on specified nodes, any variables currently configured for the HA cluster are erased and overwritten.

To work around this limitation, the microsoft.sql.server role does not set any variables for the ha_cluster role to ensure that it does not overwrite any existing Pacemaker configuration.

If you want the microsoft.sql.server to run the ha_cluster role, set this variable to true and provide variables for the ha_cluster role with the microsoft.sql.server role call.

Note, this role backs up the database to the /var/opt/mssql/data/ directory.

Additional resources

  • /usr/share/ansible/roles/microsoft.sql-server/README.md file