SQLAlchemy Connection to Snowflake with Azure AD Authentication

0
votes

I'm trying to use the below code to connect to my snowflake account using SQL alchemy. It's not working and my guess is that this is because we log in using Azure AD authentication. Any help appreciated.

from sqlalchemy import create_engine
    
    engine = create_engine(
        'snowflake://{user}:{password}@{account}/'.format(
            user='xxx',
            password='xxx',
            account='xxx'
            
        )
    )
    try:
        connection = engine.connect()
        results = connection.execute('select current_version()').fetchone()
        print(results[0])
    finally:
        connection.close()
        engine.dispose()

The error message:

DatabaseError: (snowflake.connector.errors.DatabaseError) 250001 (08001): Failed to connect to DB: QB67303.eu-west-1.snowflakecomputing.com:443. Incorrect username or password was specified.
(Background on this error at: http://sqlalche.me/e/13/4xp6)
2
sqlalchemyazure-active-directorysnowflake-cloud-data-platform
How is it not working. What errors are you getting? – Taf Munyurwa
Hi @TafMunyurwa, I've added the error message to the question. Thanks for looking. – jh1111111
Have you tried adding the authenticator='externalbrowser' option to your connection parameters? That will then prompt you to log into your SSO provider when python makes a connection. – Mike Walton

2 Answers

2
votes

Though this is not with sqlalchemy but with the native snowflake sql connector, this works for me:

import snowflake.connector

ctx = snowflake.connector.connect(
    user='<YOUR_USERNAME>',
    account='<YOUR_ACCOUNT ; not always needed>',
    host='<YOUR SF host, ex: xxxxxx.snowflakecomputing.com / xxxxxx.a0.satoricyber.net>',
    authenticator='externalbrowser',
    warehouse='<optional>'
    )
cs = ctx.cursor()
try:
    cs.execute("SELECT * FROM ORGDATA.PUBLIC.CUSTOMERS")
    rows = cs.fetchall()
    for row in rows:
        print(row)
finally:
    cs.close()
ctx.close()
0
votes

This is what I went with in the end. Thanks to Mike Walton for getting me on the right track.

from snowflake.sqlalchemy import URL
from sqlalchemy import create_engine

engine = create_engine(URL(
    account = 'x',
    user = 'x',
    password = 'x',
    database = 'x',
    schema = 'x',
    warehouse = 'x',
    role='x',
    authenticator='externalbrowser'
))

try:
        connection = engine.connect()
        sql_df = pd.read_sql(
                                "Select top 1 * from a.b",
                                con=engine
                                )
        
finally:
        connection.close()
        engine.dispose()