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 – not 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 wind 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 users 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! 

Two ADF Presentations Next Week!

I am honored to deliver Moving Data with Azure Data Factory to two User Groups next week!

Abstract

Azure Data Factory – ADF – is a cloud data engineering solution. ADF version 2 sports a snappy web GUI (graphical user interface) and supports the SSIS Integration Runtime (IR) – or “SSIS in the Cloud.”

Attend this session to learn:

How to build a “native ADF” pipeline;
How to lift and shift SSIS to the Azure Data Factory integration Runtime; and
ADF Design Patterns to execute and monitor pipelines and packages.

Dates and Locations

07 Jan 2019 – 6:00 PM ET

Columbus Georgia SQL Server Users Group
Meeting at the Columbus Public Library
3000 Macon Rd
Columbus, Georgia 31906

Register today!

08 Jan 2019 – 5:30 PM ET

Midlands PASS
Meeting at WeKnowIT
440 Knox Abbott Drive
Suite 424
Cayce, SC 29033

Register today!

If you live – or will be – nearby, I hope to see you!

:{>

PS – want more? Join me 04 Mar 2019 for a full-day of training! 
Learn more:
Getting Started with Azure Data Factory – Live, 1-Day Training

Testing SSIS Catalog Compare v4.5

I just posted a preview of the next version of SSIS Catalog Compare at the brand new DILM Suite blog. You can head on over and check out the video is you have five minutes to spare.

I’ll let you in on a secret: the star of the show is the new SCCPAC Deployment Wizard:

The new kid on the DILM block, SCCPAC Deployment Wizard

I demo this utility starting around the 2-minute mark in the video.

Check it out and let me know what you think.

And Happy New Year!

*<:{>

Thinking of You…

The holidays are a joyous and happy time of year. 

Almost everyone struggles in some way with the holidays, though. There are numerous posts about how to have difficult conversations with family who disagree with you on some matter or other – usually politics or religion.

For some folks, though, it’s worse.

Some struggle with depression year round. The holidays seem to pile on with emotional overload combined with factors like longer nights and shorter days and colder weather that keeps people inside (in the northern hemisphere). 

If you know someone struggling with depression in any of its myriad manifestations, whether diagnosed or not; if you know someone who is just… down; please reach out to them during this time of year.

If you are struggling, you are not alone. 

If you are considering suicide, please reach out to someone. I’m here. You can reach out to me. If you cannot reach me, reach out to a friend or pastor, or call 800-273-8255. 

Q: “What are Pitfalls for Software Developers to Avoid?”

I speak with up-and-coming software developers often. A couple junior / intermediate software developers work with me at Enterprise Data & Analytics. As I’ve navigated four decades (plus) of software development I have witnessed – and experienced – places on my career-trajectory-map marked, “Here be dragons.” 

“I Can Learn All the Things”

“My name is Andy and I am a learning addict.” There are worse things to which one may be addicted (ask me how I know). There are at least two motivations for wanting to learn all the things:

  1. To “lord it over others.” Some seek power. Some want wealth or fame. Some desire to be the smartest person in the room. It’s easy to judge people with such motivations. I ask you to consider grace instead. There’s some reason they feel the way that they do. It’s likely the result of an unpleasant childhood.
  2. To be stronger, better, faster. Some seek to compete with themselves, to learn more than they knew yesterday, to “suck less each day,” as my former boss and mentor Ben McEwan used to say.

We live in an age where it is simply not possible for most human minds to keep up with all the things in technology. As in other scientific disciplines, we can become generalists or specialists in practice. We can go wide or deep – or wide with one or two areas of depth – but not much more.

Anecdote: Don’t Believe Your Own Publicity

About once per decade I encounter a soul who is impressed with stuff I’ve done, said, or written. It’s a cycle. They are enamored with “the myth of Andy” for some time. Then time passes. They learn more about me. They read about my mistakes. They catch me in an error. In their mind, I fall from the pedestal. 

To quote the wise philosopher, Joe Walsh, “Everybody’s so different. I haven’t changed.” In the song, “Life’s Been Good,” Joe is expounding on his rock star life. He’s being sarcastic. Kinda. I like to think Joe is poking a little fun at his detractors. and maybe some fans Someone thinking I’m somebody doesn’t make me think more of myself than I ought. 
I know the cycle.

My response to the (temporarily) enamored: “I’ve been trapped in here with me for nn years and I am not impressed.” I mean that. Don’t fall for it, it’s a trap.

One Way: Learn and Practice One Thing (to Start)

I suggest to anyone who cares to read my advice (that’s you at the moment) that you find something that you enjoy – or that you believe you can enjoy – something for which you have a passion, and do that. I posit you have a natural inclination or  genetic predisposition to do some thing. In doing that thing, you are doing what you are.

It’s popular these days to poo-poo passion. I get that. I think it’s wrong but I understand the error. Your personal learning path is going to be, well, personal. It’s not going to track with my learning path. Years ago I realized this and stopped writing “better” to describe some path or solution. I replaced the word “better” with “one.” That was me recognizing your path will differ from mine. And that’s not only ok, it’s a good thing. 

Keep one thing in mind, please: Learning is not easy. If it was easy, anyone could do it. It’s not easy. You can do it. You will not learn it all by reading one blog post. The best blog posts and books and articles – the ones from which you learn the most – are going to frustrate you to no end. Before you pop over to Amazon to leave a scathing helpful review, though, please believe me : you learn more than you realize from books which “do not help you learn technology.”

So, learn. Learn for good and right reasons. Take motivation from wherever it comes – even bad experiences. Use the bad examples as anti-patterns. If you find yourself battling demons from your past, welcome to the club. Frank Herbert, in his (awesome) book Dune, offered a solution: transmute the poison. That’s what I did, so I know it can be done.

Why pick a passion? Because learning is work. Hard work. There are going to be days when you want to quit. If you don’t enjoy the topic, if it’s not a passion, you will quit. I don’t want you to quit.

“This is Taking Too Long”

Yes.

Becoming proficient at software development will consume more time than you think.
Becoming proficient at anything will consume more time than you think. Malcolm Gladwell famously mentions the 10,000-Hour Rule in his book, Outliers.

from Goodreads.com

Learning things – especially mastering things – will take time. There’s no substitute for hard work.

Anecdote: Just Give Me a Chance…

As I share in a post titled It Costs Precisely $0.00USD to be Nice:

There was a time when I would have done almost anything to have recruiters reaching out to me almost daily. It wasn’t that long ago, actually – just a couple decades. I remember begging recruiters to just give me a chance! I knew I could do the work. I could learn anything and I have a strong work ethic.

When I was learning, I felt like it was taking forever to break into software development. Software was a hobby for decades before I landed my first job where my primary purpose at work was coding. Decades.

Part of the reason was the field of software development was relatively small in the 1970’s, 1980’s, and 1990’s – when compared with today. Landing a job almost always required a bachelor’s degree and I don’t have one. It was an obstacle.

One Way: Suck it Up, Buttercup

That advice sounds crass, doesn’t it? There’s a good reason for that. It is crass. It’s also the best advice I can give you. No one who succeeded at anything quit. Not. One. Keep going. Don’t stop. Make the problems give up before you do.

Grant Cardone wrote a couple books every software developer should read listen to (he reads his audio books and they are awesome!): The 10X Rule and Be Obsessed or Be Average. In these books, Grant does an excellent job of explaining the dynamics of a successful work ethic. He has his detractors and critics (let me Google that for you…). I’ve not found one I consider successful (there’s more to success than making money).

“I Want What You Have”

Stop.
Stop right now. 
Stop and figure out what you really want.

<Christian Material>

Note: I am a Christian.

Colossians 3:5

In other translations, the word “greed” is translated “covetousness.” Covetousness is wanting what someone else has. Of the seven deadly sins, only envy provides no pleasure for the person engaging in it. 

Nada. Zero. Zilch. 

Anecdote: We Are Different

If you feel someone doesn’t deserve some thing that they have – be that thing a relationship, success, knowledge, what have you – you are making a judgment for which you are most likely utterly and completely unqualified to make.

If you’ve ever experienced someone wanting what you have, you may have felt compelled to share that they should do what you’ve done to earn what you have. 
I know I did when it happened to me.
This is pride. Pride is even more deadly than covetousness. Pride is the root of all sin.

It took me a chunk of years to realize that people are people. People believe what they believe for a reason. People have different experiences. That individual who irritated you to no end on social media earlier? That one you unfollowed? That person had experiences that led them to believe their way of thinking is better – makes more sense – than your way of thinking.

via Goodreads.com

I’m not saying you should love your enemies or pray for them.
Jesus said that.
I’m quoting Jesus.

One Way: Let It Go

from Goodreads.com

This is way easier to type than to practice. Joanna Weaver got this right: “Bitterness is like drinking poison and waiting for the other person to die.” You are as utterly unqualified to fix this issue in others as you are to judge it.

Let it go.

</Christian Material>

“Play Nice Well With Others”

If I had a nickel for every developer community post I’ve read about soft skills, I’d have a lot of nickels. I think the reason there are so many posts about soft skills is because we need to read them. Many such posts extol the virtues of “playing nice with others.”

I get it. I promise I do.

Anecdote: Fighting to Win Well

During my half-century+ on this earth, I’ve shared the concept of fighting well with a few people. Which people? Their names don’t matter. They shared a fiercely-competitive nature. There’s nothing wrong with being competitive. I am competitive. It’s the target of one’s competition that’s important.

I like – and agree with – Tim’s tweet:

Tim Mitchell, Twitter

Fighting well is playing un-nice with others, at least some of the time.
Fighting well is taking a risk for what you believe in.
Fighting well is saying in response to, “You should pick your battles,”
  “I choose this battle. This one. Right here. Right now.”
Fighting well is taking a stand.

Fighting well isn’t shutting down – or shouting down – the opposition.
Fighting well isn’t deflecting, answering questions not asked, controlling the narrative, deflecting, negotiating, manipulating.
Fighting well isn’t winning at all costs.
Fighting well is not fighting to win.

Fighting well means sometimes you lose.

One Way: Love People

I wrote a book – based on a series of posts on this very blog – about Managing Geeks. It’s more of a diary than a how-to book. I was mentored by the aforementioned Ben McEwan and former business partner Brian Moran in the art and science of management.

I remember one conversation when Ben asked me, “What are you doing to these people?” He was referring to my team. If you’ve ever been mentored you may have experienced the same reaction I did, My first thought was, “Great. What did I mess up this time?” 

But it wasn’t that kind of conversation (although we had many conversations about how I messed up…). It was a genuine question where the student took the teacher’s seat for a minute. (Note: all good mentors learn from their mentees…). He continued, “They will kill themselves for you.” Ben wanted to know how I inspired the team to follow me.

My response? “I love them.”

Servant leadership is now popular in the culture of business, but it has its roots in Christian faith communities. That’s where I learned it.

Fighting well means loving the opposition.
If I love you, I want the best for you.

Does that mean I’m going to capitulate? Not always. Sometimes what you want is not best for you. Sometimes I am in a position to know; to judge with wisdom. This is why we should respect our elders; they’ve lived longer and experienced more. Am I always going to be right just because I’m older? Goodness no! But I’m not always wrong just because I’m older, either.

I postulate experience matters but I balance that with fighting well.

Conlusion

These aren’t all the pitfalls for software developers but this post is long enough. Here’s hoping it helps.

Peace.