Q. How do I connect to my OLAP data source?
A. Dundas OLAP Services uses Microsoft's ADOMD/ADOMD.NET, which are the technologies provided by Microsoft SQL Server Analysis Services for client applications to access the database. The first step to allow the Dundas controls to connect successfully is to install the correct data providers.
If you have installed SQL Server onto your machine, you may not need the downloads listed below but often a component is missing. If you do not have all of the necessary providers installed, or are pointing to them incorrectly in your connection string, various exceptions can appear such as:
Figure 1 A possible exception.
- "The specified module could not be found."
- "The handle is invalid."
- "The parameter is incorrect."
- "Errors in the metadata manager."
- "A connection cannot be made. Ensure that the server is running."
- "An unexpected internal error has occurred."
If the problem is an issue with access security and not your data providers, other exceptions can appear, including:
- "Unspecified error."
- "Either the user, Domain\User, does not have access to the Database Name database, or the database does not exist."
- "An existing connection was forcibly closed by the remote host."
Finding the Right Data Provider
There are two steps to accessing the correct data provider for your data source. One is to ensure that it is installed on the web server machine (for ASP.NET applications) or the desktop client machines (for Windows Forms applications). Secondly, your access to the data provider can differ slightly depending on whether you use the Dundas-supplied data provider wrapper for ADOMD or for ADOMD.NET.
SQL Server 2000 Analysis Services, and Offline or Local Cube Files
Drivers: OLE DB drivers will be needed to connect to these data sources, which go by the name Microsoft SQL Server 2000 PivotTable Services. PivotTable Services can be downloaded here.
Dundas OLAP Data Provider: You can use either our AdomdDataProvider or AdomdNetDataProvider controls: both ADOMD and ADOMD.NET will end up connecting using the same OLE DB driver. (You will also need to install ADOMD.NET if you want to use our AdomdNetDataProvider—see below for links.)
SQL Server 2005 Analysis Services
Drivers: If you are connecting to Microsoft SQL Server 2005, it is most efficient to install and use ADOMD.NET. If you use Visual Studio 2005 or newer, download ADOMD.NET here, or for older versions of Visual Studio download ADOMD.NET 8.0 along with the hotfix here.
You can also choose to use the OLE DB (COM) Provider for Analysis Services 2005. If you do, be sure to install the pre-requisite Microsoft Core XML Services.
Dundas OLAP Data Provider: Use our AdomdNetDataProvider control to use ADOMD.NET to connect to your database. Use the AdomdDataProvider if you choose to use the OLE DB COM drivers.
- Dundas OLAP Samples
The best way to get into our product and learn how to use it is to run our installed samples. Our samples use an offline cube file and the AdomdNetDataProvider, which means you need both Microsoft SQL Server 2000 PivotTable Services and ADOMD.NET installed on your system. See above for details.
For both ADOMD and ADOMD.NET, the connection string parameters are listed in the MSDN Library.
- ADOMD: An example of a connection string using ADOMD is:
Data Source=machinename; Provider=MSOLAP; Initial Catalog=databasename;
If you have multiple OLE DB providers installed (for example both SQL Server 2000 PivotTable Services and the OLE DB provider for Analysis Services 2005) you may want to specify which provider to use. When connecting to SQL Server 2000, you can specify "Provider=MSOLAP.2", and when connecting to SQL Server 2005 using OLE DB providers say "Provider=MSOLAP.3".
- ADOMD.NET: In ADOMD.NET, you do not need to specify a provider. The connection string can be of the form:
Data Source=machinename; Initial Catalog=databasename;
If you are using the AdomdNetDataProvider along with OLE DB providers and need help, see above for a description of specifying a "Provider" ADOMD parameter.
Where to manage Roles in SQL Server Management Studio 2005.
Even with the correct data providers and connection strings, you can still get an exception when trying to connect. Remember that Analysis Services uses Windows Authentication and requires you to grant access to the Windows user account used by the process that is trying to access the database. You can do this in SQL Server Management Studio (or Analysis Manager in SQL 2000) by adding or modifying a database Role and changing the membership settings. Roles also allow you to grant and deny access to specific schema elements and cell data for specific users: see "Granting User Access" in SQL Server Books Online.
In Windows Forms, the user account that should be granted access in the database is the account that the user is logged into Windows with when running the desktop application.
In ASP.NET, the user account that ASP.NET runs as on the web server must be granted access. By default on Windows XP, this is the local account "ASPNET", and on Windows Server 2003 or Windows Vista, this is the "IIS_WPG" group or "NETWORK SERVICE" account. This can normally only work, however, if the database is on the same machine as the web server, otherwise see below.
You can use impersonation to change the identity of the ASP.NET process. For example, you can use the authenticated Windows credentials supplied by individual users through their web browser, or a specific identity you specify, using either the Web.Config file or code. For more information, see Q306158 on Microsoft's support site. Note: When using impersonation, be sure to set the UseImpersonation property to true on your AdomdDataProvider or AdomdNetDataProvider.
Another option in IIS6 or above to change the account is to change the identity of the application pool used for the web site.
For a more general overview of connecting to Analysis Services from ASP.NET, see "How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0" in the MSDN Library, keeping in mind that only Windows Authentication can be used to directly connect to Analysis Services.
If the Analysis Services database is on a separate machine from the web server or the desktop application, it is best if both operate within a Windows domain and a domain account is used when running OLAP Services, otherwise you must create a local account on both the client machine or web server machine and the database server machine with identical names and passwords. This account can then be granted access in Analysis Services and used since it is recognized by both machines.
One final option to consider for authentication is to use HTTP access to Analysis Services. This is more complicated to configure but lets you extend access to the database outside of your local intranet, and use your choice of authentication to connect from your application, meaning you are not limited to Windows Authentication. This is possible because IIS acts as a mediary between the application and the database, which also makes SSL (HTTPS) available as a means of securing your data if you have enabled it. (As one example for configuration, you could use basic authentication with IIS securely over HTTPS so that a user name and password could be specified in your connection string.) For details, see Microsoft's articles: