Authenticating into Azure from Python

Question

I am trying to connect from Python to Azure SQL Server to create a table.

My code:

def create_and_store_dataframe_to_azure(df, table_name, server, database, password, driver="ODBC Driver 18 for SQL Server", schema='dbo'):
    try:     
        params = quote_plus(
            f"DRIVER={driver};SERVER={server},1433;DATABASE={database};UID={username}@myazure.windows.net;PWD={password};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=60;"
        )
        engine = sqlalchemy.create_engine(f"mssql+pyodbc:///?odbc_connect={params}")
        column_types = {
            'int64': 'BIGINT',
            'float64': 'FLOAT',
            'object': 'NVARCHAR(MAX)',
            'bool': 'BIT',
            'datetime64[ns]': 'DATETIME'
        }
        create_table_query = f"CREATE TABLE {schema}.{table_name} ("
        for col in df.columns:
            col_type = column_types.get(str(df[col].dtype), 'NVARCHAR(MAX)')  # Default to NVARCHAR if type unknown
            create_table_query += f"[{col}] {col_type}, "
        create_table_query += "[InsertedTimestamp] DATETIME DEFAULT GETDATE() );"
        with engine.begin() as conn:
            conn.execute(text(f"DROP TABLE IF EXISTS {schema}.{table_name}"))  # Drop table if it exists
            conn.execute(text(create_table_query))
        print(f"Table {schema}.{table_name} created successfully.")        
        df = df.drop(columns=['InsertedTimestamp'], errors='ignore')        
        df.to_sql(name=table_name, con=engine, schema=schema, if_exists='append', index=False, method="multi", chunksize=50)
        print(f"Data successfully inserted into {schema}.{table_name}")
    except Exception as e:
        print(f"Error creating or inserting data into Azure SQL: {e}")

server = "myazure.windows.net"
database = "DB"
driver = "ODBC Driver 18 for SQL Server"
username = "[email protected]"
authentication = "ActiveDirectoryInteractive"
password = "mypwd"

but then when running the function I get the following error:

Error creating or inserting data into Azure SQL: (pyodbc.InterfaceError) ('28000', "[28000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user '[email protected]'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 18 for SQL Server]Invalid connection string attribute (0); [28000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user '[email protected]'. (18456); [28000] [Microsoft][ODBC Driver 18 for SQL Server]Invalid connection string attribute (0)")

I checked and

I have the driver ODBC Driver 18 for SQL Server installed
pwd and user are correct
server and DB are correct

Any error with the code??

Qwedon · Accepted Answer · 2025-03-28 13:10:44Z

0

If you are passing the password to the connection string, pyodbc automatically tries to use SQL Server authentication, because it is only one authentication method which supports password passing to the connection string.

But your code is using Active Directory authentication which isn't supports the authentication with password.

You should use passwordless authentication, if your code is running on Windows machine, you could use this guide to setup paswordless auth - Migrate a Python application to use passwordless connections with Azure SQL Database , if it is running on Linux machine you should configure Kerberos first - Authenticating a Linux or macOS Computer with Active Directory

edited Mar 28 at 13:10

answered Mar 28 at 13:03

Qwedon

236 bronze badges

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

Collectives™ on Stack Overflow

Authenticating into Azure from Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related