SSIS 2025 with no password in the package: Entra ID, TLS 1.3 and Fabric with Microsoft SqlClient

The problem: the password that lives in the connection manager

In many companies, SSIS is still the ETL engine: stable, fast and paid for years ago. But the way it connects rarely evolved along with it. It's common to find packages with:

• a SQL login with the password stored in the connection manager (with ProtectionLevel protecting it… more or less);
• OLE DB or old SqlClient connections, with no decent Entra ID support;
• no official path to talk to Microsoft Fabric, which does not accept SQL or Windows authentication.

In practice, this becomes a deadlock: governance asks for a managed identity and strong encryption, and the legacy package only knows a username and password.

What's new: Microsoft SqlClient in the ADO.NET connection manager

SSIS 2025 (SQL Server 2025, GA in November 2025) brought as its main novelty support for the Microsoft SqlClient Data Provider (Microsoft.Data.SqlClient) in the ADO.NET connection manager.

This is Microsoft's modern driver for SQL Server and Azure SQL. With it, SSIS gains:

• Microsoft Entra ID in several modes: Service Principal, Managed Identity, Interactive, Default, among others;
• TLS 1.3 and the Encrypt=Strict mode (TDS 8.0), in which the session is encrypted from the first byte, even before the login handshake;
• connection to Fabric Warehouse, the SQL analytics endpoint and SQL database in Fabric, which only accept Entra ID.

Step by step

1) Create the connection manager with the new provider

In Visual Studio (SSIS Projects 2022+), create an ADO.NET connection manager and, under Provider, choose Microsoft SqlClient Data Provider.

2) Configure authentication on the All tab

The Connection tab shows only a few authentication options. The rest (Service Principal, Managed Identity, Interactive) live on the All tab, in the Authentication property. The resulting connection string looks like this:

Data Source=<workspace>.datawarehouse.fabric.microsoft.com;
Initial Catalog=dw_sales;
Authentication=Active Directory Service Principal;
User ID=<app-client-id>;
Password=<client-secret>;
Encrypt=Strict;

For Azure SQL Database or SQL Server 2025 with Entra ID, the format is the same, just changing the Data Source.

3) Take the secret out of the package

The service principal's Password should not live in the package. The pattern I recommend:

1. Create a sensitive project parameter (SpnSecret) and map it to the connection manager's Password property (or to the whole connection string).
2. In SSISDB, create an environment per environment (dev/test/prod) with the corresponding sensitive variable.
3. Reference the environment in the project and run the jobs pointing to it.

That way the same .ispac is deployed to all environments, and the secret stays encrypted in the catalog (and, ideally, rotated from Key Vault by your deployment process).

4) Develop with your identity, run with the service's

Active Directory Interactive is great in Visual Studio: you sign in with your account and test. But it doesn't work running in the catalog, because there's no one to click the pop-up. Use the parameter/environment to swap the connection string at deploy time to Service Principal.

Running on the Azure-SSIS Integration Runtime (ADF), you can go further and use Managed Identity, with no secret at all.

When this makes a difference

• Gradual migration to Fabric: on-premises SSIS can write directly to Fabric Warehouse while you migrate the rest at your own pace, without rewriting the package.
• Auditing and compliance: service-principal connections appear with their own identity in the logs, with minimal permissions and centralized credential rotation.
• The end of the shared password: nobody needs to know the etl_user account's password anymore.

Upgrade checklist for SSIS 2025

Before migrating, it's worth doing this inventory (source: Microsoft's official documentation):

• ☐ 32-bit mode is deprecated. SSMS 21 and SSIS Projects 2022+ are 64-bit. Packages that depend on 32-bit drivers (old Excel/Access, 32-bit ODBC) need attention.
• ☐ The legacy Integration Services service is deprecated. It affects anyone still using the package deployment model (packages in msdb/file system). Time to move to the project deployment model + SSISDB.
• ☐ The SDS (SqlClient Data Provider) connection type is deprecated in the Maintenance Tasks and the Foreach Loop. Migrate to ADO.NET.
• ☐ Attunity's CDC components and the CDC Service for Oracle were removed, as was the Microsoft Connector for Oracle. Plan alternatives (ADF, Fabric Mirroring/Copy Job, third-party connectors).
• ☐ Hadoop tasks (Hive, Pig, File System) were dropped from the product.
• ☐ Anyone using the .NET API Microsoft.SqlServer.Dts.Runtime to generate packages needs to update references and recompile.

Conclusion

SSIS 2025 isn't a revolution, but it solves an old pain: connecting legacy packages with a modern identity and strong encryption. If you keep SSIS in production, the first step is simple: list the connection managers with a SQL login and start with the ones that talk to Azure SQL or Fabric.