Azure Hybrid Connection Not finding MS SQL Server

Question

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:

Then my Sql Server I want to connect with the:

My Hybrid Connection Manager:

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.

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? — AlwaysLearning
– AlwaysLearning, Commented Mar 7, 2023 at 3:48
Amazing, that worked!! Makes perfect sense. I thought I was routing through a specific port but was not. As a result, I assume it was trying to use the default port.. Thank you! — ghawes
– ghawes, Commented Mar 7, 2023 at 3:58

ghawes · Accepted Answer · 2023-03-07 05:18:22Z

0

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

answered Mar 7, 2023 at 5:18

ghawes

3032 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Azure Hybrid Connection Not finding MS SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related