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

[[email protected]@linux ~]$ kinit [email protected]
Password for [email protected]:
[[email protected]@linux ~]$ kvno [email protected]
kvno: Server not found in Kerberos database while getting credentials for [email protected]
[[email protected]@linux ~]$ kvno MSSQLSvc/primarydc.SQLREPRO.EDU:[email protected]
MSSQLSvc/primarydc.SQLREPRO.EDU:[email protected]: 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 [email protected] /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser SQLREPRO\sqlroot /in sqlroot.keytab /out sqlroot.keytab -setpass -setupn /kvno 2 /pass MyPasswo0d1
ktpass /princ [email protected] /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/[email protected]/sqlroot.keytab
[root@linux ~]# chown [email protected]:mssql /home/[email protected]/sqlroot.keytab
[[email protected]@linux ~]$ kinit [email protected] -k -t sqlroot.keytab
[[email protected]@linux ~]$ kvno MSSQLSvc/primarydc.SQLREPRO.EDU:[email protected]
MSSQLSvc/primarydc.SQLREPRO.EDU:[email protected]: kvno = 2
[[email protected]@linux ~]$ klist
Ticket cache: KEYRING:persistent:1626404610:krb_ccache_Hw8mQWy
Default principal: [email protected]

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

• test connection

[[email protected]@linux ~]$ sqlcmd -S primarydc -E -Q 'select system_user'
                                                                                                                   
----------------------------
SQLREPRO\sqlroot                                                                                                   

(1 rows affected)