Share via

Azure Synapse database: How to fix HY000 "login failed" error

Chuck Roberts 65 Reputation points
2024-05-23T10:56:39.69+00:00

I'm getting an error when using my Python program to connect to an Azure Synapse MS SQL server.

My info:

  1. Using Python 3.11 on Windows 10 Pro. And using the pyodbc 5.1.0 module.
  2. Server: [blahserver.sql.azuresynapse.net]. This is an Azure Synapse MS SQL server. I do not have admin access to this server but I can use it via Power BI just fine using my Active Directory login info.
  3. This is my first time using the server via Python. I connect to this Azure SQL server fine in Power BI.
  4. Port: 1433
  5. We use an Active Directory for login info for all our PCs and for this database.
  6. Connection string: DRIVER={ODBC Driver 18 for SQL Server};[SERVER=blahserver.sql.azuresynapse.net][;DATABASE=mydbname;UID=myser@email.com];PWD=mypwd

I installed the MS ODBC driver on my laptop, I did not configure it. And installed pyodbc for my program in Test\piecework. When my Python program tries to connect to the database on Azure I get this error:

pyodbc.Error: ('HY000', '[HY000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Cannot open server "[xxx.com]" requested by the login. The login failed. (40532)

(SQLDriverConnect); [HY000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Cannot open server "[xxx.com]" requested by the login. The login failed. (40532)')

My second try.

When I use this for the connection string DRIVER={ODBC Driver 18 for SQL Server};SERVER=myemail@domain.com@blahsomething.sql.azuresynapse.net;DATABASE=pace;UID=myemail@domain.com;PWD=mypass;PORT=1433;Authentication=ActiveDirectoryPassword I get this error:

pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53]. (53) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 18 for SQL Server]Invalid connection string attribute (0); [08001] [Microsoft][ODBC Driver 18 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to myuser@blahsomething.sql.azuresynapse.net. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (53)')

Questions

  • Do I have to use an ODBC Python module or is there one just for Azure Synapse SQL?
  • Do I have to configure the ODBC driver on Windows some how?
  • How do I get this to connect to our SQL database?
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,558 questions
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Chuck Roberts 65 Reputation points
    2024-05-23T13:15:52.2466667+00:00

    Ok this is how I got it to work. I hope this helps others. 

    # Make connection details.
username = 'user@email.com' # For Microsoft Active Directory username  
password = 'password'  # Active Directory password
dbport = 1433
server = f'myserver.sql.azuresynapse.net'  
database = 'dbname'  
driver = '{ODBC Driver 18 for SQL Server}'
# We might need this in conn string: Authentication=ActiveDirectoryPassword;

connection_string = f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password};PORT={dbport};Authentication=ActiveDirectoryPassword'
# print(f"Connecting with connection string: {connection_string}...")

try:
    # Establish a connection
    conn = pyodbc.connect(connection_string)
    if not conn: 
        errstr = f"ERROR: connection not made to {connection_string}"
        print(f"{errstr}")
        sys.exit()
    # Create a cursor
    cursor = conn.cursor()
    0 comments