教程:对 Linux 上的 SQL Server 使用 Active Directory 身份验证Tutorial: Use Active Directory authentication with SQL Server on Linux

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) - Linux适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) - Linux

本教程介绍如何在 Linux 上配置 SQL ServerSQL Server 以支持 Active Directory (AD) 身份验证(也称为集成身份验证)。This tutorial explains how to configure SQL ServerSQL Server on Linux to support Active Directory (AD) authentication, also known as integrated authentication. 有关概述,请参阅 Linux 上的 SQL Server 的 Active Directory 身份验证For an overview, see Active Directory authentication for SQL Server on Linux.

本教程包含以下任务:This tutorial consists of the following tasks:

  • SQL ServerSQL Server 主机加入 AD 域Join SQL ServerSQL Server host to AD domain
  • SQL ServerSQL Server 创建 AD 用户并设置 SPNCreate AD user for SQL ServerSQL Server and set SPN
  • 配置 SQL ServerSQL Server 服务 keytabConfigure SQL ServerSQL Server service keytab
  • 保护 keytab 文件Secure the keytab file
  • 将 SQL Server 配置为使用 keytab 文件进行 Kerberos 身份验证Configure SQL Server to use the keytab file for Kerberos authentication
  • 在 Transact-SQL 中创建基于 AD 的登录名Create AD-based logins in Transact-SQL
  • 使用 AD 身份验证连接到 SQL ServerSQL ServerConnect to SQL ServerSQL Server using AD Authentication

先决条件Prerequisites

在配置 AD 身份验证前,需要:Before you configure AD Authentication, you need to:

SQL ServerSQL Server 主机加入 AD 域Join SQL ServerSQL Server host to AD domain

将 SQL Server Linux 主机加入 Active Directory 域控制器。Join your SQL Server Linux host with an Active Directory domain controller. 有关如何加入 Active Directory 域的信息,请参阅将 Linux 主机上的 SQL Server 加入 Active Directory 域For information on how to join an active directory domain, see Join SQL Server on a Linux host to an Active Directory domain.

SQL ServerSQL Server 创建 AD 用户(或 MSA)并设置 SPNCreate AD user (or MSA) for SQL ServerSQL Server and set SPN

备注

以下步骤使用完全限定的域名The following steps use your fully qualified domain name. 如果位于 Azure 中,则必须 创建一个 才能继续操作。If you are on Azure, you must create one before you proceed.

  1. 在域控制器上,运行 New-ADUser PowerShell 命令以创建密码永不过期的新 AD 用户。On your domain controller, run the New-ADUser PowerShell command to create a new AD user with a password that never expires. 以下示例将帐户命名为 mssql,但帐户名称可以是你喜欢的任何名称。The following example names the account mssql, but the account name can be anything you like. 系统将提示输入帐户的新密码。You'll 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
    

    备注

    为 SQL Server 提供专用 AD 帐户是最佳安全做法,这样一来,SQL Server 的凭据就不会与使用同一帐户的其他服务共享。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. 但是,如果你知道帐户的密码(在下一步中生成 keytab 文件时需要),则可以选择重复使用现有 AD 帐户。However, you can optionally reuse an existing AD account if you know the account's password (which is required to generate a keytab file in the next step). 此外,应在用户帐户上启用该帐户以支持 128 位和 256 位 Kerberos AES 加密(msDS-SupportedEncryptionTypes 属性) 。Additionally, the account should be enabled to support 128-bit and 256-bit Kerberos AES encryption (msDS-SupportedEncryptionTypes attribute) on the user account. 若要验证帐户是否已启用 AES 加密,请在“Active Directory 用户和计算机”实用工具中找到帐户,并选择“属性”。 To validate the account is enabled for AES encryption, locate the account in Active Directory Users and Computers utility, and select Properties. 在“属性”中找到“帐户”选项卡,并验证是否选中了具有以下标题的两个复选框。 Locate Accounts tab in the Properties and validate two checkboxes titled following are selected.

    1. 此帐户支持 Kerberos AES 128 位加密This account supports Kerberos AES 128 bit encryption

    2. 此帐户支持 Kerberos AES 256 位加密This account supports Kerberos AES 256 bit encryption

  2. 使用 setspn.exe 工具为此帐户设置 ServicePrincipalName (SPN)。Set the ServicePrincipalName (SPN) for this account using the setspn.exe tool. 必须完全按照以下示例设置 SPN 的格式。The SPN must be formatted exactly as specified in the following example. 可通过在 SQL ServerSQL Server 主机上运行 hostname --all-fqdns 来查找 SQL ServerSQL Server 主机的完全限定域名。You can find the fully qualified domain name of the SQL ServerSQL Server host machine by running hostname --all-fqdns on the SQL ServerSQL Server host. 除非已将 SQL ServerSQL Server 配置为使用其他端口号,否则 TCP 端口应为 1433。The TCP port should be 1433 unless you have configured SQL ServerSQL Server to use a different port number.

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

    备注

    如果收到错误 (Insufficient access rights),请与域管理员联系,确保你有足够的权限在此帐户上设置 SPN。If you receive an error, Insufficient access rights, check with your domain administrator that you have sufficient permissions to set an SPN on this account. 用于注册 SPN 的帐户将需要“写入 servicePrincipalName”权限。The account that is used to register an SPN will need the Write servicePrincipalName permissions. 有关详细信息,请参阅 为 Kerberos 连接注册服务主体名称For more information, see Register a Service Principal Name for Kerberos Connections.

    如果以后更改 TCP 端口,则必须使用新端口号再次运行 setspn 命令。If you change the TCP port in the future, you must run the setspn command again with the new port number. 还需要按照下一部分中的步骤将新的 SPN 添加到 SQL Server 服务 keytab。You also need to add the new SPN to the SQL Server service keytab by following the steps in the next section.

有关详细信息,请参阅 为 Kerberos 连接注册服务主体名称For more information, see Register a Service Principal Name for Kerberos Connections.

配置 SQL ServerSQL Server 服务 keytabConfigure SQL ServerSQL Server service keytab

为 Linux 上的 SQL Server 配置 AD 身份验证,需要 AD 帐户(MSA 或 AD 用户帐户)和在上一部分创建的 SPN。Configuring AD authentication for SQL Server on Linux requires an AD account (MSA or an AD user account) and the SPN created in the the previous section.

重要

如果更改了 AD 帐户的密码或更改了 SPN 所分配到的帐户的密码,则必须使用新密码和密钥版本号 (KVNO) 更新 keytab。If the password for the AD account is changed or the password for the account that the SPNs are assigned to is changed, you must update the keytab with the new password and Key Version Number (KVNO). 某些服务可能还会自动轮换密码。Some services might also rotate the passwords automatically. 查看相关帐户的所有密码轮换策略,并使其与计划性维护活动保持一致,以避免产生意外停机时间。Review any password rotation policies for the accounts in question and align them with scheduled maintenance activities to avoid unexpected downtime.

SPN keytab 条目SPN keytab entries

  1. 检查上一步中创建的 AD 帐户的密钥版本号 (KVNO)。Check the Key Version Number (KVNO) for the AD account created in the previous step. 它通常为 2,但如果多次更改帐户的密码,则它可能是其他整数。Usually it is 2, but it could be another integer if you changed the account's password multiple times. 在 SQL Server 主机上,运行以下命令:On the SQL Server host machine, run the following commands:

    • 以下示例假定 user@CONTOSO.COM 域中。The below examples assume the user is in the @CONTOSO.COM domain. 将用户和域名修改为你的用户和域名。Modify the user and domain name to your user and domain name.
    kinit user@CONTOSO.COM
    kvno user@CONTOSO.COM
    kvno MSSQLSvc/<fully qualified domain name of host machine>:<tcp port>@CONTOSO.COM
    

    备注

    SPN 可能需要几分钟才能在域中传播,特别是在域很大的情况下。SPNs can take several minutes to propagate through your domain, especially if the domain is large. 如果收到错误 (kvno: Server not found in Kerberos database while getting credentials for MSSQLSvc/<fully qualified domain name of host machine>:<tcp port>@CONTOSO.COM),请等待几分钟,然后重试。If you receive the error, kvno: Server not found in Kerberos database while getting credentials for MSSQLSvc/<fully qualified domain name of host machine>:<tcp port>@CONTOSO.COM, please wait a few minutes and try again.
    以上命令只有在服务器已加入 AD 域时才起作用,上一部分已说明。The above commands will only work if the server has been joined to an AD domain, which was covered in the previous section.

  2. 使用 ktpass,然后在 Windows 计算机命令提示符处使用以下命令为每个 SPN 添加 keytab 条目:Using ktpass, add keytab entries for each SPN using the following commands on a Windows machine Command Prompt:

    • <DomainName>\<UserName> - 可以是 MSA 或 AD 用户帐户<DomainName>\<UserName> - Could be an MSA or AD user account
    • @CONTOSO.COM - 使用你的域名@CONTOSO.COM - Use your domain name
    • /kvno <#> - 将 <#> 替换为之前步骤中获得的 KVNO/kvno <#> - Replace <#> with the KVNO obtained in an earlier step
    • <StrongPassword> - 使用强密码<StrongPassword> - Use a strong password
    ktpass /princ MSSQLSvc/<fully qualified domain name of host machine>:<tcp port>@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser <DomainName>\<UserName> /out mssql.keytab -setpass -setupn /kvno <#> /pass <StrongPassword>
    
    ktpass /princ MSSQLSvc/<fully qualified domain name of host machine>:<tcp port>@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser <DomainName>\<UserName> /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno <#> /pass <StrongPassword>
    
    ktpass /princ MSSQLSvc/<netbios name of the host machine>:<tcp port>@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser <DomainName>\<UserName> /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno <#> /pass <StrongPassword>
    
    ktpass /princ MSSQLSvc/<netbios name of the host machine>:<tcp port>@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser <DomainName>\<UserName> /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno <#> /pass <StrongPassword>
    
    ktpass /princ <UserName>@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser <DomainName>\<UserName> /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno <#> /pass <StrongPassword>
    
    ktpass /princ <UserName>@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser <DomainName>\<UserName> /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno <#> /pass <StrongPassword>
    

    备注

    以上命令允许使用 AES 和 RC4 加密密码进行 AD 身份验证。The commands above allow both AES and RC4 encryption ciphers for AD authentication. RC4 是一个比较旧的加密密码,如果需要更高的安全性,可以选择仅使用 AES 加密密码创建 keytab 条目。RC4 is an older encryption cipher and if a higher degree of security is required, you can choose to create the keytab entries with only the AES encryption cipher. 最后两个 UserName 条目必须为小写,否则权限身份验证可能会失败。The last two UserName entries must be in lowercase, or the permssion authentication might fail.

  3. 执行上述命令后,你将拥有一个名为 mssql.keytab 的 keytab 文件。After executing the above command, you should have a keytab file named mssql.keytab. 将该文件复制到 /var/opt/mssql/secrets 文件夹下的 SQL Server 计算机。Copy the file over to the SQL Server machine under the folder /var/opt/mssql/secrets.

  4. 保护 keytab 文件。Secure the keytab file.

    有权访问此 keytab 文件的任何人都可以在域上模拟 SQL Server,因此,请确保限制对该文件的访问,以便只有 mssql 帐户具有读取访问权限:Anyone with access to this keytab file can impersonate SQL Server 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
    
  5. 以下配置选项需要使用 mssql-conf 工具来设置,以指定访问 keytab 文件时要使用的帐户。The following configuration option needs to be set with the mssql-conf tool to specify the account to be used while accessing the keytab file.

    sudo mssql-conf set network.privilegedadaccount <username>
    

    备注

    仅包括用户名,而不是域名\用户名或 username@domain。Only include the username and not domainname\username or username@domain. SQL Server 在内部添加域名以及该用户名(如果需要使用)。SQL Server internally adds domain name as required along with this username when used.

  6. 使用以下步骤配置 SQL Server,以开始使用 keytab 文件进行 Kerberos 身份验证。Use the following steps to configure SQL Server to start using the keytab file for Kerberos authentication.

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

    提示

    (可选)禁用与域控制器的 UDP 连接以提高性能。Optionally, disable UDP connections to the domain controller to improve performance. 在许多情况下,UDP 连接在连接到域控制器时始终会失败,因此,可在 /etc/krb5.conf 中设置配置选项以跳过 UDP 调用。In many cases, UDP connections consistently fail when connecting to a domain controller, so you can set config options in /etc/krb5.conf to skip UDP calls. 编辑 /etc/krb5.conf 并设置以下选项:Edit /etc/krb5.conf and set the following options:

    /etc/krb5.conf
    [libdefaults]
    udp_preference_limit=0
    

此时,你已准备好在 SQL Server 中使用基于 AD 的登录名。At this point, you are ready to use AD-based logins in SQL Server.

在 Transact-SQL 中创建基于 AD 的登录名Create AD-based logins in Transact-SQL

  1. 连接到 SQL Server 并创建基于 AD 的新登录名:Connect to SQL Server and create a new, AD-based login:

    CREATE LOGIN [CONTOSO\user] FROM WINDOWS;
    
  2. 验证登录名现在是否在 sys.server_principals 系统目录视图中列出:Verify that the login is now listed in the sys.server_principals system catalog view:

    SELECT name FROM sys.server_principals;
    

使用 AD 身份验证连接到 SQL ServerConnect to SQL Server using AD Authentication

使用域凭据登录客户端计算机。Log in to a client machine using your domain credentials. 现在,你可以使用 AD 身份验证连接到 SQL Server,而无需重新输入密码。Now you can connect to SQL Server without reentering your password by using AD Authentication. 如果为 AD 组创建登录名,则身为该组成员的任何 AD 用户都能够以相同方式连接。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.

客户端用于 AD 身份验证的特定连接字符串参数取决于你使用的驱动程序。The specific connection string parameter for clients to use AD Authentication depends on which driver you are using. 请考虑以下部分中的示例。Consider the examples in the following sections.

已加入域的 Linux 客户端上的 sqlcmdsqlcmd on a domain-joined Linux client

使用 ssh 和域凭据登录已加入域的 Linux 客户端:Log in to a domain-joined Linux client using ssh and your domain credentials:

ssh -l user@contoso.com client.contoso.com

确保已安装 mssql-tools 包,然后使用 sqlcmd 进行连接,而无需指定任何凭据:Make sure you've installed the mssql-tools package, then connect using sqlcmd without specifying any credentials:

sqlcmd -S mssql-host.contoso.com

与 SQL Windows 不同,Kerberos 身份验证用于 SQL Linux 中的本地连接。Different from SQL Windows, Kerberos authentication works for local connection in SQL Linux. 但是,你依旧需要提供 SQL Linux 主机的 FQDN,并且如果你尝试连接到“.”、“localhost”、“127.0.0.1”等,AD 身份验证将不起作用。However, you still need to provide the FQDN of the SQL Linux host, and AD Authentication will not work if you attempt to connect to '.' ,'localhost','127.0.0.1',etc.

已加入域的 Windows 客户端上的 SSMSSSMS on a domain-joined Windows client

使用域凭据登录已加入域的 Windows 客户端。Log in to a domain-joined Windows client using your domain credentials. 确保已安装 SQL Server Management Studio,然后通过在“连接到服务器”对话框中指定“Windows 身份验证”来连接到 SQL Server 实例(例如,mssql-host.contoso.com)。Make sure SQL Server Management Studio is installed, then connect to your SQL Server instance (for example, mssql-host.contoso.com) by specifying Windows Authentication in the Connect to Server dialog.

使用其他客户端驱动程序的 AD 身份验证AD Authentication using other client drivers

下表介绍适用于其他客户端驱动程序的建议:The following table describes recommendations for other client drivers:

客户端驱动程序Client driver 建议Recommendation
JDBCJDBC 使用 Kerberos 集成身份验证连接到 SQL Server。Use Kerberos Integrated Authentication to Connect SQL Server.
ODBCODBC 使用集成身份验证。Use Integrated Authentication.
ADO.NETADO.NET 连接字符串语法。Connection String Syntax.

其他配置选项Additional configuration options

如果使用第三方实用工具(例如 PBISVASCentrify)将 Linux 主机加入 AD 域,并且希望强制 SQL Server 直接使用 openldap 库,则可使用 mssql-conf 配置 disablesssd 选项,如下所示:If you are using third-party utilities such as PBIS, VAS, or Centrify to join the Linux host to AD domain and you would like to force SQL server in using the openldap library directly, you can configure the disablesssd option with mssql-conf as follows:

sudo mssql-conf set network.disablesssd true
systemctl restart mssql-server

备注

有些实用工具(例如 realmd)会设置 SSSD,而其他工具(例如 PBIS、VAS 和 Centrify)则不会设置 SSSD。There are utilities such as realmd which set up SSSD, while other tools such as PBIS, VAS and Centrify do not setup SSSD. 如果用于加入 AD 域的实用工具不会设置 SSSD,则建议将 disablesssd 选项配置为 trueIf the utility used to join AD domain does not setup SSSD, it is recommended to configure disablesssd option to true. 虽然不是必需的(因为 SQL Server 会在回退到 openldap 机制前尝试将 SSSD 用于 AD),但配置该选项可提高性能,以便 SQL Server 直接绕过 SSSD 机制来进行 openldap 调用。While it is not required as SQL Server will attempt to use SSSD for AD before falling back to openldap mechanism, it would be more performant to configure it so SQL Server makes openldap calls directly bypassing the SSSD mechanism.

如果域控制器支持 LDAPS,则可以强制从 SQL Server 到域控制器的所有连接都通过 LDAPS 完成。If your domain controller supports LDAPS, you can force all connections from SQL Server to the domain controllers to be over LDAPS. 若要检查客户端是否可以通过 ldaps 与域控制器通信,请运行以下 bash 命令:ldapsearch -H ldaps://contoso.com:3269To check your client can contact the domain controller over ldaps, run the following bash command, ldapsearch -H ldaps://contoso.com:3269. 若要将 SQL Server 设置为仅使用 LDAPS,请运行以下命令:To set SQL Server to only use LDAPS, run the following:

sudo mssql-conf set network.forcesecureldap true
systemctl restart mssql-server

如果在主机上通过 SSSD 包加入 AD 域,且 disablesssd 未设置为 true,则此设置将使用 LDAPS,而不使用 SSSD。This will use LDAPS over SSSD if AD domain join on host was done via SSSD package and disablesssd is not set to true. 如果 disablesssd 设置为 true 且 forcesecureldap 也设置为 true,则它将通过 SQL Server 发出的 openldap 库调用使用 LDAPS 协议。If disablesssd is set to true along with forcesecureldap being set to true, then it will use LDAPS protocol over openldap library calls made by SQL Server.

发布 SQL Server 2017 CU14Post SQL Server 2017 CU14

从 SQL Server 2017 CU14 开始,如果 SQL Server 使用第三方提供程序加入 AD 域控制器并配置为通过将 disablesssd 设置为 true 来使用 openldap 调用进行常规 AD 查找,则还可以使用 enablekdcfromkrb5 选项强制 SQL Server 使用 krb5 库进行 KDC 查找,而不是针对 KDC 服务器进行反向 DNS 查找。Starting with SQL Server 2017 CU14, if SQL Server was joined to an AD domain controller using third-party providers and is configured to use openldap calls for general AD lookup by setting disablesssd to true, you can also use enablekdcfromkrb5 option to force SQL Server to use krb5 library for KDC lookup instead of reverse DNS lookup for KDC server.

这对于想要手动配置 SQL Server 尝试与之通信的域控制器的方案可能很有用。This may be useful for the scenario where you want to manually configure the domain controllers that SQL Server attempts to communicate with. 通过使用 krb5.conf 中的 KDC 列表来使用 openldap 库机制。And you use the openldap library mechanism by using the KDC list in krb5.conf.

首先,将 disablessd 和 enablekdcfromkrb5conf 设置为 true,然后重启 SQL Server:First, set disablesssd and enablekdcfromkrb5conf to true and then restart SQL Server:

sudo mssql-conf set network.disablesssd true
sudo mssql-conf set network.enablekdcfromkrb5conf true
systemctl restart mssql-server

接下来配置 /etc/krb5.conf 中的 KDC 列表,如下所示:Then configure the KDC list in /etc/krb5.conf as follows:

[realms]
CONTOSO.COM = {
  kdc = dcWithGC1.contoso.com
  kdc = dcWithGC2.contoso.com
}

备注

尽管不建议这样做,但在将 disablesssd 配置为 true 时,可以使用 realmd 等实用工具(这些实用工具在将 Linux 主机加入域时设置 SSSD),以便 SQL Server 使用 openldap 调用(而不是 SSSD)进行与 Active Directory 相关的调用。While it is not recommended, it is possible to use utilities, such as realmd, that set up SSSD while joining the Linux host to the domain, while configuring disablesssd to true so that SQL Server uses openldap calls instead of SSSD for Active Directory related calls.

后续步骤Next steps

在本教程中,我们介绍了如何对 Linux 上的 SQL Server 设置 Active Directory 身份验证。In this tutorial, we walked through how to set up Active Directory authentication with SQL Server on Linux. 你已了解如何执行以下操作:You learned how to:

  • SQL ServerSQL Server 主机加入 AD 域Join SQL ServerSQL Server host to AD domain
  • SQL ServerSQL Server 创建 AD 用户并设置 SPNCreate AD user for SQL ServerSQL Server and set SPN
  • 配置 SQL ServerSQL Server 服务 keytabConfigure SQL ServerSQL Server service keytab
  • 在 Transact-SQL 中创建基于 AD 的登录名Create AD-based logins in Transact-SQL
  • 使用 AD 身份验证连接到 SQL ServerSQL ServerConnect to SQL ServerSQL Server using AD Authentication

接下来,探索 Linux 上的 SQL Server 的其他安全方案。Next, explore other security scenarios for SQL Server on Linux.