MSSQL Server SPN Connections: Linux to MSSQL Server (setspn, kinit, kerberos authentication)

After AD Admin added a SQL server SPN using the setspn tool, the linux server is not able to use the SPN using MS SQL Server ODBC Driver 18 with error “[HY000] [Microsoft][ODBC Driver 18 for SQL Server]SSPI Provider: Server not found in Kerberos database”

I have a working kerberos based authentication to one server (dev), and I am trying to implement the same connection setup to a new server (rel). To configure dev, my AD admin set the server SPN using the SPN setting tool:

setspn -S MSSQLSvc/dev:dev_sql_port domaindev_service_user

And we can verify that the spn has been created using

setspn -Q MSSQLSvc/dev*

There are many domain controlers on the ad domain (7 or so, as determined by using the command adcli info domain.org from linux server). At first we were not able to connect to dev using ODBC, but after a few months the following pattern started to work:

kinit
Password for user@domain.org: ********

Followed by a connection using the connection string:

Driver={ODBC Driver 18 for SQL Server};Server=dev;Trusted_Connection=yes;Encrypt=No;TrustServerCertificate=yes

The AD admin duplicated everything from the dev setup for rel, using the correct information for the rel sql server instance. I am able to use the following to initialize a kerberos ticket for the new server:

kinit -S "MSSQLSvcrel:rel_port"
Password for user@domain.org: ********

However, the python pattern:

cnxn_str = ("Driver={ODBC Driver 18 for SQL Server};Server=rel;Trusted_Connection=yes;Encrypt=No;TrustServerCertificate=yes")
cnxn = pyodbc.connect(cnxn_str)

Gives error:

Error: ('HY000', '[HY000] [Microsoft][ODBC Driver 18 for SQL Server]SSPI Provider: Server not found in Kerberos database (851968) (SQLDriverConnect)')

There are minimal changes to the krb5.conf configuration besides top section:

[libdefaults]
        default_realm = domain.org
        dns_lookup_realm = true
        dns_lookup_kdc = true

I have access to appropriate admins to make changes that are researched, however, we (I) am at a bit of a loss why dev works, why it took several months for dev to work, and why rel does not work (yet). It has been 2 weeks since we made the changes to rel.

Answer

To fix this, we determined that there was a typo in the original rel setspn statement, and that there were two listed under setspn -L realmrel_sql_service_account one with the incorrect port, and one with no port. We used the commands

setspn -D MSSQLSvc/rel.domain:wrong_port realmrel_sql_service_account
setspn -D MSSQLSvc/rel.domain realmrel_sql_service_account
setspn -S MSSQLSvc/rel.domain:right_port realmrel_sql_service_account

There is chance this was done from a command window on a different windows server than where it was originally set. Double checking that was all the same.

Advertisement

Answer