Error: Unable to get list of drives

Resolving The Problem

Install the SQL Server Client tools (if this has not already been done).
NB For SQL Server 2005 you need to have the full SQL Client installed (comes with SQL Express) as the Native Client does not install all of the required .dll files.
For SQL Server 2008 full client tools required.

Once the SQL Server Client tools are installed, ensure that the Contributor user account used to connect to SQL Server has EXEC permissions to the following Extended Stored Procedures in the Masters Database:

xp_availablemedia
xp_subdirs

Steps:

1. Open SQL Server Enterprise Manager.
2. Expand the Master database and select users.
3. Right-click the user that is used by Contributor to connect to the database, and select 'Properties'.
4. On the 'General' tab, select 'Permissions'.
5. Select the check box in the EXEC column for xp_availablemedia and xp_subdirs.

In SQL Server 2005, you will find these extended stored procedures in Databases\System Databases\master\Programmability\Extended Stored Procedures\System Extended Stored Procedures.

IBM Cognos Planning uses a DMO (Distributed Management Objects) method that calls xp_availablemedia to browse drives, and with SQL 2005, the user account needs to be a member of the SysAdmin role; or to have permissions to xp_availablemedia and xp_subdirs, to perform this action.

For 2008 from the EP 8.4.1 Install Guide (omitted from C10.1 Install Guide):

In addition to the client tools, you need to install the Microsoft® SQL Server 2005 Backward
Compatibility Components.
Tip: On the Microsoft download Web site, search for SQL Server Feature Pack, and then sort by
Release Date to find the latest version.
Required-
You need to install the following Microsoft SQL Server 2005 Backward Compatibility Component:
● SQL Distributed Management Objects (SQL-DMO)