SSIS, Docker, and Windows Containers, Part 4 – Adding an SSIS Catalog, Attempt 2

In my previous four posts I examined how to install Docker for Windows, pull a test image, search for a SQL Server on Windows image, install and interact with it (Part 0). I shared why I want to do all this (Part 1). I shared one way to execute SSIS on a container in Part 2. And I shared one way to (attempt to) add an SSIS Catalog to a SQL Server-on-Windows container in Part 3 (but that attempt failed).

In this post, I show my next step: connecting to an SSIS Catalog to a container and deploying SSIS to the Catalog. Spoilers:
1. It’s more work than you think; 
2. This is merely one way to do it
3. This way actually creates an SSIS Catalog on our container; and
4. This is another step closer to my ultimate goal.

If You Skipped the Previous Post…

If you skipped the previous post titled SSIS, Docker, and Windows Containers, Part 3 – Adding the SSIS Catalog, Attempt 1, you need to do some things. If you worked through the last post you may skip to the heading “About Connecting…”.

Getting the SSIS Catalog-Required Objects Installed On This Image

I can hear some of you thinking, “How do we accomplish this, Andy?”
I’m glad you asked.
The answer is “We modify our container.”

Disclaimer: I’m about 100% certain there’s another way to do this and about 99% sure there’s a better way. I’m going to show you what I did. Cool?
Cool.

Create a Local Host Directory

On my laptop I created D:\docker\install as shown here.

In the directory I placed the installation media for SQL Server 2017 Developer Edition. Note: If you browse to that link you will need to scroll to see the Download Now button for Developer Edition:

I blogged about one way to install SQL Server 2017 Developer Edition if you’d like to learn a little more. But I warn you, this post doesn’t describe many configuration options and should not be used to configure a production SQL Server 2017 instance.

For more information about extracting SQL Server 2017 Developer Edition installation media to the file system, please see the post titled One Way to Extract SQL Server 2017 Installation Media.

Reference a Host Directory When Starting a Container

Here I choose to reuse mySqlServerContainer2. To accomplish that, though, I need to tear down the previous version. I do so using the following PowerShell commands:

docker stop mySqlServerContainer2
docker rm mySqlServerContainer2

This is one reason I like containers: I can burn them down and rebuild them easily. Try that with a virtual machine…

You can configure a Docker volume when you run a container by adding the -v switch and referencing a directory on the host. Our modified docker run command appears as show here:

docker run -d -p 1433:1433 -e sa_password=$up3r$3cr3t -e ACCEPT_EULA=Y –name mySqlServerContainer2 -m 2048M -v D:\docker\install:c:\install microsoft/mssql-server-windows-developer

Docker run creates a container for us that has 2G RAM and its C:\install directory is mapped into our D:\docker\install directory on the host. I have to tell you, this functionality beats the dickens out of transferring files into the container using PowerShell (as demonstrated in Part 2)!

Run:

docker start mySqlServerContainer2
docker exec -it mySqlServerContainer2 powershell

This starts the container and drops you into PowerShell inside the container. Run the “ls” command to list files:

Change to the install directory and view its contents by executing the following commands:

cd install
ls

As you can see, if we want to add files that are accessible from within our container, we merely add them to our host directory.

Add Integration Services to the Container

To add the IS (Integration Services) features to our default instance of SQL Server 2017 on the container, we execute the SQL Server 2017 Developer Edition installation application – setup.exe – from the command line. Note: I ran the container with 2G RAM to provide enough RAM for this operation to complete. The command is:

C:\install\SQLServer2017Developer\setup.exe /Q /ACTION=Install /FEATURES=IS /INSTANCENAME=MSSQLServer /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS

In response to this command, the container spins for a few minutes. When the installation completes, the container appears as shown here:

(click to enlarge)

About Connecting…

My goal is to connect to the instance of SQL Server that resides on my container using SQL Server Management Studio (SSMS). Once connected, I want to be able to interact with the SSIS Catalog on the container’s SQL Server instance.

So, two requirements:
1. Connect; and
2. Interact with the SSIS Catalog.

Connecting, Part 0

I can open SSMS and connect using a SQL Login. But… to what am I connecting? One way to connect is to use the IP address of the container. I can get the IP by executing the following docker command from a command prompt on the host:

docker inspect –format ‘{{.NetworkSettings.Networks.nat.IPAddress}}’ mySqlServerContainer2

When I execute this command, my results appear as shown here:

(click to enlarge)

Copy the IP address.

Open SSMS and paste the IP address into the Server textbox. Set Authentication to SQL Server Authentication. Enter “sa” (without the double-quotes) into the Login textbox. Enter the password you configured for the a login when you started the docker container.

The command I used was:

docker run -d -p 1433:1433 -e sa_password=$up3r$3cr3t -e ACCEPT_EULA=Y –name mySqlServerContainer2 -m 2048M -v D:\docker\install:c:\install microsoft/mssql-server-windows-developer

The sa_password argument contains my sa password: $up3r$3cr3t:

When I attempt to connect I get the following error message:

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.)

A quick Bingle search reveals the connection string is requesting an encrypted connection to the SQL Server instance, and that’s just a little more than the container is prepared to offer. The solution? Click Options and view the settings on the Connection Properties tab:

If it is checked, un-check the Encrypt Connection checkbox.
Click the Connect button and SSMS should connect to the default SQL Server instance on the container:

w00t!

Expanding the SSMS Object Explorer’s Integration Services Catalogs node, we see… no SSIS Catalog.

I take back my w00t…

What gives?

Well, remember the PowerShell script we executed to create the SSIS Catalog?
It did not create the SSIS Catalog.

Why not?

My Best Guess: The SSIS Catalog uses objects in the .Net Framework to perform some operations (this is why SQLCLR must be enabled to create an SSIS Catalog). If you work with the SSIS Catalog – and try new and different things (like connecting using a SQL Login)…

… you will learn some SSIS Catalog functionality will not work unless you connect using Windows Authentication.



I can hear you thinking, “Why, Andy?”
I do not know.
I know we must take a different approach.
As stated in previous posts in this series, I am certain there are other ways to solve this issue, and I am nearly certain there’s a better way. This is how I address this obstacle:
I first create a local administrator account.

Creating a Local Administrator Account

Use the following PowerShell script to create a local administrator account with the following credentials on mySqlServerContainer2:

  • Name: su
  • Password: $up3r$3cr3t
  • Privileges: Administrator

$Username = “su”
$Password = “$up3r$3cr3t”
$group = “Administrators”
$adsi = [ADSI]”WinNT://$env:my”
NET USER $Username $Password /add /y /expires:never
NET LOCALGROUP $group $Username /add
WMIC USERACCOUNT WHERE “Name=’$Username'” SET PasswordExpires=FALSE

When I execute these commands my command prompt appears as shown here:

(click to enlarge)

Start SSMS from a command prompt using the runas command with the /netonly switch:

runas /netonly /user:mySqlServerContainer2\su “D:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe”

When I execute this command my initial command prompt results appear as shown here:

(click to enlarge)

Here’s what’s cool about starting SSMS using runas with the /netonly switch: When prompted to login to the SQL Server instance, you configure the connection to use Windows Authentication:

(click to enlarge)

One note: Be very careful typing the password into the runas command. If you supply an incorrect password, SSMS will raise an error when trying to connect:

(click to enlarge)

The error message is:

Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.

If you get it right, we connect (yay!) but still no SSIS Catalog (boo!):

(click to enlarge)

Creating an SSIS Catalog on a Container

Right-click the Integration Services Catalogs node in SSMS’s Object Explorer, and then click Create Catalog:

When the Create Catalog dialog displays, check the Enable CLR Integration checkbox to enable the remainder of the Catalog configuration controls on the form:

(click to enlarge)

Once configured, click the OK button to create the SSIS Catalog on the container:

(click to enlarge)

The SSIS Catalog is created. No, really.

Conclusion

After two attempts I have an SSIS Catalog running in a SQL Server Windows container.

Next, deploying an SSIS project to this SSIS Catalog.

“Andy, I Want More ____”

I am always learning.

I enjoy learning and this trait led me to technology in the first place. It turns out a love of learning is all but essential for technologists.

After I learn, I share what I’ve learned in blog posts, webinars, and via presentations at SQL Saturdays and events like the PASS Summit.

What Do You Want to Learn?

Drop me a comment and let me know what you’d like to learn! I may take your suggestion and run with it.

:{>

The Recording for Troubleshooting SSIS (Part 1) is Available

A fantastic crowd attended today’s (13 Mar 2019) free webinar titled Troubleshooting SSIS. If you attended you got the full show. If not, you may view the content at SSIS Academy for a nominal fee.

Enjoy this focused content to SSIS Self-Help, a service from Enterprise Data & Analytics designed to help folks quickly and affordably overcome SSIS challenges.

Preview Available

The first two segments of the presentation are available for free at the top of this post. In the full version I walk you through:

  • Design-time validation
  • Debugging most SSIS tasks
  • Debugging the Script Task
  • Debugging the SSIS Data Flow Task
    • Using Data Taps
  • Debugging the Script Component

Enjoy.

:{>

Andy’s Database Credentials Hack

Ok, this isn’t a hack. It’s more of a tip that may help you protect data. Some of you would not have clicked a link to Andy’s Database Credentials Tip. You are welcome.

LastPass

I like LastPass. I pay for Premium and it’s not a bad deal, in my humble opinion.

I also like Azure – a lot. As a Microsoft Data Platform MVP, I get a free subscription to Azure. But there was a time when I paid for my Azure subscription. It was worth it, in my humble opinion.

Provisioning an Azure SQL DB

When it’s time to provision an Azure SQL DB, I sometimes configure a username and password. (You can also use Azure Active Directory to connect to an Azure SQL DB instance).

Back in the day, I would use some generic – but easy to guess – name for the Administrator account. Something like “andy,” for example.

The LastPass Chrome extension includes a Generate Secure Password option:

I use this to generate the Server Admin Login value:

(click to enlarge)

My thinking? Why give away half the key-value pair for logins?

Conclusion

LastPass isn’t the only way to generate a random string. I leverage it because, well, it’s right there in my browser already.

I cannot provide metrics for how often this trick has thwarted would-be attacks. I also do not know if this even slows bad guys down these days. But I’ve been doing this for a while and – if nothing else – it gives me a little peace of mind.

:{>

SQL Saturday 813 – Boston #SSIS Precon 29 Mar 2019!

I’m honored to deliver Intelligent Data Integration with SSIS – a full-day pre-conference session – at SQL Saturday 813 in Boston 29 Mar 2019!

What is Intelligent Data Integration?
SSIS (SQL Server Integration Services) packages developed using tried and true design patterns, built to participate in a DevOps enterprise practicing DILM (Data Integration Lifecycle Management), produced using Biml (Business Intelligence Markup Language) and executed using an SSIS Framework.
Attend a day of training focused on intelligent data integration delivered by an experienced SSIS consultant who has also led an enterprise team of several ETL developers during multiple projects that spanned 2.5 years. And delivered.
Attendees will learn:

  • a holistic approach to data integration design.
  • a methodology for enterprise data integration that spans development through operational support.
  • how automation changes everything. Including data integration with SSIS.

Topics include:

  1. SSIS Design Patterns
  2. Executing SSIS in the Enterprise
  3. Custom SSIS Execution Frameworks
  4. DevOps and SSIS
  5. Biml, Biml Frameworks, and Tools

Register today!

I hope to see you there!

:{>

Enterprise Data & Analytics Welcomes Eugene Meidinger!

I am honored and thrilled to welcome Eugene Meidinger (@SQLGene | blog | LinkedIn | Pluralsight) to the Enterprise Data & Analytics team! Eugene is self-described as a Business Intelligence Consultant but I think he’s being modest (in my humble opinion… see what I did there?).

Eugene has authored three Pluralsight courses on the topics Power BI, DAX, Power Pivot, and SSAS. His experience includes loading ERP solutions, Power Reports, ETL, QlikView, and SQL Server development.

Eugene is a regular community presenter at SQL Saturdays and delivered a great session titled Introduction to Power Query (The M Language) ranked one the best Data Analytics sessions of the PASS Summit 2017:

Eugene is active in the SQL Server Community, serving as a co-organizer of the Pittsburgh Power BI User Group.

SQLGene is Internet-Famous!

Here’s another video of Eugene and Bert Wagner (@bertwagner) – #SQLChefs:

I continue to be humbled and thankful and excited to watch Enterprise Data & Analytics (quietly) grow – adding cool people like Eugene and Shannon and service offerings like SSIS Self-Help. It’s very cool to watch!

Welcome Eugene! I am honored to work with you, my brother and friend.

For more information, please contact Enterprise Data & Analytics!

SSIS Project Connection Manager Naming Error

I am sometimes asked, “Andy, how do I start a technical blog?” I demonstrate one way here – a way that has served me well with search engines.
1. Describe how the error occurred.
2. Copy the error message, post it as text.  
3. Describe the solution.

I am helping a large enterprise client migrate their data integration from another data integration platform to Azure-SSIS. It’s a two-step process:

Step 1: Convert the existing data integration operations to SSIS and test the conversion by deploying to an on-premises SSIS Catalog.

Step 2: Lift and shift the SSIS Catalog configurations from on-premises to Azure-SSIS (we will be using SSIS Catalog Compare for this part).

An SSIS Error

In the midst of designing SSIS solutions we encountered an interesting SSIS error:

Failed to start project

Exception deserializing the package “Operation is not valid due to the current state of the object.”. (Microsoft.DataTransformationServices.VsIntegration)

As a professional consultant who has been blogging about SSIS for 12 years and authored and co-authored a dozen books related to Microsoft data technologies, my first response was:

“Whut?!”

I bingled (credit for the term, “Bingle”: Ed Danger Watson) and found nothing helpful. I was beginning to think something unfortunate had happened to the client’s SQL Server Data Tools SSIS template when a crazy idea popped into my noggin. I said to myself:

Self? Could it be the odd-looking name of the Project Connection Manager?

Andy – circa 2019

The Odd-Looking Name of the Project Connection Manager

When you create a new SSIS Connection Manager, SSIS automagically names it for you. Awesome, right? Most of the time. But sometimes the default name created for a package connection manager doesn’t play nice with Project Connection naming requirements.

Don’t believe me? Try this:

Create a new SSIS package.
Add a package connection manager aimed at a named instance of SQL Server, such as “vDemo\Dev”:

Next, (attempt to) promote the package connection manager to a project connection manager:

Note the error:

The error message in this case reads:
Failed to convert the package connection mananager ‘vDemo\Dev.WeatherData’ to a project connection manager because ‘vDemo\Dev.WeatherData’ is not a valid stream name in the project. Rename the package connection manager and do the conversion again.
Object name ‘vDemo\Dev.WeatherData.conmgr’ is not valid. Name must contain only alphanumeric characters or underscores ‘_’.

The backslash – “\” – in the connection manager name – which works just fine for the Package Connection Manager – blocks promotion of the package connection manager to a Project Connection Manager.

Bummer.

Back to the problem at hand…

My client’s Project Connection Manager name included square brackets (it’s an Oracle thing…). Renaming the project connection manager – simply removing the square brackets – cleared the error:

The package now executes in the SSIS debugger:

SSIS is a complex and flexible software development platform. I learn new things with each and every consulting project. Why did I think of the connection manager name? In testing SSIS Catalog Compare version 4, I tried out a bunch of naming combinations. I was shocked to learn some characters are permitted (did you know you can create an SSIS Catalog Folder name that contains an apostrophe??).

Happy Integrating!

:{>

Start Azure-SSIS, Execute Package, Stop Azure-SSIS

Last August, I had a blast at the Data Platform Summit 2018 in Bengaluru, India. One highlight of the trip was meeting and conversing with the Azure Data Factory SSIS Integration Runtime Product/Program Manager, Sandy Winarko.

Sandy and I swapped coder stories and talked about data integration design patterns and I learned a few new design patterns sitting in his session. This post is about one of the patterns I learned from Sandy.

Before I move on, though, you should really check out the Data Platform Summit! It is an awesome show – Asia’s largest Microsoft data conference.

Stop In The Name Of… Money

I love Azure! I love the technology and the velocity of development. The cloud is changing the world and Azure is changing the cloud – almost every day. One cool thing about Azure is cost savings. A lot services are free or nearly free. That. is. Awesome! But some services are not free.  (That’s not a bad thing!)

Azure Data Factory’s SSIS Integration Runtime (aka Azure-SSIS IR) is not free. It costs money even when it’s just sitting there switched on with no SSIS packages executing. Depending on the size of the Azure-SSIS IR, one can wrack up some serious charges for what amounts to, well, no work.

That’s why I was excited to learn about this pattern from Sandy. The pattern is made up of three parts:

  1. Start Azure-SSIS
  2. Execute an SSIS package
  3. Stop Azure-SSIS

Sandy, Nick SchonningDouglas Laudenschlager, and Sreedhar Pelluru authored a post at Microsoft Docs titled How to start and stop Azure-SSIS Integration Runtime on a schedule. When I tried to follow the instructions I encountered one issue. Now, you may read that last sentence and think, “Andy, don’t you have a problem following instructions anyway?” Yes, but that’s beside the point… mostly.

Their article walks one through setting up an instance of Azure-SSIS. It then proceeds to describe how to build the start-execute-stop pattern Sandy described to me (and everyone who attended his presentation) in Bengaluru. Now this pattern has evolved since Sandy first described it. The first version used Azure Runbooks and Webhooks. I like this version much better. You can still use Runbooks and there’s more information in the How to start and stop Azure-SSIS Integration Runtime on a schedule article.

Before You Start

I’m going to share the part that gave me trouble first – it was security. In the article, Sandy and company write in Step 5 of the Create Your Pipelines section:

Assign the managed identity for your ADF a Contributor role to itself, so Web activities in its pipelines can call REST API to start/stop Azure-SSIS IRs provisioned in it.

I read that and proceeded to assign myself to the Contributor role of Azure Data Factory. That didn’t work. Not at all. And that’s not what the instructions said. The instructions said to “Assign the managed identity for your ADF a Contributor role to itself…” 

What’s a Managed Identity?

Well, I searched for the answer and found this spiffy article titled Azure Data Factory service identity that not only explained what an ADF Managed Identity was, it also told me where to find it

See, when I browsed over to assign the ADF Contributor Role, all I saw in the available list of people / accounts I could assign was li’l ol’ me:

So that’s who I assigned to the Contributor Role: me. As I said, that did not work.

The article titled Azure Data Factory service identity took me right to the location of the ADF Service Identity Application ID, which was the value I needed: 

After I figured this out – with awesome and patient help from Sandy himself (thanks Sandy!) – I was in business.

Stop It!

I created a separate pipeline to stop Azure-SSIS. I run it every night at 11:00 PM ET because if I’m awake at that time (I usually rise around 4:00 AM ET), I am not being productive on my computer (if I am on it at all…). I do this in case I forget and leave Azure-SSIS running after a presentation, class, or client demo.

Following the instructions, I created a new pipeline and added a Web Activity. I configured the Web Activity according to the instructions. Mine looked like this:

(click to enlarge)

The tricksy part (if there is one) is configuring the URL. There’s a shortcut for that. Click on the Monitor page of the ADF portal:

Click the Integration Runtimes tab at the top of the page, and then click the “View Integration Runtime Detail” link under the Actions column to bring up the snappy new mini-dashboard for Azure-SSIS:

Regardless of the state of Azure-SSIS, click the status link to open a functional  pane that contains the Resource ID and a Copy button:

This copies a lot of what you will need to set up the call to stop Azure-SSIS (and also to start it): 

/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.DataFactory/factories/{factoryName}/integrationRuntimes/{integrationRuntimeName}

When you copy the Resource ID, you get all those missing values filled in for you. How neat is that? Prefix that with “https://management.azure.com” and add the suffix “/stop?api-version=2018-06-01”, and you are golden.

Finish following the instructions in the Create Your Pipelines section, and then take it out for a test drive.

Clone

Things begin to pick up here partly because you’ve done a lot more work than you realize already, and partly because supper’s ready and I’ve been blogging for the past couple hours.

(For you, Dear Reader! I do it all for You!) And for me. In case I forget…

Click the ellipsis beside the pipeline and then click Clone to make a fresh copy of your pipeline (it still has that new pipeline smell!)

I name my new clone Start Run Stop.

The Stop AndyDemoSSIS Web Activity is in there already and just begging to be cloned.

Look how easy it is to clone an activity in ADF. I mean, could they make this any easier? I think not.

Once its cloned, it’s not much work to edit the configuration of the cloned Web Activity to start the AndyDemo Azure-SSIS instance:

Boom. That just happened.

“Run-Time!” (Spoken in the Voice of the Geico Camel)

Next up? The Execute SSIS Package Activity:

I’m not going to write much about how to configure the Execute SSIS Package Activity. You can learn more in one of these blog posts where I’ve already written about it. Supper’s waiting.

Conclusion

Thanks to Sandy and the team at Microsoft for the excellent documentation. This is a very cool Azure Data factory Design Pattern!

Enjoy!

SSIS Catalog Compare Version 4 – Three Videos

I am excited to announce general availability of SSIS Catalog Compare version 4!

I made three short-ish (~2 minutes each) videos demonstrating some of the capabilities of the product. I really like the SCCPAC files and SCCPAC Deployment Wizard, which are only available in the Enterprise Edition.

Use SCCPAC Deployment Wizard to Lift and Shift SSIS Projects and Configurations Metadata to Azure-SSIS

Use SCCPAC Deployment Wizard to Manage SSIS DevOps

Use SSIS Catalog Compare Enterprise Edition and SCCPAC Deployment Wizard to Deploy from the QA to Production Catalog in Real-Time

Enjoy!

:{>

Grant SSIS Catalog Read-Only Permissions

The SSIS Catalog has built-in security to manage permissions. SSISDB – the database behind the SSIS Catalog – is “just a database” in many respects. When it comes to security, the SSIS Catalog surfaces an internal mechanism that provides granular permissions management. 
In this post I intend to demonstrate how to use SSIS Catalog security to provide read-only access to SSIS Catalog artifacts. But first…

Two Thoughts About SSISDB Roles

Thought 1: “Help us DBAs, you’re our only hope.” – Princess Leia (paraphrased)

If you work with SSIS you already know the Microsoft team of technical writers is comprised of artists, masters of the field of technical writing. I’m convinced a large part of the successful adoption of SSIS is due these good people. You can see a sample of their outstanding artistry in the article titled Integration Services Roles (SSIS Service).

Two important roles in the SSIS Catalog are ssis_admin and ssis_logreader. According to the article linked above:

ssis_admin. This role provides full administrative access to the SSIS Catalog database.

ssis_logreader This role provides permissions to access all the views related SSISDB operational logs.

SSIS_admin and ssis_logreader are SQL Server database roles. As such, they are typically set and maintained by Database Administrators (DBAs).

Thought 2: Although SSISDB is a SQL Server database, it’s more like an application coded in T-SQL.

One for-instance, for instance, is the SSIS Catalog requires Windows authentication for most administrative activities. It took me a while to understand why Windows authentication is necessary. I now get it, but the explanation is for another post. This has implications, such as:

  • SQL Logins – even sysadmins such as sa – cannot deploy SSIS projects to an SSIS Catalog. Or execute SSIS packages.
    • One exception: an Azure-SSIS SSISDB database hosted on an instance of Azure SQL DB can perform SSIS Catalog administration using a SQL Login.

In sum, the SSIS Catalog is a database application that requires Windows authentication for administrative tasks.

Null Use Case: No Access

When a user has no access to SSIS Catalog artifacts, the SSMS Object Explorer Integration Services Catalogs node appears as shown here:

SSIS Catalog Browser presents a unified view of all SSIS Catalog artifacts (and it’s free!).

All SSIS Catalog-related products and utilities at DILM Suite respect SSIS Catalog security. Early versions of SSIS Catalog Compare allowed users to login with SQL Server Login credentials and access SSIS Catalog artifacts that SSIS Catalog security would block. A couple years ago I refactored CatalogBase – the object beneath DILM Suite’s SSIS Catalog products and utilities that interacts with the SSIS Catalog – to respect SSIS Catalog security.

A user with no access will see an empty SSIS Catalog using SSIS Catalog Browser:

Grant Read-Only Access to Folders

In SSMS, right-click the SSIS Catalog Folder you wish to surface for a 
Windows authentication-based SQL Server login:

When the Folder Properties window displays, click the Permissions page. On the Permissions page, click the Browse button to select one or more Windows authentication logins:

The Browse All Principals dialog displays. Select one or more Windows authentication logins (Windows User type):

To assign read-only permission to the SSIS Catalog Folder, click the OK button on the Browse All Principals dialog and check the Grant checkbox for the Read permission in the Folder Properties window:

The user(s) you selected are now able to view the SSIS Catalog folder using the SSMS Object Explorer’s Integration Services Catalogs node:

Similarly, SSIS Catalog Browser will display the SSIS Catalog folder for the assigned user(s);

“Where’s the Beef?”

I can hear you thinking, “Where are the projects and environments, Andy?” That is an excellent question. I’m glad you asked! They exist, of course, but we only granted the user Read permission for the SSIS Catalog Folder.

Grant Read-Only Access to Folder Artifacts

To see SSIS Projects and SSIS Catalog Environments, a user must be granted Read Objects permission on the SSIS Catalog Folder:

Now users can view SSIS Catalog folder contents using SSMS:

SSIS Catalog Browser users enjoy a similar view:

Conclusion

The SSIS Catalog surfaces a rich and powerful security model that may be leveraged by enterprise data integration DevOps teams using SSMS or SSIS Catalog Browser.

Want to learn more? From me? Join me for the next delivery of Expert SSIS – a 2-day, live, remote SSIS training event!