Connect to SSIS remotely and edit default location where packages are stored

There may be situations when some users have to connect to SSIS remotely form the workstations because users cannot be logging into the actual servers. There are some steps that need to be done for having users to successfully connect to SSIS. Else they may get an error message

Perform the below steps to change the configuration of how SSIS accepts remote connections in a cluster

Note that the first step is only required for those instances in a cluster and it did not require me to make these changes to connect to a server not in cluster.

1. Search for the MsDTSsrvr.ini file on the server and open it with notepad:

The content of the .ini file:

<?xml version="1.0" encoding="utf-8"?>

<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

  <TopLevelFolders>

    <Folder xsi:type="SqlServerFolder">

      <Name>MSDB</Name>

      <ServerName>servername\instancename</ServerName>

    </Folder>

    <Folder xsi:type="FileSystemFolder">

      <Name>File System</Name>

      <StorePath>M:\MSSQL$SQL3\SSISPackages</StorePath>

    </Folder>

  </TopLevelFolders> 

</DtsServiceConfiguration>

Edit the highlighted ones in red as required.

The store path is by default D:\AppWin32\MSSQL\90\DTS\Packages or C:\Program Files\Microsoft SQL Server\90\DTS\Packages unless you wish to modify like in the above case. That location is where the packages get stored when you import into a file system and not msdb.

When we import any package into the msdb d/b there is no physical .dtsx file stored on the server.

1. Add the user/group to Distributed Com group on the server

2. Go to Admin tools -- Component services -- Computers -- My computer -- DComConfig -- MsDtsSrvr, right click on it and hit properties

3. Go to ‘Security’ tab and edit the first 2 sections: ‘Launch and Activation Permissions’ and ‘Access Permissions’ and hit edit to add/remove users as required.

Execute an SSIS package under a proxy account using SQL Agent job

Database administrators might sometimes come across the situations where the users need access to run SSIS packages (in environments where the DBA team does not generally own the responsibility of ancillary services). We can’t let the users have admin privileges to be able to run the packages as jobs. In such situations proxy account comes into play. A proxy account can be created and the job can be configured to be run under that proxy account. Follow the below steps to accomplish that

1. A new NT account needs to be created for the purpose of having a SQL job (SSIS package) executed under a proxy account. Most important the NT account has to be created with ‘No Password Expiry’ option.

2. Provide the SQL DBA team a list of users that need to have access and execute the jobs on SQL Server.

3. Add the userid (that needs to execute the jobs) provided by the Business Unit to SQLAgentUserRole in the msdb database. Also make that userid owner for the job that needs to be executed.

Creating a new proxy by linking it to an already existing credential:

4. Grant access to the above created NT account in this case on SQL Server with sysadmin permissions. (creating a new login).

5. Create a new credential:

Expand security --> go to credentials --> right-click and hit new credential.

Put in the password for the already existing NT account which has sysadm permissions on the SQL server

6. Expand SQL Server Agent --> right click on Proxies and hit new proxy

On ‘General’ tab enter the proxy name and enter the credential name created in the first step select the subsystems as required

7. On the ‘Principal’ hit on the button ‘Add’ and add the required logins that are required to make use of the proxy account.

8. Edit the job properties and hit ‘Edit’.

In the drop down list of ‘Run as’ select the newly created proxy account and hit ok.

The job should run successfully now.