Connect SQL Server from Linux Client using Windows Authentication and troubleshoot steps

Connect SQL Server from Linux Client using Windows Authentication is supported.


Here are the Prerequisites.

1.As Kerberos is the only one supported, the Kerberos authentication needs to work between the SQL Server and other Windows clients. Do not proceed until the Kerberos works for Windows Client.
2.The Linux servers needs to join the domain.
3.The connection to SQL Server needs to be done by a domain user.

In case it still fails when all three criteria are met, please collect following logs and contact Microsoft Support.

1.SQL Server service account name.
2.SPN of the service account.(setspn -L accountName).
3.List IP Addresses of all Domain controllers.
4.Connect to Linux client using Windows user, and run following command(klist lists the Kerberos principal and Kerberos tickets held in a credentials cache, or the keys held in a keytab file.)

klist

Here is a screenshot.

image
5.Run following command to enable Kerberos trace.(case sensitive)

export KRB5_TRACE=/home/userName@DomainFQDN/sqlcmdKerberostrace.log

Here is an example of enable Kerberos trace for user SQLRepro\user1

image

6.Collect Network trace at Linux client. Do not use any filter
7.Collect Network trace on the SQL Server box
8.Then run sqlcmd to connect the SQL Server and note down the error message
Here is an example

image
9.Collect the Kerberos trace log.

Here is an example that failed to request TGS ticket

image\

10.Collect network trace files on Linux client and SQL Box.
11.Connect to Linux client using Windows user, and run klist again.

Here is an example of Kerberos trace of good connection

[5826] 1576287628.761086: ccselect module realm chose cache KEYRING:persistent:140601626:krb_ccache_AQWQ0sD with client principal user1@SQLREPRO.EDU for server principal MSSQLSvc/node1.sqlrepro.edu:1433@SQLREPRO.EDU
[5826] 1576287628.761087: Getting credentials user1@SQLREPRO.EDU -> MSSQLSvc/node1.sqlrepro.edu:1433@SQLREPRO.EDU using ccache KEYRING:persistent:140601626:krb_ccache_AQWQ0sD
[5826] 1576287628.761088: Retrieving user1@SQLREPRO.EDU -> MSSQLSvc/node1.sqlrepro.edu:1433@SQLREPRO.EDU from KEYRING:persistent:140601626:krb_ccache_AQWQ0sD with result: -1765328243/Matching credential not found
[5826] 1576287628.761089: Retrieving user1@SQLREPRO.EDU -> krbtgt/SQLREPRO.EDU@SQLREPRO.EDU from KEYRING:persistent:140601626:krb_ccache_AQWQ0sD with result: 0/Success
[5826] 1576287628.761090: Starting with TGT for client realm: user1@SQLREPRO.EDU -> krbtgt/SQLREPRO.EDU@SQLREPRO.EDU
[5826] 1576287628.761091: Requesting tickets for MSSQLSvc/node1.sqlrepro.edu:1433@SQLREPRO.EDU, referrals on
[5826] 1576287628.761092: Generated subkey for TGS request: aes256-cts/3833
[5826] 1576287628.761093: etypes requested in TGS request: aes256-cts, aes128-cts, aes256-sha2, aes128-sha2, des3-cbc-sha1, rc4-hmac, camellia128-cts, camellia256-cts
[5826] 1576287628.761095: Encoding request body and padata into FAST request
[5826] 1576287628.761096: Sending request (1685 bytes) to SQLREPRO.EDU
[5826] 1576287628.761097: Initiating TCP connection to stream 192.168.2.100:88
[5826] 1576287628.761098: Sending TCP request to stream 192.168.2.100:88
[5826] 1576287628.761099: Received answer (1649 bytes) from stream 192.168.2.100:88
[5826] 1576287628.761100: Terminating TCP connection to stream 192.168.2.100:88
[5826] 1576287628.761101: Response was from master KDC
[5826] 1576287628.761102: Decoding FAST response
[5826] 1576287628.761103: FAST reply key: aes256-cts/5A7A
[5826] 1576287628.761104: TGS reply is for user1@SQLREPRO.EDU -> MSSQLSvc/node1.sqlrepro.edu:1433@SQLREPRO.EDU with session key aes256-cts/1370
[5826] 1576287628.761105: TGS request result: 0/Success
[5826] 1576287628.761106: Received creds for desired service MSSQLSvc/node1.sqlrepro.edu:1433@SQLREPRO.EDU
[5826] 1576287628.761107: Storing user1@SQLREPRO.EDU -> MSSQLSvc/node1.sqlrepro.edu:1433@SQLREPRO.EDU in KEYRING:persistent:140601626:krb_ccache_AQWQ0sD
[5826] 1576287628.761109: Retrieving user1@SQLREPRO.EDU -> krbtgt/SQLREPRO.EDU@SQLREPRO.EDU from KEYRING:persistent:140601626:krb_ccache_AQWQ0sD with result: 0/Success
[5826] 1576287628.761110: Get cred via TGT krbtgt/SQLREPRO.EDU@SQLREPRO.EDU after requesting krbtgt/SQLREPRO.EDU@SQLREPRO.EDU (canonicalize off)
[5826] 1576287628.761111: Generated subkey for TGS request: aes256-cts/8A3E
[5826] 1576287628.761112: etypes requested in TGS request: aes256-cts
[5826] 1576287628.761114: Encoding request body and padata into FAST request
[5826] 1576287628.761115: Sending request (1651 bytes) to SQLREPRO.EDU
[5826] 1576287628.761116: Initiating TCP connection to stream 192.168.2.100:88
[5826] 1576287628.761117: Sending TCP request to stream 192.168.2.100:88
[5826] 1576287628.761118: Received answer (1602 bytes) from stream 192.168.2.100:88
[5826] 1576287628.761119: Terminating TCP connection to stream 192.168.2.100:88
[5826] 1576287628.761120: Response was from master KDC
[5826] 1576287628.761121: Decoding FAST response
[5826] 1576287628.761122: FAST reply key: aes256-cts/1625
[5826] 1576287628.761123: TGS reply is for user1@SQLREPRO.EDU -> krbtgt/SQLREPRO.EDU@SQLREPRO.EDU with session key aes256-cts/F4BE
[5826] 1576287628.761124: Got cred; 0/Success
[5826] 1576287628.761126: Creating authenticator for user1@SQLREPRO.EDU -> MSSQLSvc/node1.sqlrepro.edu:1433@SQLREPRO.EDU, seqnum 698322566, subkey aes256-cts/465F, session key aes256-cts/1370
[5826] 1576287628.761131: Read AP-REP, time 1576287628.761127, subkey aes256-cts/2276, seqnum 2103228290

3 thoughts on “Connect SQL Server from Linux Client using Windows Authentication and troubleshoot steps

  1. Hello,
    Can you pls let me know what windows user are you referring to in this statement?
    “4.Connect to Linux client using Windows user, and run following command(klist lists the Kerberos principal and Kerberos tickets held in a credentials cache, or the keys held in a keytab file.)”

    Are you saying we need to SSH into linux host using the AD SQL Service account?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s