Analysis Services Security and HTTP

Have you ever needed to set up Analysis Services in a multidomain network environment? Have you needed to provide access to your Analysis Services server through a firewall? If so, you probably needed to choose an alternative security configuration. By default, Analysis Services uses integrated authentication, which requires your users to be on the same domain as the server; so the default security option won't work if your users are on a different domain. Fortunately, Analysis Services supports three security options, so you can adapt your environment for a variety of security requirements. Let's look at how security works in Analysis Services, examine each of the available security options, and learn how to configure security for Analysis Services. By the time you're done reading this article, you should be ready to deploy an Internet-accessible OLAP solution.

If you compare the architecture of Analysis Services to the architecture of a relational database such as SQL Server, you might notice a piece missing from the Analysis Services security layer: Analysis Services doesn't have its own authentication mechanism. Unlike SQL Server 2000, Analysis Services uses Windows authentication exclusively to verify user permissions, and Analysis Services role definitions are based on Windows users and groups. When designing Analysis Services, Microsoft had good reasons to not develop a database security model like SQL Server authentication. Windows security provides features such as secure validation, password encryption, user grouping, user administration, and auditing that would have been difficult to duplicate. In fact, in SQL Server 2000, Microsoft deemphasizes SQL Server authentication and recommends using Windows authentication. But SQL Server authentication provides some features that Windows authentication doesn't duplicate. For example, with SQL Server authentication, a client can connect from anywhere across the Internet and can easily log in to SQL Server and Windows separately. In SQL Server 2000, Microsoft introduced support for HTTP between client and server that gives users the benefits of SQL Server authentication while maintaining the added security of Windows authentication.

When a user connects to Analysis Services directly, Analysis Services attempts to authenticate the user's Windows credentials. Although the interface lets the user specify a username and password in the connection string, Analysis Services ignores the string credentials. The user must be logged in to the Windows domain that the Analysis server resides in or the authentication will fail. If the user is in the correct domain, she doesn't have to do anything when connecting to the Analysis server; she's logged in automatically.

Direct connections are great when you're running your analysis applications on the same domain as the Analysis server, but what happens when that isn't the case? What if the Analysis server you need to access is on a different domain, or what if you're on the road and can't directly access your corporate network? In SQL Server 2000 Enterprise Edition, Microsoft answers these questions by providing the ability to connect to Analysis Services over HTTP by using Microsoft IIS. The initial release of SQL Server 2000 requires both IIS and Analysis Services to be installed on the same Windows server when you're configuring SQL Server for HTTP access. But SQL Server 2000 Service Pack 3 (SP3) eliminates that requirement. The HTTP components can now be on a different server than Analysis Services; a named pipe enables communication between the two. Figure 1, page 39, shows configurations for Analysis Services and IIS on one server and on separate servers. You can learn more about the new multiple-machine capability in SP3 by reading the Microsoft article "Improved Web Connectivity in Microsoft SQL Server 2000 Analysis Services" at http://msdn.microsoft.com/library/en-us/dnsql2k/html/sql_datapump.asp.

Configuring HTTP Access

Before you can configure Analysis Services for HTTP connectivity, you need to make sure IIS is installed on the server. Windows Server 2003 doesn't install IIS by default, so you might need to install IIS manually. To do so, select Add or Remove Programs from the Control Panel, click Add/Remove Windows Components, and select Internet Information Services. Then, click Next to complete the IIS installation.

To enable HTTP access, you need two Analysis Services components: msmdpump.dll and msolap.asp. These components install automatically on the server with Analysis Services in SQL Server 2000 Enterprise Edition, but to activate them, you have to copy msolap.asp to a specific directory. In standard Analysis Services installations, you'll find msolap.asp in the C:Program FilesMicrosoft Analysis ServicesBin directory. Copy the msolap.asp component to the C:inetpubwwwroot directory. Putting the file in that directory eliminates the need to include subdirectories in the connection-string URL (e.g., http://myserver.mydirectory). Instead, you use just the machine name of the server in the URL (e.g., http://myserver). You don't have to copy the msmdpump.dll component; it's a COM component and is registered automatically when it's installed. After a component is registered, Windows can find it no matter what directory it's in.

If you want to put msolap.asp in its own virtual directory on the server (to apply a security level that's different from that of the default IIS directory, for example), you can create a new virtual directory and copy msolap.asp into the folder that virtual directory points to. (To learn how to create a new virtual directory, see the IIS documentation at http://www.microsoft.com/windows2000/en/server/iis/htm/core/iicodirv.htm.) If you create a virtual directory named analysis, the URL in the connection string will be http://myserver/analysis. Notice that the file msolap.asp doesn't appear in the URL. PivotTable Service appends the file automatically, so you don't need to include it.

Security Options

Using IIS to connect to an Analysis server lets IIS control client authentication independently of Analysis Services, which gives you more flexible ways to access data. IIS can authenticate clients in one of three ways: Anonymous authentication, Basic authentication, and Integrated Windows authentication.

Anonymous authentication. Anonymous authentication lets every client access the IIS server without providing any credentials. Internally, IIS impersonates a specific user for every request--that is, IIS creates a pseudo-identity that matches the user in the thread the request is executing. The thread retains that specific user identity during all interactions with Windows services. The default pseudo-identity that IIS selects is a local account named IUSR_computername, in which computername is the name of your server. For example, if your server is named SATURN, the default account would be named IUSR_SATURN. The IUSR account has no network privileges, so if you want to separate the IIS server and the Analysis Services server, you have to change the default account to a domain account that has network access. When an account doesn't have network privileges, it can't access any other servers, so the IUSR account can't delegate the OLAP request to the Analysis Services server.

To configure the domain account and let the client access the network, IIS uses the IIS Manager, which we describe in a moment. If your OLAP application needs to have every user access Analysis Services as the same user (e.g., when everyone needs to see the same view of the data), Anonymous authentication is a perfect choice. The administrator can let users access selected parts of the cube and can make sure that all users see the same information. The administrator can control the anonymous user's permissions and doesn't have to set permissions for each user accessing the server. Of course, because Anonymous authentication means that anyone can access the server, if any sensitive OLAP data is on the server, Anonymous authentication isn't a good choice.

Basic authentication. Basic authentication requires a username and password for every request to IIS. When you select Basic authentication, IIS looks in 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 uses Base64 encoding to send the username and password to the server, thus obscuring them so they're not plaintext; but the credentials aren't encrypted and therefore aren't secure. Many Web sites use a combination of Secure Sockets Layer (SSL) and Basic authentication to encrypt the credentials the client sends to the server. Because Basic authentication requests the credentials from the client, users can access the Web site with different credentials than the ones they used to log in to Windows. Thus, if a user is using a machine that's outside the Windows domain that the IIS server is on, she can present valid credentials to authenticate herself to the IIS server. Otherwise, the client's browser won't automatically send the user's username and password to the server. All browsers automatically send the client's credentials with each subsequent request to the server so that the browser doesn't prompt the user more than once for a username and password.

Integrated Windows authentication. When a user is logged in to a Windows client machine, Integrated Windows authentication automatically sends the user's credentials to the Analysis server without prompting the user for a username and password. Integrated Windows authentication is more secure than Basic authentication because with this option, the password never travels across the network. Instead, the browser calculates a hash representation of the user's credentials on the client and sends the result to the server. (In this case, the browser is Microsoft Internet Explorer because it's the only browser that supports Integrated Windows authentication.) The server sends the hash representation to the domain controller, which uses the same hash formula on the user's credentials and compares the two hash values to decide whether the user is authorized to access the data. Integrated Windows authentication works only when the client machine is on the same domain as the server. This security option doesn't let the client impersonate any other user. In effect, Integrated Windows authentication gives the users the same behavior as if they had connected directly to the Analysis server. This option is the best choice when all the users are running in the same domain, such as when multiple users access the same intranet Web site.

Configuring Security

To select an IIS authentication method, you use the IIS Manager. To start the IIS Manager, from the Start menu, select All Programs, Administrative Tools, Internet Information Services, IIS Manager. On the left side of the window, you'll see an organizational tree. If you've installed msolap.asp in the default location, select Internet Information Services, and expand the tree until you get to the heading Default Web Site. Right-click Default Web Site, and select Properties. Select the Directory Security tab, and click Edit in the Anonymous access and authentication control section. When you see the Authentication Methods dialog box that Figure 2 shows, you can choose the authentication method you prefer. You can choose more than one option, but note that if you select Anonymous access, that option will override any other setting. Additionally, for Anonymous authentication, you can choose the domain user that IIS will impersonate when it processes each client request. Type the username and password for that domain user in the Account used for anonymous access section. Make sure you clear the Allow IIS to control password check box or you won't be able to type a password.

Now, you're ready to connect to Analysis Services through IIS. The only difference between connecting to Analysis Services directly and connecting through HTTP is the connection string. The Datasource property is a URL in the format we described earlier. You need to include username and password properties only for IIS Basic authentication; otherwise, the username and password properties are unnecessary. If you're using Basic authentication, PivotTable Service extracts the username and password from the connection string and puts them in the header of each HTTP request so that the authentication happens automatically. An example connection string that uses ADO MD or ADO MD.NET would be

Provider = msolap; Datasource =   http://myserver/; Initial   Catalog = FoodMart 2000; User   ID = myname; Password =   mypassword

Once you've configured Analysis Services connectivity over HTTP, congratulate yourself. This process isn't easy, but when it's in place, it enables complex network environments such as those that require Internet access and multidomain access.

Cautions

Although accessing Analysis Services over HTTP can replace accessing Analysis Services directly, in some environments, HTTP access isn't advisable. For clients to have access to the Analysis server, the server has to have IIS running and be able to receive HTTP traffic. But an open HTTP port can create a security vulnerability because many server viruses use the HTTP port. Implementing a network security system such as a firewall can alleviate some risk, but you have to determine whether your organization's need for HTTP access outweighs the potential security risks.

Also, running Analysis Services over HTTP is slower than running Analysis Services directly. PivotTable Service sends data to Analysis Services through msolap.asp by an HTTP POST request. IIS has to parse the data, unpack it, and send it to Analysis Services. After the query executes, the results go through IIS, which packs them and delivers them to the client. This process is slower than a direct client/server connection to Analysis Services, so you might notice a degradation in query performance.

Analysis Services gives you a variety of options to fit your security needs. Departments that have all users running on the same domain can work well with the security of a direct-access configuration, but wider deployments in which clients on different domains need to connect to the Analysis server might work better with the flexibility of Basic authentication or Anonymous access through IIS. For more information about Analysis Services security, search for the Analysis Services topic in the MSDN library at http://msdn.microsoft.com.