Issue - Cannot open server requested by the login. The login fails when connecting to Azure SQL database with Python

Question

I am using Python to query Azure SQL database. I tried connecting using SSMS 2018 and it works. Also, I have ODBC 17, 18 drivers installed. But when I try from code, it gives:

cannot open server requested by the login

The credentials are correct. Below is my code. please assist.

import pyodbc
with pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};Server=tcp:****-dev.database.windows.net,1433;Database=****SQLdb;Uid=****@abc.com;Pwd=****;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;') as conn:
    with conn.cursor() as cursor:
        cursor.execute("select * from [dbo].Test")
        row = cursor.fetchone()
        while row:
            print (str(row[0]) + " " + str(row[1]))
            row = cursor.fetchone()

Answer 1

Here is the full code that worked:

import pyodbc

#Variables
driver='{ODBC Driver 18 for SQL Server}'
server = 'xxxxxxx.database.windows.net'
database='sqldb-data*****1'

#Inserting JSON data into SQL database
def insertToSQL(jsonData):
    with pyodbc.connect('Driver={};Server=tcp:{},1433;Database={};trusted_connection=yes'.format(driver,server,database)) as conn:
        with conn.cursor() as cursor:
            for item in jsonData:
                sql = """INSERT INTO [dbo].test 
                        VALUES ('{}','{}')""".format(item['EMPL_NO'],
                        item['EMPL_NAME'])
    print('Insert completed')

#Read text file with comma delimiter
def readTextFile(filePath):
    data = []
    with open(filePath, 'r') as file:
        headers = (file.readline().strip()).split(',')
        for line in file:
            fields = line.split(',')
            data.append({
                headers[0]: fields[0].strip(),
                headers[1]: fields[1].strip()
            })
    print('Reading file completed')
    return data

fileData = readTextFile(r'\\abcd\*****\test.txt')
#print(fileData)
insertToSQL(fileData)

Answer 2

If your SQL Database server is called myazureserver and your login is [email protected], then you must supply your login as [email protected]@myazureserver.

Reference: [USING EMAIL ADDRESSES AS LOGIN USERNAMES ON AZURE SQL] (https://stegriff.co.uk/upblog/using-email-addresses-as-login-usernames-on-azure-sql/)