Issues deploying SSIS package to SQL Server in Azure AD Domain Services (AADDS) environment

active-directoryazuressis

We use Azure Active Directory (Azure AD) in my organisation, and we recently deployed Azure AD Domain Services AADDS) to handle a SQL Server 2019 we use internally.

My Azure AD domain is (fictitiously) contoso.com, while my AADDS is aaddscontoso.com. Microsoft strongly recommends that users do not create their managed domain (the AADDS deployment) with the same domain name as their Azure deployment. They also strongly recommend against using something like contoso.local. So of I went to create our AADDS as aaddscontoso.com and everything seems to be working perfectly.

The Azure AD works with AADDS, (at least with the Vanilla way we have set-up) is:

  • All of all laptops, desktops, etc are joined to the Azure AD through the Azure AD join (the cloud method)
  • We can access the managed domain and its hots (including the SQL server) in our Azure Network via Azure VPN. For instance, right now I am VPN into our Azure network and I can remote desktop to the SQL Server.

Here is where the problem starts. Today I just noticed that trying to deploy an SSIS package from Visual Studio from my laptop (whilst connected on VPN) won't work.

I tried using Windows Authentication and the authentication fails with the following error:

The target principal name is incorrect. Cannot generate SSPI context.
(Microsoft SQL Server, Error: 0)

enter image description here

So I tried using the Active Directory Password Authentication option, then I got the following error:

A connection was successfully established with the server, but then an
error occurred during the login process. (provider: SSL Provider,
error: 0 – The certificate chain was issued by an authority that is
not trusted.) (Microsoft SQL Server, Error: -2146893019)

enter image description here

Then I tried to be clever and create a SQL Login with the right administrative rights and the connection worked. However it fails on the deployment of the package, saying that you cannot use a SQL account for this. At least this proves that the connection to the server is not the issue.

I THINK that this problem could be simplified as follows: I want to deploy an SSIS package from the PC in DOMAIN A to a SQL server found in DOMAIN B. How can I do that? (Granted that this is not really it, but SSIS seems to think it is).

Best Answer

My solution was to create a shortcut to the following command:

C:\Windows\System32\runas.exe /netonly /user:targetuser@aaddscontoso.com "C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE\devenv.exe"

  • Replace targetuser@aaddscontoso.com with the user name of your Azure Managed Domain (AADDS).
  • Make sure you are pointing to the path of the correct Visual Studio shell. In my case I have SSDT for Integration Services install on the shell of Visual Studio 2019 Enterprise.

If you want this on your start menu, you can open the start menu location on Windows Explorer at: C:\Users\YourLocalHostUser\AppData\Roaming\Microsoft\Windows\Start Menu\Programs

I created a shortcut just like this:

enter image description here

I might even create my own icon (some sort of mesh between VS icon and the Azure AADDS icon).

Now, the only caveat (not really) is that you must provide the password of the the target credentials at a command prompt upon launching the shortcut. I think it is a nice compromise, to be honest.

Alternatively, you could download Sysinternals ShellRunAs to have a nicer GUI at the start, and use it instead of the runas.exe. But not only would you still need to provide the password upon launching the shortcut, but the username as well!

enter image description here

Related Question