Linux Client With AD Authentication Login Windows SQL Server

概览

Pre-Work

• linux should join AD same as SQL Server
• setspn on windows SQL Server for linux

Windows SQL Server

C:\Users\Administrator>setspn -L sqladmin
Registered ServicePrincipalNames for CN=sqladmin,CN=Users,DC=sqlrepro,DC=edu:
        MSSQLSvc/primarydc.sqlrepro.edu:1433
        MSSQLSvc/primarydc:1433
        MSSQLSvc/2016cl.sqlrepro.edu:1433

C:\Users\Administrator>setspn -S MSSQLSvc/linux.sqlrepro.edu:1433 sqlrepro\sqladmin     # linux.sqlrepro.edu is linux server which joined AD
Checking domain DC=sqlrepro,DC=edu

Registering ServicePrincipalNames for CN=sqladmin,CN=Users,DC=sqlrepro,DC=edu
        MSSQLSvc/linux.sqlrepro.edu:1433
Updated object

C:\Users\Administrator>setspn -L sqladmin
Registered ServicePrincipalNames for CN=sqladmin,CN=Users,DC=sqlrepro,DC=edu:
        MSSQLSvc/linux.sqlrepro.edu:1433
        MSSQLSvc/primarydc.sqlrepro.edu:1433
        MSSQLSvc/primarydc:1433
        MSSQLSvc/2016cl.sqlrepro.edu:1433

linux:

[[email protected]@linux ~]$ hostname
linux.sqlrepro.edu
[[email protected]@linux ~]$ realm -v join -U "[email protected]" SQLREPRO.EDU
 * Resolving: _ldap._tcp.sqlrepro.edu
 * Performing LDAP DSE lookup on: 192.168.2.50
 * Successfully discovered: sqlrepro.edu
realm: Already joined to this domain
[[email protected]@linux ~]$ id [email protected]
uid=1626404604([email protected]) gid=1626400513(domain users@sqlrepro.edu) groups=1626400513(domain users@sqlrepro.edu)
[[email protected]@linux ~]$ kinit [email protected]
Password for [email protected]:
[[email protected]@linux ~]$ klist
Ticket cache: KEYRING:persistent:1626404604:1626404604
Default principal: [email protected]

Valid starting       Expires              Service principal
08/15/2022 02:52:21  08/15/2022 12:52:21  krbtgt/[email protected]
        renew until 08/22/2022 02:52:18
[[email protected]@linux ~]$ whoami
[email protected]
[[email protected]@linux ~]$ sqlcmd -S primarydc -E -Q'select system_user'
                                                                                                           
---------------------------------------------------------------------------
SQLREPRO\sqladmin                                                                                          

(1 rows affected)