Accessing Azure SQL Server using 1) Python (local script--not going to be Azure function), 2) pyodbc-CodePudding

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.

CodePudding user response：

I want to utilize user-managed identity (UMI) to authenticate into my SQL Server.

As suggested by @Ondrej here

Currently, the server identity for Azure SQL does not support user-assigned managed identities (UMI)

Based on the MS DOC

The ODBC Driver on Linux and macOS before version 17.6 only supports Azure Active Directory authentication directly against Azure Active Directory. If you are using Azure Active Directory username/password authentication from a Linux or macOS client and your Active Directory configuration requires the client to authenticate against an Active Directory Federation Services endpoint, authentication may fail. As of driver version 17.6, this limitation has been removed.

If trying to authenticate using access token:

The ODBC Driver version 13.1 only supports this authentication on Windows.

EDIT: For more information please refer this SO THREAD

CodePudding user response：

@AjayKumarGhose-MT

Received the following from Microsoft after using Microsoft Q&A:

Thanks for using Microsoft Q&A!! You are getting this error as you cannot use either user assigned identity or >system assigned managed identity to access from SQL server from you local >environment as these identities are meant for accessing Azure AD protected >resources from other Azure services like Azure functions, web apps etc.

Please refer to managed identities documentation for details. You need to either provide the username and password in the code or you can >store these as environment variables.