Active Directory Service Principal is giving Invalid value for key 'authentication'

Question

I am trying to execute a SSIS package where source is SQL Server and destination is Azure SQL with authentication "Active Directory Service Principal". ODBC driver is 17.8 and OLEDB driver is 18.5.

While executing the SSIS package, it fails for a script task with this error

Invalid value for key 'authentication'

Below connection string is used:

Server=tcp:servername.database.windows.net,1433;
    Initial Catalog=DBName;Persist Security Info=False;
    User ID=clientid;Password=secrets;
    Authentication="Active Directory Service Principal";
    MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;

Script task is using System.Data.SQLClient library and targetting the .NET Framework v4.7

Answer 1

SSIS package is using System.Data.SQLClient version 4.0.0, Is there any way we can use service principal authentication without using Microsoft.Data.SqlClient??

System.Data.SQlClient only supports SQL authentication or AAD User Account Authentication .

So, if you want to use Service Principal or other authentication Methods then you should use Microsoft.Data.SQLClient as it is supported by it from version 2.0.0+.

Command to install Microsoft.Data.SQLClient package from dotnet CLI:

dotnet add package Microsoft.Data.SqlClient --version 3.0.1

Then you can use the below :

string ConnectionString = @"Server=servername.database.windows.net; Authentication=Active Directory Service Principal; Database=testdb; User Id=AppId; Password=secret";

using (SqlConnection conn = new SqlConnection(ConnectionString)) {
    conn.Open();
}

Reference:

Using Azure Active Directory authentication with SqlClient - ADO.NET Provider for SQL Server | Microsoft Docs