CentOS 7
Sponsored Link

SQL Server 2019 : Windows Authentication2020/01/20

 
Configure SQL Server on Linux to enable Windows Authentication.
[1]
Add CentOS Host which SQL Server runs to Active Directory Domain, refer to here.
This example is based on the environment like follows.
Domain Server : Windows Server 2019
NetBIOS Name : FD3S01
Domain Name : srv.world
Realm : SRV.WORLD
Hostname : fd3s.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 Hosts' FQDN):(SQL Server Port) (SQL Server service user)
[4] Configure on CentOS Host 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-cts-hmac-sha1-96

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:
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=1539401000(serverworld) gid=1539400513(domain users) groups=1539400513(domain users)
[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_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                          

(22 rows affected)
[6] Login to CentOS Host 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@dlp ~]$
kinit

Password for serverworld@SRV.WORLD:
[serverworld@dlp ~]$
klist

Ticket cache: KEYRING:persistent:321801103:krb_ccache_Ig0qJ6m
Default principal: Serverworld@SRV.WORLD

Valid starting       Expires              Service principal
01/17/2020 17:22:11  01/18/2020 03:22:11  krbtgt/SRV.WORLD@SRV.WORLD
        renew until 01/24/2020 17:22:07

[serverworld@dlp ~]$
sqlcmd -S dlp.srv.world

1> select @@version;
2> go

-----------------------------------------------------------------------------------
Microsoft SQL Server 2019 (RTM-CU1) (KB4527376) - 15.0.4003.23 (X64)
        Dec  6 2019 14:53:33
        Copyright (C) 2019 Microsoft Corporation
        Developer Edition (64-bit) on Linux (CentOS Linux 7 (Core)) <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