• Azure SQL DB (Singleton, Elastic Pool & Managed Instance) don’t support Windows/Active Directory (AD) integrated authentication
  • But they do support Azure Active Directory (AAD) integrated authentication
  • Problem is AAD is a separate authentication mechanism as shown by it’s specific drop-down options when you configure the connection
  • 
• Unfortunately SSIS OLEDB connectors only support SQL & Windows/AD authentication
• Whilst ADO.NET connectors support SQL and AAD authentication
• The impact of this in terms of required code changes depends on 3 things (and I make no comment here about the potential performance impact of ADO.NET vs OLEDB):

1. What authentication mechanism you want to use i.e. SQL Server or integrated (AAD) authentication
2. What provider you’re currently using to connect to the SQL Server DBs that are being migrated to Azure SQL DB (OLEDB or ADO.NET)
3. How you manage connections within Packages – specifically whether you store connections at the Project (you’ll be glad you are) or Package level (dear God I hope not)

SO: If you want AAD integrated authentication you need to use ADO.NET connectors

Impact on existing ADO.NET conns:

• If you’re using Project level conns you can open them centrally and modify to use one of the AAD mechanisms
  • You should get away without having to re-open Data Flow sources/targets and remap columns
• If you’re not using Project level conns (apart from ask yourself why the hell not) you will need to open each Package conn and change to use AAD authentication
  • Now would be a good really time to promote Package conns to be Project conns

Impact on existing OLEDB Conns:

• These need to be replaced by ADO.NET conns if want to use AAD integrated authentication
• If OLEDB Project level conns being used these need to be replaced with ADO.NET conns
  • If using OLDB Package level conns now would be a really good time to promote to Project conns…
• Each Data Flow then needs to be edited to use the new Project level ADO.NET conns & the columns remapped