Not able to connect MSSQL server from Python using pyodbc (while jaydebeapi works fine)

Question

I am trying to connect to MSSQL server using Pyodbc.

connStr = "DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={0};UID={1}/{2};PWD={3};Trusted_Connection=no".format("host,port", "mydomain_name", "myuser", "mypassword")

pyodbc.connect(connStr)

The error trace:

[ODBC][62095][1669186101.493930][__handles.c][499]
                Exit:[SQL_SUCCESS]
                        Environment = 0x1a84840
[ODBC][62095][1669186101.494078][SQLSetEnvAttr.c][189]
                Entry:
                        Environment = 0x1a84840
                        Attribute = SQL_ATTR_ODBC_VERSION
                        Value = 0x3
                        StrLen = 4
[ODBC][62095][1669186101.494173][SQLSetEnvAttr.c][381]
                Exit:[SQL_SUCCESS]
[ODBC][62095][1669186101.494272][SQLAllocHandle.c][395]
                Entry:
                        Handle Type = 2
                        Input Handle = 0x1a84840
                UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2LE'

[ODBC][62095][1669186101.494503][SQLAllocHandle.c][531]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0x1a9a7b0
[ODBC][62095][1669186101.495647][SQLDriverConnectW.c][298]
                Entry:
                        Connection = 0x1a9a7b0
                        Window Hdl = (nil)
                        Str In = [DRIVER={ODBC Driver 17 for SQL Server};SERVER=host,port;UID=mydomain_name/myuser;PWD=mypasswordlength = 155 (SQL_NTS)]
                        Str Out = (nil)
                        Str Out Max = 0
                        Str Out Ptr = (nil)
                        Completion = 0
[ODBC][62095][1669186101.547891][__handles.c][499]
                Exit:[SQL_SUCCESS]
                        Environment = 0x1b28e80
[ODBC][62095][1669186101.548070][SQLGetEnvAttr.c][157]
                Entry:
                        Environment = 0x1b28e80
                        Attribute = 65002
                        Value = 0x7ffcf258c390
                        Buffer Len = 128
                        StrLen = 0x7ffcf258c32c
[ODBC][62095][1669186101.548172][SQLGetEnvAttr.c][273]
                Exit:[SQL_SUCCESS]
[ODBC][62095][1669186101.548301][SQLFreeHandle.c][220]
                Entry:
                        Handle Type = 1
                        Input Handle = 0x1b28e80
[ODBC][62095][1669186101.548574][SQLDriverConnectW.c][869]
                Exit:[SQL_ERROR]
[ODBC][62095][1669186101.548693][SQLDriverConnect.c][751]
                Entry:
                        Connection = 0x1a9a7b0
                        Window Hdl = (nil)
                        Str In = [DRIVER={ODBC Driver 17 for SQL Server};SERVER=host,port;UID=mydomain_name/myuser;PWD=mypassword][length = 155 (SQL_NTS)]
                        Str Out = 0x7ffcf258ab20
                        Str Out Max = 2048
                        Str Out Ptr = (nil)
                        Completion = 0
                DIAG [28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'mydomain_name/myuser'.

[ODBC][62095][1669186101.594689][SQLDriverConnect.c][1717]
                Exit:[SQL_ERROR]
[ODBC][62095][1669186101.594808][SQLGetDiagRecW.c][535]
                Entry:
                        Connection = 0x1a9a7b0
                        Rec Number = 1
                        SQLState = 0x7ffcf258f316
                        Native = 0x7ffcf258f304
                        Message Text = 0x1ad89c0
                        Buffer Length = 1023
                        Text Len Ptr = 0x7ffcf258f302
[ODBC][62095][1669186101.594923][SQLGetDiagRecW.c][596]
                Exit:[SQL_SUCCESS]
                        SQLState = [28000]
                        Native = 0x7ffcf258f304 -> 18456 (32 bits)
                        Message Text = [[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'mydomain_name/myuser'.]
[ODBC][62095][1669186101.595099][SQLFreeHandle.c][290]
                Entry:
                        Handle Type = 2
                        Input Handle = 0x1a9a7b0
[ODBC][62095][1669186101.595192][SQLFreeHandle.c][339]
                Exit:[SQL_SUCCESS]

I tried with many different options:

I have tried with single / between user and domain and user. Similarly with single \, double // and double \. I have tried with Trusted_Connection=no/yes I have tried with Authentication=ActiveDirectoryPassword and Authentication=ActiveDirectoryIntegrated I have tried with uid=user@domain

I have also tried with Jaydebeapi.

import sys
import jaydebeapi
# jTDS Driver.
driver_name = "net.sourceforge.jtds.jdbc.Driver"

# jTDS Connection string.
connection_url = "jdbc:jtds:sqlserver://host:port;ssl=require;domain=domain_name;useNTLMv2=true;databaseName=db_name"
user=<username>
password=<pwd>
connection_properties = {"user": user,"password": password}
# Path to jTDS Jar
jar_path = "path_to_jar/jtds-1.3.1.jar"

# Establish connection.
connection = jaydebeapi.connect(driver_name, connection_url, connection_properties, jar_path)

Jaydebeapi works fine and I am able to connect to MSSQL through this and fetch data.

The difference that I can see is that in Jaydebeapi, I am passing domain name as a separate parameter while in Pyodbc, there is no such parameter. I have tried many different ways of passing domain name (as mentioned above) but none works.

I am getting a login failed error for all above ways, one of whose trace I have mentioned above.

If anyone have some insights as to how to resolve this and make Pyodbc working, Please answer this question.

Not able to connect MSSQL server from Python using pyodbc (while jaydebeapi works fine)

0 Answers0