Chapter 33. Configuring Microsoft SQL Server by using RHEL system roles You can use the microsoft.sql.server Ansible system role to automate the installation and management of Microsoft SQL Server. This role also optimizes Red Hat Enterprise Linux (RHEL) to improve the performance and throughput of SQL Server by applying the mssql TuneD profile. Note During the installation, the role adds repositories for SQL Server and related packages to the managed hosts. Packages in these repositories are provided, maintained, and hosted by Microsoft. 33.1. Installing and configuring SQL Server with an existing TLS certificate by using the microsoft.sql.server Ansible system role If your application requires a Microsoft SQL Server database, you can configure SQL Server with TLS encryption to enable secure communication between the application and the database. By using the microsoft.sql.server Ansible system role, you can automate this process and remotely install and configure SQL Server with TLS encryption. In the playbook, you can use an existing private key and a TLS certificate that was issued by a certificate authority (CA). Depending on the RHEL version on the managed host, the version of SQL Server that you can install differs: RHEL 7.9: SQL Server 2017 and 2019 RHEL 8: SQL Server 2017, 2019, and 2022 RHEL 9.4 and later: SQL Server 2022 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. You installed the ansible-collection-microsoft-sql package or the microsoft.sql collection on the control node. The managed node has 2 GB or more RAM installed. The managed node uses one of the following versions: RHEL 7.9, RHEL 8, RHEL 9.4 or later. You stored the certificate in the sql_crt.pem file in the same directory as the playbook. You stored the private key in the sql_cert.key file in the same directory as the playbook. SQL clients trust the CA that issued the certificate. Procedure Store your sensitive variables in an encrypted file: Create the vault: After the ansible-vault create command opens an editor, enter the sensitive data in the <key> : <value> format: sa_pwd: <sa_password> Save the changes, and close the editor. Ansible encrypts the data in the vault. Create a playbook file, for example ~/playbook.yml , with the following content: --- - name: Installing and configuring Microsoft SQL Server hosts: managed-node-01.example.com vars_files: - vault.yml tasks: - name: SQL Server with an existing private key and certificate ansible.builtin.include_role: name: 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: 2022 mssql_password: "{{ sa_pwd }}" mssql_edition: Developer mssql_tcp_port: 1433 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: true The settings specified in the example playbook include the following: mssql_tls_enable: true Enables TLS encryption. If you enable this setting, you must also define mssql_tls_cert and mssql_tls_private_key . mssql_tls_cert: <path> Sets the path to the TLS certificate stored on the control node. The role copies this file to the /etc/pki/tls/certs/ directory on the managed node. mssql_tls_private_key: <path> Sets the path to the TLS private key on the control node. The role copies this file to the /etc/pki/tls/private/ directory on the managed node. mssql_tls_force: true Replaces the TLS certificate and private key in their destination directories if they exist. For details about all variables used in the playbook, see the /usr/share/ansible/roles/microsoft.sql-server/README.md file on the control node. Validate the playbook syntax: Note that this command only validates the syntax and does not protect against a wrong but valid configuration. Run the playbook: Verification On the SQL Server host, use the sqlcmd utility with the -N parameter to establish an encrypted connection to SQL server and run a query, for example: If the command succeeds, the connection to the server was TLS encrypted. Additional resources /usr/share/ansible/roles/microsoft.sql-server/README.md file Ansible vault 33.2. Installing and configuring SQL Server with a TLS certificate issued from IdM by using the microsoft.sql.server Ansible system role If your application requires a Microsoft SQL Server database, you can configure SQL Server with TLS encryption to enable secure communication between the application and the database. If the SQL Server host is a member in a Red Hat Enterprise Linux Identity Management (IdM) domain, the certmonger service can manage the certificate request and future renewals. By using the microsoft.sql.server Ansible system role, you can automate this process. You can remotely install and configure SQL Server with TLS encryption, and the microsoft.sql.server role uses the certificate Ansible system role to configure certmonger and request a certificate from IdM. Depending on the RHEL version on the managed host, the version of SQL Server that you can install differs: RHEL 7.9: SQL Server 2017 and 2019 RHEL 8: SQL Server 2017, 2019, and 2022 RHEL 9.4 and later: SQL Server 2022 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. You installed the ansible-collection-microsoft-sql package or the microsoft.sql collection on the control node. The managed node has 2 GB or more RAM installed. The managed node uses one of the following versions: RHEL 7.9, RHEL 8, RHEL 9.4 or later. You enrolled the managed node in a Red Hat Enterprise Linux Identity Management (IdM) domain. Procedure Store your sensitive variables in an encrypted file: Create the vault: After the ansible-vault create command opens an editor, enter the sensitive data in the <key> : <value> format: sa_pwd: <sa_password> Save the changes, and close the editor. Ansible encrypts the data in the vault. Create a playbook file, for example ~/playbook.yml , with the following content: --- - name: Installing and configuring Microsoft SQL Server hosts: managed-node-01.example.com vars_files: - vault.yml tasks: - name: SQL Server with certificates issued by Red Hat IdM ansible.builtin.include_role: name: 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: 2022 mssql_password: "{{ sa_pwd }}" mssql_edition: Developer mssql_tcp_port: 1433 mssql_manage_firewall: true mssql_tls_enable: true mssql_tls_certificates: - name: sql_cert dns: server.example.com ca: ipa The settings specified in the example playbook include the following: mssql_tls_enable: true Enables TLS encryption. If you enable this setting, you must also define mssql_tls_certificates . mssql_tls_certificates A list of YAML dictionaries with settings for the certificate role. name: <file_name> Defines the base name of the certificate and private key. The certificate role stores the certificate in the /etc/pki/tls/certs/ <file_name> .crt and the private key in the /etc/pki/tls/private/ <file_name> .key file. dns: <hostname_or_list_of_hostnames> Sets the hostnames that the Subject Alternative Names (SAN) field in the issued certificate contains. You can use a wildcard ( * ) or specify multiple names in YAML list format. ca: <ca_type> Defines how the certificate role requests the certificate. Set the variable to ipa if the host is enrolled in an IdM domain or self-sign to request a self-signed certificate. For details about all variables used in the playbook, see the /usr/share/ansible/roles/microsoft.sql-server/README.md file on the control node. Validate the playbook syntax: Note that this command only validates the syntax and does not protect against a wrong but valid configuration. Run the playbook: Verification On the SQL Server host, use the sqlcmd utility with the -N parameter to establish an encrypted connection to SQL server and run a query, for example: If the command succeeds, the connection to the server was TLS encrypted. Additional resources /usr/share/ansible/roles/microsoft.sql-server/README.md file Requesting certificates by using RHEL system roles Ansible vault 33.3. Installing and configuring SQL Server with custom storage paths by using the microsoft.sql.server Ansible system role When you use the microsoft.sql.server Ansible system role to install and configure a new SQL Server, you can customize the paths and modes of the data and log directories. For example, configure custom paths if you want to store databases and log files in a different directory with more storage. Important If you change the data or log path and re-run the playbook, the previously-used directories and all their content remains at the original path. Only new databases and logs are stored in the new location. Table 33.1. SQL Server default settings for data and log directories Type Directory Mode Owner Group Data /var/opt/mssql/data/ [a] mssql mssql Logs /var/opt/mssql/los/ [a] mssql mssql [a] If the directory exists, the role preserves the mode. If the directory does not exist, the role applies the default umask on the managed node when it creates the directory. 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. You installed the ansible-collection-microsoft-sql package or the microsoft.sql collection on the control node. The managed node has 2 GB or more RAM installed. The managed node uses one of the following versions: RHEL 7.9, RHEL 8, RHEL 9.4 or later. Procedure Store your sensitive variables in an encrypted file: Create the vault: After the ansible-vault create command opens an editor, enter the sensitive data in the <key> : <value> format: sa_pwd: <sa_password> Save the changes, and close the editor. Ansible encrypts the data in the vault. Edit an existing playbook file, for example ~/playbook.yml , and add the storage and log-related variables: --- - name: Installing and configuring Microsoft SQL Server hosts: managed-node-01.example.com vars_files: - vault.yml tasks: - name: SQL Server with custom storage paths ansible.builtin.include_role: name: 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: 2022 mssql_password: "{{ sa_pwd }}" mssql_edition: Developer mssql_tcp_port: 1433 mssql_manage_firewall: true mssql_datadir: /var/lib/mssql/ mssql_datadir_mode: '0700' mssql_logdir: /var/log/mssql/ mssql_logdir_mode: '0700' The settings specified in the example playbook include the following: mssql_datadir_mode and mssql_logdir_mode Set the permission modes. Specify the value in single quotes to ensure that the role parses the value as a string and not as an octal number. For details about all variables used in the playbook, see the /usr/share/ansible/roles/microsoft.sql-server/README.md file on the control node. Validate the playbook syntax: Note that this command only validates the syntax and does not protect against a wrong but valid configuration. Run the playbook: Verification Display the mode of the data directory: Display the mode of the log directory: Additional resources /usr/share/ansible/roles/microsoft.sql-server/README.md file Ansible vault 33.4. Installing and configuring SQL Server with AD integration by using the microsoft.sql.server Ansible system role You can integrate Microsoft SQL Server into an Active Directory (AD) to enable AD users to authenticate to SQL Server. By using the microsoft.sql.server Ansible system role, you can automate this process and remotely install and configure SQL Server accordingly. Note that you must still perform manual steps in AD and SQL Server after you run the playbook. Depending on the RHEL version on the managed host, the version of SQL Server that you can install differs: RHEL 7.9: SQL Server 2017 and 2019 RHEL 8: SQL Server 2017, 2019, and 2022 RHEL 9.4 and later: SQL Server 2022 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. You installed the ansible-collection-microsoft-sql package or the microsoft.sql collection on the control node. The managed node has 2 GB or more RAM installed. The managed node uses one of the following versions: RHEL 7.9, RHEL 8, RHEL 9.4 or later. An AD domain is available in the network. A reverse DNS (RDNS) zone exists in AD, and it contains Pointer (PTR) resource records for each AD domain controller (DC). The managed host's network settings use an AD DNS server. The managed host can resolve the following DNS entries: Both the hostnames and the fully-qualified domain names (FQDNs) of the AD DCs resolve to their IP addresses. The IP addresses of the AD DCs resolve to their FQDNs. Procedure Store your sensitive variables in an encrypted file: Create the vault: After the ansible-vault create command opens an editor, enter the sensitive data in the <key> : <value> format: sa_pwd: <sa_password> sql_pwd: <SQL_AD_password> ad_admin_pwd: <AD_admin_password> Save the changes, and close the editor. Ansible encrypts the data in the vault. Create a playbook file, for example ~/playbook.yml , with the following content: --- - name: Installing and configuring Microsoft SQL Server hosts: managed-node-01.example.com vars_files: - vault.yml tasks: - name: SQL Server with AD authentication ansible.builtin.include_role: name: 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: 2022 mssql_password: "{{ sa_pwd }}" mssql_edition: Developer mssql_tcp_port: 1433 mssql_manage_firewall: true mssql_ad_configure: true mssql_ad_join: true mssql_ad_sql_user: sqluser mssql_ad_sql_password: "{{ sql_pwd }}" ad_integration_realm: ad.example.com ad_integration_user: Administrator ad_integration_password: "{{ ad_admin_pwd }}" The settings specified in the example playbook include the following: mssql_ad_configure: true Enables authentication against AD. mssql_ad_join: true Uses the ad_integration RHEL system role to join the managed node to AD. The role uses the settings from the ad_integration_realm , ad_integration_user , and ad_integration_password variables to join the domain. mssql_ad_sql_user: <username> Sets the name of an AD account that the role should create in AD and SQL Server for administration purposes. ad_integration_user: <AD_user> Sets the name of an AD user with privileges to join machines to the domain and to create the AD user specified in mssql_ad_sql_user . For details about all variables used in the playbook, see the /usr/share/ansible/roles/microsoft.sql-server/README.md file on the control node. Validate the playbook syntax: Note that this command only validates the syntax and does not protect against a wrong but valid configuration. Run the playbook: In your AD domain, enable 128 bit and 256 bit Kerberos authentication for the AD SQL user which you specified in the playbook. Use one of the following options: In the Active Directory Users and Computers application: Navigate to ad.example.com > Users > sqluser > Accounts . In the Account options list, select This account supports Kerberos AES 128 bit encryption and This account supports Kerberos AES 256 bit encryption . Click Apply . In PowerShell in admin mode, enter: Authorize AD users that should be able to authenticate to SQL Server. On the SQL Server, perform the following steps: Obtain a Kerberos ticket for the Administrator user: Authorize an AD user: Repeat this step for every AD user who should be able to access SQL Server. Verification On the managed node that runs SQL Server: Obtain a Kerberos ticket for an AD user: Use the sqlcmd utility to log in to SQL Server and run a query, for example: Additional resources /usr/share/ansible/roles/microsoft.sql-server/README.md file Ansible vault

Commands:
ansible-vault create vault.yml New Vault password: <vault_password> Confirm New Vault password: <vault_password>
sa_pwd: <sa_password>
--- - name: Installing and configuring Microsoft SQL Server hosts: managed-node-01.example.com vars_files: - vault.yml tasks: - name: SQL Server with an existing private key and certificate ansible.builtin.include_role: name: 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: 2022 mssql_password: "{{ sa_pwd }}" mssql_edition: Developer mssql_tcp_port: 1433 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: true
ansible-playbook --ask-vault-pass --syntax-check ~/playbook.yml
ansible-playbook --ask-vault-pass ~/playbook.yml
/opt/mssql-tools/bin/sqlcmd -N -S server.example.com -U "sa" -P <sa_password> -Q 'SELECT SYSTEM_USER'
ansible-vault create vault.yml New Vault password: <vault_password> Confirm New Vault password: <vault_password>
sa_pwd: <sa_password>
--- - name: Installing and configuring Microsoft SQL Server hosts: managed-node-01.example.com vars_files: - vault.yml tasks: - name: SQL Server with certificates issued by Red Hat IdM ansible.builtin.include_role: name: 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: 2022 mssql_password: "{{ sa_pwd }}" mssql_edition: Developer mssql_tcp_port: 1433 mssql_manage_firewall: true mssql_tls_enable: true mssql_tls_certificates: - name: sql_cert dns: server.example.com ca: ipa
ansible-playbook --ask-vault-pass --syntax-check ~/playbook.yml
ansible-playbook --ask-vault-pass ~/playbook.yml
/opt/mssql-tools/bin/sqlcmd -N -S server.example.com -U "sa" -P <sa_password> -Q 'SELECT SYSTEM_USER'
ansible-vault create vault.yml New Vault password: <vault_password> Confirm New Vault password: <vault_password>
sa_pwd: <sa_password>
--- - name: Installing and configuring Microsoft SQL Server hosts: managed-node-01.example.com vars_files: - vault.yml tasks: - name: SQL Server with custom storage paths ansible.builtin.include_role: name: 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: 2022 mssql_password: "{{ sa_pwd }}" mssql_edition: Developer mssql_tcp_port: 1433 mssql_manage_firewall: true mssql_datadir: /var/lib/mssql/ mssql_datadir_mode: '0700' mssql_logdir: /var/log/mssql/ mssql_logdir_mode: '0700'
ansible-playbook --ask-vault-pass --syntax-check ~/playbook.yml
ansible-playbook --ask-vault-pass ~/playbook.yml
ansible managed-node-01.example.com -m command -a 'ls -ld /var/lib/mssql/' drwx------. 12 mssql mssql 4096 Jul 3 13:53 /var/lib/mssql/
ansible managed-node-01.example.com -m command -a 'ls -ld /var/log/mssql/' drwx------. 12 mssql mssql 4096 Jul 3 13:53 /var/log/mssql/
ansible-vault create vault.yml New Vault password: <vault_password> Confirm New Vault password: <vault_password>
sa_pwd: <sa_password> sql_pwd: <SQL_AD_password> ad_admin_pwd: <AD_admin_password>
--- - name: Installing and configuring Microsoft SQL Server hosts: managed-node-01.example.com vars_files: - vault.yml tasks: - name: SQL Server with AD authentication ansible.builtin.include_role: name: 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: 2022 mssql_password: "{{ sa_pwd }}" mssql_edition: Developer mssql_tcp_port: 1433 mssql_manage_firewall: true mssql_ad_configure: true mssql_ad_join: true mssql_ad_sql_user: sqluser mssql_ad_sql_password: "{{ sql_pwd }}" ad_integration_realm: ad.example.com ad_integration_user: Administrator ad_integration_password: "{{ ad_admin_pwd }}"
ansible-playbook --ask-vault-pass --syntax-check ~/playbook.yml
ansible-playbook --ask-vault-pass ~/playbook.yml
C:\> Set-ADUser -Identity sqluser -KerberosEncryptionType AES128,AES256
kinit Administrator@ad.example.com
/opt/mssql-tools/bin/sqlcmd -S. -Q 'CREATE LOGIN [AD\<AD_user>] FROM WINDOWS;'
kinit <AD_user> @ad.example.com
/opt/mssql-tools/bin/sqlcmd -S. -Q 'SELECT SYSTEM_USER'