Microsoft KB Archive/280431

Knowledge Base

How to Enable Commerce Server 2000 OLAP Reports Over HTTPS with SQL Server 2000

Article ID: 280431

Article Last Modified on 6/28/2004

---

APPLIES TO

• Microsoft Commerce Server 2000 Standard Edition
• Microsoft SQL Server 2000 Enterprise Edition

---

This article was previously published under Q280431

SUMMARY

This article describes how to enable Commerce Server 2000 OLAP reports over HTTP. Although OLAP reports cannot be run across the Internet because of the connection method used by the PivotTable Service, you can change the connection method to use HTTP rather than the default Integrated Authentication.

To deploy this solution, your OLAP server must be running the Enterprise Analysis Services Edition of SQL Server 2000.

There following two hotfixes are also necessary for this solution:

290638 Cannot Access Business Desk Analysis Services Over Domains

291563 FIX: Analysis Serv HTTPS Connections Slower Than HTTP and TCP/IP

MORE INFORMATION

The following steps use SSL to protect report data over the wire. Microsoft recommends using SSL when you are passing sensitive data over the Internet.

Platform Configuration

1. Use the recommended build procedure for Commerce Server 2000, which is located at:

   http://support.microsoft.com/support/commerceserver/2000/install

2. Use SQL Server 2000 Enterprise Edition (Required).
3. Install Commerce Server 2000 using the Complete Install option.
4. Create a new IIS Web site with the following properties:

   Description: Retail Site
   Path: C:\Inetpub\RetailRoot

   This site will be used to unpackage the Retail solution site.
5. Install an SSL certificate on the Retail Site.

Configuring the Server

1. Unpackage the Retail solution site to the Retail Site IIS Web site.
2. From the SQL Server Enterprise Manager, connect to the database server, and then expand the MSCS_Admin database.
3. Click Tables.
4. Right-click the ResourceProps table, highlight Open Tables, and then click Return all rows.
5. Under the s_PropertyName='connstr_OLAP' column, change the value under s_value "data source=server" to use data source=https://www.server.com (where https://www.server.com will browse to the root of the Retail Site).
6. Add ";User ID=domain\username;password=password;" to the end of the connection string that you edited in step 5.
   
   NOTE: The users needs to have Read access to the cubes in Analysis Services.
7. Copy the Msolap.asp file from the C:\Program Files\Microsoft Analysis Services\Bin folder to C:\Inetpub\RetailRoot.
8. Open the Internet Services Manager.
9. In the console tree, expand the server you are administering, and then open the property sheet for the Retail Site.
10. In the Details pane, right-click Msolap.asp, and then click Properties.
11. On the File tab, click to select the Read option, and then click Apply and close the dialog box.
12. On the File Security tab, under IP address and domain name restrictions, click Edit.
13. Select the Denied Access radio buttons, allow access only to authorized IP ranges, and then click OK.
14. Under Anonymous access and authentication control, click Edit.
15. Verify that the Basic authentication check box is selected, and the Anonymous access check box is NOT selected.
16. Under Security Communications, click Edit.
17. Verify that the Require Secure Channel (SSL) check box is selected.
18. Install the following hot fix on the Commerce Server Server:

    290638 Cannot Access Business Desk Analysis Services Over Domains

Enabling Static OLAP Reports

When the server has been configured, you must modify the report definitions for the Static OLAP reports. These report definitions are contained in the Data Warehouse database for your site (for example, Retail_DW). The report definition contains a connection string fragment that enforces SQL Server integrated authentication. This fragment must be removed for the Static OLAP reports to run.

Paste the following code into Notepad, save it as a .vbs file, modify the connection string so that it points to the correct server and database, and then run the .vbs file from the command line to make the necessary changes to the report definitions:

set cxn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.RecordSet")
 
strConn = "Provider=SQLOLEDB;Data Source=MyServer;" & _
               "Initial Catalog=Retail_DW;User ID=<username>;Password=<strong password>;"
sql = "Select XMLData FROM report"
 
cxn.Open strConn
rst.Open sql, cxn, 1, 3
 
strFind = "<x:ConnectionString>Provider=SQLOLEDB;Integrated Security=SSPI;"
strReplace = "<x:ConnectionString>Provider=SQLOLEDB;"
 
While Not rst.EOF
   xmlData = rst.Fields("xmldata").value
   If instr(xmlData, strFind) > 0 Then
      rst.Fields("xmldata").value = replace(xmlData, strFind, strReplace)
      rst.Update
   End If
   rst.MoveNext
Wend
 
rst.Close
cxn.Close
 
set cxn = nothing
Set rst = nothing
                

Running the Reports

When the platform and server have been configured, you must install the Analysis Services Components of Microsoft SQL Server 2000 Service Pack 2 or later on each Business Desk client computer:

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

Additional query words: cs2k com2k sql olap reports

Keywords: kbinfo KB280431

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.