Complete Guide: Service Principal Connection String

To create a connection string using App Registration/Service Principal for your ASP.NET Core Web API with EF Core and SQL Server using Microsoft Entra MFA, you need to use the Active Directory Service Principal authentication mode with your client ID and secret. The proper connection string format is Server=tcp:your-server.database.windows.net,1433;Initial Catalog=your-database;Authentication=Active Directory Service Principal;User Id=your-client-id;Password=your-client-secret;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;, along with configuring your app settings and securing the credentials in production environments.

Contents

• Understanding Microsoft Entra Authentication Methods
• Creating the Proper Connection String
• Configuring ASP.NET Core Web API
• Security Best Practices
• Deployment Considerations
• Troubleshooting Common Issues

Understanding Microsoft Entra Authentication Methods

Microsoft Entra (formerly Azure Active Directory) supports several authentication modes for SQL Server connections, each suited for different scenarios:

1. Active Directory Interactive - Opens a browser prompt for user authentication (what you’re currently using locally)
2. Active Directory Service Principal - Uses a service principal’s client ID and secret for programmatic authentication
3. Active Directory Managed Identity - Uses the application’s managed identity (ideal for Azure-hosted applications)
4. Active Directory Integrated - Uses Windows authentication
5. Active Directory Password - Uses username and password

For your deployed application scenario, Active Directory Service Principal is the appropriate choice as it doesn’t require user interaction and can be automated using your service principal credentials.

According to the Microsoft documentation, the Active Directory Service Principal authentication mode is specifically designed for applications that need to authenticate as a service principal, making it perfect for your ASP.NET Core Web API deployment.

Creating the Proper Connection String

Based on your credentials and the research findings, here’s the proper connection string format for your service principal:

Server=tcp:your-server.database.windows.net,1433;
Initial Catalog=your-database;
Authentication=Active Directory Service Principal;
User Id=your-client-id;
Password=your-client-secret;
Encrypt=True;
TrustServerCertificate=False;
Connection Timeout=30;

Key components explained:

• Server=tcp:your-server.database.windows.net,1433 - Your SQL Server endpoint
• Initial Catalog=your-database - Your database name
• Authentication=Active Directory Service Principal - Specifies service principal authentication
• User Id=your-client-id - Your application (client) ID from Entra registration
• Password=your-client-secret - Your service principal secret

From the Stack Overflow discussion, this format is confirmed to work with .NET applications, though you’ll need to ensure your service principal has the appropriate SQL Server permissions.

Configuring ASP.NET Core Web API

1. App Settings Configuration

Add your connection string to appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=tcp:your-server.database.windows.net,1433;Initial Catalog=your-database;Authentication=Active Directory Service Principal;User Id=your-client-id;Password=your-client-secret;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
  },
  "AzureAd": {
    "ClientId": "your-client-id",
    "ClientSecret": "your-client-secret",
    "TenantId": "your-tenant-id"
  }
}

2. DbContext Configuration

Configure your DbContext to use the connection string:

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) 
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Your entity configurations
    }
}

// In Startup.cs or Program.cs
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

3. Secure Configuration in Production

In production, never store secrets directly in configuration files. Instead:

Option A: Use Azure App Configuration

builder.Configuration.AddAzureAppConfiguration(options =>
{
    options.Connect(builder.Configuration["AppConfig:ConnectionString"])
           .ConfigureRefresh(refresh => 
           {
               refresh.Register("ConnectionStrings:DefaultConnection", refreshAll: true);
           });
});

Option B: Use Azure Key Vault
As mentioned in the research findings from WebNetHelper, you can access Azure SQL connection strings from Azure Key Vault:

builder.Configuration.AddAzureKeyVault(
    new Uri(builder.Configuration["KeyVault:Endpoint"]),
    new DefaultAzureCredential());

Security Best Practices

1. Never commit secrets to source control - Use Azure Key Vault or App Configuration for production secrets

2. Use managed identities in Azure - For Azure-hosted applications, consider using managed identity instead of service principals for better security:

   csharp

   // Connection string for managed identity
   Server=tcp:your-server.database.windows.net,1433;
   Initial Catalog=your-database;
   Authentication=Active Directory Managed Identity;
   Encrypt=True;
   TrustServerCertificate=False;
   Connection Timeout=30;
   

3. Implement proper credential rotation - Regularly rotate your service principal secrets

4. Use environment-specific configurations - Have different settings for development, staging, and production

5. Secure your DbContext - Ensure your DbContext is properly configured to use secure connection handling

Deployment Considerations

Azure App Service Deployment

When deploying to Azure App Service:

1. Configure App Settings in the Azure portal:

   • Add ConnectionStrings:DefaultConnection with your service principal connection string
   • Or use managed identity configuration

2. Set Environment Variables for sensitive data:

   • AzureAd:ClientId
   • AzureAd:ClientSecret
   • AzureAd:TenantId

3. Enable Managed Identity (recommended):

   powershell

   az webapp identity assign --resource-group your-resource-group --name your-app-name
   

As mentioned in the Microsoft Learn tutorial, this approach generates a passwordless connection string that your app can use.

Container Deployment

For containerized deployments:

1. Use Azure Key Vault to inject secrets into containers
2. Configure connection strings via environment variables
3. Implement health checks to verify database connectivity

Troubleshooting Common Issues

1. “Login failed for user” errors

This typically occurs when:

• Service principal doesn’t have SQL Server permissions
• Incorrect connection string format
• Expired or invalid credentials

Solution: Verify your service principal has been granted access to the SQL database:

CREATE USER [your-client-id] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [your-client-id];
ALTER ROLE db_datawriter ADD MEMBER [your-client-id];

2. “Cannot set the AccessToken property if ‘Authentication’ has been specified”

As seen in the Stack Overflow discussion, this error occurs when mixing authentication methods. Ensure you’re using either:

• Connection string with authentication parameters, OR
• Programmatic token setting without authentication in the connection string

3. Certificate validation issues

If you encounter SSL certificate errors, ensure:

• Encrypt=True is set
• TrustServerCertificate=False for production
• Proper certificate chain validation

4. Performance optimization

Consider these performance improvements:

• Use connection pooling
• Configure appropriate timeout values
• Implement retry policies for transient failures

Conclusion

To successfully implement service principal authentication for your ASP.NET Core Web API with SQL Server:

1. Use the correct connection string format with Authentication=Active Directory Service Principal
2. Properly configure your service principal with the necessary SQL Server permissions
3. Implement secure credential management using Azure Key Vault or managed identities
4. Configure your ASP.NET Core application to use the connection string properly
5. Test thoroughly in both development and production environments

For optimal security in Azure deployments, consider migrating to managed identity authentication as recommended in the Microsoft documentation, which eliminates the need to store secrets and provides better security posture for cloud-native applications.

Sources

1. Microsoft Learn - Connect to Azure SQL with Microsoft Entra authentication and SqlClient
2. Stack Overflow - How do I create connection string with App Registration/Service Principal for a Web API using EF Core app with SQL Server using Microsoft Entra MFA
3. Microsoft Learn - Securely connect .NET apps to Azure SQL Database using Managed Identity
4. Microsoft Learn - Deploy ASP.NET Core and Azure SQL Database app
5. WebNetHelper - Accessing Azure SQL Connection String from Azure Key Vault in ASP.NET Core API
6. Stack Overflow - What SQL Server connection string works with .NET Framework 4.5 in Microsoft Entra ID?