SQL Server 2022 : Windows Authentication2023/12/07 |
Configure SQL Server on Linux to enable Windows Authentication. |
|||||||||||
[1] |
Add CentOS 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. |
# 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/cent total 4 -rw-r--r--. 1 cent cent 440 Dec 7 15:35 mssql.keytab
[root@dlp ~]#
mv /home/cent/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: KCM:214001103:87964 Default principal: Serverworld@SRV.WORLD Valid starting Expires Service principal 12/07/2023 15:21:43 12/08/2023 01:21:43 krbtgt/SRV.WORLD@SRV.WORLD renew until 12/14/2023 15:21:40[serverworld@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 (CentOS Stream 9) <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 |
|