SQL Server 2025 : Windows Authentication2026/03/09 |
|
Configure SQL Server on Linux to enable Windows Authentication. |
|||||||||||
| [1] |
Add Ubuntu 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 Ubuntu 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/ubuntu/mssql.keytab total 4 -rw-rw-r-- 1 ubuntu ubuntu 440 Mar 9 00:40 /home/ubuntu/mssql.keytab
root@dlp:~#
mv /home/ubuntu/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=1275601103(serverworld@srv.world) gid=1275600513(domain users@srv.world) groups=1275600513(domain users@srv.world),1275600512(domain admins@srv.world),1275600572(denied rodc password replication group@srv.world)root@dlp:~# sqlcmd -C -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\NETWORK SERVICE NT AUTHORITY\SYSTEM ubuntu FD3S01\Serverworld (33 rows affected) |
| [8] | Login to Ubuntu 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_1275601103_z7MlVp
Default principal: Serverworld@SRV.WORLD
Valid starting Expires Service principal
03/09/26 00:46:10 03/09/26 10:46:10 krbtgt/SRV.WORLD@SRV.WORLD
renew until 03/10/26 00:46:05
serverworld@srv.world@dlp:/$ sqlcmd -C -S dlp.srv.world 1> select @@version; 2> go ----------------------------------------------------------------------------------- Microsoft SQL Server 2025 (RTM-CU2) (KB5075211) - 17.0.4015.4 (X64) Jan 29 2026 17:56:01 Copyright (C) 2025 Microsoft Corporation Enterprise Developer Edition (64-bit) on Linux (Ubuntu 24.04.4 LTS) <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. |
|
|
Matched Content