Unable to access Azure SQL database from Azure Load Test using managed identity

Question

Hi,

I am trying to do load testing on my Azure SQL database using Azure Load Tests. I have setup user assigned managed identity on Azure Load Test and given it Reader and DB SQL Contributor role on SQL server. I am using the following connection string:

jdbc:sqlserver://server_url:1433;database=database_name;encrypt=true;Authentication=ActiveDirectoryMSI&USER ID=<manged identity client id>;

I have created a user account using the following SQL commands in my database as given below:

declare @MSIname sysname = '<managed identity name>'
declare @clientId uniqueidentifier = '<managed identity client id>';
-- convert the guid to the right type and create the SQL user
declare @castClientId nvarchar(max) = CONVERT(varchar(max), convert (varbinary(16), @clientId), 1);
-- Construct command: CREATE USER [@MSIname] WITH SID = @castClientId, TYPE = E;
declare @cmd nvarchar(max) = N'CREATE USER [' + @MSIname + '] WITH SID = ' + @castClientId + ', TYPE = E;'
EXEC (@cmd)
--For basic select rights:
ALTER ROLE [db_datareader] ADD MEMBER managed_identity_name;
--For insert/update rights:
ALTER ROLE [db_datawriter] ADD MEMBER managed_identity_name;
--For full dbowner rights:
ALTER ROLE [db_owner] ADD MEMBER managed_identity_name;

I am getting this error when I try to run a test just trying to run a select query.

2024-07-15 06:26:46,133 ERROR c.a.i.i.IdentityClient: ManagedIdentityCredential authentication unavailable. Connection to IMDS endpoint cannot be established, Connection refused. ManagedIdentityCredential authentication unavailable. Connection to IMDS endpoint cannot be established, Connection refused. 2024-07-15 06:26:46,134 ERROR c.a.i.ManagedIdentityCredential: Azure Identity => ERROR in getToken() call for scopes [https://database.windows.net//.default]: Managed Identity authentication is not available.

Just to clarify, I have a jmeter script preparted locally and uploaded to Azure load tests and then being run. So the tests are run from Azure portal not locally. I have also tried the same thing with system assigned managed identity and that failed with the same error. Is there something I am missing in the setup here?

Answer 1

Hey,

Here are some suggestions to help resolve the issue you are facing:

1. Missing CONNECT Permission: SQL user requires CONNECT permission on the database. You can grant this permission using the following command:

      GRANT CONNECT TO [managed_identity_name];
   

2. Connection String: When using Active Directory Managed Identity (ActiveDirectoryMSI), you may not need to specify the USER ID.
3. Managed Identity Configuration:
   • Ensure that Azure Load Test has Managed Identity enabled.
   • Check for any network issues that may be preventing access to the IMDS endpoint.

Please let me know how it goes!

Answer 2

Hi Amrata Dhankhar,

Welcome to Microsoft Q&A forum.

As I understand, you are Unable to access Azure SQL database from Azure Load Test using managed identity.

Please refer to https://techcommunity.microsoft.com/t5/apps-on-azure-blog/load-testing-databases-with-azure-load-testing/ba-p/3846557

and

https://learn.microsoft.com/en-us/azure/load-testing/how-to-use-a-managed-identity?tabs=azure-portal

if configuration is done the same way as mentioned in the documentation.

Since the question is inclined towards Azure Load Testing, please reach out here https://developercommunity.microsoft.com/loadtesting

Thanks

Answer 3

Found a stack overflow post saying IMDS is blocked for Azure load testing specifically. So switching to ActiveDirectoryPassword auth mode.