SQL Server 2022 : Windows Authentication2023/12/15 |
Configure SQL Server on Linux to enable Windows Authentication. |
|||||||||||
[1] |
Add Debian Server which SQL Server runs to Active Directory Domain, refer to here.
|
||||||||||
[2] |
Run PowerShell with admin privilege on AD Server and create a domain user for SQL Server service. PS > New-ADUser mssql ` -Surname "mssql" ` -GivenName "mssql" ` -DisplayName "MS SQL" ` -EmailAddress "mssql@srv.world" ` -AccountPassword (ConvertTo-SecureString -AsPlainText "P@ssw0rd01" -Force) ` -ChangePasswordAtLogon $false ` -PasswordNeverExpires $true ` -KerberosEncryptionType "AES128,AES256" ` -Enabled $true |
[3] |
Set service principal to SQL Server service user.
PS > setspn -A MSSQLSvc/(SQL Server FQDN):(SQL Server Port) (SQL Server service user)PS > setspn -A MSSQLSvc/(SQL Server NetBIOS Name):(SQL Server Port) (SQL Server service user) |
[4] | Find the Key Version Number (kvno) on the CentOS server running SQL Server. |
root@dlp:~#
apt -y install krb5-user # get Kerberos ticket with SQL Server service user root@dlp:~# kinit mssql@SRV.WORLD Password for mssql@SRV.WORLD: # find Key Version Number (kvno) root@dlp:~# kvno MSSQLSvc/dlp.srv.world:1433 MSSQLSvc/dlp.srv.world:1433@SRV.WORLD: kvno = 2 |
[5] |
On the AD side, enter the following command to create a keytab file and transfer it to the SQL Server operating host. PS > ktpass /princ MSSQLSvc/(SQL Server FQDN):(SQL Server listen port)@(domain name) /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser (domain name)\(SQL Server service user) /out mssql.keytab -setpass -setupn /kvno (kvno number found above) /pass (SQL Server service user password) PS > ktpass /princ MSSQLSvc/(SQL Server FQDN):(SQL Server listen port)@(domain name) /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser (domain name)\(SQL Server service user) /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno (kvno number found above) /pass SQL Server service user password) PS > ktpass /princ MSSQLSvc/(SQL Server NetBIOS Name):(SQL Server listen port)@(domain name) /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser (domain name)\(SQL Server service user) /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno (kvno number found above) /pass (SQL Server service user password) PS > ktpass /princ MSSQLSvc/(SQL Server NetBIOS Name):(SQL Server listen port)@(domain name) /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser (domain name)\(SQL Server service user) /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno (kvno number found above) /pass (SQL Server service user password) PS > ktpass /princ (SQL Server service user)@(domain name) /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser (domain name)\(SQL Server service user) /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno (kvno number found above) /pass (SQL Server service user password) PS > ktpass /princ (SQL Server service user)@(domain name) /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser (domain name)\(SQL Server service user) /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno (kvno number found above) /pass (SQL Server service user password) * example ⇒ PS > ktpass /princ MSSQLSvc/dlp.srv.world:1433@SRV.WORLD /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser FD3S01\mssql /out mssql.keytab -setpass -setupn /kvno 2 /pass P@ssw0rd01 PS > ktpass /princ MSSQLSvc/dlp.srv.world:1433@SRV.WORLD /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser FD3S01\mssql /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass P@ssw0rd01 PS > ktpass /princ MSSQLSvc/DLP:1433@SRV.WORLD /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser FD3S01\mssql /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass P@ssw0rd01 PS > ktpass /princ MSSQLSvc/DLP:1433@SRV.WORLD /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser FD3S01\mssql /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass P@ssw0rd01 PS > ktpass /princ mssql@SRV.WORLD /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser FD3S01\mssql /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass P@ssw0rd01 PS > ktpass /princ mssql@SRV.WORLD /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser FD3S01\mssql /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass P@ssw0rd01 |
[6] | Configure the transferred keytab file on the SQL Server running host. |
# keytab file transferred from AD root@dlp:~# ll /home/debian total 4 -rw-r--r-- 1 debian debian 440 Dec 14 23:42 mssql.keytab
root@dlp:~#
mv /home/debian/mssql.keytab /var/opt/mssql/secrets/ root@dlp:~# chown mssql:mssql /var/opt/mssql/secrets/mssql.keytab root@dlp:~# chmod 400 /var/opt/mssql/secrets/mssql.keytab
# set keytab file root@dlp:~# /opt/mssql/bin/mssql-conf set network.privilegedadaccount mssql root@dlp:~# /opt/mssql/bin/mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab SQL Server needs to be restarted in order to apply this setting. Please run 'systemctl restart mssql-server.service'. root@dlp:~# systemctl restart mssql-server |
[7] | Create SQL Server Login. |
# for example, create [serverworld] user login root@dlp:~# id FD3S01\\serverworld uid=214001103(serverworld@srv.world) gid=214000513(domain users@srv.world) groups=214000513(domain users@srv.world)root@dlp:~# sqlcmd -S localhost -U SA Password: 1> create login [FD3S01\Serverworld] from windows; 2> go 1> select name from sys.server_principals; 2> go name --------------------------------------------------- sa public sysadmin securityadmin serveradmin setupadmin processadmin diskadmin dbcreator bulkadmin ##MS_ServerStateReader## ##MS_ServerStateManager## ##MS_DefinitionReader## ##MS_DatabaseConnector## ##MS_DatabaseManager## ##MS_LoginManager## ##MS_SecurityDefinitionReader## ##MS_PerformanceDefinitionReader## ##MS_ServerSecurityStateReader## ##MS_ServerPerformanceStateReader## ##MS_SQLResourceSigningCertificate## ##MS_SQLReplicationSigningCertificate## ##MS_SQLAuthenticatorCertificate## ##MS_PolicySigningCertificate## ##MS_SmoExtendedSigningCertificate## ##MS_PolicyEventProcessingLogin## ##MS_PolicyTsqlExecutionLogin## ##MS_AgentSigningCertificate## BUILTIN\Administrators NT AUTHORITY\SYSTEM NT AUTHORITY\NETWORK SERVICE FD3S01\Serverworld (32 rows affected) |
[8] | Login to CentOS Server as an AD user who has SQL Server Login right and verify it's possible to login to SQL Server, too. |
# get Kerberos ticket serverworld@srv.world@dlp:~$ kinit Password for Serverworld@SRV.WORLD: serverworld@srv.world@dlp:~$ klist Ticket cache: FILE:/tmp/krb5cc_214001103_FQtzrL Default principal: Serverworld@SRV.WORLD Valid starting Expires Service principal 12/14/2023 23:45:49 12/15/2023 09:45:49 krbtgt/SRV.WORLD@SRV.WORLD renew until 12/15/2023 23:45:45serverworld@srv.world@dlp:~$ sqlcmd -S dlp.srv.world 1> select @@version; 2> go ---------------------------------------------------------------------------------------- Microsoft SQL Server 2022 (RTM-CU10) (KB5031778) - 16.0.4095.4 (X64) Oct 30 2023 16:12:44 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Linux (Debian GNU/Linux 12 (bookworm)) <X64> (1 rows affected) |
It's also possible to login to SQL Server on Linux with [Windows Authentication] from Windows Clients that is in Active Directory Domain. |
Sponsored Link |
|