1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3934763
  • 23
  • 7
  • Hello @user3934763, may I know what is the microsoft.data.sqlclient version that you are using ? as its supported for 2.0.0+ , you can refer this [doc](https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver15#using-active-directory-service-principal-authentication) – Ansuman Bal Oct 20 '21 at 09:44
  • can you please try using microsoft.data.sqlclient instead of System.data.sql client , you can install it from dotnet cli using command `dotnet add package Microsoft.Data.SqlClient --version 3.0.1` – Ansuman Bal Oct 20 '21 at 09:59
  • Hi @AnsumanBal-MT, 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?? – user3934763 Oct 20 '21 at 10:15
  • AFAIK,, system.data.sqlclient only supports for SQL auth or AAD user auth but not service principal or other authentication methods , so if you want to use service principal or others like device code flow or managed identity then you need to use microsoft.data.sqlclient – Ansuman Bal Oct 20 '21 at 10:23
  • Hi @AnsumanBal-MT, I have GACed and used Microsoft.Data.SqlClient instead of System.Data.SqlClient dll in script task. But now package is failing for error Unable to load DLL 'Microsoft.Data.SqlClient.SNI.x64.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E) Also not able to GAC this dll. Error Failure adding assembly to the cache: The module was expected to contain an assembly manifest. – user3934763 Oct 21 '21 at 10:13
  • hello @user3934763, have you installed the package `Microsoft.data.sqlclient`? – Ansuman Bal Oct 21 '21 at 10:16
  • PLease refer this : [MS QA thread](https://learn.microsoft.com/en-us/answers/questions/434478/unable-to-load-dll-39microsoftdatasqlclientsnix64d.html) – Ansuman Bal Oct 21 '21 at 10:25

1 Answers1

4

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

Ansuman Bal
  • 9,705
  • 2
  • 10
  • 27