"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: 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 Default or Active Directory Interactive modes reliably. 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: Ins