Connect .NET app using System.Data.SqlClient and Entity Framework 6 to a SQL Database in Fabric?

[berkleyg]

Options to connect legacy .NET app using System.Data.SqlClient and Entity Framework 6 to a SQL Database in Fabric? 

We hope to avoid refactoring to upgrade to Microsoft.Data.Sqlclient is  EF Core 3.0+ (.NET Core/.NET 5+) or higher for Fabric.

We’ve tried https://learn.microsoft.com/en-us/ef/ef6/what-is-new/microsoft-ef6-sqlserver but still raises errors.

Internal reference: ITK-261

Thanks,

Berkley

[dlevy]

Hi @berkleyg - I'm Dave from the SQL Drivers team.

Please migrate to Microsoft.Data.SqlClient. We know it is a lot of work to do that right now and we are working on ways to make it easy. I realize that doesn't help you today though. To get you unblocked for now by making SDS work with SQL database in Fabric you have to do a few extra steps:

1. In the Visual Studio Package Manager Console, add the NuGet package Azure.Identity and update Entity Framework.

Install-Package Azure.Identity
Update-Package EntityFramework

The token caching feature for Managed Identity is available starting from Azure.Identity version 1.8.0. To help reduce network port usage, consider updating Azure.Identity to this version or later.

2. Where you create your database connection you will need code similar to this.

Azure.Identity.DefaultAzureCredential credential;
var managedIdentityClientId = ConfigurationManager.AppSettings["ManagedIdentityClientId"];
if(managedIdentityClientId != null ) {
//User-assigned managed identity Client ID is passed in via ManagedIdentityClientId
var defaultCredentialOptions = new DefaultAzureCredentialOptions { ManagedIdentityClientId = managedIdentityClientId };
credential = new Azure.Identity.DefaultAzureCredential(defaultCredentialOptions);
}
else {
//System-assigned managed identity or logged-in identity of Visual Studio, Visual Studio Code, Azure CLI or Azure PowerShell
credential = new Azure.Identity.DefaultAzureCredential();
}
var conn = (System.Data.SqlClient.SqlConnection)Database.Connection;
var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default" }));
conn.AccessToken = token.Token;

The preceding code uses Azure.Identity.DefaultAzureCredential to get a usable token for SQL Database from a Microsoft Entra ID, and then adds it to the database connection. You can customize `DefaultAzureCredential`, but it's already versatile by default. When `DefaultAzureCredential` runs in App Service, it uses the app's system-assigned managed identity by default.

If you prefer to use a user-assigned managed identity, add a new app setting named `ManagedIdentityClientId` and enter the `Client Id` GUID from your user-assigned managed identity in the `value` field. When the code runs locally, it can get a token using the logged-in identity of Visual Studio, Visual Studio Code, Azure CLI, or Azure PowerShell.

You now have everything you need to connect to SQL Database when you debug in Visual Studio. Your code uses the Microsoft Entra user you configured when you set up your dev environment. You can set up SQL Database later to allow connection from the managed identity of your App Service app.

One thing to be aware of is that while Azure.Identity.DefaultAzureCredential is the most convenient for development because auth just works wherever you run it, it is also the slowest because it tries all of the auth types in order until it finds one that works. If your app creates a lot of connections you will be better off using the specific auth type you need.

It has been about a year since I last ran this code - I pulled it from an archived learn doc. When I wrote this code, it was against a SQL database in Fabric though and it worked nicely. I'll keep an eye out for replies in case you run into trouble.

[berkleyg]

Thanks, Dave.  We'll review the very helpful info you provided and update after that review.

Thanks,

Berkley

[berkleyg]

Dave, a follow up question for you is below...

"...question about the Microsoft recommendation: using the DefaultAzureCredential, what should I configure for the connection for the authentication? is ‘Active Directory Integrated’ correct? since I am passing an Entra id and password for the connection.

for example my connection string will be: <add name="*******" connectionString="Data Source=*******.database.fabric.microsoft.com,1433;Initial Catalog=*******;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False;Authentication=Active Directory Integrated" providerName="System.Data.SqlClient" />..."

Thanks,

Berkley

[dlevy]

I wouldn't use Entra Id and Password, use Service Principal Authentication instead using ClientSecretCredential. If you are running in Azure, System Assigned Managed Identity is a good choice too.

DefaultAzureCredential Class (Azure.Identity) - Azure for .NET Developers | Microsoft Learn

ClientSecretCredential Class (Azure.Identity) - Azure for .NET Developers | Microsoft Learn

[berkleyg]

Thanks, Dave we'll follow up on that option and update.

[mengxie1848]

I tried the option to use service principal for the Azure authentictaion with System.Data.SQLClient provider, I got conflict error. The suggestion is for the connection string, do not put any authentication key word in, so my connection string is: <add name="LocalSqlServer" connectionString="Data Source=<......>;Initial Catalog=<......>;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False" providerName="System.Data.SqlClient" />

My connection code is using ClientSecretCredential as following: 

private static DbConnection CreateAzureSqlConnection()
{
var connectionString = System.Configuration.ConfigurationManager
.ConnectionStrings["LocalSqlServer"].ConnectionString;

var conn = new SqlConnection(connectionString);

// Read service principal details from config
var clientId = ConfigurationManager.AppSettings["AzureClientId"];
var clientSecret = ConfigurationManager.AppSettings["AzureClientSecret"];
var tenantId = ConfigurationManager.AppSettings["AzureTenantId"];

// Use ClientSecretCredential for service principal authentication
var credential = new Azure.Identity.ClientSecretCredential(
tenantId,
clientId,
clientSecret
);

var token = credential.GetToken(
new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default" })
);
conn.AccessToken = token.Token;

return conn;
}

I got error: System.Data.SqlClient.SqlException
HResult=0x80131904
Message=Login failed for user ''.
Source=.Net SqlClient Data Provider

The Visual studio 2022 GitHub Copilot suggest the SDS provider default windows or sql authentication, don't understand the connection without the authetication keyword. So I added the authentication keyword back to the connection string as: <add name="LocalSqlServer" connectionString="Data Source=<......>;Initial Catalog=<......>;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False;Authentication=Active Directory Service Principal" providerName="System.Data.SqlClient" />
now I got error: The exception "Invalid value for key 'authentication'" occurs when trying to open a SQL connection in your AlertSelectByAppType method. This is not a SQL error, but a .NET error related to the connection string configuration.

Is there anything not quite right in my connection code that make the system understand that I am trying to use Azure authentication.

[dlevy]

Hi @mengxie1848 - SDS (System.Data.SqlClient) doesn't know about Active Directory Service Principal authentication. It's too old for that. You can drop that from your connection string.

I'd recommend against posting full connection strings here since it is publicly visible. If you are able to edit your message it would be good to do some masking on your connection string.

The first error message suggests that you are not getting a token back. Have you been able to debug and see the token?

[dlevy]

@mengxie1848 - This is what my connection string looks like:

Data Source=<Data Source>;Database=<Initial Catalog>;MultipleActiveResultSets=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False;ConnectRetryCount=6;ConnectRetryInterval=10;

[v-dineshya]

Hi @berkleyg ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @dlevy , Thank you for your prompt response.

Hi @berkleyg ,

To connect a legacy .NET Framework app using System.Data.SqlClient and Entity Framework 6 to a SQL Database in Microsoft Fabric, while avoiding a full migration to Microsoft.Data.SqlClient or EF Core, Microsoft provides a workaround using token-based authentication via Azure.Identity.DefaultAzureCredential.

You can continue using System.Data.SqlClient with EF6 by injecting an access token into the connection, obtained via DefaultAzureCredential. This allows you to authenticate securely with Microsoft Entra ID (formerly Azure AD).

Please try below steps.

1. Install Required NuGet Packages.

Install-Package Azure.Identity

Update-Package EntityFramework

2.  Configure Token-Based Authentication in Code

var managedIdentityClientId = ConfigurationManager.AppSettings["ManagedIdentityClientId"];
var credential = managedIdentityClientId != null
? new DefaultAzureCredential(new DefaultAzureCredentialOptions { ManagedIdentityClientId = managedIdentityClientId })
: new DefaultAzureCredential();

var token = credential.GetToken(new TokenRequestContext(new[] { "https://database.windows.net/.default" }));
var conn = (System.Data.SqlClient.SqlConnection)Database.Connection;
conn.AccessToken = token.Token;

3.  Use a minimal connection string without specifying authentication mode:

<add name="MyDbContext"
connectionString="Data Source=yourserver.database.fabric.microsoft.com,1433;Initial Catalog=yourdb;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False;"
providerName="System.Data.SqlClient" />

Note: Do not use Authentication=Active Directory Integrated in the connection string when using AccessToken. That mode is for Windows Integrated Authentication and conflicts with token-based auth.

Microsoft Fabric supports multiple Microsoft Entra authentication modes. For legacy apps using token injection, the recommended mode is Active Directory Managed Identity or Active Directory Default (via DefaultAzureCredential). Avoid Active Directory Integrated unless you are using Windows domain-joined machines with federated identity.

Avoid DefaultAzureCredential in production unless you are confident in the environment. It tries multiple credential sources and may fall back to unintended ones (e.g. Azure CLI). Instead, use below thing.

var credential = new ManagedIdentityCredential("<client-id>");

Upgrade to Microsoft.Data.SqlClient with EF6, If you are open to partial migration, Microsoft now supports EF6 with Microsoft.Data.SqlClient via a new provider, Add the NuGet package: Microsoft.EntityFramework.SqlServer. Update your DbConfiguration or App.config to use the new provider. This allows full support for modern SQL features and Entra authentication modes.

Please refer below microsoft official documents.

Connect to Azure SQL with Microsoft Entra authentication and SqlClient - ADO.NET Provider for SQL Se...

Microsoft Entra Authentication in Fabric Data Warehouse - Microsoft Fabric | Microsoft Learn

Authentication best practices with the Azure Identity library for .NET - .NET | Microsoft Learn

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[berkleyg]

Thanks, Dinesh.  We'll follow up on the options and recommendations you provided and update.

[v-dineshya]

Hi @berkleyg ,

Thank you for the respose. As you mentioned in your previous response, you are working on the suggested workarounds. Please provide ETA  ( Estimated time for arrival).  Please do let us know if you have any further queries.

Regards,

Dinesh

[berkleyg · Accepted solution]

Update

• "...the [legacy .NET app] is able to connect to Fabric using the existing System.Data.SQLClient data provider with added Azure Core and Azure Identity package and updated Entity Frame work 6.5 to use Azure service principal authentication, the call to [SQL] database in Fabric from [legacy .NET app] is successful now...."

Thanks Dave and Dinesh for all your quick help,

Berkley

[dlevy]

❤️❤️❤️

[v-dineshya]

Hi @berkleyg ,

Thanks for the update. We are happy to hear that you have resolved the issue. Thanks for sharing the details here. Please do let us know if you have any further queries.

Regards,

Dinesh