Error message when you try to build an OLAP cube in a deployment of Project Server 2003 that uses SQL Server 2005 Analysis Services: "Could not connect to the OLAP server specified"

Consider the following scenario. You have a single server deployment, a server farm deployment, or a 3-tier configuration of Microsoft Project Server 2003. You configure the single server deployment, the server farm deployment, or a 3-tier configuration of Project Server 2003 to use Microsoft SQL Server 2005 Analysis Services. You log on to Microsoft Office Project Web Access 2003, and then try to build an online analytical processing (OLAP) cube. However, the operation is not completed successfully. Additionally, you receive an error message that resembles the following error message:

This problem was first fixed in a hotfix that is now contained in a service pack. If you installed the latest Project Server 2003 service pack, you do not have to install the hotfix.

Service pack information

This problem is corrected in Project Server 2003 Service Pack 3. To resolve this problem, obtain the latest service pack for Project Server 2003. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

Hotfix information

How to obtain the hotfix

This issue is fixed in the Project Server 2003 post-Service Pack 2a Hotfix Package that is dated June 16, 2006. For more information, click the following article number to view the article in the Microsoft Knowledge Base: Important After you apply the hotfix, follow these steps. You must perform the following procedure before you can successfully build an OLAP in a deployment of Project Server 2003 that uses SQL Server 2005 Analysis Services.

1. On the computer that is running SQL Server 2005 Analysis Services, follow these steps:
   1. Install Microsoft SQL Server 2005 Service Pack 1 (SP1) or a later service pack. For more information about how to obtain the latest service pack for SQL Server 2005, click the following article number to view the article in the Microsoft Knowledge Base:
      913089  (http://support.microsoft.com/kb/913089/ ) How to obtain the latest service pack for SQL Server 2005
   2. Make sure that you are a member of the Server role on the Analysis Server. To add members to the server role for an instance of Analysis Services, follow these steps:
      Note Members of the server role in an instance of SQL Server 2005 Analysis Services have unrestricted access to all Analysis Services objects and data in that instance. A member of the Analysis Services server role can add Microsoft Windows users and groups to the Analysis Services server role.
      1. In SQL Server Management Studio, connect to the instance of Analysis Services, right-click the instance name in Object Explorer, and then click Properties.
      2. Click Security in the Select a Page pane, and then click Add to add one or more Windows users or groups to the server role.
2. Use one of the following methods, as appropriate for your situation:
   • If you have a single server deployment of Project Server 2003 in which Project Server 2003, SQL Server 2005, and SQL Server 2005 Analysis Services are installed on one computer, go to step 3. You do not have to perform the procedure in step 2.
   • If you have a server farm deployment of Project Server 2003, follow these steps:
     1. Start Microsoft Internet Explorer, and then visit the following Microsoft Web site:
        http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en (http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en)
     2. On the computer or computers that are running the Project Server Views Notification Service, download and then install the following programs.
        
        Note Install these programs in the order in which they are presented.
        • Microsoft .NET Framework 2.0
          
          Note Install the .NET Framework 2.0 only if it is not already installed on the computer.
        • Microsoft Core XML Services (MSXML) 6.0
        • Microsoft SQL Server Native Client
        • Microsoft SQL Server 2005 Management Objects Collection
        • Microsoft SQL Server 2005 Backward Compatibility Components
          
          Note When you install SQL Server 2005 Backward Compatibility Components, you have to install only Decision Support Objects (DSO). You do not have to install the other components.
        • Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider
3. On the computer that is running SQL Server 2005 Analysis Services, follow these steps:
   Note This includes SQL Server 2005 Analysis Services that are running remotely in a 3-tier configuration.
   1. Create a subfolder that is named DSO9 in the following folder:
      C:\Program Files\Microsoft Sql Server\Mssql.Number\OLAP
      Note Mssql.Number is the folder in which the SQL Server 2005 Analysis Services files are stored.
   2. Copy the Msmdrep.mdb file from a computer that is running a fully patched SQL Server 2000 Analysis Services to the DSO9 folder on the computer that is running SQL Server 2005 Analysis Services.
   3. Share the DSO9 folder, and then specify MSOLAPRepository$ as the share name for the shared folder.
   4. Assign Read access and Write access for the shared folder. Do this for the Local Administrators group or for any account that Project Server 2003 will use to connect to Analysis Services for processing.
      
      The account that accesses Analysis Services from Project Server 2003 must be granted the appropriate permissions on the Analysis Services instance.
   5. Open the Msmdsrv.ini file, and then replace the contents of the <DSO> tag with the following code:

      <DSO>
      <RemoteRepositoryConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\ComputerName\MSOLAPRepository$\msmdrep.mdb;Persist Security Info=False</RemoteRepositoryConnectionString>
      <RepositoryConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft SQL Server\MSSQL.Number\OLAP\DSO9\msmdrep.mdb;Persist Security Info=False</RepositoryConnectionString>
      <RemoteLocksDirectory>\\ComputerName\MSOLAPRepository$</RemoteLocksDirectory>
      <LocksDirectory>C:\Program Files\Microsoft SQL Server\MSSQL.Number\OLAP\DSO9</LocksDirectory>
      </DSO>
      

      Note The Msmdsrv.ini file is located in the Path\Config folder. Make sure that you replace ComputerName in the code with the name of the computer that is running SQL Server 2005 Analysis Services.
      
      The first time that you save the Msmdsrv.ini file, SQL Server 2005 Analysis Services encrypts the "ConnectionString" information. After this occurs, you cannot read the information when you open the file again. Therefore, you should keep a copy of the code in case you need it in the future.
   6. Stop and then restart the SQL Server 2005 Analysis Services service.
4. Log on to Project Web Access 2003, and then build an OLAP cube. When you build the OLAP cube, specify the computer that is running SQL Server 2005 Analysis Services on the Update resource tables and OLAP cube page.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

This problem was first corrected in Project Server 2003 Service Pack 3.

For more information about issues that occur when you use SQL Server 2005 as the database server for Project Server 2003, click the following article numbers to view the articles in the Microsoft Knowledge Base: For more information about how to set up DSO objects in SQL Server 2005 Analysis Services, visit the following Microsoft Web site: For more information about SQL Server 2005 Service Pack 1, click the following article numbers to view the articles in the Microsoft Knowledge Base: