Admdconnection connection string and analysisservice Server Configuration

From: http://hi.baidu.com/zpylh_hust/blog/item/8d4ac68b85498b789e2fb496.html

Adomdconnection object:

This connection and ADO. net is very similar, mainly responsible for connecting to the target library. but there is a difference, that is, from this object, you can obtain allCubeBody and dimension information, which is different from the objects we normally connect to the database. Remember to read the nbearCodeThe structure of the database table read from a database is taken from the system table and cannot be obtained directly from the connection object.

The connection string can be in the following format:

[Analysis Service 2005]

Provider = msolap.3; Integrated Security = sspi; persist Security info = true; initial catalog = adventure works dw; Data Source = http: // 127.0.0.1

[Analysis Service 2000]

Provider = msolap; initial catalog = Food Mart 2000; Data Source = http: // 127.0.0.1

Analysis Services in sqlserver2005 supports two Database Connection Methods
(1) Windows authentication method (for example, "Data Source = limj; Catalog = Analysis Services tutorial; connectto = 9.0; Integrated Security = sspi ");
(2) HTTP authentication (for example, "provider = msolap; user id = administrator; Password = 119; persist Security info = true; initial catalog = datacenterdw; Data Source = http: // limj/OLAP/msmdpump. DLL ")
The following describes the configuration process of this method on the window2003 server. (BelowArticleFrom Microsoft msdn)

Overview

Microsoft SQL Server 2005 Analysis Services and SQL Server 2000 analysis services use the same architecture to provide HTTP access.

The pump component is loaded to IIS (Internet Information Service) and used as an ISAPI extension to transmit data between the client and the analysis services server.

This White Paper describes how to use Microsoft Windows Server 2003 SP1 to set HTTP access to analysis services.

Figure 1 provides an overview of the component architecture.
View the actual size image

Back to Top

Obtain binary data

Obtain binary data:

Copy the contents of the % installation folder % \ OLAP \ bin \ ISAPI directory to the folder to be used as the basis of the virtual directory in IIS.

In this example, all the files in the c: \ Program Files \ Microsoft SQL Server \ mssql.1 \ OLAP \ bin \ ISAPI folder will be copied to the C: \ Inetpub \ wwwroot \ OLAP directory.

Note: To use all security settings, make sure that the folder underlying the virtual directory is on the drive formatted as the NTFS file system, which is very important. Due to IIS restrictions, the path to the directory cannot contain spaces.

If you want to run an HTTP pump on a server other than the analysis services server, make sure that oledb is installed for the analysis redistributable package.

Back to Top

Create an application Program Library

Create an application Library:

1.	To open the "Computer Management" console, open "Control Panel", "Administrative Tools", and "Computer Management" in turn ". (Alternatively, right-click my computer and choose manage from the shortcut menu .)
2.	On the "Computer Management" console, expand the "service and application" nodes and "Internet Information Service" nodes. If the "Internet Information Service" node cannot be found, it may be because IIS is not installed on your computer. To install the program, open "add or delete programs" in "Control Panel ". Select Add/delete Windows Components. Then add IIS to Windows.
3.	Right-click application pool to open the shortcut menu, select new, and then select application pool.
4.	Specifies the name of the application pool. In this example, we name it OLAP. (See figure 2 .) Figure 2 View the actual size image

Back to Top

Create virtual directory

Create a virtual directory:

1.	To open the "Computer Management" console, open "Control Panel", "Administrative Tools", and "Computer Management" in turn ". (Alternatively, right-click my computer and choose manage from the shortcut menu .)
2.	On the "Computer Management" console, expand the "service and application" nodes and "Internet Information Service" nodes. If the "Internet Information Service" node cannot be found, it may be because IIS is not installed on your computer. To install the program, open "add or delete programs" in "Control Panel ". Select Add/delete Windows Components. Then add IIS to Windows.
3.	Right-click "website" to open the shortcut menu, select new, and select virtual directory.
4.	Specifies the name of the virtual directory. In this example, we name it OLAP. The "content directory" should point to the folder you just created. In this example, point to c: \ Inetpub \ wwroot \ OLAP.
5.	Make sure that only the second check box is selected under the access permission to run the script (such as ASP ). (See figure 3 .) Figure 3 View the actual size image

Back to Top

Set virtual directory Properties

Set virtual directory properties:

1.	Right-click the virtual directory node and select Properties from the menu. You will see the screen shown in 4. Figure 4 View the actual size image The property to be modified is circled by red lines. Attributes that do not need to be modified but are critical to security and performance are circled in green lines.
2.	Select the application pool you just created.
3.	Click Configure to view the screen shown in Figure 5. Figure 5 View the actual size image
4.	Click Add.
5.	In the executable option, enter the complete path name of msmdpump. dll. In this example, c: \ wwroot \ OLAP \ msmdpump. dll
6.	Enter. dll in the extension text box. You can see the dialog box 6 on the screen. Figure 6 View the actual size image
7.	Click OK to accept the settings.

Back to Top

Select Security Settings

Select security settings:

1.	Select the Directory Security tab and click Edit on authentication and access control. You will see the dialog box 7. Figure 7 View the actual size image
2.	You will see three options. Select one of them. This section briefly describes these options and points out their advantages, disadvantages, and security issues.

Anonymous Access

When this mode is selected, the pump (msmdpump. dll) runs with creden。; in this example, these creden。 are the creden of IUSR_machinename users. Therefore, each connection to analysis services is opened as the IUSR_machinename user. When this mode is selected, the user is connected to IIS or analysis services, and there is no difference between them. Therefore, users cannot be differentiated.

This mode is applicable when the security infrastructure does not use the security features of analysis services. In most cases, it is a strictly controlled environment where users are allowed or denied access to virtual directories.

Integrated Windows Authentication

This is the safest mode and is recommended.

This mode requires the IIS server to obtain the user's domain identity creden. This can be done using Microsoft Active Directory or other mechanisms. This article does not discuss all possible configurations one by one.

Digest authentication for Windows Domain servers

This topic does not discuss this option. You can view more information through the help of IIS.

Basic Authentication

In this mode, you need to enter the user name and password. The user name and password are transmitted to IIS over an HTTP connection. IIS will try to simulate the user through the provided creden.

NOTE: For the password transmission system, you must take measures to ensure the security of the communication channel. IIS provides a large number of tools for setting and requires that all communications be encrypted using HTTPS.

This document describes how to set https for a virtual directory later.

Back to Top

Set web service extension

Set web service extension:

1.	In "Computer Management", right-click the "Web Service extension" node and select "Add a new Web Service extension ".
2.	Specify the extension.
3.	Click the Add button and specify a path for your msmdpump. dll file.
4.	Select the "set extension status to allow" check box. On the screen, you will see the dialog box shown in figure 8. Figure 8 View the actual size image

Back to Top

Select the target analysis services server

As shown in the architecture diagram shown in figure 1, each pump component uses its own configuration file.

Open the msmdpump. ini file in your folder and view its content. The content should be as follows:

<Configurationsettings> <servername> localhost </servername> <sessiontimeout> 3600 </sessiontimeout> <connectionpoolsize> 100 </connectionpoolsize> <minthreadpoolsize> 0 </minthreadpoolsize> <strong> 0 </maxthreadpoolsize> <maxthreadsperclient> 4 </maxthreadsperclient> </configurationsettings>
 

The only setting you care about is <servername>.

If the analysis services instance you want to access is located in the Local Computer and installed as the default instance, you do not need to change this setting. Otherwise, you must specify the computer name and Instance name (mymachine \ inst1 ).

You can also specify a pointer to a virtual directory on an IIS server that is set to access Analysis Services over HTTP.

For example, you can specify <servername> http: // secondmachine/OLAP/msmdpump. dll </servername>

Back to Top

To sum up

Now you have set up your HTTP pump and can connect to it from your application.

If your application can specify the server name, you only need to change your server name to the path of the virtual directory that is connected in series with "msmdpump. dll.

In SQL Server 2000 analysis services, the msolap oledb provider can identify the server name including the URL path and automatically start it using HTTP.

For example, to connect to "mymachine" from the MDX sample application, you can use "http: // mymachine/OLAP/msmdpump. dll" as the server name to connect to the analysis services server.