Linux client login windows SQL Server with keytab

Linux client login windows SQL Server with keytab#

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-ver16

create sql login SQLREPRO\sqlroot and request kdc ticket#

[sqlroot@sqlrepro.edu@linux ~]$ kinit sqlroot@SQLREPRO.EDU
Password for sqlroot@SQLREPRO.EDU:
[sqlroot@sqlrepro.edu@linux ~]$ kvno sqlroot@SQLREPRO.EDU
kvno: Server not found in Kerberos database while getting credentials for sqlroot@SQLREPRO.EDU
[sqlroot@sqlrepro.edu@linux ~]$ kvno MSSQLSvc/primarydc.SQLREPRO.EDU:1433@SQLREPRO.EDU
MSSQLSvc/primarydc.SQLREPRO.EDU:1433@SQLREPRO.EDU: kvno = 2
[root@linux ~]# sqlcmd -S primarydc -Usa -Q 'CREATE LOGIN [SQLREPRO\sqlroot] FROM WINDOWS'-Q 'CREATE LOGIN [SQLREPRO\sqlroot] FROM WINDOWS'
[root@linux ~]# sqlcmd -S primarydc -Usa -Q ' SELECT name  FROM sys.server_principals WHERE name like "%sqlroot%"'
Password:
name
--------------------------------------------------------------------------------------------------------------------------------
SQLREPRO\sqlroot

(1 rows affected)

create keytab on windows for user sqlrepro\sqlroot#

ktpass /princ MSSQLSvc/primarydc.SQLREPRO.EDU:1433@SQLREPRO.EDU /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser SQLREPRO\sqlroot /out sqlroot.keytab -setpass -setupn /kvno 2 /pass MyPasswo0d1
ktpass /princ MSSQLSvc/primarydc.SQLREPRO.EDU:1433@SQLREPRO.EDU /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser SQLREPRO\sqlroot /in sqlroot.keytab /out sqlroot.keytab -setpass -setupn /kvno 2 /pass MyPasswo0d1
ktpass /princ MSSQLSvc/primarydc.SQLREPRO.EDU:1433@SQLREPRO.EDU /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser SQLREPRO\sqlroot /in sqlroot.keytab /out sqlroot.keytab -setpass -setupn /kvno 2 /pass MyPasswo0d1
ktpass /princ MSSQLSvc/primarydc.SQLREPRO.EDU:1433@SQLREPRO.EDU /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser SQLREPRO\sqlroot /in sqlroot.keytab /out sqlroot.keytab -setpass -setupn /kvno 2 /pass MyPasswo0d1
ktpass /princ sqlroot@SQLREPRO.EDU /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser SQLREPRO\sqlroot /in sqlroot.keytab /out sqlroot.keytab -setpass -setupn /kvno 2 /pass MyPasswo0d1
ktpass /princ sqlroot@SQLREPRO.EDU /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser SQLREPRO\sqlroot  /in sqlroot.keytab /out sqlroot.keytab -setpass -setupn /kvno 2 /pass MyPasswo0d1

Copy the keytab to linux server and grant required permission#

# copy the sqlroot.keytab to linux server with winscp
[root@linux ~]# chmod 755 /home/sqlroot@sqlrepro.edu/sqlroot.keytab
[root@linux ~]# chown sqlroot@SQlREPRO.EDU:mssql /home/sqlroot@sqlrepro.edu/sqlroot.keytab
[sqlroot@sqlrepro.edu@linux ~]$ kinit sqlroot@SQLREPRO.EDU -k -t sqlroot.keytab
[sqlroot@sqlrepro.edu@linux ~]$ kvno MSSQLSvc/primarydc.SQLREPRO.EDU:1433@SQLREPRO.EDU
MSSQLSvc/primarydc.SQLREPRO.EDU:1433@SQLREPRO.EDU: kvno = 2
[sqlroot@sqlrepro.edu@linux ~]$ klist
Ticket cache: KEYRING:persistent:1626404610:krb_ccache_Hw8mQWy
Default principal: sqlroot@SQLREPRO.EDU

Valid starting       Expires              Service principal
08/15/2022 04:27:47  08/15/2022 14:27:44  MSSQLSvc/primarydc.SQLREPRO.EDU:1433@SQLREPRO.EDU
        renew until 08/22/2022 04:27:44
08/15/2022 04:27:44  08/15/2022 14:27:44  krbtgt/SQLREPRO.EDU@SQLREPRO.EDU
        renew until 08/22/2022 04:27:44

Test connection#

[sqlroot@sqlrepro.edu@linux ~]$ sqlcmd -S primarydc -E -Q 'select system_user'
                                                                                                                   
--------------------------------------------------------------------------------------------------------------------------------
SQLREPRO\sqlroot                                                                                                   

(1 rows affected)