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;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"
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??

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;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"
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??

• python
• azure
• azure-sql-database
• odbc
• pyodbc

1 Answer 1

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