"Everything was working fine. Then someone added an Azure database connection string. And suddenly, nothing was fine."
— Every senior developer on a legacy project, at least once
🧭 Overview
I have been working on enterprise .NET applications for over 11+ years now. Most of them are what people politely call "legacy systems" — Web Forms,API's, IIS deployments, App.config files, the works. These applications run without issues for years. On-prem SQL Server connections are rock solid. Everyone is happy.
Then one day, your team decides to add a cloud database — Azure SQL — because the client wants to "move to the cloud" or some new feature needs it. You add a new connection string, you test locally, it kind of works, and then you deploy to IIS.
That is when the fun begins.
This article is about exactly that situation — what went wrong in one of our production systems, why it went wrong, and how we fixed it properly. If you are working on a legacy .NET app and adding Azure SQL connections, please read this fully before you touch anything.
😤 The Real Problem
Let me describe what happened on our project.
We had a large ASP.NET Web API is running on IIS. It was talking to an on-prem SQL Server using System.Data.SqlClient. No issues. Hundreds of stored procedures, millions of rows, everything running smoothly for years.
Then we added a new module that needed to connect to an Azure SQL Database using Azure Active Directory (AAD) authentication. The connection string looked something like this:
<add name="AzureDB"
connectionString="Server=tcp:myserver.database.windows.net,1433;
Initial Catalog=MyDatabase;
Authentication=Active Directory Default;
Encrypt=True;
TrustServerCertificate=False;"
providerName="System.Data.SqlClient" />
Locally on dev machines — it worked. Sometimes. On IIS in staging — error. In production — different error. The errors we saw were:
SSQL: A connection was successfully established with the server,
but then an error occurred during the login process.
(provider: SSL Provider, error: 0 - The certificate chain was issued
by an authority that is not trusted.)
System.Data.SqlClient.SqlException:
Failed to authenticate the user in Active Directory (Authentication=ActiveDirectoryDefault).
Our on-prem connections were still working perfectly. Only the Azure connection was failing. And every time we tried to fix one error, another one showed up.
🔍 Root Cause — The Actual Diagnosis
After a lot of head-scratching and reading through event logs, we figured out the real problem.
System.Data.SqlClient is the old provider. It is the one that ships with the .NET Framework itself. Microsoft stopped actively developing it for new features years ago.
Specifically:
It does not support AAD authentication properly in older versions.
It has SSL/TLS handling issues with Azure SQL, especially around certificate validation.
It does not support the newer
Authentication=Active Directory DefaultorActive Directory Interactive modesreliably.
Microsoft.Data.SqlClient is the new, actively maintained NuGet package from Microsoft. It:
Supports all AAD authentication modes.
Has proper TLS 1.2/1.3 support for Azure connections.
Works correctly in both .NET Framework and .NET Core/.NET 5+.
Is the officially recommended replacement.
The root cause was simple: we were trying to use a 2005-era database driver to connect to a 2023-era cloud authentication system. It was never going to work reliably.
⚠️ The Partial Migration Trap
WARNING: This is the most dangerous part. Please read carefully.
After reading about Microsoft.Data.SqlClient, many developers do what seems logical — they install the NuGet package and start updating files one by one. Some files get the new using Microsoft.Data.SqlClient;and some still have using System.Data.SqlClient;
This causes a whole new category of problems:
System.InvalidCastException:
Unable to cast object of type 'Microsoft.Data.SqlClient.SqlConnection'
to type 'System.Data.SqlClient.SqlConnection'.
System.ArgumentException:
DbCommand is of an invalid type for this DbConnection.
These happen because objects from the two namespaces look identical but are completely different types. Passing a Microsoft.Data.SqlClient.SqlConnection to a method that expects System.Data.SqlClient.SqlConnection will throw a cast exception at runtime — not at compile time. It will pass your unit tests and explode in production.
You cannot mix these two providers in the same application unless you are extremely careful about isolation. The safest approach — and what we did — was to migrate fully.
✅ The Solution — Step by Step
Here is exactly what we did, in order.
Step 1 — Install the NuGet Package
Open your Package Manager Console or right-click the project in Visual Studio:
Install-Package Microsoft.Data.SqlClient
Or via CLI:
dotnet add package Microsoft.Data.SqlClient
At the time of our migration, we used version 5.x. Use the latest stable version. This package works fine with .NET Framework 4.6.1 and above, as well as .NET 6/7/8.
Step 2 — Update All C# Files (The usingStatement)
This is the tedious part but you cannot skip it. Go through every single C# file that uses SQL:
Before:
using System.Data.SqlClient;
public class UserRepository
{
public DataTable GetUsers(string connString)
{
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand("SELECT * FROM Users", conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
}
}
After:
using Microsoft.Data.SqlClient; // ← Only this line changes
public class UserRepository
{
public DataTable GetUsers(string connString)
{
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand("SELECT * FROM Users", conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
}
}
The class names — SqlConnection, SqlCommand, SqlDataAdapter, SqlDataReader, SqlParameter, SqlException— all remain exactly the same. Only the namespace changes. This makes the migration much smoother.
Pro Tip: Use Visual Studio's Find & Replace across the entire solution:
Find:
using System.Data.SqlClient;Replace:
using Microsoft.Data.SqlClient;Scope: Entire Solution
Then do a full build and fix any remaining compilation errors one by one.
Step 3 — Update Web.config — DbProviderFactories
This step is critical for IIS deployments and the one most people miss.
If your application uses DbProviderFactory (which many enterprise apps do for abstraction), you need to register the new provider in Web.config under system.data :
<system.data>
<DbProviderFactories>
<!-- Remove or comment out the old one -->
<!--
<remove invariant="System.Data.SqlClient" />
<add name="SqlClient Data Provider"
invariant="System.Data.SqlClient"
description=".Net Framework Data Provider for SqlServer"
type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
-->
<!-- Add the new Microsoft.Data.SqlClient provider -->
<remove invariant="Microsoft.Data.SqlClient" />
<add name="SqlClient Data Provider"
invariant="Microsoft.Data.SqlClient"
description=".NET Framework Data Provider for SQL Server"
type="Microsoft.Data.SqlClient.SqlClientFactory, Microsoft.Data.SqlClient" />
</DbProviderFactories>
</system.data>
If you skip this step, IIS will throw:
Unable to find the requested .Net Framework Data Provider.
It may not be installed.
Even if your code compiles perfectly and runs on localhost, IIS uses these registered factories to resolve providers at runtime.
Step 4 — Update Connection Strings in Web.config
Update the providerName in your connection strings from the old value to the new one:
Before:
<connectionStrings>
<add name="OnPremDB"
connectionString="Server=MYSERVER;Database=MyDB;Integrated Security=True;"
providerName="System.Data.SqlClient" />
<add name="AzureDB"
connectionString="Server=tcp:myserver.database.windows.net,1433;
Initial Catalog=MyDatabase;
Authentication=Active Directory Default;
Encrypt=True;
TrustServerCertificate=False;"
providerName="System.Data.SqlClient" />
</connectionStrings>
After:
<connectionStrings>
<add name="OnPremDB"
connectionString="Server=MYSERVER;Database=MyDB;Integrated Security=True;"
providerName="Microsoft.Data.SqlClient" />
<add name="AzureDB"
connectionString="Server=tcp:myserver.database.windows.net,1433;
Initial Catalog=MyDatabase;
Authentication=Active Directory Default;
Encrypt=True;
TrustServerCertificate=False;"
providerName="Microsoft.Data.SqlClient" />
</connectionStrings>
After:
<connectionStrings>
<add name="OnPremDB"
connectionString="Server=MYSERVER;Database=MyDB;Integrated Security=True;"
providerName="Microsoft.Data.SqlClient" />
<add name="AzureDB"
connectionString="Server=tcp:myserver.database.windows.net,1433;
Initial Catalog=MyDatabase;
Authentication=Active Directory Default;
Encrypt=True;
TrustServerCertificate=False;"
providerName="Microsoft.Data.SqlClient" />
</connectionStrings>
Step 5 — Handle the Encrypt=True Change (Breaking Change Alert!)
Starting from Microsoft.Data.SqlClient version 4.0, Encrypt=True is the default. This is a breaking change from the old driver where Encrypt=False was the default.
If your on-prem SQL Server does not have a properly configured SSL certificate, you will suddenly get SSL errors on previously working connections after migration.
Fix this by being explicit in your on-prem connection strings:
<!-- For On-Prem SQL Server without SSL certificate -->
<add name="OnPremDB"
connectionString="Server=MYSERVER;
Database=MyDB;
Integrated Security=True;
Encrypt=False;"
providerName="Microsoft.Data.SqlClient" />
<!-- For Azure SQL — always use Encrypt=True -->
<add name="AzureDB"
connectionString="Server=tcp:myserver.database.windows.net,1433;
Initial Catalog=MyDatabase;
Authentication=Active Directory Default;
Encrypt=True;
TrustServerCertificate=False;"
providerName="Microsoft.Data.SqlClient" />
🏢 Special Case — On-Prem + Cloud Running Together
This is our exact scenario, and I want to give it its own section because it has some nuances.
When you have both on-prem SQL Server and Azure SQL in the same application, you need to be careful about:
1. Authentication Mode Differences
On-prem typically uses Windows Authentication (Integrated Security) or SQL Authentication. Azure SQL uses AAD-based authentication or SQL Authentication. These are completely different mechanisms, and Microsoft.Data.SqlClient handles both cleanly — you just need the right connection string.
<!-- On-Prem: Windows Auth -->
<add name="OnPremDB"
connectionString="Server=SQLPROD01;
Database=CoreDB;
Integrated Security=True;
Encrypt=False;"
providerName="Microsoft.Data.SqlClient" />
<!-- Azure: AAD Managed Identity -->
<add name="AzureDB"
connectionString="Server=tcp:prodserver.database.windows.net,1433;
Initial Catalog=CloudDB;
Authentication=Active Directory Managed Identity;
Encrypt=True;"
providerName="Microsoft.Data.SqlClient" />
<!-- Azure: AAD Username/Password (for dev/testing) -->
<add name="AzureDevDB"
connectionString="Server=tcp:devserver.database.windows.net,1433;
Initial Catalog=DevDB;
Authentication=Active Directory Password;
User ID=myuser@mycompany.com;
Password=MyPassword;
Encrypt=True;"
providerName="Microsoft.Data.SqlClient" />
2. IIS Application Pool Identity Matters for AAD
When you use Authentication=Active Directory Default or Authentication=Active Directory Managed Identity on IIS, the Application Pool Identity must have the right permissions assigned in Azure.
If you are using a Managed Identity, make sure:
- Your App Service or VM has a System Assigned Managed Identity enabled.
2.That identity is added to the Azure SQL database as a user:
-- Run this in your Azure SQL database
CREATE USER [your-app-name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [your-app-name];
ALTER ROLE db_datawriter ADD MEMBER [your-app-name];
If the app pool is running under a service account and you are using Active Directory Integrated, that service account must be synced to Azure AD.
3. Firewall and Network Rules
On-prem SQL Server connections go through your internal network. Azure SQL connections go out through the internet (or Express Route / VPN). Make sure your IIS server's outbound IP is whitelisted in the Azure SQL firewall rules.
❌ Common Mistakes We Made (So You Don't Have To)
Mistake 1 — Partial Migration
Already covered above, but worth repeating: if you update half the files and leave the rest, you will get casting exceptions at runtime. Do the complete migration in one go.
Mistake 2 — Forgetting the DbProviderFactories Section
Every time we forgot to update Web.config DbProviderFactories, we got:
Unable to find the requested .Net Framework Data Provider.
This happens only on IIS, not on local dev. Very frustrating.
Mistake 3 — Not Setting Encrypt Explicitly
After upgrading to v4+, our on-prem connections started failing with SSL certificate errors because Encrypt=True became the default. We had to explicitly add Encrypt=False for on-prem connections where SSL was not configured.
Mistake 4 — Using TrustServerCertificate=True as a Shortcut
Yes, this silences the SSL error. But don't do it for Azure connections in production. It makes your connection vulnerable to man-in-the-middle attacks. Fix the actual certificate issue properly.
<!-- ❌ Don't do this for Azure production connections -->
TrustServerCertificate=True
<!-- ✅ Do this instead — use proper certificate validation -->
Encrypt=True;TrustServerCertificate=False
Mistake 5 — Not Restarting the IIS Application Pool
After deploying the updated DLLs and Web.config, always recycle the Application Pool in IIS. The old provider might be cached in the worker process.
iisreset /noforce
Or from IIS Manager → Application Pools → Recycle.
Mistake 6 — Ignoring the Event Viewer
Most AAD and SSL errors on IIS show up in Windows Event Viewer under Application Logs — not just in your application logs. Always check:
Event Viewer → Windows Logs → Application
Event Viewer → Windows Logs → System
🎉 Final Result
After the complete migration:
✅ On-prem SQL Server connections — still working, no change in behavior.
✅ Azure SQL connection — connected successfully using AAD authentication.
✅ SSL/TLS errors — gone completely. Microsoft.Data.SqlClient handles TLS 1.2 properly.
✅ IIS deployment — stable. No more "Data Provider not found" errors.
✅ No more mixed namespace issues — clean codebase with a single provider.
Here is a quick sanity check you can run after migration to make sure both connections are working:
using Microsoft.Data.SqlClient;
public class ConnectionTester
{
public static bool TestConnection(string connectionString)
{
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
Console.WriteLine($"Connected to: {conn.DataSource} | Database: {conn.Database}");
return true;
}
}
catch (SqlException ex)
{
Console.WriteLine($"Connection failed: {ex.Message}");
return false;
}
}
}
🏁 Conclusion
If your legacy .NET application is running on IIS and you are adding Azure SQL connections, do not try to use System.Data.SqlClient for it. That provider was not built for AAD authentication or modern Azure SSL requirements.
The migration from System.Data.SqlClient to Microsoft.Data.SqlClient is actually not that complicated — the class names are the same, the APIs are the same, only the namespace changes. The tricky parts are:
Doing it completely, not partially.
Updating the Web.config DbProviderFactories section (this one bites everyone on IIS).
Being explicit about Encrypt= in all your connection strings.
Understanding that on-prem and Azure connections need different settings even when using the same provider.
📝 Short Summary
Problem: Legacy IIS app using System.Data.SqlClient started failing with AAD and SSL errors after adding Azure SQL connections.
Reason: System.Data.SqlClient doesn't properly support AAD authentication modes or modern TLS requirements for Azure SQL.
Fix: Full migration to Microsoft.Data.SqlClient NuGet package — update usingstatements across all C# files, register the new provider in Web.configDbProviderFactories, update connection string providerName, and be explicit about Encryptsettings per connection.
💡 Practical Advice from Experience
Plan the migration as a single Sprint task, not a side change. Partial migration causes more problems than the original bug.
Test on IIS staging first — many of these errors don't show up on your local IIS Express setup.
Keep one environment running the old code until you verify the new deployment is stable. Don't cut over both environments at the same time.
For AAD connections, always test with the exact IIS App Pool identity — not your personal Windows account. What works for you locally might not work for the service account.
Read the Microsoft.Data.SqlClient release notes before upgrading major versions. The team has been making breaking changes between version 3, 4, and 5. Encrypt default change in v4 is the most common surprise.
If this article helped you, please share it with your team. Legacy app migrations don't have to be painful if you know exactly what to change and in what order.
Happy shipping! 🚀
Top comments (0)