Search code examples
pythonsql-serverpyodbckerberos

"Server not found in Kerberos database" when trying to connect via pyodbc to MS SQL server using Windows Authentication from Linux

Good day, I am trying to connect to a MS SQL server database from a Linux host running Python, using Windows authentication.

The MS SQL server is hosted on a machine running Windows Server 2012 R2 Standard, and the version is SQL Server Enterprise (64-bit) 12.0.5579.0.

The Linux host is a Docker container running Ubuntu 20.04 with Python 3.9.7 and pyodbc 40.030.

Querying the database via pyodbc using SQL password authentication succeeds (so it looks like the ODBC connection is set up correctly), but I need to authenticate on the SQL server using Windows authentication via our domain server, as described here.

I tried running kinit <user>@<domain>, and it asked for my password (I inserted my Windows Active Directory password there). When I try to check the ticket that was generated I get:

$ klist
Ticket cache: FILE:/tmp/krb5cc_1001
Default principal: <user>@<domain>

Valid starting     Expires            Service principal
02/23/23 12:56:26  02/23/23 22:56:26  krbtgt/<domain>@<domain>
        renew until 02/24/23 12:56:21

So it looks like a ticket was generated and it's valid. I also created an /etc/krb5.conf file starting with:

[libdefaults]
        default_realm = <domain>
[...]

However, when I run python, and I try to establish a connection, using the commands

>>> import pyodbc
>>> pyodbc.connect('driver={ODBC Driver 17 for SQL Server};server=<sql_server_name>;DATABASE=<db_name>;Trusted_Connection=Yes;TrustServerCertificate=Yes')

I get the error message:

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

What am I missing? How can I understand if the problem lies in my host's configuration or in the server's configuration?

Thanks!

Solution

  • Issue solved: I was able to authenticate via Kerberos after (re)registering the Service Principal Name as suggested by @Charlieface, and described here. The syntax I used is: setspn -S MSSQLSvc/<server>.<domain>:<instance> <domain>\<SQL service account>.

    2 important details:

    1. I had to add all the SPNs that were mentioned in the SQL error log of the server (Windows return code: 0x2098<c/> state: 15.), and use the right SQL service account (can be checked by running SELECT service_account FROM sys.dm_server_services)
    2. setspn -S <server> is not listing all the SPNs, therefore I wasn't able to add the new SPN due to a duplicate being present. I had to use the -Q flag of setspn to check for already existing "MSSQLSvc" SPNs, deleted them with setspn -D <spn> and then ran the command to register the SPNs I needed.

    Thanks again @Charlieface!