I've created a Python script on my local machine & I'm attempting to authenticate into an Azure SQL Server (serverless (i.e., not managed instance)). Rather than storing creds in code, I want to utilize user-managed identity (UMI) to authenticate into my SQL Server.
I created the UMI in Azure portal, assigned it to my SQL Server & gave it read, write & admin authorization in SQL server.
I'm utilizing pyodbc in my script & I believe I'm having trouble with the connection string. After reviewing documentation & vids I thought it might be the case I could simply use the UMI client id rather than using Key Vault (as I prefer not to use that if at all possible); similarly, I don't want to use the ODBC GUI Client (i.e., ODBC Data Source Administrator) to store creds if I don't have to.
My thought is at the very least I have to pass in the UMI client id string via the pyodbc connection string, but then again I really don't have a lot of experience with this.
Here is my connection string:
db_connect = pyodbc.connect(f"DRIVER={pyodbc_driver}; SERVER={pyodbc_server}; DATABASE={pyodbc_db}; UID={pyodbc_umi_client_id}; Authentication=ActiveDirectoryMsi", autocommit=True)
Here is the error I'm getting:
...Failed to authenticate the user 'pyodbc_umi_client_id' in Active Directory (Authentication option is 'ActiveDirectoryMSI').\r\nError code 0xA190; state 41360\r\n (0); [CE267] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Timeout error [258]. (258); [CE267] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [CE267] [Microsoft][ODBC Driver 17 for SQL Server]Unable to complete login process due to delay in login response (258)")
For the UID, I've tried the client ID string from the UMI on the Azure portal. Additionally, I also tried importing the following from one of the Azure modules:
from azure.identity import DefaultAzureCredential
...
pyodbc_umi_client_id = 'client_id' # client id string from umi in azure portal
db_umi_crd = DefaultAzureCredential(managed_identity_client_id=pyodbc_umi_client_id)
Here is all the script with identifying info removed it somehow it is helpful:
"""Dec 27, 2021
Want to connect to SQL db by using Azure
user-managed identity (UMI).
"""
import datetime
from azure.identity import DefaultAzureCredential
import pyodbc
# global vars
program_name = 'AZURE SQL UMI CONNECTION'
original_date = datetime.datetime(2021, 12, 27)
def main():
"""Run main part (i.e., all functions) of the program
Arguments:
None
Returns:
None
Raises:
None
"""
print_header(program_name, original_date)
db_work()
def db_work():
"""Connect to the db and do work
Arguments:
None
Returns:
None
Raises:
None
"""
# connection string vars
pyodbc_driver = '{ODBC Driver 17 for SQL Server}'
pyodbc_server = 'tcp:server_url,1433'
pyodbc_db = 'sql_db'
pyodbc_umi_client_id = 'client_id' # client id string from umi in azure portal
db_umi_crd = DefaultAzureCredential(managed_identity_client_id=pyodbc_umi_client_id)
# connection string
# db_connect = pyodbc.connect(f"DRIVER={pyodbc_driver}; SERVER={pyodbc_server}; DATABASE={pyodbc_db}; UID={db_umi_crd}")
db_connect = pyodbc.connect(f"DRIVER={pyodbc_driver}; SERVER={pyodbc_server}; DATABASE={pyodbc_db}; UID={pyodbc_umi_client_id}; Authentication=ActiveDirectoryMsi", autocommit=True)
# db cursor
db_cursor = db_connect.cursor()
# do work
rows = db_cursor.execute('select * from orderitems').fetchall()
for row in rows:
print(row)
def print_header(program_name, original_date, border='*'):
"""Print header indicating name of program
Arguments:
Program name: Positional arg. This is global var.
Original Date: Positional arg. Date script was originally created.
Border: Keyword arg. Border that is to print around name of program.
Returns:
None
Raises:
None
"""
program_name_len = len(program_name) + len(str(original_date))
print()
print(border * program_name_len)
print(program_name, ' ', str(original_date))
print(border * program_name_len)
print()
if __name__ == '__main__':
main()
Thanks for your time.
