I get to the https://login.microsoftonline.com/ page and I get a “Authentication complete. You can close this window.” message. I’m running the following script. I can’t figure out if there’s something wrong with the script. On the other side, my IP is already added. My user has admin.
Anyway, find any problems?
import pyodbc
import pandas as pd
from azure.identity import InteractiveBrowserCredential
#Azure SQL Database connection parameters
server = 'conosur.database.windows.net'
database = 'legacy (conosur/legacy)'
driver = '{ODBC Driver 17 for SQL Server}'
#Set up the Azure AD credential
credential = InteractiveBrowserCredential()
#Obtain an Azure AD token
token = credential.get_token('https://database.windows.net/.default')
#Create the connection string with the token
connection_string = (
f'DRIVER={driver};'
f'SERVER={server};'
f'DATABASE={database};'
f'Authentication=ActiveDirectoryAccessToken;' # Correct Authentication parameter
f'ACCESS_TOKEN={token.token}' # Correctly set the access token
)
try:
connection = pyodbc.connect(connection_string)
print("Connection established successfully")
except pyodbc.Error as ex:
sqlstate = ex.args[0]
print(f"Error connecting to SQL Server: {sqlstate}")
print(f"Detailed error: {ex}")
exit()
#Define query
query = 'SELECT * FROM your_table_name'
try:
df = pd.read_sql(query, connection)
print("DataFrame created successfully")
except Exception as e:
print(f"Error executing query: {e}")
finally:
connection.close() # Ensure the connection is closed
#Display the DataFrame
print(df.head())
Error message:
Error connecting to SQL Server: 08001 Detailed error: ('08001', "[08001] [Microsoft][ODBC Driver 17 for SQL Server]Invalid value specified for connection string attribute 'Authentication' (0) (SQLDriverConnect)")
I’m hoping to log in with my Entra ID in the prompted window. But after doing it, I get the error message above.
3
Detailed error: (‘08001’, “[08001] [Microsoft][ODBC Driver 17 for SQL Server]Invalid value specified for connection string attribute ‘Authentication’ (0) (SQLDriverConnect)”)
You have provided ActiveDirectoryAccessToken for Authentication parameter in connection string, it is invalid that may be the reason forgetting above error. To fix this, you should use the ActiveDirectoryInteractive authentication method, which is designed for scenarios where the user must manually authenticate (e.g., through a browser popup). You can use below code to connect azure SQL database:
import pyodbc
server = '<serverName>.database.windows.net'
database = '<dbName>'
driver = '{ODBC Driver 17 for SQL Server}'
connection_string = (
f'DRIVER={driver};'
f'SERVER={server};'
f'DATABASE={database};'
f'Authentication=ActiveDirectoryInteractive;'
)
try:
connection = pyodbc.connect(connection_string)
print("Connection established successfully")
except pyodbc.Error as ex:
print(f"Detailed error: {ex}")
It will give a pop-up window for authentication, after successful authentication you will be able connect SQL database successfully as shown below: