How to Change the Service Account for Analysis Services

As part of a routine check, I found that one of our servers had an instance of Analysis Services (SSAS) running under a local service account. As many of our solution pull data from various sources, on other servers, there is almost always a need to do a double hop. To enable that, the service needs to run under a domain service account.

This being a simple task and only a small part of the bigger Kerberos puzzle, I filed a ticket with our support, for them to change the service account to one already existing. The reply I got is the cause of this blog post. I needed to provide the individual steps for the change. A quick googlebing turned up rather empty on specific SSAS guides, to my surprise, so I decided to create on myself (I had to anyhow).

EDITED PART:

As Patrice Truong (b|t|l) correctly called out, the recommended way of doing this change, is to do it through the SQL Server Configuration Manager. To do so follow these steps:

Type [Windows] + [r], in the promt type ‘SQLServerManagerXX.msc’ (depending on the edition XX, 12 for 2014, 11 for 2012, 10 for 2008) – i.e ‘SQLServerManager12.msc’ for SQL Server 2014

Depending on your security settings, you may encounter this next dialog:

UAC

 

 

 

 

 

 

 

Clicking ‘Yes’ will bring you to the SQL Server Configuration Manager:

SQL Server Configuration Manager

Here you can select (double click) the particular instance you would like to re-configure. Doing so will open this Dialog, where you can edit the login information:

SSAS Configuration Log On Dialog

 

 

 

 

 

 

 

 

 

 

 

 

 

Change the login information and provide the corresponding password:

SSAS Configuration Log On Dialog Edit

 

 

 

 

 

 

 

 

 

 

 

 

 

Click OK/Apply and the Service will need a restart for the changes to be applied.

ORIGINAL PART:

This guide will be for Windows 2012 R2, but will be applicable on other Windows versions.

Type [Windows] + [r], in the prompt type ‘services.msc’

Services Prompt

 

 

 

 

 

 

This will open up the Services Dialog, where you can scroll to the SQL Server services listed

Services SQLServer

Double click the Analysis Services Service, and the following Dialog will appear:

SSAS Service

 

 

 

 

 

 

 

 

 

 

 

 

Click the ‘Log on’ tab

SSAS Service LogOn
 

 

 

 

 

 

 

 

 

 

 

 

Change the account to the desired domain account and type the corresponding password

SSAS Service LogOn Edit

 

 

 

 

 

 

 

 

 

 

 

 

Click OK/Apply and the Service will need a restart, before the changes are applied.
Happy hopping :)

Posted in Programming | Tagged , , | Leave a comment

New York Re-Visit

Please allow me to express my gratitude towards the team behind the upcoming SQLSaturday in New York for allowing me to speak on Security in Analysis Services (SSAS). I am really looking forward to get back to the Big Apple, and also very exited about delivering my first session on another Continent.
I have only visited New York once before, on the way home from PASS Summit 2014 I made a stop over to watch The New York Jets (1-8) host my favorite NFL team The Pittsburgh Steelers (6-3). In the weeks leading up to that game, Big Ben had just set an NFL record of throwing 12 touchdowns in two consecutive games, so you can imagine, expectations were very high. And you can imagine my disappointment, as The Steelers lost 13-20 by poor performance (M. Vick had as great 1st qtr). I left with 8 minutes on the clock, in order to get from MetLife to JFK in time for my flight, via public transportation (so if you want to know how it’s done, I did it).

IMG_2920

My View from the Top of MetLife Stadium

Enough with the Fottball alright – This time in New York, my 2nd time, I will be speaking at the SQLSaturday event about Analysis Services Security (and as the entire NFL is in trainingcamp, except maybe The Patriots who will be in court, there’s no Football this time around).

My session will go from a brief introduction to the SSAS way of dealing with security (which is quite opposite to std. Windows way), over a quick guided tour through the GUI in SQL Server Management Studio. Most of the session will however cover how to set security through the use of PowerShell and the Analysis Services Management Objects (AMO). I hope to leave the attendees with a high level of confidence and insight into the ways of the SSAS security model. I also hope to broaden the toolbox and discuss the challenges of those attending my session. For me, I hope to be inspired by some of the great work that is done out there. So please join me on a great May morning in New York to discuss on SSAS Security.

PS: My wife still has no clue, so please keep it quiet :)

Posted in Community, Programming | Tagged , , | Leave a comment

Note to Self: The Key to Success

Is to stop reading blog posts with a Titel like this, and get back to work…

Posted in Personal | Tagged , , | Leave a comment

Hush hush about the #sqlsat380 event…

Spoiler Alert: If you know my wife – hush hush, please let me go through with the surprise :)

New York

I was very thrilled, highly excited and a little surprised to receive the note, that my abstract on Analysis Services Security was accepted for the SQL Saturday #380 in New York City, US. The number of submission were through the roof, so I am very grateful for the extra ordinary opportunity.

I know for a fact, that my wife is not going to see this post, so I can safely shout it out in this blog post. At home however, I am bound to keep my joy tamed, as I am planning to surprise my wife, with a couple of kids-free days in The Big Apple. Currently she has accepted an invitation to a bachelors party on the same Saturday, but I guess she’s going to miss out on that :) But in case you read this and have a connection to my wife, please do keep my little secret safe.

If you haven’t already heard about the concept of SQL Saturday, I suggest you rush of to www.sqlsaturday.com and dive into the numerous opportunities to attend free Microsoft SQL Server training sessions. There is bound to be  some event close to you, in near future. Surely it’s going to be a smashing event in New York end of May.

SQL Saturday #380

Posted in Community | Tagged , , | Leave a comment

TSQL Tuesday #63 – How do you manage security

 

TSQL Tuesday

This month’s T-SQL Tuesday is hosted by Kenneth Fisher (blog | twitter) and the topic is security.

Security is one of those subjects that most DBAs have to deal with regardless of specialty. So as something we all have to work with at some point or another what are some tips you’d like to share? What’s the best security design? You’ve picked up a legacy system and the security is awful, how do you fix it? Any great tools out there you’d like to share? Hate it or love it I’m betting we all have something to say.

As others in the community contribute in their own way, in their field of expertise, I’d like to chip in with some of my own observations and lessons learned through my years of working with Analysis Services (SSAS). Here goes my first #TSQL2SDAY blog post.

Having worked with the security model in SSAS and having presented on the topic a couple of times, this topic is really something I have had my hands on. I have previously posted several blog posts on the topic and this blog post will introduce a Microsoft Security Management Tool named Forefront Identity Manager (FIM) which is a  Tool that enables self-service identity management for business users. Yeah, you heard that right. So how does that fit in with cubes and SSAS?

In every SSAS database there is a Collection of roles. Each role can be assigned specific permissions in terms of cube access (r/w), drill-through, dimension and cell access. The magic link between the SSAS roles and FIM are Active Directory (AD) Groups. Each role can have one-to-many members, which can be specific (local) Users or Security Groups. Through FIM this allows the users provisioning and de-provisioning access through an semi-automated approach.

Personally, I haven’t even scratched the surface of FIM, but for the purpose of letting the business decide who gets access to what, and who doesn’t, it was well worth raising this flag. In the end, the business is happy to feel in control, and you are happy that the business is locked down to what options you expose through the cube. In the end, the mechanism of creating and managing the roles within the cubes, still reside on developer/administrative side, e.g. IT (and not Business).

If you don’t already know about Forefront Identity Manager I urge you to take a look at the capabilities. I bet you’ll be pleasantly surprised. :)

 

Posted in Community, Programming | Tagged , , | 2 Comments