Retrieving the connection string for a Mssql database running inside a container

Question

I'm writing a local python script that queries an MSSQL database that runs locally and having difficulties connecting to the database. The database runs inside a docker container since I'm using a mac (i5). I'm not sure what is the correct format for the connection string required to create the engine using sqlalchemy. I use Azure Data Studio to query the database directly. How can I check for the connection string from the docker desktop app or the Azure Data studio app?

I ran the following code:

def generate_engine(path_to_yml):
    # Load credentials from the .yml file
    credentials_file_path = path_to_yml
    credentials = load_credentials(credentials_file_path)

    if credentials:
        server = credentials['server']
        port = credentials['port']
        database = credentials['database']
        username = credentials['username']
        password = credentials['password']

        # Construct the connection string
        connection_string = f'mssql+pyodbc://{username}:{password}@{server}:{port}/{database}?driver=ODBC+Driver+17+for+SQL+Server'

        # Create the engine
        engine = create_engine(connection_string)  # Set echo=True for debugging

        # Test the connection
        try:
            connection = engine.connect()
            print("Connection successful!")
            connection.close()
        except Exception as e:
            print("Connection error:", e)

and got the following error: Connection error: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot open database "mcr.microsoft.com/mssql/server:2019-latest" requested by the login. The login failed. (4060) (SQLDriverConnect)') This is my yaml file:

server: localhost
port: 1433
database: mcr.microsoft.com/mssql/server:2019-latest
username: sa
password: #mypassword

I'm not sure i put the correct string in the database field in the yaml file and in the end of the connection field.

connection pane in Azure data studio

database details from the docker desktop app

Thanks!

score 0 · Answer 1 · answered Aug 30 '23 at 11:38

I have got same error when I tried to connect MSSQL database:

enter image description here

There is no direct way to get connection string in Azure data studio . You can find the connection string of MSSQL server from here.

As per this Microsoft ODBC Driver 17 for SQL Server Connection String is as follows:

Driver={ODBC Driver 17 for SQL Server};Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;

I have used the connection string as above format and tried to connect SQL database with below code:

import urllib.parse
from sqlalchemy import create_engine

server = '<serverName>'
database = '<databaseName>'
user = '<userName>'
password = '<password>'
driver = 'ODBC Driver 17 for SQL Server'
conn = f'DRIVER={driver};SERVER={server};DATABASE={database};UID={user};PWD={password};'
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 * from Student")
    for row in result:
        print(row)

It connected successfully and executed the query successfully:

enter image description here

Retrieving the connection string for a Mssql database running inside a container

1 Answers1