Troubleshoot Azure SQL Connection with AutoGen Studio

[victordibia]

Following up from the previous thread. by @dharanidharan59 dharanidharan59
write code to confirm you can indeed connect to azure sql

`
from loguru import logger
import pyodbc

def connect_to_azure_sql():
    """Simple function to establish connection to Azure SQL Database."""
    try:
        # Connection parameters
        server = ""
        database = ""
        username = ""
        password = ""
        
        # Create connection string for pyodbc
        conn_str = (
            f'DRIVER={{ODBC Driver 17 for SQL Server}};'
            f'SERVER={server};'
            f'DATABASE={database};'
            f'UID={username};'
            f'PWD={password};'
            f'Encrypt=yes;TrustServerCertificate=no;'
        )
        
        # Attempt connection
        logger.info(f"Connecting to Azure SQL Database on {server}...")
        conn = pyodbc.connect(conn_str)
        
        # Test connection with a simple query
        cursor = conn.cursor()
        cursor.execute("SELECT @@VERSION")
        version = cursor.fetchone()[0]
        logger.info(f"Connection successful! SQL Server version: {version}")
        
        # Return the connection if successful
        return conn
    
    except Exception as e:
        logger.error(f"Connection failed: {str(e)}")
        return None

if __name__ == "__main__":
    # Set up logging format
    logger.remove()
    logger.add(lambda msg: print(msg, end=""), colorize=True, format="<green>{time:YYYY-MM-DD HH:mm:ss}</green> | <level>{level: <8}</level> | <cyan>{message}</cyan>")
    
    # Try to connect
    connection = connect_to_azure_sql()
    
    # Check if connection was successful
    if connection:
        logger.success("Database connection established successfully!")
        
        connection.close()
        logger.info("Connection closed")
    else:
        logger.error("Failed to connect to the database")
`

The above code is able to connect to the database

the dbmanager in Ags uses sql model . So write code to verify you can connect to azure sql with sqlmodel and a db uri

`
import os
from loguru import logger
import pyodbc
from sqlmodel import SQLModel, create_engine, Session, text

def connect_to_azure_sql_sqlmodel():
    """Simple function to establish connection to Azure SQL Database using SQLModel."""
    try:
        # Connection parameters
        server = ""
        database = ""
        username = ""
        password = ""
        
        # Create connection string for pyodbc
        driver = '{ODBC Driver 17 for SQL Server}'
        conn_str = (
            f'DRIVER={driver};'
            f'SERVER={server};'
            f'DATABASE={database};'
            f'UID={username};'
            f'PWD={password};'
            f'Encrypt=yes;TrustServerCertificate=no;'
        )
        
        # Construct the SQLModel engine URI
        engine_uri = f"mssql+pyodbc:///?odbc_connect={conn_str}"
        
        # Create the engine
        engine = create_engine(engine_uri, echo=False)  # echo=True for debugging SQL queries
        
        # Test the connection by creating tables (if they don't exist) and running a simple query
        SQLModel.metadata.create_all(engine)
        
        with Session(engine) as session:
            # Execute a simple query
            result = session.execute(text("SELECT @@VERSION")).scalar()
            logger.info(f"SQL Server version: {result}")
        
        logger.info("SQLModel connection to Azure SQL Database successful!")
        return True
    
    except Exception as e:
        logger.error(f"SQLModel connection failed: {str(e)}")
        return False

if __name__ == "__main__":
    # Set up logging format
    logger.remove()
    logger.add(lambda msg: print(msg, end=""), colorize=True, format="<green>{time:YYYY-MM-DD HH:mm:ss}</green> | <level>{level: <8}</level> | <cyan>{message}</cyan>")
    
    # Try to connect using SQLModel
    if connect_to_azure_sql_sqlmodel():
        logger.success("SQLModel Database connection established successfully!")
    else:
        logger.error("SQLModel Failed to connect to the database")
`

The above code also got executed successfully

Write debug print logs in debmanager to trace errors

2025-03-04 22:00:51 | INFO     | Initializing database manager...
2025-03-04 22:00:56 | INFO     | Testing database connection...
2025-03-04 22:00:56 | INFO     | Connected to SQL Server version: Microsoft SQL Azure (RTM) - 12.0.2000.8 
        Feb  9 2025 20:57:20 
        Copyright (C) 2024 Microsoft Corporation

2025-03-04 22:00:56 | INFO     | Initializing database schema...
2025-03-04 22:00:57 | INFO     | Checking database schema...
2025-03-04 22:00:57 | INFO     | Resetting migrations and updating to current schema...
2025-03-04 22:00:57 | INFO     | Cleared alembic directory
2025-03-04 22:00:57 | INFO     | Reset alembic version
2025-03-04 22:00:57 | INFO     | Alembic initialization complete
2025-03-04 22:00:58 | ERROR    | Failed to generate revision: (pyodbc.InterfaceError) ('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'shellpoc'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0); [28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'shellpoc'. (18456); [28000] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0)")
(Background on this error at: https://sqlalche.me/e/20/rvf5)
2025-03-04 22:00:58 | ERROR    | Failed to generate new migration
2025-03-04 22:00:58 | ERROR    | Error initializing database: Database upgrade failed

It is getting failed during the Generate revision of schema_manager.py

Please help here!

[victordibia]

@dharanidharan59,
What I see so far

• You can connect to the db from python which is good.
• CLI says there was a db upgrade issue. This typically happens if the db schema changes after the db was created. One thing you can do is reset the db or create a new db. and try again.
• Also, try to double check that you connection string is correct
  The error you have shows that your connection string might be wrong .

Failed to generate revision: (pyodbc.InterfaceError) ('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'shellpoc'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0); [28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'shellpoc'. (18456); [28000] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0)")
(Background on this error at: https://sqlalche.me/e/20/rvf5)

"Login failed for user 'shellpoc'". This means the credentials being used to connect to the SQL Server aren't working. Error code 18456 is SQL Server's standard authentication failure error.
Can you print out double check that the successful connection string you have is what you are passing to autogenstudio?

engine_uri = f"mssql+pyodbc:///?odbc_connect={conn_str}"

Can you share what your connection string is (redact sensitive info)?

[dharanidharan59]

Sure, let me check that!

Connection String for your reference

mssql+pyodbc://username:[email protected]:1433/dbname?driver=ODBC+Driver+17+for+SQL+Server&TrustServerCertificate=no&Encrypt=yes

[dharanidharan59]

Hi @victordibia

Tried with the new db and go the below log

INFO:     Loading environment from 'C:\Users\abc\.autogenstudio\temp_env_vars.env'
udio\temp_env_vars.env'
2025-03-06 19:51:58.504 | INFO     | autogenstudio.web.initialization:__init__:40 - Initializing application data folderudio\temp_env_vars.env'
udio\temp_env_vars.env'
2025-03-06 19:51:58.504 | INFO     | autogenstudio.web.initialization:__init__:40 - Initializing application data folder: C:\Users\abc\.autogenstudio 
INFO:     Started server process [29596]
INFO:     Waiting for application startup.
2025-03-06 19:52:03.836 | INFO     | autogenstudio.database.db_manager:initialize_database:52 - Creating database tables...
2025-03-06 19:52:07.278 | INFO     | autogenstudio.database.schema_manager:_initialize_alembic:154 - Alembic initialization complete
2025-03-06 19:52:07.278 | INFO     | autogenstudio.database.schema_manager:_initialize_alembic:154 - Alembic initialization complete
2025-03-06 19:52:07.299 | ERROR    | autogenstudio.database.schema_manager:generate_revision:490 - Failed to generate revision: '%' must be followed by '%' or '(', found: '%3D%7BODBC+Driver+17+for+SQL+Server%7D%3BSERVER%3Dmyserver.database.windows.net%3BDATABASE%3Dmydb%3BUID%3Dmyusername%3BPWD%3DMyPassword123%3BEncrypt%3Dyes%3BTrustServerCertificate%3Dno%3BConnection+Timeout%3D60%3B'    
2025-03-06 19:52:07.300 | INFO     | autogenstudio.web.app:lifespan:35 - Application startup complete. Navigate to http://127.0.0.1:8081

Connection String format:
mssql+pyodbc:///?odbc_connect=DRIVER%3D%7BODBC+Driver+17+for+SQL+Server%7D%3BSERVER%3Dexample-server.database.windows.net%3BDATABASE%3Dexample-db%3BUID%3Dexample-user%3BPWD%3DExample-Password123%3BEncrypt%3Dyes%3BTrustServerCertificate%3Dno%3BConnection+Timeout%3D60%3B

And the execution so far created the below tables in Azure SQL DB

There seems to be an error with the way data gets pushed to the mssql
Could you please help here?

[victordibia]

Hi,

I had some time to troubleshoot this.
I have made some changes that should fix the problem above (there seemed to have been an issue specific to azuresql connection strings and how they get written to a migration file).

Can you install and try the latest dev build

pip install autogenstudio==0.4.2.dev1

[dharanidharan59]

Thanks @victordibia
It works! Appreciate your quick turnaround on the issue !

[victordibia]

Great. Should we mark this as resolved?
I’d also love to learn more about your use case