search

mkleehammer / pyodbc

Python ODBC bridge
https://github.com/mkleehammer/pyodbc/wiki
MIT No Attribution
2.95k stars 563 forks source link

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

Closed Sauravmzmdr09 closed 1 year ago

Sauravmzmdr09 commented 1 year ago

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:

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 commented 1 year ago

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 commented 1 year ago

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

v-chojas commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

pymssql (Free TDS Driver with pyodbc?)

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

v-chojas commented 1 year ago

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

Sauravmzmdr09 commented 1 year ago

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 commented 1 year ago

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