CODEWITHSUNDEEP
pythonsql-serverazureauthentication
user5932842
user5932842

Reputation: 67

Connect to SQL Server DB with Active Directory Universal Authentication using Python

First, I'm not a developer so I apologize if this is a redundant or unhelpful question.

In Microsoft SQL Server Management Studio, I am able to connect to a certain server using Active Directory Universal Authentication, but I can't figure out a way to do this using Python.

When I login with the application, after I click connect, an Azure login screen pops up, and I am able to login with my username: [email protected] and password.

However, when I try to connect using pyodbc with the following code, I get the error below:

import pyodbc

def main():
    serverName = "name.database.windows.net"
    dbName = "DatabaseName"

    connection = pyodbc.connect("DRIVER={SQL Server};SERVER=" + serverName + 
                 ";DATABASE=" + dbName + 
                 ";[email protected];PWD=mypassword")
    cursor = connection.cursor()
    data = cursor.execute("select * from sometable;")
    allData = data.fetchall()
    connection.close()
    for i in allData:
        print(i)

if __name__== "__main__":
    main()

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open server 'domain' requested by the login. Client with IP address 'xx.xx.xx.xx' is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect. (40615) (SQLDriverConnect); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]

I know there have been posts about similar issues, but I am not sure this is the same problem. I have to use Active Directory Universal Authentication for this server. I have tried all of the SQL Server drivers that return from pyodbc.drivers() with no luck. I am using the same IP address to login through SQL Server Management Studio as when I am attempting it through the Python program. Is there a different module I can use to login with Azure, or is this impossible to do?

For reference, the popups that I see when I login through Microsoft SQL Server Management Studio are below. I have been stuck on this for a while, so any help is much appreciated. enter image description here

enter image description here

Upvotes: 0

Views: 1732

Answers (1)

Jason Ye
Jason Ye

Reputation: 13954

I get the same error message if I have not set Azure SQL firewall: enter image description here

We can via azure portal to set SQL server firewall, like this: enter image description here

After I add the firewall settings, I can use your python script to query, python script works for me.

Upvotes: 1

Related Questions