CODEWITHSUNDEEP
pythonsqlalchemyazure-active-directoryssms
Mangal Maheshwari
Mangal Maheshwari

Reputation: 3

How to connect to the sql azure database with python SQL alchemy using active directory integrated authentication

I am using the connection string as below

params=parse.quote_plus("Driver={ODBC Driver 17 For SQL server};Server=tcp:server name,1433;database=database name;Encrypt=yes;TrustServerCertificate=no;Authentication=ActiveDirectoryIntegrated'
engine=sqlalchemy.create_engine("mssql:///?odbc_connect=%s" %params)

using the above connection string it is giving me the error

[Microsoft][ODBC Driver 17 for SQL server][SQL server]111214 an attempt to an attempt to complete the transaction has failed no corresponding transaction found

Upvotes: 0

Views: 2725

Answers (1)

Jason Pan
Jason Pan

Reputation: 21916

UPDATE

You can add connect_args, then try.

Please make sure you have same account login your windows pc and sql server.

engine = create_engine('mssql+pyodbc:///?odbc_connect=%s' % params, echo=True, connect_args={'autocommit': True})

PREVIOUS

You can consider to use Authentication=ActiveDirectoryPassword which be easier than Authentication=ActiveDirectoryIntegrated, and the code as below which is works for me.

Thank for Peter Pan's answer, for more details, you can refer his description. His answer has detailed usage of Authentication=ActiveDirectoryIntegrated in his description, I prefer Authentication=ActiveDirectoryPassword, so I posted my answer, you can refer to it.

How to connect to Azure sql database with python SQL alchemy using Active directory integrated authentication

enter image description here

from urllib import parse
from sqlalchemy import create_engine

your_user_name = 'pa**i@**a.onmicrosoft.com'
your_password_here = 'J***20'
connecting_string = 'Driver={ODBC Driver 17 for SQL Server};Server=tcp:yoursqlserver.database.windows.net,1433;Database=yoursqldb;Uid='+your_user_name+';Pwd='+your_password_here+';Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryPassword'
params = parse.quote_plus(connecting_string)

engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
connection = engine.connect()
result = connection.execute("select 1+1 as res")
for row in result:
    print("res:", row['res'])
connection.close()

Upvotes: 1

Related Questions