CODEWITHSUNDEEP
pythonpython-3.xazure-active-directoryazure-sql-database
Akshay Godase
Akshay Godase

Reputation: 187

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

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?

Upvotes: 2

Views: 3049

Answers (1)

Leon Yue
Leon Yue

Reputation: 16411

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.

Upvotes: 4

Related Questions