Azure SQL db connection Python with AzureIntegrated RBAC

Question

I'm working on some new data and i'm trying to save it to Azure SQL Database. I got it working by SQL-Authentification. But now i want to build up a connection based on the azure integrated rbac.

Here is the function i use for SQL that works.


def azure_sql_engine(host,db,user,pwd):
    driver = '{ODBC Driver 18 for SQL Server}'
    connection_string = f'Driver={driver};Server=tcp:{host}.database.windows.net,1433;Database={db};Uid={user};Pwd={pwd};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;'
    engine = create_engine(f"mssql+pyodbc:///?odbc_connect={parse.quote_plus(connection_string)}", fast_executemany=True)
    return engine

This is the connection string for integrated

Driver={ODBC Driver 18 for SQL Server};Server=tcp:.database.windows.net,1433;Database=;Uid=;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryIntegrated

I am getting errors regarding to MFA with the code FA004.

Intend to use this for security reasons. In the end i want to build a (Azure) Function to schedule my Notebook as a cron job.

Any ideas on this one?

Tested with pyodbc and sqlalchemy. In VS code i tested also to Sign In on Azure.

Bhavani · Accepted Answer · 2023-06-30 13:07:26Z

0

I tried using your code to connect to Azure SQL database in which you are using Azure Active directory Integrated authentication and got the same error.

enter image description here

As per this article MFA is the default method of login for all azure tenants. Hence in the below code, I used ActiveDirectoryInteractive instead of ActiveDirectoryIntegrated to conform to MFA standards.

import urllib.parse
from sqlalchemy import create_engine

server = '<serverName>.database.windows.net'
database = '<datbaseName>'
user = '<userName>'

driver = 'ODBC Driver 17 for SQL Server'
conn = f'DRIVER={driver};SERVER={server};DATABASE={database};UID={user};Authentication=ActiveDirectoryInteractive'
conn_str = urllib.parse.quote_plus(conn)

engine = create_engine(f'mssql+pyodbc:///?odbc_connect={conn_str}', fast_executemany=True)

with engine.connect() as connection:
    result = connection.execute("SELECT 1")
    print(result.scalar())

I got sign in page to connect with Azure SQL database I have provided my credentials. It connected successfully without any error:

enter image description here

If you don't want to use MFA for your logins, please refer the same article to check how you can disable MFA.

If you want to run this code as an Azure function cronjob please check MSI as a login approach.

answered Jun 30, 2023 at 13:07

Bhavani

5,4962 gold badges6 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

datagestuurd Over a year ago

Thanks for your effort! altough i got my code working with the use of the DBC Driver 17 for SQL Server. You should think Azure Functions could work with the most recent one (18) but it isn't. This minor change did the trick.

Collectives™ on Stack Overflow

Azure SQL db connection Python with AzureIntegrated RBAC

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related