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.
|
||||||||||
| [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