I have a function app that attempts to insert [1234, 'Hello'] into a sql table called test_table_do_not_use by using a user-assigned managed identity resource called MI_ODBC and DefaultAzureCredential from the azure.identity python library for authentication--i.e. authentication without credentials. I was able to successfully do this previously for our Azure SQL DB by following this microsoft guide, but after switching to Azure SQL managed instance, I am now encountering problems. The function app code:
import os
import pyodbc, struct
from azure.identity import DefaultAzureCredential
import azure.functions as func
import logging
sql_insert_data = [1234, 'Hello']
def insert_all(data):
sql = "INSERT INTO database.dbo.test_table_do_not_use(column1, column2) VALUES (?, ?)"
cnxn = get_conn()
crsr = cnxn.cursor()
try:
crsr.execute(sql, data)
except Exception as e:
crsr.rollback()
print(e)
print('Transaction rollback')
else:
cnxn.commit()
crsr.close()
cnxn.close()
# Managed Identity Auth
def get_conn():
credential = DefaultAzureCredential(exclude_interactive_browser_credential=False)
token_bytes = credential.get_token("https://database.windows.net/.default").token.encode("UTF-16-LE")
token_struct = struct.pack(f'<I{len(token_bytes)}s', len(token_bytes), token_bytes)
SQL_COPT_SS_ACCESS_TOKEN = 1256 # This connection option is defined by microsoft in msodbcsql.h
connection_string = os.environ["SQLAZURECONNSTR_connectionstringname"]
conn = pyodbc.connect(connection_string, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})
return conn
app = func.FunctionApp()
@app.schedule(schedule="0 */1 * * * *", arg_name="myTimer", run_on_startup=True,
use_monitor=False)
def timer_trigger(myTimer: func.TimerRequest) -> None:
if myTimer.past_due:
logging.info('The timer is past due!')
logging.info('Python timer trigger function executed.')
return insert_all(data=sql_insert_data)
This is the connection string I have been using for user-assigned managed identity / DefaultAzureCredential authentication:
Driver={ODBC Driver 18 for SQL Server};Server=tcp:<database-server-name>.database.windows.net,1433;Database=<database-name>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30
However, I keep getting this error:
Result: Failure Exception: InterfaceError: ('28000', "[28000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user '<token-identified principal>'. (18456) (SQLDriverConnect)") Stack: File "/azure-functions-host/workers/python/3.11/LINUX/X64/azure_functions_worker/dispatcher.py", line 505, in _handle__invocation_request call_result = await self._loop.run_in_executor( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/concurrent/futures/thread.py", line 58, in run result = self.fn(*self.args, **self.kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/azure-functions-host/workers/python/3.11/LINUX/X64/azure_functions_worker/dispatcher.py", line 778, in _run_sync_func return ExtensionManager.get_sync_invocation_wrapper(context, ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/azure-functions-host/workers/python/3.11/LINUX/X64/azure_functions_worker/extension.py", line 215, in _raw_invocation_wrapper result = function(**args) ^^^^^^^^^^^^^^^^ File "/home/site/wwwroot/function_app.py", line 73, in timer_trigger return insert_all(data=sql_insert_data) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/site/wwwroot/function_app.py", line 30, in insert_all cnxn = get_conn() ^^^^^^^^^^ File "/home/site/wwwroot/function_app.py", line 62, in get_conn conn = pyodbc.connect(connection_string, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})
I can get the function app to work if I use sql authentication with credentials stored in the function app's Configuration -> Connection Strings then call the enivronment variable with this code:
def get_conn():
connection_string = os.environ["SQLAZURECONNSTR_sqlauthstringname"]
conn = pyodbc.connect(connection_string)
return conn
I added the user-assigned managed identity to the "Identity" section of the function app and to the managed instance, I also added the managed identity as a user in the Azure SQL MI database with the following SQL code:
CREATE USER [user-assigned-identity-name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [user-assigned-identity-name];
ALTER ROLE db_datawriter ADD MEMBER [user-assigned-identity-name];
ALTER ROLE db_ddladmin ADD MEMBER [user-assigned-identity-name];
The user-assigned managed identity has the role assignment SQL Managed Instance Contributor for the resource group the managed instance is located in.
Lastly, I also added "AzureCloud" to the inbound security rules of the managed instance's network security group resource (priority set to 400) to whitelist all function apps.
If anyone has any ideas as to why I keep getting the authentication error with user-assigned managed identity--like maybe some consideration I haven't taken into account with setting up managed identity for sql managed instances, a problem with my connection string, etc.
The answer was to add the user to the specific database I was connecting to. I used the alter role code while connected to database1 when I should have been connected to database2 or I should have written USE database2 before the alter role code. In the future I am going to use a modified version of this code from mssql tips
Use master
GO
DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(max)
DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @statement = 'use '+@dbname +';'+ 'CREATE USER [TipsDemoUser]
FOR LOGIN [TipsDemoUser]; EXEC sp_addrolemember N''db_datareader'',
[TipsDemoUser];EXEC sp_addrolemember N''db_datawriter'', [TipsDemoUser]'
exec sp_executesql @statement
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
I say modified because sp_addrolemember stored procedure is depricated, as noted by microsoft here, "This feature [sp_addrolemember] will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER ROLE instead."
There is apparently a way to add a server level role assignment, so that role and permissions would be inherited by all databases. If this is possible, that would be much better than the above solution from MSSQL tips.