Azure Question posted in
The official documentation to get you started can be found here.

Connecting to Azure database through python

I am trying to connect to an Azure Synapse database through python code. I already have access to the database itself and can run queries using Azure Data Studio. However, the database connection fails specifying:

Error: (‘HY000’, ‘[HY000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Cannot open server "iberdrola.es" requested by the login. The login failed. (40532) (SQLDriverConnect); [HY000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Cannot open server "iberdrola.es" requested by the login. The login failed. (40532)’)

Below is the code i have used (wo passwords). The user and password is the same that works in the database itself. The ODBC controller is installed already. Any ideas why i am not allowed?

Thanks in advance,

Jon

import pyodbc

server = '*sql.azuresynapse.net'
database = 'D**l'
username = '***'
password = '***'
driver= '{ODBC Driver 13 for SQL Server}'

connection_string = f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD=        {password}'

with pyodbc.connect(connection_string) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT TOP 10 * FROM ***")
        row = cursor.fetchone()
        while row:
            print (str(row[0]) + " " + str(row[1]))
            row = cursor.fetchone()

My only purpose is to get proper connetion to the database through python to be able to run programs based on that Azure database.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks Bhavani,

    The spaces after PSW seemed to be a typo error, not in the code. I have tried with your code but still the same issue. I think it could be the old ODBC controller i have since mine is 13. I have asked my admin to update it just in case.

    Thanks,

    Jon

    import pyodbc
  
server = '--.azuresynapse.net'
database = '--SQLPool_Retail'
username = '--'
password = '--'
driver= '{ODBC Driver 13 for SQL Server}'  
connection_string = f'DRIVER={driver};SERVER={server};DATABASE={database};UID= 
{username};PWD={password}'
with pyodbc.connect(connection_string) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM --")
        row = cursor.fetchone()
        while row:
            print (str(row[0]) + " " + str(row[1]))
            row = cursor.fetchone()

  2. 0 
    connection_string = f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}'

    In the above-mentioned connection string, you have given a gap between the password, which may be the reason for getting the login failed error. Provide the connection string in the following format:

    connection_string = f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}'

    Then, you will be able to connect to the database using the code below successfully:

    import pyodbc
      
server = 'synpool.sql.azuresynapse.net'
database = 'dedsql'
username = '<userNmae>'
password = '<password>'
driver= '{ODBC Driver 18 for SQL Server}'  
connection_string = f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}'
with pyodbc.connect(connection_string) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM student")
        row = cursor.fetchone()
        while row:
            print (str(row[0]) + " " + str(row[1]))
            row = cursor.fetchone()

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search