Share via

How to: Set Up DSO in Analysis Services

  • Article

New: 14 April 2006

The Decision Support Objects (DSO) library included with Microsoft SQL Server 2005 Analysis Services (SSAS) provides compatibility with SQL Server 2000 Analysis Services applications. The DSO library enables programs written for Analysis Services in SQL Server 2000 to work with Analysis Services in SQL Server 2005.

DSO is installed by the typical SQL Server 2005 installation process when the Analysis Services option is selected. No special options are required.

To run DSO applications from a client machine, you need to first install DSO. DSO can be installed as part of the Microsoft SQL Server 2005 Backward Compatibility Components, which is one of a set of redistributable packages included in SQL Server 2005 Feature Pack. To download the Microsoft SQL Server 2005 Backward Compatibility Components, go to Feature Pack for SQL Server 2005. To install msCoName ssVersion2005 Backward Compatibility Components, you must have the following packages:

  • Microsoft Core XML Services (MSXML) 6.0
  • Microsoft SQL Server Native Client
  • Microsoft SQL Server 2005 Management Objects Collection

To be able to use DSO in SQL Server 2005 Analysis Services, you must do the following:

  • Have full access to a copy of the SQL Server 2000 Analysis Services repository for which the DSO application is being used. The repository must be an up-to-date copy. If DSO application creates the working database and all other objects then the repository could be any valid repository, even the sample repository that is included with SQL Server 2000 Analysis Services.
  • Edit the Configuration Settings for SQL Server 2005 Analysis Services so that the repository is configured to be used from DSO in SQL Server 2005 Analysis Services.
  • Migrate the database from SQL Server 2000 Analysis Services to SQL Server 2005 Analysis Services.

Obtaining Full Access to SQL Server 2000 Analysis Services Repository

The SQL Server 2000 Analysis Services repository can be either a Microsoft Access database (.mdb) or a SQL database (.sql). If the SQL Server 2000 Analysis Services repository is a Microsoft Access database, then it has to be copied to the server where SQL Server 2005 Analysis Services is running, as explained in the following procedure; if the SQL Server 2000 Analysis Services repository is a SQL database, see Editing Configuration Settings for SQL Server 2005 Analysis Services later in this topic.

To copy a Microsoft Access database repository

  1. Locate the file Msmdrep.mdb in the existing program files (Bin) folder in SQL Server 2000 Analysis Services. The location of this folder depends on the installation path for the instance of SQL Server 2000 Analysis Services, but it is typically located as follows: C:\Program Files\Microsoft Analysis Services\ Bin

  2. Be sure that no DSO applications, including Analysis Manager, are using the file before you copy the file.

    If the Msmdrep.mdb file is open, the copy might fail or generate a corrupted file.

  3. Copy Msmdrep.mdb in the existing program files (Bin) folder in SQL Server 2000 Analysis Services to the program files (Bin) folder for the instance of SQL Server 2005 Analysis Services to which you want to add DSO functionality.

Editing Configuration Settings for SQL Server 2005 Analysis Services

Change settings in the Analysis Services configuration file (Msmdsrv.ini) to enable DSO to work correctly in SQL Server 2005 Analysis Services.

To edit the Analysis Services configuration file

  1. Open the Analysis Services configuration file (Msmdsrv.ini) in any text editor, such as Notepad. Depending on the installation path for SQL Server 2005 Analysis Services, this file is typically in the following location: C:\Program Files\Microsoft SQL Server\MSQL.n\OLAP\Config\Msmdsrv.ini, where n represents the installation sequence number.

  2. Search for the <RepositoryConnectionString/> entry that is enclosed in <DSO> statement. Typically the structure will look like the following sample:

    <DSO>

    <RemoteRepositoryConnectionString/>

    <RepositoryConnectionString/>

    <RemoteLocksDirectory/>

    <LocksDirectory/>

    </DSO>

  3. Add the appropriate connection string to the <ConfigSettings><DSO><RepositoryConnectionString/> entry. For example, for a .mdb file repository:

    <RepositoryConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program files\Microsoft SQL Server\MSSQL.n\OLAP\DSO\msmdrep.mdb;Persist Security Info=False</RepositoryConnectionString>

    For a .sql repository:

    <RepositoryConnectionString>Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=AS2000_Repository;Data Source=sql-svr</RepositoryConnectionString>

  4. Specify a value for the <ConfigSettings><DSO><LocksDirectory/> entry in the Msmdsrv.ini configuration file. For example:

    <LocksDirectory>C:\Program Files\Microsoft SQL Server\MSSQL.n\OLAP\DSO\LocksDirectory</LocksDirectory>

Note

Parts of the file content are encrypted after the file is saved.

Enabling Remote Administration for SQL Server 2005 Analysis Services (Optionally)

This step is only necessary if you will use DSO to remotely administer your instance of SQL Server 2005 Analysis Services. If you plan to remotely manage the instance of Analysis Services, you have to create a share on the server pointing to the Bin folder (typically, C:\Program Files\Microsoft SQL Server\MSQL.n\OLAP\Bin). Follow these steps to create the share.

To create a share to enable remote administration using DSO

  1. In Windows Explorer or My Computer, right-click the Bin folder and then click Properties.

  2. In the Properties dialog box, on the Sharing tab, select Share this folder.

  3. In the Share name box, type a name for the share, for example MSOLAPRepository$.

  4. Click Permissions to specify share permissions for the shared folder.

ms403370.security(en-US,SQL.90).gifSecurity Note:
Opening a share creates an access risk. Provide full control option only to those users who will be administering the instance of Analysis Services. Other users should not have permission to access the share.

After you create the share, change the Msmdsrv.ini configuration file to specify the remote connection string and lock directory values.

To change the configuration file to specify remote connection string

  1. Add the appropriate connection string to the <ConfigSettings><DSO><RemoteRepositoryConnectionString/> entry in the Msmdsrv.ini configuration file. For example:

    <RemoteRepositoryConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\yourmachine\MsOLAPRepository$\msmdrep.mdb;Persist Security Info=False</RemoteRepositoryConnectionString>

  2. Specify a value for the <ConfigSettings><DSO><RemoteLocksDirectory/> entry in the Msmdsrv.ini configuration file. For example:

    <RemoteLocksDirectory>\\yourmachine\MsOLAPRepository$</RemoteLocksDirectory>

See Also

Concepts

Setting Up Decision Support Objects (DSO) in Analysis Services

Help and Information

Getting SQL Server 2005 Assistance