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

Question

Hi ,

 

We were able to retrieve the access token, but when I attempted to open the SQL connection, I encountered the following error:

 

Microsoft.Data.SqlClient.SqlException: **'Login failed for user '

Answer 1

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.

Answer 2

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?

Answer 3

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()