1

From last few days I am looking for solution- I need to connect to Azure SQL MI server using service principal authentication through SSIS script task - C# code. I am using Microsoft.Data.SqlClient.dll v5.0.0, .net framework (by default) v4.7, Visual studio 2019, targetserverversion 2019. I have registered this dll to GAC. Also, below dependent dlls I have copied to below path and referencing it in code from there.

Microsoft.IdentityModel.Abstractions.6.21.0
Microsoft.Identity.Client.4.45.0
Microsoft.Identity.Client.Extensions.Msal.2.19.3
Microsoft.IdentityModel.Logging.6.21.0
Microsoft.IdentityModel.Tokens.6.21.0
Microsoft.IdentityModel.JsonWebTokens.6.21.0
Microsoft.IdentityModel.Protocols.6.21.0
System.Buffers.4.5.1
System.IdentityModel.Tokens.Jwt.6.21.0
Microsoft.IdentityModel.Protocols.OpenIdConnect.6.21.0
System.IO.4.3.0
System.Numerics.Vectors.4.5.0
System.Runtime.4.3.0
System.Runtime.CompilerServices.Unsafe.4.7.1
System.Memory.4.5.4
System.Diagnostics.DiagnosticSource.4.6.0
System.Runtime.InteropServices.RuntimeInformation.4.3.0
System.Security.Cryptography.Encoding.4.3.0
System.Security.Cryptography.Primitives.4.3.0
System.Security.Cryptography.Algorithms.4.3.1
System.Security.Cryptography.ProtectedData.4.7.0
System.Security.Cryptography.X509Certificates.4.3.0
System.Net.Http.4.3.4
System.Security.Principal.Windows.5.0.0
System.Security.AccessControl.5.0.0
System.Security.Permissions.5.0.0
System.Configuration.ConfigurationManager.5.0.0
System.Text.Encodings.Web.4.7.2
System.Threading.Tasks.Extensions.4.5.4
Microsoft.Bcl.AsyncInterfaces.1.1.1
System.ValueTuple.4.5.0
System.Text.Json.4.7.2
System.Memory.Data.1.0.2
Azure.Core.1.24.0
Azure.Identity.1.6.0
Microsoft.Data.SqlClient.5.0.0

But still I am getting below error (which seems something related to dll?)- Error: Object reference not set to an instance of an object.

Same C# code is working for service principal authentication method using in dotnet project. So there is no any ant code error. But when it comes to SSIS script task, it seems issue with dll installation.

Let me know if anyone has done this before and how or any suggestions. Thanks.

user18963348
  • 11
  • 2
  • Can you share C# code of the task? At what line it throws the error? Is there any logged information for the SSIS task? – BorisR Aug 27 '22 at 13:58
  • Did you publish application and install using setup.exe that is created by publish? c# code will not run on a deploy machine unless the version of windows and net are exactly the same in build and deploy machine. Setup.exe installed windows dlls so the compiled code will run on a deploy machine. – jdweng Aug 27 '22 at 14:39
  • @BorisR There's no issue with code.Same code works if I use SQL server Authentication (System.Data.Sqlclient.dll) in SSIS-Script task. It's something wrong with dll reference/namespaces used in SSIS-Script task. Even same code works with Service principal authentication on dotnet project. I want to know whether Service principal authentication is supported by SSIS-Script Task - C# in Visual studio 2019. If yes, what's exact process to add supported dll (Microsoft.Data.Sqlclient) in SSIS-Script task (VS2019). – user18963348 Aug 27 '22 at 18:25
  • @jdweng This is about SSIS - Script task (Visual studio 2019) where I am using C# code. – user18963348 Aug 27 '22 at 18:30
  • All your libraries are Net and your code target is Net 4.7. And You are using Visual Studio. The same applies for c# and Net Library. – jdweng Aug 28 '22 at 00:45

0 Answers0