Not able to connect to Azure Arc Enabled MI server for Windows Authenticated only user

[Sauravmzmdr09]

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be
sure to specify 32-bit Python or 64-bit:

• Python: 3.6
• pyodbc: 4.0.39
• OS: Ubuntu
• DB: Azure Arc MI
• driver: Microsoft ODBC Driver 17 for SQL Server

Issue

The environment is like this :

1. user has only Windows Authentication mode enabled
2. Server is hosted on Linux and is a ARC MI enabled SQL server
3. User wants to access via user ID and password
4. Client is on a Linux based docker

Need a Linux +python based solution to this problem. Can something be changed in the Microsoft DRIVER in the pyodbc library to support this use case?

Connection String used :

cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';ENCRYPT=yes;UID='+username+';PWD='+password+';TrustServerCertificate=yes')

Expected Behavior:
Connection should go through without any errors.

Observed Behavior:
Error received:
pyodbc.InterfaceError: ('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'JDNET\APC0321'. (18456) (SQLDriverConnect)")

Note : Adding a Trusted_Connection=yes in the connection string
throws this error:
pyodbc.Error: ('HY000', '[HY000] [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_1000) (851968) (SQLDriverConnect)')
Also since the client and server is not on same AD group setting Kerberos credentials is not possible.

[v-chojas]

This isn't a pyODBC problem, but in any case...

https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/release-notes-odbc-sql-server-linux-mac?view=sql-server-ver16

18.3.1, July 2023
Support for MSI authentication for Azure Arc

Try using ODBC Driver 18 for SQL Server? Azure Arc support was just added to it very recently.

[Sauravmzmdr09]

Same issue is seen with ODBC Driver 18 for SQL Server as well

[v-chojas]

Looking at your sample connection string, that is for regular SQL authentication (users created directly on the server, and supplying their username/password credentials to server to login). It is not clear what type of account you want to login with, as "Windows Authentication" implies integrated Kerberos, and Azure Arc is relevant for AAD users.

[Sauravmzmdr09]

The user is a part of a group "JDNET". It doesn't have regular SQL authentication enabled due to organisational constraints.
Note: The connection goes through with pymssql (Free TDS Driver with pyodbc?) library but only for client on Windows platform.

[gordthompson]

pymssql (Free TDS Driver with pyodbc?)

No, pymssql does not use ODBC. It works directly with FreeTDS.

[v-chojas]

You'll likely need to setup Kerberos in this scenario.

[Sauravmzmdr09]

Do I need to change something on the server side to support Kerberos?
If that is true then that may not be possible as well. If not, then can someone let me know what to do to setup automatic Kerberos ticket generation as this is for a bot user.

[v-chojas]

If you can already connect using that account, probably not, but give this a read: https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication

On the client side: https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/using-integrated-authentication