Azure Data Factory - Unable to connect to an Azure SQL Database using a connection string key vault secret. Unable to load adalsql.dll (Authentication=ActiveDirectoryPassword). Error code: 0x2.
In Azure Data Factory, I've created a linked service definition for an Azure SQL Database. I've chosen to use a key vault secret value as the connection string, and the connection string looks like this:
Server=tcp:xxxxx.public.8ee29e8c8f43.database.windows.net,3342;Persist Security Info=False;Initial Catalog=Database01;User ID=<uid>;Password=<pwd>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication=ActiveDirectoryServicePrincipal;
So the User ID and Password values actually represent a service principal. But when I test the connection, I get this error:
Unable to load adalsql.dll (Authentication=ActiveDirectoryPassword). Error code: 0x2.
So it appears the linked service component is not properly set up to find that DLL for Active Directory based authentication. But that also confuses me because the Authentication type is actually ActiveDirectoryServicePrincipal.
If I enter the connection string manually and specify Service Principal as the authentication type, then enter the service principal ID and key, the connection test works.
I would very much prefer to use the key vault value as the connection string. Am I doing something wrong, or is this a bug on the ADF side?