Red Hat Summit Red Hat SummitSupportConsoleDéveloppeursCommencer un essaiContact

Ce contenu n'est pas disponible dans la langue sélectionnée.

Chapter 31. 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.

By using the microsoft.sql.server Ansible system role, you can automate the installation and configuration of Microsoft 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).

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. 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.
  • The account you use to connect to the managed nodes has sudo permissions for these nodes.
  • 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

  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>
      Copy to Clipboard Toggle word wrap

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

      sa_pwd: <sa_password>
      Copy to Clipboard Toggle word wrap
    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_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_self_sign: false
        mssql_tls_cert: sql_crt.pem
        mssql_tls_private_key: sql_cert.key
        mssql_tls_version: 1.2
        mssql_tls_force: true
    Copy to Clipboard Toggle word wrap

    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_self_sign: false
    Indicates whether the certificates that you use are self-signed or not. Based on this setting, the role decides whether to run the sqlcmd command with the -C argument to trust certificates.
    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
    Copy to Clipboard Toggle word wrap

    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
    Copy to Clipboard Toggle word wrap

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: 

    $ /opt/mssql-tools/bin/sqlcmd -N -S server.example.com -U "sa" -P <sa_password> -Q 'SELECT SYSTEM_USER'
    Copy to Clipboard Toggle word wrap

    If the command succeeds, the connection to the server was TLS encrypted.

By using the microsoft.sql.server Ansible system role, you can automate the installation and configuration of Microsoft SQL Server with TLS encryption.

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 am Identity Management (IdM) in Red Hat Enterprise Linux domain, the certmonger service can manage the certificate request and future renewals.

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.
  • The account you use to connect to the managed nodes has sudo permissions for these nodes.
  • 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 an IdM domain.

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>
      Copy to Clipboard Toggle word wrap

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

      sa_pwd: <sa_password>
      Copy to Clipboard Toggle word wrap
    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_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
    Copy to Clipboard Toggle word wrap

    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
    Copy to Clipboard Toggle word wrap

    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
    Copy to Clipboard Toggle word wrap

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: 

    $ /opt/mssql-tools/bin/sqlcmd -N -S server.example.com -U "sa" -P <sa_password> -Q 'SELECT SYSTEM_USER'
    Copy to Clipboard Toggle word wrap

    If the command succeeds, the connection to the server was TLS encrypted.

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.

Expand
Table 31.1. SQL Server default settings for data and log directories
TypeDirectoryModeOwnerGroup

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.
  • The account you use to connect to the managed nodes has sudo permissions for these nodes.
  • 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

  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>
      Copy to Clipboard Toggle word wrap

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

      sa_pwd: <sa_password>
      Copy to Clipboard Toggle word wrap
    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_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'
    Copy to Clipboard Toggle word wrap

    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
    Copy to Clipboard Toggle word wrap

    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
    Copy to Clipboard Toggle word wrap

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/
    Copy to Clipboard Toggle word wrap

  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/
    Copy to Clipboard Toggle word wrap

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.

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

Note that you must still perform manual steps in AD and SQL Server after you run the playbook.

Prerequisites

  • You have prepared the control node and the managed nodes.
  • The account you use to connect to the managed nodes has sudo permissions for these nodes.
  • 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

  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>
      Copy to Clipboard Toggle word wrap

    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>
      Copy to Clipboard Toggle word wrap
    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_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 }}"
    Copy to Clipboard Toggle word wrap

    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
    Copy to Clipboard Toggle word wrap

    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
    Copy to Clipboard Toggle word wrap

  5. 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
      Copy to Clipboard Toggle word wrap

    2. Authorize an AD user: 

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

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

Verification

  • On the managed node that runs SQL Server:

    1. Obtain a Kerberos ticket for an AD user: 

      $ kinit <AD_user>@ad.example.com
      Copy to Clipboard Toggle word wrap

    2. Use the sqlcmd utility to log in to SQL Server and run a query, for example: 

      $ /opt/mssql-tools/bin/sqlcmd -S. -Q 'SELECT SYSTEM_USER'
      Copy to Clipboard Toggle word wrap
Retour au début
Red Hat logoGithubredditYoutubeTwitter

Apprendre

Essayez, achetez et vendez

Communautés

À propos de la documentation Red Hat

Nous aidons les utilisateurs de Red Hat à innover et à atteindre leurs objectifs grâce à nos produits et services avec un contenu auquel ils peuvent faire confiance. Découvrez nos récentes mises à jour.

Rendre l’open source plus inclusif

Red Hat s'engage à remplacer le langage problématique dans notre code, notre documentation et nos propriétés Web. Pour plus de détails, consultez le Blog Red Hat.

À propos de Red Hat

Nous proposons des solutions renforcées qui facilitent le travail des entreprises sur plusieurs plates-formes et environnements, du centre de données central à la périphérie du réseau.

Theme

© 2026 Red Hat