Ubuntu 20.04
Sponsored Link

SQL Server 2022 : Windows Authentication2023/01/30

 
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.
Domain Server : Windows Server 2022
Domain Name : srv.world
Hostname : fd3s.srv.world
NetBIOS Name : FD3S01
Realm : SRV.WORLD
[2] Create a domain user on AD for SQL Server service.
On this example, create [mssql] user like follows.
[3]
Run PowerShell with admin priviledge 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.
# 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. /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=765401103(serverworld@srv.world) gid=765400513(domain users@srv.world) groups=765400513(domain users@srv.world),765400512(domain admins@srv.world),765400572(denied rodc password replication group@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
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_765401103_z7AHaZ
Default principal: Serverworld@SRV.WORLD

Valid starting       Expires              Service principal
01/27/2023 07:47:34  01/27/2023 17:47:34  krbtgt/SRV.WORLD@SRV.WORLD
        renew until 01/28/2023 07:47:30

serverworld@srv.world@dlp:~$
sqlcmd -S dlp.srv.world

1> select @@version;
2> go

---------------------------------------------------------------------------
Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64)
        Oct  8 2022 05:58:25
        Copyright (C) 2022 Microsoft Corporation
        Developer Edition (64-bit) on Linux (Ubuntu 20.04 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