sql-serverazureazure-webappsazure-hybrid-connections

Azure Hybrid Connection Not finding MS SQL Server

I am trying to use an Azure Hybrid connection on a Linux Azure Web App. When I connect to a Hybrid connection on my local computer, it works with no problem. I just enter the Computer name, and the SQL port, and create my connection string. This returns the expected results.

When I change it to work on the Prod Server, I run into issues.

My connection code in python using Flask:

def get_cursor():
    server = current_app.config['SERVER']
    database = current_app.config['DATABASE']
    db_username = current_app.config['DB_USERNAME']
    db_password = urllib.parse.quote_plus(current_app.config['DB_PASSWORD'])
    db_password = current_app.config['DB_PASSWORD']
    print(db_password)
    db_port = current_app.config['DB_PORT']
    
    params = urllib.parse.quote_plus("DRIVER={ODBC Driver 17 for SQL Server};"
                                 f"SERVER={server};"
                                 f"DATABASE={database};"
                                 f"PORT={db_port};"
                                 f"UID={db_username};"
                                 f"PWD={db_password}")

    my_stirng = (f"mssql+pyodbc:///?odbc_connect={params}")
    print(my_stirng)
    engine = sa.create_engine(f"mssql+pyodbc:///?odbc_connect={params}")
    connection = engine.raw_connection()

    return connection.cursor()

My Hybrid connection in Azure:

enter image description here

Then my Sql Server I want to connect with the:

enter image description here

My Hybrid Connection Manager:

enter image description here

Finally, things I have checked or tried:

Checked that TCP/IP is enabled and has a static Port I changed the computer name to the full name. So instead of SQL01, it is SQL01.fireball.ads Ensured ODBC 17 for SQL Server is installed driver

When I run my app, I get a timeout error:

sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)') (Background on this error at: https://sqlalche.me/e/14/e3q8)

To me it looks like the server and and connection manager do see one another. However it can't locate the sql instance on the port. It seems like it being a named instance is what is giving me problems.

My endpoint in the AHC is:

sql01.fireball.ads:1435

and my server in my connections string is:

sql01\sql2016_01

Any help would be appreciated.

Solution

  • As @AlwaysLeaning posted in the comments:

    PORT is not an ODBC Connection String keyword. Have you tried to use SERVER=tcp:YourServerNameOrIpAddress,PortNumber; where the comma delimits the server and port?

    I changed that line, and the ports lined up, allowing me to connect