Connect the Azure SQL Server Database with Active Directory Password using python (Got error)

Question

I am trying to connect the Azure, SQL Server Database using Active Directory Password with python. But i got the below error.

Please check the below error:-

Traceback (most recent call last):
  File "database_test.py", line 20, in <module>
    main()
  File "database_test.py", line 11, in main
    connection = pyodbc.connect('DRIVER='+driver+';SERVER='+serverName+';PORT=1443;DATABASE='+dbName+';UID='+User_name+';PWD='+ password+';Authentication=ActiveDirectoryPassword')
pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 13 for SQL Server]SQL Server Network Interfaces: The Microsoft Online Services Sign-In Assistant could not be found. Install it from http://go.microsoft.com/fwlink/?Link Id=234947. If it is already present, repair the installation. [2].  (2) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 13 for SQL Server]Client unable to establish connection (2); [08001] [Microsoft][ODBC Driver 13 for SQL Server]In
valid connection string attribute (0)')

Please Check the Below code:-

import pyodbc

def main():
    serverName = "<ServerName>"
    dbName = "<DatabaseName>"
    User_name = '<UserName>'
    password = '<Password>'

    driver= '{ODBC Driver 13 for SQL Server}'
    connection = pyodbc.connect('DRIVER='+driver+';SERVER='+serverName+';PORT=1443;DATABASE='+dbName+';UID='+User_name+';PWD='+password+';Authentication=ActiveDirectoryPassword')
    cursor = connection.cursor()
    data = cursor.execute("select * from dbo.test;")
    allData = data.fetchall()
    connection.close()
    for i in allData:
        print(i)

if __name__== "__main__":
    main()

Is there any way to resolve the above problem?

Is it possible to connect azure sql server Database using pyodbc with Active Directory Password authentication? if possible what is the proper way to connect the Azure Sql Server Database with Active directory Password?

@LinPy I have checked the error message. I got one link from that error. I have already installed it then i got the same error — Akshay Godase
– Akshay Godase, Commented Aug 30, 2019 at 6:47
@AkshayGodase I tested your code with my Azure sql database, there's no error. — Leon Yue
– Leon Yue, Commented Aug 30, 2019 at 8:07
@LeonYue But which type of authentication you have used means Active Directory Password or SQL Server authentication — Akshay Godase
– Akshay Godase, Commented Aug 30, 2019 at 9:14
@AkshayGodase I also used the Active Directory Password authentication. Please see my answer. — Leon Yue
– Leon Yue, Commented Aug 30, 2019 at 9:16

Leon Yue · Accepted Answer · 2019-08-30 08:14:51Z

4

Please make you have installed the driver for Azure SQL database. You can download from this document Quickstart: Use Python to query an Azure SQL database.

This document can give more guides with Python.

And according you error message, you have missed "The Microsoft Online Services Sign-In Assistant". Please download and install it from the link provided for you:http://go.microsoft.com/fwlink/?Link Id=234947.

Here is my test Python code, I made some change that you can see more clearly:

import pyodbc

def main():
    serverName = "****.database.windows.net"
    dbName = "Mydatabase"
    User_name = '****@****.com'
    password = '****'
    Authentication='ActiveDirectoryPassword'
    driver= '{ODBC Driver 17 for SQL Server}'

    connection = pyodbc.connect('DRIVER='+driver+
                      ';Server='+serverName+
                      ';PORT=1433;DATABASE='+dbName+
                      ';UID='+User_name+
                      ';PWD='+ password+
                      ';Authentication='+Authentication)

    cursor = connection.cursor()
    data = cursor.execute("select * from dbo.tb1;")
    allData = data.fetchall()
    connection.close()
    for i in allData:
        print(i)

if __name__== "__main__":
    main()

Note: I use ODBC Driver 17 for SQL Serve in my computer.

Hope this helps.

answered Aug 30, 2019 at 8:14

Leon Yue

16.5k1 gold badge17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Akshay Godase Over a year ago

I tested the above code and follows the above steps. It's working fine.

Collectives™ on Stack Overflow

Connect the Azure SQL Server Database with Active Directory Password using python (Got error)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related