Monthly Archives: August 2008

15 Comments

Since Team Foundation Server (TFS) uses SQL Server Analysis Services (SSAS) as its repository for all kinds of info, people are interested to expose the data through Excel or SQL Server Reporting Services (SSRS). SSAS doesn't expose itself through the HTTP protocol by default, but this can be configured using this paper. I didn't created the complete document myself but used this paper from Edward Melomed as start. The paper on Microsoft TechNet uses SSAS 2005 and Windows Server 2003. Both products are still widely used, but 2008 is the magic word nowadays. By the way, I can't wait till his new book Microsoft SQL Server 2008 Analysis Services Unleashed is released.

Summary:  This paper is intended for anyone who is interested in providing access to SQL Server 2008 Analysis Services through HTTP protocol.The paper explains the steps required to set up HTTP access and discusses different performance and security settings. All the instructions in this paper are specific to the Microsoft Windows® Server™ 2008 operating system.

Overview

Microsoft® SQL Server™ 2008 Analysis Services uses the same architecture for providing HTTP access as did SQL Server 2005 Analysis Services.

The pump component is loaded into IIS (Internet Information Services) and serves as an ISAPI extension, pumping data from the client to an Analysis Services server and back.

This white paper walks you through the process of setting up HTTP access to Analysis Services when using Microsoft Windows Server™ 2008.

httpssas01_big

Figure 1 provides a high-level overview of the component architecture.

Getting binaries

To get binaries:

Copy the contents of the %Installation folder%OLAPbinisapi directory into the folder you would like to become the base for the virtual directory in IIS.

In this example, we are going to copy all the files from the C:Program FilesMicrosoft SQL ServerMSAS10.MSSQLSERVEROLAPbinisapi folder into the C:inetpubwwwrootolap directory.

Notes:  To take advantage of the full set of security settings, it is important to make sure that the folder to become the base for the virtual directory is located on the drive formatted for the NTFS file system.

Due to IIS limitations, the path to your directory should not contain spaces.

If you are planning to run the HTTP pump on a different server than the Analysis Services server, please make sure that you also install OLEDB for Analysis Redistributable package.

Creating an application pool

To create an application pool:

1.

To open the Computer Management console, open Control Panel, then Administrative Tools, then Computer Management.

(Or, right-click the My Computer icon and select Manage on the shortcut menu.)

2.

In the Computer Management console, expand the Roles node and then the Web Server (IIS) and Internet Information Services node.

If you can’t find the Internet Information Services node, IIS is probably not installed on your machine. To install it, open Add or Remove Programs in Control Panel. Select Add/Remove Windows Components. Then add IIS to Windows.

3.

Right-click the Application Pools to open the shortcut menu and select Add Application Pool...

4.

Name the application pool. In this example, we call it OLAP. (See Figure 2.)

httpasws02
Figure 2

Setting up an application

To use the application pool:

1.

To open the Computer Management console, open Control Panel, then Administrative Tools, then Computer Management.

(Or, right-click the My Computer icon and select Manage on the shortcut menu.)

2.

In the Computer Management console, expand the Roles node and then the Web Server (IIS) and Internet Information Services node.

If you can’t find the Internet Information Services node, IIS is probably not installed on your machine. To install it, open Add or Remove Programs in Control Panel. Select Add/Remove Windows Components. Then add IIS to Windows.

3.

Right-click the OLAP directory in your Web site to open the shortcut menu and select Convert to Application. Choose the application pool as one you’ve just created. (See Figure 3.)

httpasws03
Figure 3

Setting up directory properties

To set properties for the directory:

1.

Click your virtual directory node and select Handler Mappings from the menu. (See Figure 4.)

httpasws04
Figure 4

2.Make sure the directory has Read and Script permissions. This can be checked by clicking the "Edit Feature Permissions..." in

the top right corner of the Handler Mappings screen as shown in Figure 5.

httpasws04a
Figure 5

3.

In the top right corner of the Handler Mappings screen click the option " Add Script Map...".

4.

Enter *.dll in the Request path text box.

In the Executable option, enter the full path name to msmdpump.dll. In this example, it would be: C:wwwrootolapmsmdpump.dll

5.

Name the Script Map. In this example, we call it OLAP.

Your screen should look something like the dialog box shown in Figure 6.

httpasws05
Figure 6

6.

Click the OK button to accept the settings.

7.

When you hit OK to add the script mapping you will be prompted if you want to enable the ISAPI extension. (See Figure 7.)

httpasws06
Figure 7

8.

Click the Yes button to accept the settings.

9.The configuration of this decision can be found under ISAPI and CGI Restrictions of the server. (See Figure 8.)

httpasws07
Figure 8

10.Search for your extension and name it as shown in Figure 9.

httpasws08
Figure 9

11.

Click the OK button to accept the settings.

Selecting security settings

To select security settings:

1.

Click your virtual directory node and select Autentication from the menu. (See Figure 10.)

httpasws09
Figure 10

2.

You are presented with three options. Choose one of them. (See Figure 11)

httpasws10
Figure 11

This section briefly describes these options, citing the advantages, disadvantages, and security concerns of each one. All other options are not discussed in the paper. You can use Help for IIS to read more about it.

Anonymous access

When this mode is selected, the pump (msmdpump.dll) is running with credentials; in our case, these are the credentials of IUSR_MACHINENAME user. Therefore, every connection to Analysis Services is opened as IUSR user. When this mode is selected, there is no distinction between which user is connecting to IIS and which to Analysis Services. There is no way to distinguish between users.

This mode is to be used when the security infrastructure does not take advantage of the security functionality of Analysis Services. This is most likely an extremely controlled environment, where users are given or denied access to the virtual directory.

Windows authentication

This is the most secure and the recommended mode.

It requires that IIS Server be able to access user domain credentials. This could be done using Microsoft Active Directory® or another mechanism. It is beyond the scope of this paper to discuss all the possible configurations.

Basic authentication

This mode requires that the user 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 absolutely imperative for anyone building a system where the password is transmitted to have ways of securing the communication channel. IIS provides a great set of tools for setting up and requiring that all communications be encrypted using HTTPS protocol.

3.For configuring anonymous authentication right-click Anonymous Authentication and then click Edit. You will see a dialog box similar to the one in Figure 12 Here you can configure a specific account which will be used to logon SSAS.

httpasws11
Figure 12

Selecting the target Analysis Services server

As you can see in the architectural diagram in Figure 1, every pump component uses its own configuration file.

Open the msmdpump.ini file located in your folder and take a look at the contents of this file. It should look like the following:

    1 <ConfigurationSettings>

    2     <ServerName>localhost</ServerName>

    3     <SessionTimeout>3600</SessionTimeout>

    4     <ConnectionPoolSize>100</ConnectionPoolSize>

    5     <MinThreadPoolSize>0</MinThreadPoolSize>

    6     <MaxThreadPoolSize>0</MaxThreadPoolSize>

    7     <MaxThreadsPerClient>4</MaxThreadsPerClient>

    8 </ConfigurationSettings>

The only setting you are interested in at this point is <ServerName>.

If the Analysis Services instance that you need to provide access to is located on the local machine and installed as a default instance, there is no reason to change this setting. Otherwise, you need to specify the machine name and instance name ( mymachineinst1).

It is also possible to specify a pointer to the virtual directory on another IIS server that is set up for HTTP access to Analysis Services.

For example, you could have <ServerName>http://secondmachine/olap/msmdpump.dll</ServerName>

Getting it all together

At this point you should have configured your HTTP pump and should be ready to connect from your application.

If your application provides you with a way to specify the server name, all you need to do is to substitute your server name with the path to your virtual directory concatenated with “msmdpump.dll”.

As in SQL Server 2005 Analysis Services, the MSOLAP OLEDB provider will understand that the server name includes an URL path and will automatically start using HTTP protocol.

For example, to connect to “MyMachine” from the MDX sample application, you should be able to connect to the Analysis Services server using “http://MyMachine/olap/msmdpump.dll” as a server name.

 

78 Comments

The Internet Movie Database (IMDb) is an online database of information related to movies, actors, television shows, production crew personnel, and video games. IMDb launched on October 17, 1990, and in 1998 was acquired by Amazon.com. Since I'm a big fan of this site and still loves to use Internet Explorer, why can't I combine these two.

Activities
With Activities, you can access your services from any webpage. Activities simplify the common task of copying, navigating, and pasting into a single action. Users can access an Activity on selection simply by selecting content and opening the context menu. For example, users can select an address on a webpage and the Activity button appears. Alternatively, you can access selection Activities from the context menu. Activities are also available for the current webpage in the Page menu on the Command bar and for links within a page in the context menu for the link.

The XML namespace for Activities is: http://www.microsoft.com/schemas/openservicedescription/1.0 The main components to the format are:

  • homepageUrl
  • display (Name, Icon, Description)
  • activity (Category)
    • activityAction (Context ["selection", "document", or "link"])
      • preview
        • parameter
      • execute
        • parameter

This is my activity for IMDb. I added it to same category as all Search activities. Save the file as SearchIMDb.xml.

    1 <?xml version="1.0" encoding="UTF-8"?>

    2   <openServiceDescription xmlns="http://www.microsoft.com/schemas/openservicedescription/1.0">

    3   <homepageUrl>http://www.imdb.com</homepageUrl>

    4   <display>

    5     <name>Search with IMDb</name>

    6     <icon>http://www.imdb.com/favicon.ico</icon>

    7   </display>

    8   <activity category="Search">

    9     <activityAction context="selection">

   10       <execute action="http://www.imdb.com/find">

   11         <parameter name="s" value="all"/>

   12         <parameter name="q" value="{selection}"/>

   13       </execute>

   14     </activityAction>

   15   </activity>

   20 </openServiceDescription>

Click the link below to add this activity to your IE.

Add IMDb Activity 

Update: Community Server won't allow me to add javascript to my blog posts. This is the link you should add to an html file. 

    1 <a onclick="java script:window.external.addService('SearchIMDb.xml')" href="#">Add IMDb Activity</a>

After installing the activity you can also change it to a default activity.

Manage

This will result in the following possibilities

activity 

Summary
I created a simple activity for IMDb, but you easily create some activities for Torrent sites (especially if your from Italy), MSDN, Wikipedia etc. To install a activity the installer must be run from a Web Server, this can't be from a local hard drive. Here are some interesting links:

Activities white paper
http://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=ie8whitepapers&ReleaseId=566

Activities Hands-On Lab
http://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=iemix08labs&ReleaseId=583

 

1 Comment

The .NET Framework 3.5 Service Pack features new abstractions for working with data, smaller client profiles and performance updates to Windows Presentation Foundation (WPF). It will ship before the end of the summer, Microsoft said. In a July 31 meeting with SD Times, Shanku Niyogi, product unit manager of the UI Framework and Services team at Microsoft, said that "the .NET Framework 3.5 Service Pack 1 (SP1) would be broadly available in all localized versions within a few weeks."

SP1 adds support for SQL Server 2008, the ADO.NET entity framework and ADO.NET data services, which was formerly known as “Astoria.”

Astoria uses ADO.NET to model data, and it exposes entities as URI-addressable resources to be consumed by Web clients within corporate networks and across the Internet. The entity model provides developers with an additional layer of abstraction between the database and themselves.

Since Microsoft has released to manufacturing SQL Server 2008 and the DVD images are now available on Microsoft and the MSDN subscriber downloads.

Interestingly, the Microsoft .NET Framework SP1 doesn't appear to be available although when the SQL Server 2008 setup program is ran it installs the RTM build of the .NET Framework SP1. You can install it separately by selecting the setup at ..x86redistDotNetFrameworksdotNetFx35setup.exe.

As a result of this install my Add or Remove Programs will look like this.

Add-Remove

The Product itself will be released today. I will update this post with the correct link.

Update 08/12
You can download the .NET Framework 3.5 SP1 here.

Furthermore Visual Studio 2008 SP1 was released today which can be downloaded here.

Summary
Microsoft shame on you to release such an important framework only the cd of a server product. Hopefully the holiday did good for all your employees.

 

Technorati tags: ,