SQL OAUTH Connection Setup with Managed Identity using .net Framework Command Line application - Assistance Needed

Hi Adigopula, Vijaya (CORP Admin Account),

Thanks for reaching out to Microsoft Q&A.

Yes, to use a MI for auth in SQL, the MI needs to be granted appropriate permissions on the Azure SQL Database. You will need to add the MI as a user in the db and assign it the necessary roles.

Add the MI to SQL Server:

• First, retrieve the mi object ID (for a system assigned mi, this can be found in the Azure portal under the VM or app service's Identity settings).
• Then, log in to the SQL db as a user with the necessary admin privileges and run the following SQL commands to create a login and user for the managed identity
  • CREATE USER [<Managed Identity Name>] FROM EXTERNAL PROVIDER;

Grant the Required Role to the MI:

• Depending on what actions the managed identity needs to perform in the SQL Database, you will need to assign it the appropriate roles. For example:
  • ALTER ROLE db_datareader ADD MEMBER [<Managed Identity Name>];
  • ALTER ROLE db_datawriter ADD MEMBER [<Managed Identity Name>];

Update the Connection String: Make sure you are passing the access token in your SQL connection. Your code should look something like this:

Ensure that the MI has the proper roles and permissions on the SQL db, and the conn string uses the correct server and database values!

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.

Azure.Identity.CredentialUnavailableException: DefaultAzureCredential failed to retrieve a token from the included credentials. See the troubleshooting guide for more information. https://aka.ms/azsdk/net/identity/defaultazurecredential/troubleshoot - EnvironmentCredential authentication unavailable. Environment variables are not fully configured. See the troubleshooting guide for more information. https://aka.ms/azsdk/net/identity/environmentcredential/troubleshoot - WorkloadIdentityCredential authentication unavailable. The workload options are not fully configured. See the troubleshooting guide for more information. https://aka.ms/azsdk/net/identity/workloadidentitycredential/troubleshoot - ManagedIdentityCredential authentication unavailable. No response received from the managed identity endpoint. - JSON file found at "C:\Users\INAS05775\AppData\Local.IdentityService\AzureServiceAuth\tokenprovider.json" has invalid schema. - Azure CLI not installed - Az.Accounts module >= 2.2.0 is not installed. - Azure Developer CLI could not be found. ---> System.AggregateException: Multiple exceptions were encountered while attempting to authenticate. ---> Azure.Identity.CredentialUnavailableException: EnvironmentCredential authentication unavailable. Environment variables are not fully configured. See the troubleshooting guide for more information. https://aka.ms/azsdk/net/identity/environmentcredential/troubleshoot    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    at Azure.Identity.CredentialDiagnosticScope.FailWrapAndThrow(Exception ex, String additionalMessage, Boolean isCredentialUnavailable)    at Azure.Identity.EnvironmentCredential.<GetTokenImplAsync>d__12.MoveNext() --- End of stack trace from previous location where exception was thrown ---    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)    at Azure.Identity.EnvironmentCredential.<GetTokenAsync>d__11.MoveNext() --- End of stack trace from previous location where exception was thrown ---    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    at Azure.Identity.DefaultAzureCredential.<GetTokenFromSourcesAsync>d__14.MoveNext()The tool is working fine on my machine, but when we tested the executable on a different machine, we encountered the following error. Do we need to make any changes to the Azure configuration?

The tool is working fine on my machine, but when we tested the executable on a different machine, we encountered the following error. Do we need to make any changes to the Azure configuration?

Azure.Identity.CredentialUnavailableException: DefaultAzureCredential failed to retrieve a token from the included credentials. See the troubleshooting guide for more information. https://aka.ms/azsdk/net/identity/defaultazurecredential/troubleshoot - EnvironmentCredential authentication unavailable. Environment variables are not fully configured. See the troubleshooting guide for more information. https://aka.ms/azsdk/net/identity/environmentcredential/troubleshoot - WorkloadIdentityCredential authentication unavailable. The workload options are not fully configured. See the troubleshooting guide for more information. https://aka.ms/azsdk/net/identity/workloadidentitycredential/troubleshoot - ManagedIdentityCredential authentication unavailable. No response received from the managed identity endpoint. - JSON file found at "C:\Users\INAS05775\AppData\Local.IdentityService\AzureServiceAuth\tokenprovider.json" has invalid schema. - Azure CLI not installed - Az.Accounts module >= 2.2.0 is not installed. - Azure Developer CLI could not be found. ---> System.AggregateException: Multiple exceptions were encountered while attempting to authenticate. ---> Azure.Identity.CredentialUnavailableException: EnvironmentCredential authentication unavailable. Environment variables are not fully configured. See the troubleshooting guide for more information. https://aka.ms/azsdk/net/identity/environmentcredential/troubleshoot    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    at Azure.Identity.CredentialDiagnosticScope.FailWrapAndThrow(Exception ex, String additionalMessage, Boolean isCredentialUnavailable)    at Azure.Identity.EnvironmentCredential.<GetTokenImplAsync>d__12.MoveNext() --- End of stack trace from previous location where exception was thrown ---    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)    at Azure.Identity.EnvironmentCredential.<GetTokenAsync>d__11.MoveNext() --- End of stack trace from previous location where exception was thrown ---    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    at Azure.Identity.DefaultAzureCredential.<GetTokenFromSourcesAsync>d__14.MoveNext()