question

madhavanthasuramachandran-9246 avatar image
0 Votes"
madhavanthasuramachandran-9246 asked HediHARGAM-6358 commented

SQL Azure Connection Using managed identity in Azure Function (python)

Hello All,

I have an Azure function Python 3.6, Consumption Plan and SQL Azure. Enabled system identity for the function.. Created User for this function in SQL Azure.

Now need help on implementing the connectivity using managed identity.

Based on the link here -> https://docs.microsoft.com/en-us/azure/app-service/overview-managed-identity?tabs=python

I am not getting how to set the MSI_Endpoint and MSI_Header for my function.

Any help appreciated.

Regards
Madhavan.TR

azure-functions
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello,

Did you try to modify the connection string with Authentication parameters. It should be enough to use managed identity on azure sql without using identity and headers.

pyodbc.connect(
"Driver="
+ driver
+ ";Server="
+ server
+ ";PORT=1433;Database="
+ database
+ ";Authentication=ActiveDirectoryMsi")


https://github.com/HediHargam/AzureMSI-connect-webapp-to-sqldb/blob/main/sql_db_conn.py


0 Votes 0 ·

1 Answer

JayaC-MSFT avatar image
0 Votes"
JayaC-MSFT answered madhavanthasuramachandran-9246 commented

Hello @madhavanthasuramachandran-9246, I see the document you are referring mentions this : https://docs.microsoft.com/en-us/azure/app-service/overview-managed-identity?tabs=dotnet#using-the-rest-protocol
Could you please confirm if this is the case?



UPDATE:

Code to start with :

 identity_endpoint = os.environ["IDENTITY_ENDPOINT"]
 identity_header = os.environ["IDENTITY_HEADER"]
 resource_uri="https://database.windows.net/"
 token_auth_uri = f"{identity_endpoint}?resource={resource_uri}&api-version=2019-08-01"
 head_msi = {'X-IDENTITY-HEADER':identity_header}
 resp = requests.get(token_auth_uri, headers=head_msi)
 access_token = resp.json()['access_token']
 accessToken = bytes(access_token, 'utf-8');

Please refer to : https://github.com/AzureAD/azure-activedirectory-library-for-python/wiki/Connect-to-Azure-SQL-Database

https://docs.microsoft.com/en-us/azure/app-service/overview-managed-identity?tabs=python#code-examples

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes. After enabling the system identity for my azure function i could able to see the objectid generated. But, i dont see any MSI_endpoint in the application configuration.. which is required (during the runtime to pick from environment).

0 Votes 0 ·

Hi JayaC

I believe, MSI_endpoint is set at the environment, can only be available at run time. I deployed my function with endpoint and secret. Below code helped..

identity_endpoint = os.environ["MSI_ENDPOINT"]
identity_header = os.environ["MSI_SECRET"]
logging.info('identity_endpoint: {}'.format(identity_endpoint))
logging.info('identity_header : {}'.format(identity_header))
token_auth_uri = f"{identity_endpoint}?resource={resource_uri}&api-version=2017-09-01"
head_msi = {'X-IDENTITY-HEADER':identity_header}
resp = requests.get(token_auth_uri, headers=head_msi)
access_token = resp.json()['access_token']
logging.info('response received from token endpoint: {}'.format(access_token))
return access_token

After executing the function.. i got exception saying "An exception occured Access_Token"

identity_endpoint: http://localhost:8081/msi/token
identity_header : 8E9ECXXXXXXXXXXXXXXXXXXXXX
An exception occurred: 'access_token'

What am i doing wrong here ?

0 Votes 0 ·

Thanks a lot Jaya C. It worked fine now.
I really appreciate your prompt response.

0 Votes 0 ·