Skip to content

sql-server-linux-active-directory-authentication.md

Latest commit

 

History

History
291 lines (200 loc) · 15.3 KB

sql-server-linux-active-directory-authentication.md

File metadata and controls

291 lines (200 loc) · 15.3 KB
title Active Directory authentication tutorial for SQL Server on Linux | Microsoft Docs
description This tutorial provides the configuration steps for AAD authentication for SQL Server on Linux.
author meet-bhagdev
ms.date 02/23/2018
ms.author meetb
manager craigg
ms.topic article
ms.prod sql
ms.component
ms.suite sql
ms.custom sql-linux
ms.technology linux
helpviewer_keywords
Linux, AAD authentication

Tutorial: Use Active Directory authentication with SQL Server on Linux

[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md-linuxonly]

This tutorial explains how to configure [!INCLUDEssNoVersion] on Linux to support Active Directory (AD) authentication, also known as integrated authentication. For an overview, see Active Directory authentication for SQL Server on Linux.

This tutorial consists of the following tasks:

[!div class="checklist"]

  • Join [!INCLUDEssNoVersion] host to AD domain
  • Create AD user for [!INCLUDEssNoVersion] and set SPN
  • Configure [!INCLUDEssNoVersion] service keytab
  • Create AD-based logins in Transact-SQL
  • Connect to [!INCLUDEssNoVersion] using AD Authentication

Prerequisites

Before you configure AD Authentication, you need to:

Join [!INCLUDEssNoVersion] host to AD domain

Use the following steps to join a [!INCLUDEssNoVersion] host to an Active Directory domain:

  1. Use realmd to join your host machine to your AD Domain. If you haven't already, install both the realmd and Kerberos client packages on the [!INCLUDEssNoVersion] host machine using your Linux distribution's package manager:

    # RHEL
sudo yum install realmd krb5-workstation

# SUSE
sudo zypper install realmd krb5-client

# Ubuntu
sudo apt-get install realmd krb5-user software-properties-common python-software-properties packagekit

  2. If the Kerberos client package installation prompts you for a realm name, enter your domain name in uppercase.

    [!NOTE] This walkthrough uses "contoso.com" and "CONTOSO.COM" as example domain and realm names, respectively. You should replace these with your own values. These commands are case-sensitive, so make sure you use uppercase wherever it is used in this walkthrough.

  3. Configure your [!INCLUDEssNoVersion] host machine to use your AD domain controller's IP address as a DNS nameserver.

    • Ubuntu:

      Edit the /etc/network/interfaces file so that your AD domain controller's IP address is listed as a dns-nameserver. For example:

      <...>
# The primary network interface
auth eth0
iface eth0 inet dhcp
dns-nameservers **<AD domain controller IP address>**
dns-search **<AD domain name>**

      [!NOTE] The network interface (eth0) might differ for different machines. To find out which one you are using, run ifconfig and copy the interface that has an IP address and transmitted and received bytes.

      After editing this file, restart the network service:

      sudo ifdown eth0 && sudo ifup eth0

      Now check that your /etc/resolv.conf file contains a line like the following example:

      nameserver **<AD domain controller IP address>**

    • RHEL:

      Edit the /etc/sysconfig/network-scripts/ifcfg-eth0 file (or other interface config file as appropriate) so that your AD domain controller's IP address is listed as a DNS server:

      <...>
PEERDNS=no
DNS1=**<AD domain controller IP address>**

      After editing this file, restart the network service:

      sudo systemctl restart network

      Now check that your /etc/resolv.conf file contains a line like the following example:

      nameserver **<AD domain controller IP address>**

  4. Join the domain

    Once you've confirmed that your DNS is configured properly, join the domain by running the following command. You must authenticate using an AD account that has sufficient privileges in AD to join a new machine to the domain.

    Specifically, this command creates a new computer account in AD, create the /etc/krb5.keytab host keytab file, and configure the domain in /etc/sssd/sssd.conf:

    sudo realm join contoso.com -U 'user@CONTOSO.COM' -v
<...>
* Successfully enrolled machine in realm

    [!NOTE] If you see an error, "Necessary packages are not installed," then you should install those packages using your Linux distribution's package manager before running the realm join command again.

    If you receive an error, "Insufficient permissions to join the domain," then you need to check with a domain administrator that you have sufficient permissions to join Linux machines to your domain.

    If you receive an error, "KDC reply did not match expectations," then you may not have specified the correct realm name for the user. Realm names are case-sensitive, usually uppercase, and can be identified with the command realm discover contoso.com.

    SQL Server uses SSSD and NSS for mapping user accounts and groups to security identifiers (SID's). SSSD must be configured and running in order for SQL Server to create AD logins successfully. Realmd usually does this automatically as part of joining the domain, but in some cases you must do this separately.

    Check out the following to configure SSSD manually, and configure NSS to work with SSSD

  5. Verify that you can now gather information about a user from the domain, and that you can acquire a Kerberos ticket as that user.

    The following example uses id, kinit, and klist commands for this.

    id user@contoso.com
uid=1348601103(user@contoso.com) gid=1348600513(domain group@contoso.com) groups=1348600513(domain group@contoso.com)

kinit user@CONTOSO.COM
Password for user@CONTOSO.COM:

klist
Ticket cache: FILE:/tmp/krb5cc_1000
Default principal: user@CONTOSO.COM
<...>

    [!NOTE] If id user@contoso.com returns, "No such user," make sure that the SSSD service started successfully by running the command sudo systemctl status sssd. If the service is running and you still see the "No such user" error, try enabling verbose logging for SSSD. For more information, see the Red Hat documentation for Troubleshooting SSSD.

    If kinit user@CONTOSO.COM returns, "KDC reply did not match expectations while getting initial credentials," make sure you specified the realm in uppercase.

For more information, see the Red Hat documentation for Discovering and Joining Identity Domains.

Create AD user for [!INCLUDEssNoVersion] and set SPN

Note

The next steps use your fully qualified domain name. If you are on Azure, you must create one before you proceed.

  1. On your domain controller, run the New-ADUser PowerShell command to create a new AD user with a password that never expires. This example names the account "mssql," but the account name can be anything you like. You will be prompted to enter a new password for the account:

    Import-Module ActiveDirectory

New-ADUser mssql -AccountPassword (Read-Host -AsSecureString "Enter Password") -PasswordNeverExpires $true -Enabled $true

    [!NOTE] It is a security best practice to have a dedicated AD account for SQL Server, so that SQL Server's credentials aren't shared with other services using the same account. However, you can reuse an existing AD account if you prefer, if you know the account's password (required to generate a keytab file in the next step).

  2. Set the ServicePrincipalName (SPN) for this account using the setspn.exe tool. The SPN must be formatted exactly as specified in the following example. You can find the fully qualified domain name of the [!INCLUDEssNoVersion] host machine by running hostname --all-fqdns on the [!INCLUDEssNoVersion] host, and the TCP port should be 1433 unless you have configured [!INCLUDEssNoVersion] to use a different port number.

    setspn -A MSSQLSvc/**<fully qualified domain name of host machine>**:**<tcp port>** mssql

    [!NOTE] If you receive an error, "Insufficient access rights," then you need to check with a domain administrator that you have sufficient permissions to set an SPN on this account.

    If you change the TCP port in the future, then you need to run the setspn command again with the new port number. You also need to add the new SPN to the SQL Server service keytab by following the steps in the next section.

  3. For more information, see Register a Service Principal Name for Kerberos Connections.

Configure [!INCLUDEssNoVersion] service keytab

  1. Check the Key Version Number (kvno) for the AD account created in the previous step. Usually it is 2, but it could be another integer if you changed the account's password multiple times. On the [!INCLUDEssNoVersion] host machine, run the following:

    kinit user@CONTOSO.COM

kvno MSSQLSvc/**<fully qualified domain name of host machine>**:**<tcp port>**

  2. Create a keytab file with ktutil for the AD user you created in the previous step. When prompted, enter the password for that AD account.

    sudo ktutil

ktutil: addent -password -p MSSQLSvc/**<fully qualified domain name of host machine>**:**<tcp port>**@CONTOSO.COM -k **<kvno from above>** -e aes256-cts-hmac-sha1-96

ktutil: addent -password -p MSSQLSvc/**<fully qualified domain name of host machine>**:**<tcp port>**@CONTOSO.COM -k **<kvno from above>** -e rc4-hmac

ktutil: wkt /var/opt/mssql/secrets/mssql.keytab

quit

    [!NOTE] The ktutil tool does not validate the password, so make sure you enter it correctly.

  3. Anyone with access to this keytab file can impersonate [!INCLUDEssNoVersion] on the domain, so make sure you restrict access to the file such that only the mssql account has read access:

    sudo chown mssql:mssql /var/opt/mssql/secrets/mssql.keytab
sudo chmod 400 /var/opt/mssql/secrets/mssql.keytab

  4. Configure [!INCLUDEssNoVersion] to use this keytab file for Kerberos authentication:

    sudo /opt/mssql/bin/mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab
sudo systemctl restart mssql-server

Create AD-based logins in Transact-SQL

  1. Connect to [!INCLUDEssNoVersion] and create a new, AD-based login:

    CREATE LOGIN [CONTOSO\user] FROM WINDOWS;

  2. Verify that the login is now listed in the sys.server_principals system catalog view:

    SELECT name FROM sys.server_principals;

Connect to [!INCLUDEssNoVersion] using AD Authentication

Log in to a client machine using your domain credentials. Now you can connect to [!INCLUDEssNoVersion] without reentering your password, by using AD Authentication. If you create a login for an AD group, any AD user who is a member of that group can connect in the same way.

The specific connection string parameter for clients to use AD Authentication depends on which driver you are using. Consider the following examples:

Next steps

In this tutorial, we walked through how to set up Active Directory authentication with SQL Server on Linux. You learned how to:

[!div class="checklist"]

  • Join [!INCLUDEssNoVersion] host to AD domain
  • Create AD user for [!INCLUDEssNoVersion] and set SPN
  • Configure [!INCLUDEssNoVersion] service keytab
  • Create AD-based logins in Transact-SQL
  • Connect to [!INCLUDEssNoVersion] using AD Authentication

Next, explore other security scenarios for SQL Server on Linux.

[!div class="nextstepaction"] Encrypting Connections to SQL Server on Linux