Chapter 17. Configuring Microsoft SQL Server by using the Ansible system roles

Procedure

1. Store your sensitive variables in an encrypted file:

   1. Create the vault:

      $ ansible-vault create vault.yml
      New Vault password: <vault_password>
      Confirm New Vault password: <vault_password>

   2. After the ansible-vault create command opens an editor, enter the sensitive data in the <key>: <value> format:

      sa_pwd: <sa_password>

   3. Save the changes, and close the editor. Ansible encrypts the data in the vault.

2. 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.

3. Validate the playbook syntax:

   $ ansible-playbook --ask-vault-pass --syntax-check ~/playbook.yml

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

4. Run the playbook:

   $ ansible-playbook --ask-vault-pass ~/playbook.yml

Procedure

1. Store your sensitive variables in an encrypted file:

   1. Create the vault:

      $ ansible-vault create vault.yml
      New Vault password: <vault_password>
      Confirm New Vault password: <vault_password>

   2. After the ansible-vault create command opens an editor, enter the sensitive data in the <key>: <value> format:

      sa_pwd: <sa_password>

   3. Save the changes, and close the editor. Ansible encrypts the data in the vault.

2. 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.

3. Validate the playbook syntax:

   $ ansible-playbook --ask-vault-pass --syntax-check ~/playbook.yml

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

4. Run the playbook:

   $ ansible-playbook --ask-vault-pass ~/playbook.yml

Procedure

1. Store your sensitive variables in an encrypted file:

   1. Create the vault:

      $ ansible-vault create vault.yml
      New Vault password: <vault_password>
      Confirm New Vault password: <vault_password>

   2. After the ansible-vault create command opens an editor, enter the sensitive data in the <key>: <value> format:

      sa_pwd: <sa_password>

   3. Save the changes, and close the editor. Ansible encrypts the data in the vault.

2. 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.

3. Validate the playbook syntax:

   $ ansible-playbook --ask-vault-pass --syntax-check ~/playbook.yml

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

4. Run the playbook:

   $ ansible-playbook --ask-vault-pass ~/playbook.yml

Verification

1. Display the mode of the data directory:

   $ 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/

2. Display the mode of the log directory:

   $ 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/

Procedure

1. Store your sensitive variables in an encrypted file:

   1. Create the vault:

      $ ansible-vault create vault.yml
      New Vault password: <vault_password>
      Confirm New Vault password: <vault_password>

   2. 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>

   3. Save the changes, and close the editor. Ansible encrypts the data in the vault.

2. 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.

3. Validate the playbook syntax:

   $ ansible-playbook --ask-vault-pass --syntax-check ~/playbook.yml

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

4. Run the playbook:

   $ ansible-playbook --ask-vault-pass ~/playbook.yml

5. 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:

     1. Navigate to ad.example.com > Users > sqluser > Accounts.
     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.

   • In PowerShell in admin mode, enter:

     C:\> Set-ADUser -Identity sqluser -KerberosEncryptionType AES128,AES256

6. Authorize AD users that should be able to authenticate to SQL Server. On the SQL Server, perform the following steps:

   1. Obtain a Kerberos ticket for the Administrator user:

      $ kinit Administrator@ad.example.com

   2. Authorize an AD user:

      $ /opt/mssql-tools/bin/sqlcmd -S. -Q 'CREATE LOGIN [AD\<AD_user>] FROM WINDOWS;'

      Repeat this step for every AD user who should be able to access SQL Server.