Using PerformancePoint 2013 and SSAS roles without Kerberos (configuring EffectiveUserName)

If you’ve spent any time with PerformancePoint, you’re probably at least somewhat familiar with the different authentication options that are available when creating a data source. Any time you create a new data source in PerformancePoint Dashboard Designer, you’re confronted with 3 choices.

  1. Unattended Service Account
  2. Use a stored account
  3. Per-user Identity

clip_image001

With the first two, there is also an option to pass the authenticated users name in the connection string using the CustomData property. I’ve covered how to use that option previously here: http://tavislovell.com/how-to-configure-dynamic-security-in-analysis-services-and-performance-point-2010-without-kerberos/

What most people REALLY want is the ability to create and leverage roles in their Analysis Services cube for different sets of users. If you want different users to authenticate to different roles within your SSAS cube, you’ll need to use the “Per-user Identity” option when creating your data source. The bad news is that the “Per-user Identity” option comes with its own set of obstacles you need to overcome before it works.

The main problem with using “Per-user Identity” is an issue called “The Double Hop” issue. If you are unfamiliar with the double hop issue, I highly suggest you watch the following video where it is explained in very simple terms: http://go.microsoft.com/fwlink/?LinkId=218209, but the short version is simply that by default, credentials can’t be delegated more than one server away (one hop).

There are a few ways to get around the double hop issue. Most commonly, an administrator will configure Kerberos in the environment, which allows the delegation of credentials between computers more than one hop away. This is a great option if you have the appropriate security credentials (I believe you need domain admin rights) and know how to configure Kerberos. For the uninitiated, Kerberos is not an easy setup.

HOWEVER, if configuring Kerberos in your environment sounds more than you’re ready to take on, there’s good news! In PerformancePoint 2013 we can leverage the new “EffectiveUserName” functionality that will allows us to use the “Per-user Identity” option without having to configure Kerberos! AND it’s pretty easy to setup. In order to use the EffectiveUserName functionality we’ll need to accomplish the following:

  1. In SharePoint Central Administration, you need to enable the “use the EffectiveUserName connection string property instead of windows delegation” option for the PerformancePoint service application.
  2. The application pool account that is running PerformancePoint will need to be given admin rights on the SSAS instance you want to connect to.
  3. The “Claims to Windows Token Services” will need to be running in SharePoint Central Administration.
  4. Create a roll in your cube that gives your test user(s) the correct access to the cube….otherwise they’ll just be denied access.
  5. When creating your PerformancePoint data sources, choose the “Per-user Identity” option.

For those still needing a little more guidance, the remainder of this post will walk through accomplishing the 5 requirements above and ensuring that the EffectiveUserName functionality is working in our environment.

STEP 1 - Enable the “Use the EffectiveUserName connection string property instead of Windows delegation” option.

To enable this property, you’ll need to perform the following actions:

  1. Open SharePoint Central Administration
  2. Click on the “Manage service applications” link (under the “Application Management” heading)
  3. Locate and click on the PerformancePoint service application
  4. Click on the “PerformancePoint Service Application Settings” link
  5. Locate the checkbox labeled “Use the EffectiveUserName connection string property instead of Windows delegation” and place a check in it.
  6. Scroll down to the bottom of the screen and click the “OK” button

STEP 2 - grant the PPS application pool account admin rights on the SSAS instance

In this step we need to first find out what application pool the PerformancePoint service application is using, and what account that application pool is running under. Once we know what account is being used, we simply need to add it as an administrator on the SSAS instance we’re trying to connect to. First let’s find what app pool and account is being used by following the steps below:

  1. Open SharePoint Central Administration
  2. Click on the “Manage service applications” link (under the “Application Management” heading)
  3. Locate and highlight (do not click on the actual link/text) the PerformancePoint service application
  4. Click the “Properties” button on the ribbon at the top of the screen
  5. Scroll down to the bottom of the page until you see the “Application Pool” Section
  6. Make note of the application pool being used, we’ll need to remember this name while determining what account this app pool is running as
  7. Click the “Cancel” button at the bottom of the screen

clip_image003

Now that we know what application pool PPS is using, next up we’re going to determine what account the app pool is being run as. Typically we can’t just look in IIS because the actual name of the App pool is probably a GUID rather than the name we saw listed previously. There are a few different ways we can still see which account the app pool is using, by issuing a PowerShell command, or by using Central Administration. To use PowerShell, simply open up a terminal window and issue the following commands.

Add-PSSNapin microsoft.sharepoint.powershell

Get-SPServiceApplicationPool

PowerShell should return the name of each application pool and a related “ProcessAccountName”. It’s the “ProcessAccountName” that you’ll want to add as an administrator to your SSAS instance. If Powershell is a bit too much for you, then you can find the account being used by following these steps in Central Administration.

  1. Open SharePoint Central Administration
  2. Click the “Configure service accounts” link (under the “Security” heading)
  3. Locate the application pool in the first dropdown (*note the application pool name may be prefixed by “Service Application Pool - “)

    clip_image004
  4. Make note of the Account listed at the bottom of the screen
  5. Click the “Cancel” button at the bottom of the screen

Now that we have our account, all we need to do is add it to our SSAS instance as an administrator. To do that complete the following steps:

  1. Open SQL Server Management Studio
  2. Connect to your Alalysis Services (SSAS) instance
  3. In the Object Explorer, right click on the instance and select properties to open the “Analysis Server Properties” window
  4. Select the Security page
  5. Use the “Add” button to add the app pool account
  6. Click “OK”

STEP 3 - Ensure that “Claims to Windows Token Services” (c2wts) is running

If you’re getting errors similar to the following:

“Dashboard Designer could not connect to the specified data source. Verify that either the current user or Unattended Service Account has read permissions to the data source, depending on your security configuration. Also verify that all required connection information is provided and correct.”

Chances are c2wts isn’t running in your environment. Either way, we’ll need it to be running for our EffectiveUserName to work. To ensure c2wts is running, follow these steps:

  1. Open SharePoint Central Administration
  2. Click on the “Application Management” link heading
  3. Click on the “Manage services on server” link
  4. Locate the “Claims to Windows Token Service” service, and ensure that the status is “Started”. If it is not started click “Start” button under.
  5. If you had to start the service, run an iisreset or reboot the server

STEP 4 - Create an SSAS role for your test user

Now that our SharePoint farm and datasource are configured to use the EffectiveUserName, you’ll have to grant users access to your cube by creating a role in the SSAS database and adding your users to that role. In our case we’re going to add the user “contosoo\test” to the role. If you don’t have a test account to use, either create one or have your Active Directory administrator create one. To create a simple role with read access follow these steps:

  1. Open SQL Server Management Studio
  2. Connect to your SSAS instance
  3. Click the “+” next to the SSAS database that you want to give users access to
  4. Right click on the “Roles” folder and select “New Role…”
  5. Select the “Membership” page on the “Create Role” screen
  6. Use the “Add” button to add your users/test user

    clip_image006
  7. Select the “Cubes” page on the “Create Role” screen
  8. Locate the cube you want to grant the user access to and select “Read” for the “Access” Dropdown

    clip_image008
  9. Click the “OK” Button

STEP 5 - Set your PerformancePoint Datasources to use the “Per-user Identity” option

Now that we’ve everything configured, and our roles created, all that’s left to do is set our PerformancePoint data sources to use the “Per-user Identity” option when connecting to our cube. Complete the following steps to configure your data sources:

  1. In SharePoint, open the “PerformancePoint Dashboard Designer”
  2. Note who you are logged into the Dashboard Designer as. This information is displayed at the very bottom left hand corner of the dashboard designer. This account must be in the SSAS role we create earlier in order to be able to authenticate to the cube.

    clip_image009
  3. Create a new “Analysis Services” data source, or open an existing one
  4. Enter the appropriate server, database, and cube information
  5. Choose “Per-user Identity” as the Authentication method

    clip_image010
  6. Click the “Test Data Source” button

If all has gone well, you should see that your connection has been successful.

clip_image012

That’s really all there is to it. Now you can add additional SSAS roles and have all your AD users/group(s) authenticate into the proper role when they visit your PPS dashboard. Quite an easy setup when compared to Kerberos. Good luck and happy reporting!

Microsoft Product Name Word Reuse

In the Microsoft BI space, there are several words that seem to get reused quite a bit for product/technology names. SharePoint, Power Point, Performance Point, Power Pivot, Pivot Viewer. I've heard multiple people (myself included) use the wrong name when talking about a particular technology, due in part to the names being so similar. I decided to make a venn diagram to demonstrate the reuse of words, as well as shed a little light on what each technology does...mainly because I thought it would be funny....and also because I am a nerd. Enjoy.

clip_image001[4]

Get your link on:

Pivot Viewer - Silverlight BI control

Power Pivot - Excel/SharePoint add in for data analysis

Power Point - Presentation/Slide software

SharePoint - Collaboration

Performance Point - BI Dashboarding

 

How To Import SharePoint List Data Into A Database Table Using SSIS

For a variety of reasons you might need to import data that currently resides in a SharePoint list into a relational database table. Lucky for us there are some SharePoint Web Services we can leverage as data sources. This example will walk through pulling some example data from a SharePoint list I created and storing it in an SQL Server Table.

This walkthrough assumes you have some hands on experience/ knowledge of SQL Server Integration Services (SSIS).

In order to import our SharePoint list data we're going to leverage the "SharePoint List Adapters" for SSIS. You'll need to download them at http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652. Once you've installed "SharePointListAdaptersSetup.msi" you'll probably need to add the new source and destination objects to your toolbox. Start off by making sure you're on the "Data Flow Tab" and then right click the "Choose Items..." option as shown below.

clip_image001[4]

In the new window that comes up, you'll want to select the "SSIS Data Flow Items" tab, and then check "SharePoint List Source" then click OK.

clip_image003[4]

Now that we've added our connection objects to the Toolbox, all we need to do is drag the "SharePoint List Source" object onto our Data Flow canvas and then configure it to point to our list.

clip_image004[4]

Double click on the "SharePoint List Source" object. You'll want to configure the "Site URL" and "Site List Name" of the SharePoint list you're wanting to import (shown below highlighted in yellow).

clip_image006[4]

That's pretty much it. You'll probably also need to convert your column values from Unicode using the SSIS conversion transformation object before directing them to your destination data source. Overall though, pretty simple if you're familiar with SSIS. A quick proof of concept using a list of video games I made worked perfectly the first time.

From SharePoint List:

clip_image007[4]

To database table:

clip_image008[4]

almost effortlessly. Hope this helps.

 

I’ll be speaking at "SharePoint Cincy – 2011"

I'll be speaking at the "SharePoint Cincy - 2011" conference on March 18th. If you're looking to dive into the world of SharePoint Dashboarding but aren't sure where to start, here's your chance!

The event website is here: http://www.sharepointcincy.com

Even though they failed to post my bio under the speakers page (....sigh) you'll still find me mentioned on the Sessions page under the BI track here: http://www.sharepointcincy.com/session-information/13

Or you can just read my session abstract below.

Subject: Implementing Dashboard Solutions Using PerformancePoint & SQL Reporting Services in SharePoint 2010
Presenter: Tavis Lovell – Ascendum, Senior Consultant

Learn how to build Reports, KPIs, Scorecards and Dashboards for deployment to SharePoint 2010 using PerformancePoint Services (PPS) and SQL Server Reporting Services (SSRS). Attendees will gain familiarity with PerformancePoint Dashboard Designer and SQL Servers Business Intelligence Development Studio (BIDS) and how they can be leveraged within an organization to meet strategic, tactical, and operational goals.

Hope to see you there.

 

How to update a SharePoint List using JavaScript(jQuery)

I recently had a situation where I needed to update a SharePoint list value directly from the lists display form. Probably not something most people find themselves needing to do. However it’s not totally uncommon to have a need to update a list record outside of the lists standard edit form. Typically you need to write some CAML and make a jQuery ajax call, however turns out there is a somewhat easier way thanks to the SPServices jQuery library. In my example I was able update my needed row using the following call:

             $().SPServices({

             operation: "UpdateListItems",

             listName: "MyList",

             ID: RowID,

             valuepairs: [["IsDeleted", true]],

             completefunc: function (xData, Status) {alert(Status);}

             });

In this case I was updating the column “IsDeleted” to have a value of true in the list “MyList”. All you have to do is pass in the ID of the row you want to update. Very easy to use, and no CAML required. In this example I simply have my callback throwing an alert to the page on the status of the call once it’s complete. You can download the SPServices library on CodePlex here: http://spservices.codeplex.com/. Simply unzip the downloaded file and add a reference to it in your page. You also need to have the core jQuery file referenced in your page as well, you can find that here: http://jquery.com/. Be sure to check out the documentation of other functions in the SPServices library at http://spservices.codeplex.com/documentation.

Hope this helps!

 

I’m presenting at the Cincinnati SharePoint User Group

I'm presenting at the Cincinnati SharePoint Users Group on Jan 6th 2011. If you're in the area, you should come to the meeting. Link and summary below:

https://cincyspug.securespsites.com/default.aspx

SharePoint 2010 and Business Intelligence

You’ve got data, probably lots and lots of data across multiple systems. Your end users however want timely access to information to make effective decisions and to monitor and analyze performance. Join us for a high level overview and demonstration of what SharePoint 2010 has to offer end users who have Business Intelligence needs. Topics covered will be:

• Excel Services

• PowerPivot

• Visio Services

• Reporting Services (SharePoint Integrated Mode)

• Performance Point Dashboards

Presenter: Tavis Lovell
Tavis Lovell is a Practice consultant with Ascendum Solutions, specializing in SQL Server (Integration Services, Reporting Services, Analysis Services) and database design related to transactional or BI implementations on the Microsoft stack. Tavis is also involved with using SharePoint 2010 for content delivery as it relates to Business Intelligence. When he’s not trying to help the world make better decisions, he spends his time either pretending he’s Jimi Hendrix, or falling down the sides of mountains on a snowboard.