#ProjectServer 2010 #Excel / Excel Services Report Authentication #PS2010 #EPM #SharePoint #SP2010

July 25, 2011 at 12:07 pm | Posted in Administration, Functionality, Installation | 11 Comments
Tags: , ,

Project Server 2010 uses Excel Services as the primary reporting technology, this post gives an overview on how users authenticate to each data source along with the errors seen if users / accounts don’t have access. There are two different data sources by default, the Reporting database and the OLAP Cube.

Authentication In Excel Client

When creating or editing Excel Services reports the credentials of the logged on user who launched Excel will be used to authenticate the user against the data source. Firstly we will look at the Reporting database reports. To read data from a SQL database the user will need Data Reader (db_datareader) permission against the database, in this case the PWA_Reporting database. The recommended approach from Microsoft is to create a new Active Directory Security Group for Report Authors and add all users who create / edit reports to the this group. This group is then added to the PWA_Reporting database with data reader permissions, this saves adding users directly to the database. Excel will connect directly to the PWA_Reporting database with the logged on users credentials, the logged on user must be a part of the Reporting Authors AD group otherwise they will just see a SQL Server login box in Excel unless they have access directly to the SQL server / database.

image

When editing or creating Excel reports that use the OLAP Cube data, the logged on users credentials are used to authenticate at the data source. The access to the OLAP Cube data is controlled differently to the PWA_Reporting database. To access the OLAP Cube data the logged on user needs to be a member of the ProjectServerViewOlapDataRole against the Cube as shown below:

image

All users that are members of a Project Server security group that allows “View OLAP Data” will be added to the ProjectServerViewOlapDataRole role when the Project Server cube is built. Without the “View OLAP Data” permission allowed, the user will not be able to create or edit OLAP reports in Excel. If the user doesn’t have permission to the cube they will see this login box:

image

Authentication In Excel Services

Viewing Reports in Excel Services will authenticate users using the ProjectServerApplication Secure Store Service App. This is the same for both data sources, when the ProjectServerApplication target application is created there is a members field, this is where you allow users access. Microsoft recommends to create another Active Directory Security group for Report Viewers, then add this group in the members field as shown below:

image

All users who need to view the reports need to be added to the Report Viewers AD group. Once you have created the ProjectServerApplication target app, you then need to set the credentials of the user account that will be used to authenticate against each data source, this is shown below:

image

This account will need data reader access to the PWA_Reporting database and at least the ProjectServerViewOlapDataRole against the OLAP cube. When users refresh the Excel Services reports in the web, firstly it will check that they are a member of the ProjectServerApplication target app then the data source will be accessed using the credentials set for the ProjectServerApplication target app. If the account used for the ProjectServerApplication doesn’t have access to the data sources you will see the errors below:

Excel Services Error:

An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh:

image

The same error is seen for each data source apart from the connection name.

ULS log error:

ConnectionManager.GetConnection: Caught an exception: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException: Exception of type ‘Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException’ was thrown.     at Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInterop.InitConnection()     at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.<>c__DisplayClass1.<CreateConnection>b__0()     at Microsoft.Office.Excel.Server.Credentials.TryExecuteImpersonated(WindowsIdentity wi, ExecuteImpersonatedMethod method, Boolean dispose)     at Microsoft.Office.Excel.Server.Credentials.TryExecuteImpersonated.

Advertisements

11 Comments »

RSS feed for comments on this post. TrackBack URI

  1. Paul,

    Great post! I’m trying to understand the whole MOPS to SSAS integration; I’ve completed an upgrade from 2007 to 2010 and in inspecting the ProjectServerViewOlapDataRole on the SSAS instance, I find that all three permissions for that role are checked. Of most pressing concern is that inclusive in the three permissions is Full Control. Because I’m a user in MOPS I was automatically added to the role through the MOPS cube configuration (nature of MOPS processes). By virtue of the permissions settings in SSAS for the role however, I can connect to the SSAS instance as me and delete/alter cubes and dimensions, as could I would assume any MOPS user. Can I assume that something is amiss here from a security perspective?

    • Hi Scott,

      Thanks for the feedback. The behaviour you see is as expected and I can replicate this with a “normal user” that has only the ProjecServerViewOlapDataRole against a cube in SSAS. Most users will not be able to access SSAS and delete/alter cube as they will not have access to SQL Server Management Studio. This should be the same in Project Server 2007 and Project Server 2010.

      Thanks

      Paul

      • Hello,
        Just wanted to see if you’ve had experience with this issue, and know where we can find a solution (there doesn’t seem to be one on TechNet):

        We can open sample reports online using Excel services, but when we try to open in Excel locally, we cannot. We have even tried making sure that the user we are logged on with has the rights to access the Reporting DB directly to read from it, using an excel instance in hte same domain as the reporting database, but this does not work.
        It occurs when trying to open both Sample Reports and report Templates. The Sample Reports give me the following error:
        “An error occurred while accessing application id ProjectServerApplication from Secure Store Service. The following connections failed to refresh:

        Project Server – Simple Projects List”
        After this error, a blank report opens in the browser window. I also receive the following error when trying to open a report Template:
        “[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.”
        Clicking OK opens the SQL server login window (within Excel 2010). Simply clicking OK again (with the correct SQL server machine name and the Trusted Connections option checked) gives me this error message:
        “Connection failed:
        SQLState: ‘08001’
        SQL Server Error: 17
        [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.”
        Deselecting the Trusted Connection option and trying to login using the Farm Administrator account gives me the same error message.
        http://social.technet.microsoft.com/Forums/da-DK/projserv2010setup/thread/f3457e2a-411c-40cf-a4c0-f7c222dc6c6c

        Thanks in advance for your help!
        Kevin

      • Hi Kevin,

        From a client machine that is experiencing this issue can you create an ODBC connection to the Project Server Reporting database to test if the user has access to the database? If this doesn’t work, add the user to the Project Server Reporting database with db_datareader access then re-test the ODBC connection and the reports in Excel.

        Thanks

        Paul

      • We tried this, and it does not work. Same error occurs.
        Any other ideas?

      • So this user is unable to to see the Project Server Reporting database from an ODBC connection or Excel when there account is added directly to the SQL server with db_datareader access to the Reporting database? From the client machine can you ping the SQL server, does this resolve?

      • We even tried this, but still the same error:
        http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx

      • HI Paul,
        We even tried setting up a local account on the database server, instead of going through AD, and we got the same results.

      • Hi Kevin,

        Can you test from a client machine using the farm service account? Did you provision PWA using the actual SQL server name, an SQL Alias name or a DNS Alias for the SQL server? When attempting to connect from Excel, what shows up in the SQL logs?

  2. HI Paul,
    The only way we can get the data without errors is to access the PWA server remotely, and access the data with Excel or ODBC directly on the server.
    Its a not a firewall problem: on the fact that the doamin server runs Project Server and SQL Database on the SAME machine makes it a very unlikely a Firewall related problem. Server is at same Internal environment along with our Exchange server and the IPsec VPN link we all use from our office allows ANY traffic.
    Any more ideas?

    • Hi Kevin,

      So from a client machine using Excel you can’t access the Project Server Reporting database or the OLAP cube, is this correct? If so, do the connection details have the correct SQL server? Is this the SQL server name, DNS alias or SQL alias?

      Thanks
      Paul


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: