Configure HTTP Access to SQL Server Analysis Services on IIS 7.0

jinquan26

　　after learning to Configure HTTP Access to SQL Server Analysis Services on windows server 2003 ,i will continue how to Configure HTTP Access to SQL Server Analysis Services on IIS 7.0
　　引自：http://msdn.microsoft.com/en-us/library/gg492140.aspx

　　
　　

　　You can enable HTTP access to Analysis Services by configuring MSMDPUMP.dll,  an ISAPI extension that runs in Internet Information Services (IIS) and pumps  data to and from client applications and an Analysis Services server. This  approach provides an alternative means for connecting to Analysis Services when  your OLAP solution calls for the following capabilities:

• 　　Client access is over Internet or extranet connections, with restrictions on  which ports can be enabled. Or, client connections are from non-trusted domains  in the same network.
  
• 　　Client runs in a network environment that allows HTTP but not TCP/IP  connections.
  
• 　　Authentication methods other than Windows integrated security are required.  IIS supports Anonymous connections and Basic authentication. Configuring  Analysis Services for HTTP access lets you use these alternative authentication  methods with Analysis Services.
  
• 　　Client applications cannot use the Analysis Services client libraries (for  example, a Java application running on a UNIX server). If you cannot use the  Analysis Services client libraries for data access, you can use SOAP and XML/A  over a direct HTTP connection to an Analysis Services instance.
  

　　This topic explains how to set up HTTP access to an Analysis Services  instance using IIS 7.0. These instructions are valid for any supported version  or edition of an Analysis Services OLAP instance that interfaces with IIS 7.0,  including SQL Server 2008 R2, SQL Server 2008, and SQL Server 2005.
　　This topic includes the following sections:
　　Overview
　　Copy the MSMDPUMP.dll to a folder on the Web  server
　　Create an application pool and virtual  directory in IIS
　　Configure IIS authentication and add the  extension
　　Edit the MSMDPUMP.INI file to set the target  server
　　Test your configuration

   

Overview
   
　　MSMDPUMP is an ISAPI extension that  loads into IIS and provides redirection to an Analysis Services instance that is  on the same computer or a remote computer within the same domain. You must  create and configure one virtual directory, each with its own set of MSMDPUMP  files, for each Analysis Services instance you want to connect to. The  configuration file in each set of MSMDPUMP files specifies the name of the  Analysis Services server instance used for each HTTP endpoint.
　　On IIS, MSMDPUMP connects to Analysis  Services using the Analysis Services OLE DB provider over TCP/IP. Both Analysis  Services and IIS must be in the same domain or in trusted domains in order for  the native connection to succeed.
　　When MSMDPUMP connects to Analysis  Services, it does so under a Windows user identity. This account will either be  the Anonymous account if you configured the virtual directory for anonymous  connections, or a Windows user account. The account must have the appropriate  data access rights on the Analysis Services server and database.

   Note   　　Remember to unblock the ports in Windows Firewall to allow client connections  to a remote Analysis Services server. For more information, see Configure Windows  Firewall for Analysis Services  Access.
　　The following table lists additional  considerations when you enable HTTP access for different scenarios.
  
  　　Scenario
　　Configuration
  　　IIS and Analysis Services on the same computer
　　This is the simplest configuration because it allows you to use the default  configuration (where the server name is localhost), the local Analysis Services  OLE DB provider, and Windows integrated security with NTLM. Assuming that the  client is also in the same domain, user authentication is transparent to the  user, with no additional work on your part.
  　　IIS and Analysis Services on different computers
　　For this topology, you must install the Analysis Services OLE DB provider on  the web server. You must also edit the msmdpump.ini file to specify the location  of Analysis Services instance on the remote computer.
　　This topology adds a double-hop authentication step, where credentials must  flow from the client to the web server, and on to the backend Analysis Services  server. If you are using Windows credentials and NTLM, you will get an error  because NTLM does not allow delegation of client credentials to a second server.  The most common solution is to use Basic authentication with Secure Sockets  Layer (SSL), but this will require users to provide a user name and password  when accessing the MSMDPUMP virtual directory. A more straightforward approach  might be to enable Kerberos and configure Analysis Services constrained  delegation so that users can access Analysis Services in a transparent  manner.
　　Consider which ports to unblock in Windows Firewall. You will need to unblock  ports on both servers to allow access to the web application on IIS, and to  Analysis Services on a remote server.
  　　Client connections are from a non-trusted domain or an extranet  connection
　　Client connections from a non-trusted domain introduce further restrictions  on authentication. By default, Analysis Services uses Windows integrated  authentication, which requires users to be on the same domain as the server. If  you have Extranet users who connect to IIS from outside the domain, those users  will get a connection error if the server is configured to use the default  settings.
　　Workarounds include having Extranet users connect through a VPN using domain  credentials. However, a better approach might be to enable Basic authentication  and SSL on your IIS web site.



Copy the MSMDPUMP.dll to a folder on the Web  server
   
　　In this section, you copy the MSMDPUMP  executable, configuration file, and resource files from the Analysis Services  program folders to an OLAP virtual directory folder that you create on the file  system.
　　The drive must be formatted for the NTFS  file system. The path to the folder that you create must not contain any  spaces.

• 　　On the web server, create the following folder:  <drive>:\inetpub\wwwroot\olap
  
• 　　Copy the contents of the ISAPI folder on the Analysis Services computer to  the \inetpub\wwwroot\olap folder you just created.
  　　The ISAPI folder can be found at <drive>:\Program Files\Microsoft SQL  Server\<instance>\OLAP\bin\isapi. It contains the following files and  folder: MSMDPUMP.DLL, MSMDPUMP.INI, and a Resources folder that contain language  resource files.
  
     Note   　　Note that the format of the instance name has changed in recent releases of  SQL Server. If you are using SQL Server 2008 R2, the default instance name is  MSAS10_50.MSSQLSERVER. If you are using earlier versions of SQL Server, the  instance names are created sequentially by installation order, where MSSQL.1 is  the first service installed, MSSQL.2 is the second service, and so on. You might  need to open the instance folders to determine which one has the Analysis  Services program files.
  
  
• 　　Verify that the \inetpub\wwwroot\olap\isapi folder on your web server  contains the following: MSMDPUMP.DLL, MSMDPUMP.INI, and a Resources  folder.
  

Create an application pool and virtual directory  in IIS
   
　　Configuring HTTP access starts with  creating an application pool and virtual directory that provides an endpoint to  the pump.
Create an  application pool

• 　　Start IIS Manager. Click Start, point to Run, and then type Inetmgr.
  
  
• 　　Right-click Application Pools and then click Add Application Pool. Create an application pool named OLAP, using .NET Framework v2.0.50727, with Managed pipeline  mode set to Classic.
  
  
• 　　By default, IIS creates application pools using Network Service as the  security identity. To change the identity of the application pool you just  created, right-click OLAP, and then select Advanced Settings.
  
  
• 　　In Identity, click the built-in account that IIS specified. Depending on the  version of Windows that you are using, this is either Network  Service (shown in the screenshot) or ApplicationPoolIdentity. Click the Change button for this property to replace the built-in  account with the custom account you want to use.
  
• 　　By default, on a 64-bit operating system, IIS sets the Enable 32-bit Applications property to false. If you copied msmdpump.dll from a 64-bit installation  of Analysis Services, this is the correct setting for the MSMDPUMP extension on  a 64-bit IIS server. If you copied the MSMDPUMP binaries from a 32-bit  installation, set it to true. Check this property now  to ensure it is set correctly.
  

Create a  virtual directory

• 　　In IIS Manager, open Sites, right-click Default Web Site (or whatever web site you are using to  access the pump), and then click Add Virtual Directory.  
  
• 　　In Alias, type OLAP.
  
• 　　In Physical Path, click the browse button and navigate to  C:\inetpub\wwwroot\olap. Click OK.
  
  
• 　　Right-click the OLAP virtual directory you just  created, and then click Convert to Application. Accept  all of the default values. Click OK to convert the  application.
  

Configure IIS authentication and add the  extension
   
　　In this section, you further configure  the OLAP virtual directory you just created. You will specify an authentication  method and then add a script map. For more information about authentication  methods, see IIS  Authentication Methods.

• 　　In IIS Manager, open Sites, open Default Web Site, and then select the OLAP virtual directory.
  
• 　　Double-click Authentication in the IIS section of  the main page.
  
  
• 　　Enable Windows Authentication if you are using  Windows integrated security.
  　　This is the most secure and recommended mode, but it requires that IIS be  able to access user domain credentials, using Microsoft Active Directory&#174; or  another mechanism. If Analysis Services and IIS are on different computers, you  will need to enable Kerberos and configure Analysis Services for constrained  delegation. It is beyond the scope of this topic to discuss all the possible  configurations.
  
  
• 　　Enable Anonymous Authentication if you are  supporting connections through IUSR_<computername>. Otherwise, you should  disable this authentication method. If you enable Anonymous Authentication, IIS  will always use it first, even if you enable other authentication methods.
  　　If you do enable Anonymous Authentication, be sure that the  IUSR_<computername> account on your web server has the appropriate data  access permissions on the Analysis Services database.
  　　When this mode is selected, the pump (msmdpump.dll) runs using the  credentials of IUSR_<computername>. Therefore, every connection to  Analysis Services is opened as IUSR_<computername> user. When this mode is  selected, there is no distinction between which user is connecting to IIS and  which to Analysis Services. This mode is most likely found in an extremely  controlled environment, where users are given or denied access by way of access  control lists on the virtual directory.
  
• 　　Enable Basic Authentication if your client and  server applications are in different domains. This mode requires the user to  enter a user name and password. The user name and password are transmitted over  the HTTP connection to IIS. IIS will try to impersonate the user using the  provided credentials. Please note that it is imperative for anyone building a  system where the password is transmitted to have ways of securing the  communication channel. IIS provides a set of tools that help you secure the  channel. For more information, see How to Set Up SSL on IIS  7.
  　　Basic authentication is useful when you want to collect user credentials from  the client, giving users a way to access the Web site using credentials that are  different from their personal Windows login account. When you select Basic  authentication, IIS reads the HTTP header of each request for a username and  password. If it doesn't find the proper credentials, IIS sends a response to the  client asking for a username and password. The client prompts the user for  credentials and then uses Base64 encoding to send the username and password to  the server. Although the credentials are encoded, they are not encrypted and are  therefore not secure. This is why it&#8217;s strongly recommended that Web sites use a  combination of Basic authentication and SSL to encrypt the credentials that the  client sends to the server.
  
• 　　Click the OLAP virtual directory to open the main  page. Double-click Handler Mappings.
  
  
• 　　Right-click anywhere on the page and then select Add Script  Map. In the Add Script Map dialog box, specify *.dll as the request path, specify  c:\inetpub\wwwroot\olap\msmdpump.dll as the executable, and type OLAP as the name.
  
  
• 　　Click Request Restrictions.
  
• 　　On the Verbs tab, verify that All verbs is selected.  Click OK, and then click OK  again to finish adding the script mapping.
  
  
• 　　When prompted to allow the ISAPI extension, click Yes.
  
  

Edit the MSMDPUMP.INI file to set the target  server
   
　　Open the msmdpump.ini file located in  folder C:\inetpub\wwwroot\OLAPand take a look at the contents of this file. It  should look like the following:




Copy   

<ConfigurationSettings> <ServerName>localhost</ServerName> <SessionTimeout>3600</SessionTimeout> <ConnectionPoolSize>100</ConnectionPoolSize> </ConfigurationSettings>  　　If the Analysis Services instance for  which you are configuring HTTP access is located on the local computer and  installed as a default instance, there is no reason to change this setting.  Otherwise, you must specify the server name (for example,  <ServerName>ADWRKS-SRV01</ServerName>).
　　By default, Analysis Services listens on  TCP/IP port 2838. If you installed Analysis Services as the default instance,  you do not need to specify any port in <ServerName> because Analysis  Services knows how to listen on port 2838 automatically. However, you do need to  allow inbound connections to that port in Windows Firewall. For more  information, see Configure Windows  Firewall for Analysis Services Access.
　　If you configured a named or default  instance of Analysis Services to listen on a fixed port, you must add the port  number to the server name (for example,  <ServerName>AW-SRV01:55555</ServerName>) and you must allow inbound  connections in Windows Firewall to that port.



Test your configuration
   
　　The connection string syntax for  MSMDPUMP is the URL to the MSMDPUMP.dll file.
　　If the web application is listening on a  fixed port, append the port number to the server name or IP address (for  example, http://my-web-srv01:8080/olap/msmdpump.dll or  http://123.456.789.012:8080/olap/msmdpump.dll.
　　To quickly test the connection, you can  open a connection using SQL Server Management Studio. In the Connect to Server  dialog box, select Analysis Services as the server  type. In Server name, enter the HTTP address of the msmdpump extension: http://localhost/olap/msmdpump.dll.
　　Be sure to follow-up with more rigorous  testing by using an actual client computer that runs in the network environment  from which the connections will originate. Remember to grant the appropriate  permissions on the Analysis Services server (either IUSR_<computername> if  you enabled Anonymous connections or the Windows identities of the users who are  connecting to MSMDPUMP by way of IIS).