pyodbc.OperationalError - Issue connect Microsoft SQL Server

Streamlit Cloud
#1

hi -
I followed the instructions from this page [Connect Streamlit to Microsoft SQL Server] from streamlit documentation to get my remote Microsoft SQL Server connected, however I am stuck at this error:

pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)

The error is from this line of code:

    conn = init_connection()

Thanks.

#2

Hi @jwang-e, welcome to the community!! :wave: :partying_face:

Would you mind sharing the connection string used? Additionally, were you able to connect to the instance outside of Python?

The problem could be DNS related, as described in this pyodbc issue. Do you pass the hostname to the SERVER argument in the conn string? If so, does using the IP address instead of hostname resolve the issue?

Best,
Snehan

#3

hi @snehankekre -
here is my connection string:

def init_connection():
    return pyodbc.connect(
        "DRIVER={ODBC Driver 17 for SQL Server};SERVER="
        + st.secrets["db_server"]
        + ";DATABASE="
        + st.secrets["db_database"]
        + ";UID="
        + st.secrets["db_username"]
        + ";PWD="
        + st.secrets["db_password"]
        +";Encrypt=Yes"
        +";TrustServerCertificate=No"
        +";auth = "

    conn = init_connection()

and st.secrets has been uploaded to github along with changing the setting in app page
I also tried using the IP address as my hostname, but the error seems to remain the same.

Thanks.
Jackie

#4

At this time, Streamlit Cloud does not support these additional connection parameters. We support SQL Server Authentication - legacy syntax, where the server certificate isn’t validated, and encryption is used only if the server enforces it. The username/password is passed in the connection string. server=Server;database=Database;UID=UserName;PWD=Password;.

So the following is recommended:

def init_connection():
    return pyodbc.connect(
        "DRIVER={ODBC Driver 17 for SQL Server};SERVER="
        + st.secrets["db_server"]
        + ";DATABASE="
        + st.secrets["db_database"]
        + ";UID="
        + st.secrets["db_username"]
        + ";PWD="
        + st.secrets["db_password"]

conn = init_connection()