MSAL Authentication Issue When Deploying Azure Function (Python v1), but works locally

174 views Asked by At

I have had an Azure Function deployed for quite some time. The function is written in Python and is using model v1. I am reading data from a database in Azure Synapse using a service principal. Up until 10AM CST today, this worked with no issue. However, after the aforementioned time, I began seeing 500 server errors in the logs. The error message indicates the following (as if it is passing an empty user):

Result: Failure Exception: InterfaceError: ('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user ''. (18456) (SQLDriverConnect)") Stack: File "/azure-functions-host/workers/python/3.10/LINUX/X64/azure_functions_worker/dispatcher.py", line 475, in _handle__invocation_request call_result = await self._run_async_func( File "/azure-functions-host/workers/python/3.10/LINUX/X64/azure_functions_worker/dispatcher.py", line 758, in _run_async_func return await ExtensionManager.get_async_invocation_wrapper( File "/azure-functions-host/workers/python/3.10/LINUX/X64/azure_functions_worker/extension.py", line 147, in get_async_invocation_wrapper result = await function(**args) File "/home/site/wwwroot/TestTrigger/__init__.py", line 58, in main sqlRoutesOutput = currentRoutes.synapseSql(); File "/home/site/wwwroot/classes/nexsql.py", line 32, in synapseSql synConn = pyodbc.connect(synConnString, attrs_before = {SQL_COPT_SS_ACCESS_TOKEN:tokenstruct})

And this is the code block:

 def synapseSql(self):
        creds = ConfidentialClientApplication(
            client_id=os.environ['clientid'], 
            authority=f"https://login.microsoftonline.com/{os.envion['tenantid']}",
            client_credential= os.environ['sqlcreds'])

        token = creds.acquire_token_for_client(scopes=['https://database.windows.net/.default'])

        tokenb = bytes(token["access_token"], "UTF-8")
        exptoken = b''
        for i in tokenb:
            exptoken += bytes({i})
            exptoken += bytes(1)
        tokenstruct = struct.pack("=i", len(exptoken)) + exptoken
        SQL_COPT_SS_ACCESS_TOKEN = 1256 

        synConnString = 'DRIVER={ODBC Driver 17 for SQL Server};' \
                    + f'SERVER={self.server};' \
                    + f'DATABASE={self.database};'\
                    + 'ENCRYPT=Yes;'

        synConn = pyodbc.connect(synConnString, attrs_before = {SQL_COPT_SS_ACCESS_TOKEN:tokenstruct})
        cursor = synConn.cursor()
        cursor.execute(self.query)
        return cursor

As you can see, I am using ConfidentialClientApplication to create creds and get token. And then using subsequent code I found here for the token struct.

Please let me know if I can provide any more information to help.

Thanks!

I am really at a loss since it works locally and everyone in my environment is saying nothing has changed. I did triple check the service principals permission, secret expiry, etc.

0

There are 0 answers