SQL Server 2022 : Windows Authentication2023/11/24 |
|
Configure SQL Server on Linux to enable Windows Authentication.
|
|||||||||||
| [1] |
Add Ubuntu Server which SQL Server runs to Active Directory Domain, refer to here.
This example is based on the environment like follows.
|
||||||||||
| [2] | Create a domain user on AD for SQL Server service. On this example, create [mssql] user like follows. |
|
| [3] |
Run PowerShell with admin privilege and set service principal to SQL Server service user.
PS > setspn -A MSSQLSvc/(SQL Server Host's FQDN):(SQL Server Port) (SQL Server service user)PS > setspn -A MSSQLSvc/(SQL Server Host's' NetBIOS Name):(SQL Server Port) (SQL Server service user) |
|
| [4] | Configure on Ubuntu Server which SQL Server runs. |
|
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: # make sure Key Version Number (kvno) root@dlp:~# kvno MSSQLSvc/dlp.srv.world:1433 MSSQLSvc/dlp.srv.world:1433@SRV.WORLD: kvno = 2 # create keytab file # addent -password -p MSSQLSvc/(SQL Server Host's FQDN):(SQL Server Port) -k (kvno) root@dlp:~# ktutil ktutil: addent -password -p MSSQLSvc/dlp.srv.world:1433@SRV.WORLD -k 2 -e aes256-sha1 Password for MSSQLSvc/dlp.srv.world:1433@SRV.WORLD: ktutil: addent -password -p MSSQLSvc/dlp.srv.world:1433@SRV.WORLD -k 2 -e rc4-hmac Password for MSSQLSvc/dlp.srv.world:1433@SRV.WORLD: ktutil: addent -password -p MSSQLSvc/DLP:1433@SRV.WORLD -k 2 -e aes256-sha1 Password for MSSQLSvc/dlp.srv.world:1433@SRV.WORLD: ktutil: addent -password -p MSSQLSvc/DLP:1433@SRV.WORLD -k 2 -e rc4-hmac Password for MSSQLSvc/dlp.srv.world:1433@SRV.WORLD: ktutil: wkt /var/opt/mssql/secrets/mssql.keytab ktutil: quit
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 |
| [5] | Create SQL Server Login. |
|
# for example, create [serverworld] user's login root@dlp:~# id FD3S01\\serverworld uid=581001103(serverworld@srv.world) gid=581000513(domain users@srv.world) groups=581000513(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\NETWORK SERVICE NT AUTHORITY\SYSTEM ubuntu FD3S01\Serverworld (33 rows affected) |
| [6] | Login to Ubuntu Server as an AD user who has SQL Server Login right and make sure 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_581001103_fn7Zzd
Default principal: Serverworld@SRV.WORLD
Valid starting Expires Service principal
11/24/2023 11:50:26 11/24/2023 21:50:26 krbtgt/SRV.WORLD@SRV.WORLD
renew until 11/25/2023 11:50:23
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 (Ubuntu 22.04.3 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