How to deploy SSIS packages using SSDT from an untrusted domain

deploy SSIS packages

Introduction

To deploy SSIS packages from SSDT to SSISDB, using Integration Services Deployment Wizard is one of the simplest and interactive ways. All you have to do is to right-click the package and click on Deploy, go through a few menus and you are done.

Problem

Unfortunately, SSIS is too bound to an Active Directory and would only utilize Windows authentication to understand “who can do what”. If both machines (source/deployed from and target/deployed to) are on the same domain or have domain trust/forest, you are in luck, but what if they are not? Additionally, if your development machine is running Windows 10 Home or iMac, you might not even have an option of the domain.

Additionally, we are in a public cloud era, so your company can have private and public cloud domains that are not connected (trusted). That makes the deployment from one machine to another quite a pain.

Solution

What if you could type the AD credentials of the machine you are trying to deploy to when firing up SSDT on your development machine? In that case, Integration Services Deployment Wizard will see the proper credentials and everything will work like it was on the same domain.

Sysinternals comes to rescue – https://docs.microsoft.com/en-us/sysinternals/downloads/shellrunas. ShellRunAs allows you to specify a different username/password when opening a new application. It is very similar to RunAs Administrator except that you can type a new username and password. Once SSDT gets the credentials of the target machine, everything works like a charm.

ShellRunas.jpg

Thanks go to Randy Knight for showing me that trick to deploy SSIS packages! I wish Microsoft would incorporate that handy tool into SSDT to make deployment more consistent.


If you still want to take a step back and create a new SSIS package, we recommend taking a look at the guide prepared by SentryOne. You can find information on how to create an SSIS project to add a data flow task.

Please share this

This Post Has 2 Comments

  1. Grubl3

    This is not a solution. You cannot run a program as a different user that resides at a different domain. It doesn’t work like that. The host where you have the application in question must be on the same domain as the account you are trying to run the application as. OR the domain where the host is at must have a trust relationship with the domain where the account is at.

    1. Randy Knight

      This is exactly what ShellRunAs is designed to overcome. Did you try it? If so I’d love to see what error you got as I use this technique on a regular basis.

Leave a Reply

Related Articles