SQL Server Performance

OLAP Connectivity from Pivot Table in Excel

Discussion in 'SQL Server 2005 Analysis Services' started by sqldev, Sep 24, 2007.

  1. sqldev New Member

    Hi,
    We are using Pivot Table object in MS-Excel 2003 to display the reports using OLAP objects like Cubes. It connects to an OLAP Server and database to fetch the data. When we try to refresh data in the pivot table, it internally checks whether the windows user from the current system has required access rights. Otherwise, it gives "Either the user, DomainUser, does not have access to the OLAP Database, or the database does not exist" error message and We are prompted to provide OLAP server credentials again.
    Now, we need to provide appropriate access rights to the windows user in OLAP Server using Computer Management utility. Here, we have different SQL Server groups and we tried including the user name in these groups and when we refresh the pivot table, still the problem persists. It works well, once we include the user name in "Administrators" group. Due to security reasons in the database server, we cannot add all the report viewers into this group. Can you tell us the alternative solution to our requirement? thank you.
    Regards,
    Deva
  2. satya Moderator

    Security credentials are essential once your OLAP data source becomes accessible over HTTPS because your data source is now potentially available to anyone who can browse your Web site. Analysis Services implements cube security roles based on Windows user accounts. Basically, you create one or more local Windows NT user accounts on the server and assign them to a SQL Server database role. You then assign the database role to a cube role and assign it to the specific cube. The cube role essentially inherits the Windows NT user ID and password credentials. You can then add the user ID and password credentials to the OLAP data source connection string.
    This cube must be assigned to a role that corresponds to the credentials in the OLAP data source connection string.

Share This Page