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

Question

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=******@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=******@domain.com@blahsomething.sql.azuresynapse.net;DATABASE=pace;UID=******@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 ******@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?

Answer 1

Based on this old thread :

If your server name is blahserver.sql.azuresynapse.net and your login is ******@email.com

si then you may need to supply your login as ******@email.com@blahserver.sql.azuresynapse.net

Answer 2

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

# Make connection details.
username = '******@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()