Question 37684 in Ubuntu Question posted in
The official Ubuntu documentation can be found here.

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

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!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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!


  2. 0

    Your issue is that the Service Principal Names (SPNs) were not registered for SQL Server, so Kerberos negotiation was failing. Kerberos is what underpins Windows Authentication: your local Windows session holds a Kerberos TGT ticket, and is used to get a TGS ticket for the SQL service. This cannot happen if the service is not registered.

    SQL Server will register automatically if it has permissions. You can register them manually, as you have now done, but it’s often easier to do it automatically.

    This process is fully documented.

    To give permissions to SQL Server startup account to register and modify SPN do the following:

    1. On the Domain Controller machine, start Active Directory Users and Computers.
    2. Select View > Advanced.
    3. Under Computers, locate the SQL Server computer, and then right-click and select Properties.
    4. Select the Security tab and click Advanced.
    5. In the list, if SQL Server startup account is not listed, click Add to add it and once it is added do the following:
      1. Select the account and click Edit.
      2. Under Permissions select Validated Write servicePrincipalName.
      3. Scroll down and under Properties select:
        Read servicePrincipalName
        Write servicePrincipalName
      4. Click OK twice.
    6. Close Active Directory Users and Computers.

    Note that it may be difficult to get this to work if SQL Server is running under a local service account. You may want to set it up under a Managed Service Account.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search