I'm trying to access an Azure SQL based instance from my on-prem application. This access needs to be a Service Account (eg: non-interactive login). I've done this on multiple clouds but am struggling on correct method for Azure.
I believe I need to use Managed Identities (either Service or User) and have created a User Managed Identity which I have connected to a test SQL Instance. The SQL instance has AD Auth authentication and I can manually login via my Azure AD account, however when I try to login via the Client ID or Principal ID - No dice.
I've mainly followed the following:
My basic test code:
string ConnectionString1 = $@"Server=tcp:test-ad-sql-1.database.windows.net,1433;Initial Catalog=test-db-1;Persist Security Info=False;User ID={_clientKey};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Default";
using (SqlConnection conn = new SqlConnection(ConnectionString1))
{
conn.Open();
}
Depending on construction of the connection string I either get:
- 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. Multiple attempts failed to obtain a token from the managed identity endpoint.
- Azure Developer CLI could not be found.
- Visual Studio Token provider can't be accessed at /root/.IdentityService/AzureServiceAuth/tokenprovider.json
- Azure CLI not installed
- PowerShell is not installed.
or
ManagedIdentityCredential authentication unavailable. Multiple attempts failed to obtain a token from the managed identity endpoint.
Any help appreciated
The reason you are encountering this error is because you are trying to use a Managed Identity (either system-defined or user-defined) from an on-prem application. Please note that Managed Identity is only supported in applications when they are running in Azure.
What you would need to do is create a Service Principal, grant it appropriate permissions in your SQL DB and use its client id and secret to connect to the database. Please see this link for more details: https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver16#using-active-directory-service-principal-authentication.