SQLSaturday 21 - Encrypting SSIS Connection Strings

I had the chance to speak at the recent SQLSaturday in Orlando on Encrypting SSIS Connection Strings. Many thanks to Andy Warren for giving me the opportunity.

As always the day was top notch all the way. Andy & team put on a great event.

I've commented the code used for the demo (it's fully functional, just needs the passwords & file path for the master key backup changed) with enough information to implement the solution & placed it here.

Thanks again to Andy & team for a great event. Also a special thanks for all those who attended my session & restrained from any rotten fruit throwing :-).

Checking SQL & SSIS Versions with Powershell

I'm currently in the process of applying SP3 + CU5 to all our SQL2005 Instances. I wanted to be able to quickly check whether the install was successful (we're experimenting with using SCCM to push to our standalone Instances).

Checking SQL was easy enough, just using @@version in a multi-query was enough. However SSIS is a little tougher, you can't check it via T-SQL and I don't have the patience to login to every machine and check it that way.

The solution I came up with uses SERVERPROPERTY('ProductVersion') to check the SQL build & [system.diagnostics.fileversioninfo]::GetVersionInfo() to check the Product version on the SSIS executable.

It returns a table with the Instance, Version, SQL & SSIS Builds and Notes (for example whether SSIS is installed & if installed do the SQL & SSIS Builds match). The code is here.

Connecting to a Remote SSIS Server with Access Is Denied Error

If you are getting the dreaded “Connect to SSIS Service on machine failed: Access is denied” error when you connect as follows: For Windows 2003 Server (SP1)

• If the user running under non-admin account it needs to be added to Distributed COM Users group
• Run %windir%\system32\Com\comexp.msc to launch Component Services
• Expand Component Services\Computers\My Computer\DCOM Config
• Right click on MsDtsServer node and choose properties
• In MsDtsServer Properties dialog go to Security page
• Configure your settings as described bellow
• Restart SSIS Service

In the Security page we are interested in “Launch and Activation Permissions” section. Click Edit button to see “Launch Permissions” dialog. “Launch Permissions” dialog allows you to configure SSIS server access per user/group. In the bottom of the dialog you can select:

• Local & Remote Launch permissions if you allow to a user/group to start service locally and remotely (they will need both).

• Local & Remote Activation permissions if you allow to a user/group to connect to SSIS server locally and remotely (they will need both).

• Read permissions on Change Configuration

• And also, add those users to “Distributed COM Users” group.

If the users continue to experience errors, there is final set of permissions to check on the DCOM application, controlled by the Access Limits on the "COM Security" tab.

Access Permissions

Launch Permissions