CODEWITHSUNDEEP
pysparkodbcpyodbcazure-synapse
user15464793
user15464793

Reputation: 149

Error in ODBC Connection to Synapse Dedicated SQL Pool DB via spark Notebook with pyodbc

I have a dedicated Synapse SQL pool, within which I want to Connect to a database. I want to connect to the database using Python running on a pySpark Notebook inside the same Synapse Instance.

I am using the Microsoft documentation here to go about this and my code is the following:

import pyodbc 
cnxn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};Server=tcp:SYNAPSENAME.sql.azuresynapse.net,1433;Database=DBNAME;Uid=XXXX-XXXXX-XXXXX-XXXX;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryIntegrated')
cursor = cnxn.cursor()

#Sample select query
cursor.execute("SELECT @@version;") 
row = cursor.fetchone() 
while row: 
    print(row[0])
    row = cursor.fetchone()

I obtained The connection string from the Dedicated SQL pool page in Azure Under Connection Strings/ODBC/ODBC (Includes Node.js) (Azure Active Directory integrated authentication)

However, when I run it I'm getting the following error:

OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

Traceback (most recent call last):

I have Tried running the same code with an earlier version of the Driver (13), but get the following error:

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL 
Server' : file not found (0) (SQLDriverConnect)")
Traceback (most recent call last): pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL Server' : file not found (0) (SQLDriverConnect)")

I believe this at least indicates the driver for v17 is properly installed. I have also changed the UID to be my username [email protected] but get the same error code as the first one.

I'm wondering what could be the issue?

Any help would be great, Joao

Upvotes: 0

Views: 1590

Answers (1)

Utkarsh Pal
Utkarsh Pal

Reputation: 4544

Please check your ODBC driver version.

If your version of the ODBC driver is 17.1 or later, you can use the Azure Active Directory interactive mode of the ODBC driver through pyODBC.

If the version is fine, try to use the Password in the given connection string:

Driver={SQL Server Native Client 11.0};Server=tcp:{your_server}.sql.azuresynapse.net,1433;Database={your_database};Uid={your_user_name};Pwd={your_password_here};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

Refer below helpful links:

ODBC connection string example

Microsoft ODBC Driver for SQL Server on Windows

Upvotes: 0

Related Questions