locked
Connecting to Data Source with Per-User Identity RRS feed

  • Question

  • I have successfully created an PerformancePoint Service Application (Secure Store Service also configured) with a Business Intelligence Center. Kerberos is also configured correctly (checked it with Kerbtray -  with connecting with SQL Server Management Studio and also IE).

    I can create a new Datasource in Dashboard Designer and when I use the option "Unattended Account" I can connect to the Data Source (Sql Server Analysis Services 2008 Cube). When I want to connect wit Per-User Identity I get the following error:

    ***************************************

    The user "ZH01\mma" does not have access to the following data source server.  Data source location: http://zhlabsp1.lab.vz.ch/bi/Data Connections for PerformancePoint/3_.000 Data source name: ZHSQ14 Server name: zhsq14  Exception details: Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException: The connection either timed out or was lost. ---> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. ---> System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host     at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)     --- End of inner exception stack trace ---     at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset...    6e4f4dc1-6058-49d1-a7b6-5028e8d12e18

    ..., Int32 size)     at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)     at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ForceRead(Stream stream, Byte[] buffer, Int32 length)     at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ReadHeader()     at Microsoft.AnalysisServices.AdomdClient.DimeReader.ReadRecord()     at Microsoft.AnalysisServices.AdomdClient.TcpStream.GetResponseDataType()     --- End of inner exception stack trace ---     at Microsoft.AnalysisServices.AdomdClient.XmlaClient.EndRequest()     at Microsoft.AnalysisServices.AdomdClient.XmlaClient.CreateSession(ListDictionary properties, Boolean sendNamespaceCompatibility)     at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdCon...    6e4f4dc1-6058-49d1-a7b6-5028e8d12e18

    ...nection.IXmlaClientProviderEx.CreateSession(Boolean sendNamespaceCompatibility)     at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)     at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()     at Microsoft.PerformancePoint.Scorecards.DataSourceProviders.AdomdConnectionPool`1.GetConnection(String connectionString, ConnectionContext connectionCtx, String effectiveUserName, CultureInfo culture, NewConnectionHandler newConnectionHandler, TestConnectionHandler testConnectionHandler)    6e4f4dc1-6058-49d1-a7b6-5028e8d12e18

    ***********************

    What could be the problem?

    Monday, June 28, 2010 3:34 PM

Answers

  • Hello murratore,

    I'm assuming by your description that you are attempting to setup Kerberos with PPS 2010. I've included instructions on the steps needed to get PPS 2010 working with Kerberos:

    1 - Enabled Kerberos on the SP site.
    - Navigate to the Security section and under General Security click on “Specify authentication providers”. Next make sure that you have selected the correct Web Application in the middle right side of Central Administrator. Once you have selected the correct Web Application, click on the “Default” zone. Scroll down the Edit Authentication screen to the IIS Authentication Settings section and choose “Negotiate (Kerberos)”.

    2 - Turn the Claims 2 Windows Service on on the App Server.

    - Navigate to the System Settings and under Servers click on “Manage Services on Server”. The first thing to do is verify that you have selected your App Server at the top of this configuration screen. Second find the Claims to Windows Token Service and make sure it is started.

    3 - HTTP SPN on the WFE (SP App Pool Account).

    - To determine which Service Account is running your SharePoint application pool navigate to the Security section and click on Configure service accounts. In the Credential Management click on the drop down and select the Web Application Pool. The SharePoint WFE web application pool account we need to set the HTTP SPN on is located in the account section.

    4 - MSOLAPSVC.3 SPN on SSAS Service Account.
    - The above SPN needs to be set for the account running the SSAS service.

    5 - HTTP SPN on the PPS Account running the PPS Service in SP (Used just to allow step 6 to be done).

    - Checking the PerformancePoint account can be done if you follow the instructions in Step 3 to get to the Credential Management section and select the drop down to find the PerformancePoint Server. The PerformancePoint application pool account we need to set the HTTP SPN on is located in the account section

    6 - Setup Constrained Delegation from the PPS Service account to the DS.

    - Log into the DC, open up Active Directory Users and Computers and find the PerformancePoint user account. Right click and open up the Properties for the PerformancePoint User account.
    Find the Delegation tab, select the option Trust this user for delegation to specified services only then select Use any authentication protocol. Now we need to add the service to delegate, click on “Add”. In the Add Services screen, select the “Users or Computers…” button. In the Select Users or Computers type in the name of the account running the SSAS Service. Once you find the SSAS account hit OK and the Add Services will show you the list of SPNs set for that account. Select the MSOLAPSvc.3 Service Type and hit OK. By selecting this MSOLAPSvc.3 service you have configured Constrained Delegation from the PerformancePoint service account to the SSAS service account.

    7 - Setup Constrained Delegation on the App Server to the DS.
    - Log into the DC, open up Active Directory Users and Computers and find the App Service machine account. Right click and open up the Properties for the App Service machine account.
    Find the Delegation tab, select the option Trust this user for delegation to specified services only then select Use any authentication protocol. Now we need to add the service to delegate, click on “Add”, then follow the instructions in Step 6 for selecting the correct Service Type.

    The major difference is that in PPS 2010 you have to setup Kerberos Constrained Delegation. Let me know if you have any troubles.

    Thanks,
    Jon Thomas


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, June 29, 2010 12:03 AM

All replies

  • Hello murratore,

    I'm assuming by your description that you are attempting to setup Kerberos with PPS 2010. I've included instructions on the steps needed to get PPS 2010 working with Kerberos:

    1 - Enabled Kerberos on the SP site.
    - Navigate to the Security section and under General Security click on “Specify authentication providers”. Next make sure that you have selected the correct Web Application in the middle right side of Central Administrator. Once you have selected the correct Web Application, click on the “Default” zone. Scroll down the Edit Authentication screen to the IIS Authentication Settings section and choose “Negotiate (Kerberos)”.

    2 - Turn the Claims 2 Windows Service on on the App Server.

    - Navigate to the System Settings and under Servers click on “Manage Services on Server”. The first thing to do is verify that you have selected your App Server at the top of this configuration screen. Second find the Claims to Windows Token Service and make sure it is started.

    3 - HTTP SPN on the WFE (SP App Pool Account).

    - To determine which Service Account is running your SharePoint application pool navigate to the Security section and click on Configure service accounts. In the Credential Management click on the drop down and select the Web Application Pool. The SharePoint WFE web application pool account we need to set the HTTP SPN on is located in the account section.

    4 - MSOLAPSVC.3 SPN on SSAS Service Account.
    - The above SPN needs to be set for the account running the SSAS service.

    5 - HTTP SPN on the PPS Account running the PPS Service in SP (Used just to allow step 6 to be done).

    - Checking the PerformancePoint account can be done if you follow the instructions in Step 3 to get to the Credential Management section and select the drop down to find the PerformancePoint Server. The PerformancePoint application pool account we need to set the HTTP SPN on is located in the account section

    6 - Setup Constrained Delegation from the PPS Service account to the DS.

    - Log into the DC, open up Active Directory Users and Computers and find the PerformancePoint user account. Right click and open up the Properties for the PerformancePoint User account.
    Find the Delegation tab, select the option Trust this user for delegation to specified services only then select Use any authentication protocol. Now we need to add the service to delegate, click on “Add”. In the Add Services screen, select the “Users or Computers…” button. In the Select Users or Computers type in the name of the account running the SSAS Service. Once you find the SSAS account hit OK and the Add Services will show you the list of SPNs set for that account. Select the MSOLAPSvc.3 Service Type and hit OK. By selecting this MSOLAPSvc.3 service you have configured Constrained Delegation from the PerformancePoint service account to the SSAS service account.

    7 - Setup Constrained Delegation on the App Server to the DS.
    - Log into the DC, open up Active Directory Users and Computers and find the App Service machine account. Right click and open up the Properties for the App Service machine account.
    Find the Delegation tab, select the option Trust this user for delegation to specified services only then select Use any authentication protocol. Now we need to add the service to delegate, click on “Add”, then follow the instructions in Step 6 for selecting the correct Service Type.

    The major difference is that in PPS 2010 you have to setup Kerberos Constrained Delegation. Let me know if you have any troubles.

    Thanks,
    Jon Thomas


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, June 29, 2010 12:03 AM
  • Is Kerberos delegation the only option supported?

    I have a Web Server with PPS 2010 (Server1) and my data sources are on various other SQL Servers - 

    Go to my workstation - or server - login to https://mysite/mydashboards/Pages/ppssample.aspx  and I launch the PPS Designer 2010

    Then when I try to create a connection (after I download and run designer), I get an error that I have no access?  This even happens when I try creating a data source to (Server1) which has PPS 2010 and SQL Server 2008 R2 (on the same box).  

    Thoughts?  Is Kerberos a must have?

     

    How does the Unattended Execution Service Account work?

    GUID-PPSUnattendedAccount (PerformancePoint Service Application) - how does this work?  

     

    If when I launch the designer from the server and type LocalHost as the sql server name, I still can't get it to list the databases hosted there.

    Friday, August 6, 2010 8:26 PM
  • If you are connecting to a remote source you could use the unattended account, you would just need to make sure that the account you have configured has the necessary permissions.  If you need to pass the user credentials another option that you could use without having to enable Kerberos would be the CustomData setup.

    If you are running everything in a single server, sandbox, setup then you wouldn't need to enable Kerberos if you want to use the Per User setup.  Simply make sure that you select that option in the authentication area and then go back up to the section to select the server and database.


    Dan English's BI Blog
    Saturday, August 7, 2010 11:10 AM
  • Hi Dan,

     

    Are there any good instructions on how to set up the UnAttended account?  From what I've seen, it is not clera what the textboxes (in UI) mean or how they're supposed to be used.  The PPS UnAttended Account doesn't even have a UserName textbox like the one for XLS Services does.

    also - what's the relationship between the different entries in the UnAttended Account screens?  

    Perhaps this is blog material?

    Monday, August 9, 2010 1:17 PM
  • To setup an unattended account you'll also need to configure Secure Store Service.

    1) Open “Active Directory Users and Computers”
    2) Create a Secure Store service account E.g. mydomain\svcSP2010SecStore. Give it a description of “SharePoint 2010 Secure Store service account”
    3) Create a PerformantPoint unattend service account for accessing data sources. E.g. mydomain\svcSP2010PerfPtUntd. Give it a description of “SharePoint 2010 PerformancePoint Unattended service account
    4) Open the SharePoint Central Administration Web Application
    5) Click “Security” on the Quick Launch bar on the left
    6) Under “General Security”, click “Configure managed accounts”
    7) Click “Register Managed Account”
    8) Enter the domain user name and password created in step 2 above (e.g. mydomain\ svcSP2010SecStore). Optionally “Enable automatic password change”
    9) Click “Application Management” on the Quick Launch bar
    10) Under “Server Applications” click “Manage service applications”
    11) On the ribbon, click “New” and then “Secure Store Service”
    12) Enter “Secure Store Service” in the “Name” field
    13) In the “Application Pool” section, enter a new “Application pool name”. E.g. SecureStoreAppPool
    14) From the “Configurable” dropdown, select the new managed Secure Store service account. E.g. [domain]\svcSP2010SecStore
    15) Once the Secure Store Service has been successfully created click “OK”
    16) Click the “Secure Store Service Application” link. E.g. Secure Store Service
    17) If an error appears (“Before creating a new Secure Store Target Application, you must first generate a new key for this Secure Store Service Application from the ribbon.”), on the ribbon click “Generate New Key”
    18) Enter a new “Pass Phase” and store it in a secure location
    19) Click “OK”
    20) A new database will be added to the instance hosting the SharePoint_Config database (Secure_Store_Service_DB_guid). This database is in Full Recovery mode. Ensure that it is added to the transaction log backup maintenance plan for the instance.
    21) In the “Central Administration” console click “Application Management”
    22) Under “Service Applications” click “Manage service applications”
    23) Click the “Performance Point Service Application”. E.g. PerformancePoint
    24) Click “PerformancePoint Service Application Settings”
    25) Enter an “Unattended Service Account”. E.g. mydomain\svcSP2010PerfPtUntd
    26) Click the check names icon
    27) Enter a password then click “OK”
    28) Click “Application Management” in the Quick Launch bar
    29) Under “Service Applications” click “Configure service application associations”
    30) Click “default” in the “Application Proxy Group” column
    31) Ensure that “PerformancePoint” is selected
    32) Click “OK”

    Tuesday, August 10, 2010 7:34 AM
  • Jon,

    We have followed all the steps which you outlined but are still not connecting..... Any ideas?


    When you hit test data source, we are getting:  

    This action cannot complete because PerformancePoint Services is not configured correctly.


    Thursday, May 12, 2011 4:29 PM
  • Hi Jon,

    I have almost all requiured pre-requisites like shown below as answer to every requirement u mentioned

    we have large sharepoint farm and we have kept one server(BI) for perofrmancepoint

    1 - Enabled Kerberos on the SP site.
    - Navigate to the Security section and under General Security click on “Specify authentication providers”. Next make sure that you have selected the correct Web Application in the middle right side of Central Administrator. Once you have selected the correct Web Application, click on the “Default” zone. Scroll down the Edit Authentication screen to the IIS Authentication Settings section and choose “Negotiate (Kerberos)”.<o:p></o:p>

    Answer: We are done with this step as required<o:p></o:p>

    2 - Turn the Claims 2 Windows Service on on the App Server.<o:p></o:p>

    - Navigate to the System Settings and under Servers click on “Manage Services on Server”. The first thing to do is verify that you have selected your App Server at the top of this configuration screen. Second find the Claims to Windows Token Service and make sure it is started.<o:p></o:p>

    Answer: Its started on BI server: Claims to Windows Token acct- CWTSPVC & SPN registered is - SP/apC2WTS<o:p></o:p>

    3 - HTTP SPN on the WFE (SP App Pool Account).<o:p></o:p>

    - To determine which Service Account is running your SharePoint application pool navigate to the Security section and click on Configure service accounts. In the Credential Management click on the drop down and select the Web Application Pool. The SharePoint WFE web application pool account we need to set the HTTP SPN on is located in the account section.<o:p></o:p>

    Answer: application- www.ap.azcollaboration.com , app pool account – AZAAPVC & SPN registered is - HTTP/www.application.com & HTTP/www<o:p></o:p>

    4 - MSOLAPSVC.3 SPN on SSAS Service Account.
    - The above SPN needs to be set for the account running the SSAS service.<o:p></o:p>

    Answer: Analysis service is running on BI server: account – PSACPVC & SPN registered is -  MSOLAPSvc.3/server:POWERPIVOT        MSOLAPSvc.3/server.labs.local:POWERPIVOT MSOLAPSvc.3/server.labs.local

    MSOLAPSvc.3/server

    5 - HTTP SPN on the PPS Account running the PPS Service in SP (Used just to allow step 6 to be done).<o:p></o:p>

    - Checking the PerformancePoint account can be done if you follow the instructions in Step 3 to get to the Credential Management section and select the drop down to find the PerformancePoint Server. The PerformancePoint application pool account we need to set the HTTP SPN on is located in the account section<o:p></o:p>

    Answer: PPS account – PPSAPVC & SPN registered is -SP/apPerformancePointService---> i think this is where am making mistake because am getting 401 unathorized error in BI(Perofrmancepoint server) server

    6 - Setup Constrained Delegation from the PPS Service account to the DS.<o:p></o:p>

    - Log into the DC, open up Active Directory Users and Computers and find the PerformancePoint user account. Right click and open up the Properties for the PerformancePoint User account.
    Find the Delegation tab, select the option Trust this user for delegation to specified services only then select Use any authentication protocol. Now we need to add the service to delegate, click on “Add”. In the Add Services screen, select the “Users or Computers…” button. In the Select Users or Computers type in the name of the account running the SSAS Service. Once you find the SSAS account hit OK and the Add Services will show you the list of SPNs set for that account. Select the MSOLAPSvc.3 Service Type and hit OK. By selecting this MSOLAPSvc.3 service you have configured Constrained Delegation from the PerformancePoint service account to the SSAS service account.<o:p></o:p>

    Answer: We have set delegation in PPSAPVC account to use services of SQL server acctounts(SPN's) & SQL analysis service acct(4 SPN's of step 4)<o:p></o:p>

    7 - Setup Constrained Delegation on the App Server to the DS.
    - Log into the DC, open up Active Directory Users and Computers and find the App Service machine account. Right click and open up the Properties for the App Service machine account.
    Find the Delegation tab, select the option Trust this user for delegation to specified services only then select Use any authentication protocol. Now we need to add the service to delegate, click on “Add”, then follow the instructions in Step 6 for selecting the correct Service Type.<o:p></o:p>

    Answer:  App Service machine account- i dont understand which account you are referring to :( if you are refering to web application pool acct and i have set delegation in app pool account to use services of SQL and its own SPN's

    After doing this all am getting

    "PerformancePoint Services 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."

    System.Net.WebException: The request failed with HTTP status 401: Unauthorized.

       at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

       at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

       at Microsoft.PerformancePoint.Scorecards.DataSourceProviders.ListService.GetListCollection()

       at Microsoft.PerformancePoint.Scorecards.DataSourceProviders.SpListDataSourceProvider.GetCubeNameInfos()

    when am trying to use per-user identity i will get this error but if i try to use unattended account no issues:(:(

    Any help on this as this has become very critical and i have spent almost a week on this ???


    Friday, November 9, 2012 12:48 PM