Introduction

The purpose of this document is to describe in detail how to modify the Performance Sentry Portal (hereafter referred to as “the Portal”) authentication and authorization settings.

By default, the Portal is configured to use Windows authentication to control access to the web pages as well as to control how the Portal itself connects to the SQL database. This results in a security model whereby access is only granted to Windows users logged into the local machine or logged into the NT domain in which the local machine resides, and with the rights to access the SQL database. The remainder of this document will discuss how to change the web and the SQL settings in order to modify the Portal’s security model.

Modifying SQL Server Security

The default installation of the Portal creates a Microsoft SQL database named “PDB” (Performance DataBase) which contains performance data collected by the Performance Sentry service. Full access to the Portal website and performance database is automatically granted to authenticated Windows users.

In order to remove the requirement that users be authenticated to Windows, we will grant anonymous access to the website and database access to the Windows account that is used by IIS. This will allow the Portal itself to access the PDB database regardless of who is accessing the Portal.

Determine the Windows Account Used by IIS

Login as an administrator on the machine that hosts the Portal and open “IIS Manager”. Expand the Web Sites node, then expand the appropriate sub-nodes per your Portal configuration. For a default Portal installation, the sub-nodes will be “Default Web Site”, then “Performance Sentry Portal” (see Figure 1).

Figure 1.

Right-click on the “Performance Sentry Portal” node, and select Properties. Then, navigate to the “Directory Security” tab, and click the button labeled “Edit…” (see Figure 2).

Figure 2.

This will open the “Authentication Methods” dialog. The textbox labeled “User name:” indicates the Windows account that IIS runs under (see Figure 3). Make a note of this account, as we need to create a new Login and User for this account in SQL Server.

Figure 3.

Add the IIS Windows Account to SQL Server

Broadly speaking,

SQL Server manages security via two mechanisms: Logins and Users. “Logins” represent the authentication portion of SQL’s security, whereby a user’s identity must be proven. “Users” represent theauthorization portion of SQL’s security, in which an authenticated user’s rights are established and their actions permitted or denied. Logins exist across all databases within an instance of SQL Server, whereas Users only exist within individual databases.

First, we need to create a Login for IIS’s Windows account. Then we will create a User for the PDB database, associate it with the Login we created, and restrict its rights.

Login as an administrator on the machine that hosts the PDB database and open “SQL Server Manager” or the equivalent that was installed with your version of MS SQL Server. Expand the root Server node, then expand the Security node beneath that, and the Logins node beneath that (see Figure 4).

Figure 4.

Create a SQL Login

Right-click the Login node and choose “New Login”. You will be presented with a “Login – New” dialog (see Figure 5).

Figure 5.

Click the “Search…” button to the right of the “Login name:” text box. This will open a “Select User or Group” dialog. (Note that the “From this location:” value should be the name of the machine that is hosting the Portal. If it is not, click the “Locations…” button to browse to the appropriate machine name.)

Click the “Advanced…” button at the bottom. This will open a “Select User or Group” dialog. In it, click the “Find Now” button. The bottom portion of the dialog should now be populated with a list of the Windows users and groups that are available. Scroll down until you find the Windows account used by IIS, select that entry, and click “OK” (see Figure 6).

Figure 6.

Accept all open dialogs until you are back on the “Login – New” dialog initially shown in Figure 5. Make sure that “Windows authentication” is selected. In the “Default database:” dropdown, select the name of your Performance Sentry database (“PDB” by default). Click “OK”.

Create a SQL User

Now we are going to add a SQL User specific to the PDB database. In SQL Manager, collapse the Security node, and expand the Databases node, then expand the child nodes PDB, Security, Users (see Figure 7).

Figure 7.

Right-click the Users node and select “New User”. You will be presented with a “Database User – New” dialog (see Figure 8).

Figure 8.

You must manually type in a value for the “User name:” textbox. For this example, we will enter the value “IIS_Account”. Next, we need to select the Login to associate with our new User. Click the “…” browse button next to the “Login name:” textbox. You will be presented with a “Select Login” dialog. Click the “Browse…” button. This will present you with a list of SQL Server Logins.

Scroll down the list until you find the Login we created previously and select that entry (see Figure 9). Accept all open dialogs until you return to the “Database User – New” dialog shown in Figure 8.

Figure 9.

In the “Default schema:” textbox, either type “dbo” (all lowercase, no quotations), or browse to the dbo schema via the “…” browse button next to it. In the “Owned Schemas” list, no schemas should be selected. In the “Role Members” list, select the roles “db_datareader” and “db_datawriter”. If no other roles are selected, this means that IIS_User only has the rights to read and write data, it cannot add, drop, or modify entities such as tables, views, users, stored procedures, etc. This is the recommended role membership configuration (see Figure 10).

Figure 10.

You should now see the new SQL User in the list of Users defined for your Performance Sentry database (see Figure 11).

Modifying IIS Security

Follow the steps in the section “Determine the Windows Account Used by IIS” in order to arrive at IIS Manager’s “Authentication Methods” dialog.

In the “Authentication Methods” dialog, check the “Anonymous access” checkbox. This step instructs IIS to execute in the context of the Windows account specified by the textbox labeled “User name:” (see Figure 3).

Testing Your Changes

To test your changes, first make sure you’re not logged in as an Administrator to the machine that hosts the Portal. Then, open a web browser and type the URL for your Portal installation. For example, if the Portal is hosted on a machine named “MyPortalServer”, and you have not changed the Portal’s virtual directory name in IIS, you would browse to the following URL from any machine on your network:

http://MyPortalServer/Performance%20Sentry%20Portal/

The Portal should load and run normally.