I am trying to connect to an Azure SQL managed instance using an Azure service principal in SSIS. I have created a connection manager in SSIS using the Microsoft OLE DB Driver for SQL Server. In the connection manager I am connecting to the server using Active Directory - Service Principal as the authentication type. I am passing the service principal GUID as the user name and the password in the connection manager.
When I test this in Visual Studio I am able to make the connection and run the SSIS. The SSIS package transfers the data as expected without an issue. However, when I deploy the Azure SQL server and run the SSIS package in an Azure data factory pipeline I get an error.
Login failed for user (Service Principal GUID). SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Azure Service Principal" failed with error code 0xC0202009.
The service principal is set up as a user on the SQL managed instance and it has db owner permissions on the database. I checked the logs and the authentication is getting to the SQL managed instance, but it looks like it is trying to use SQL authentication rather than the Active Directory Service Principal authentication.
This is what the connection string looks like.
Data Source=(SQL Managed Instance);User ID=(Service Principle GUID);Initial Catalog=(My Database);Provider=MSOLEDBSQL.1;Persist Security Info=False;Auto Translate=False;Application Name=(SSIS Package name and GUID);Use Encryption for Data=True;Authentication=ActiveDirectoryServicePrincipal;
I checked the connection string in Visual Studio against the one being used in data factory and they are identical. I have tried using the service principal account name rather than the GUID and I get the same result. The SSIS package is very simple, it is connecting to one server and moving data to the managed instance.