Secure Your Distributed Windows Apps

Thursday Sep 15th 2005 by Deann Corum

Authentication and authorization in Windows-based distributed application environments are key to running a tight ship. Here's what you need to know.

A few weeks ago, we looked at monitoring your Windows-based distributed applications and servers. Besides monitoring these applications once they're running, securing them and keeping them secure is equally important. So let's look at some things a system administrator should be aware of when configuring and running Windows-based distributed applications.

You'll typically use one of two ways to secure access to part or all of an application. You'll either create a table of user names and passwords and store that table in a database such as SQL Server, or you'll use Windows Active Directory (AD) or local SAM accounts for authentication.

Digest Authentication in IIS
(Click for a larger image)
With user names and passwords stored in a database, the application requests the username and password and validates this information against the user table in the database. The browser sends the user's credentials to an Active Server Pages (ASP) script that processes them. The ASP script asks SQL Server to look up the username and password to verify the user. This method is typically used for internet or extranet access, where the clients are from outside your organization or perhaps business partners.

With Windows Active Directory-based authentication or with local Security Access Manager (SAM) authentication (i.e.: No Windows Domain account), a user visits a Web page that contains an ASP script that tries to access SQL Server. At that point, SQL Server reverifies the user's security credentials with the domain controller (DC) or with the local server's SAM. This method is typically used when clients who will be accessing your application are part of your organization.

If MS SQL is running on a server that's a member of a domain, SQL Server will first check the DC to authenticate a client. If not, SQL Server checks the local server's SAM. Using Active Directory is best because it centralizes the user accounts and groups in one place where all of your servers can access them. Another advantage is that users don't need a second username and password to access the application if the account with which they're logging on to their workstation is on the server or in the domain.

Other methods of Authentication
In addition to Windows integrated and basic authentication in IIS, there are other methods of authenticating users who visit a Web site. One thing you can do is map a client certificate to a local Windows or domain user account. When users connect using that certificate, IIS (5.0 or later only) uses the mapped account to log on the user and those account credentials are used to access resources.

Another alternative (again for Win2K or later and IIS 5.0 or later shops only) is digest authentication. When you use digest authentication, the browser creates a hashed version of the username and password along with other information. These credentials can't be easily deciphered, but the DC can match the hashed information with the plaintext version stored on the DC. In this way, digest authentication lets the browser and server authenticate the user without sending clear-text passwords. In order to use digest authentication, the browser must be IE 5.5 or later, and the IIS server must be part of an Active Directory domain.


Authorization can be simply described as granting permissions to resources. This might mean granting 'read' or 'read/write' permissions to the accounts or groups you've configured authentication for. Typically these are applied to the folder where the web-based portion of your application resides. It might mean configuring impersonation settings in DCOM or ASP.NET components, particularly if your users or clients are from outside your organization or are a mixture of internal and external users. It also might mean configuring some very specific settings in SQL Server, which we'll cover below.

The SQL Server side of things: Roles and permissions

Standard SQL Server Roles
(Click for a larger image)
You can set up a Windows user or group as a SQL Server login and then use that login in various ways. One way is to make the SQL Server login (The Windows user or group) part of a SQL Server role that has the necessary permissions to the database. To add an existing login to a role, perform the following steps:

  1. Open Enterprise Manager.
  2. Open the Databases folder.
  3. Open the database to which you want to add the login.
  4. Select the Roles folder for the database.
  5. Right-click the role to which you want to add the login, and select Properties.
  6. Click Add, select the login to add, then click OK.
  7. Click OK to close the role properties and complete the action.
  8. If you used these steps to add a login to the db_datawriter role for instance, SQL Server would now be able to authenticate users in that group and allow them to read data from and write data to the database you chose in step 3, above. And you could open the properties for that SQL Server login, clear the Northwind database entry on the Database Access page, and users in this group would still have access to that database because they're still in the db_datareader and db_datawriter roles. However, the group would not have insert, update or delete permission because you haven't granted those permissions. You could create another login and assign it to another role that provides more authority (such as db_owner).

    Stored Procedure Permissions
    (Click for a larger image)
    If you need even more control over security than just letting a login have access to an entire database, you can edit the role selections you made when you created the login or add your own roles with custom permissions. The roles you create can limit or grant access to specific tables and even to specific columns.

    Best practice in many application environments is to grant users or roles permissions to the stored procedures that operate on a database, rather than granting them direct access to the actual data:

    1. Open Enterprise Manager.
    2. Open the Databases folder.
    3. Open the database to which you want to add the permissions.
    4. Select the Stored Procedures folder for the database and select Properties.
    5. Click the Permissions button.
    6. Under the Users/Database Roles column, select the account you wish to grant permission to.
    7. Click the appropriate column (for instance 'Exec' to allow the account execute permissions on that stored procedure)
    8. Click OK twice to close the stored procedure properties and complete the action.

    So now we've covered authentication and authorization in Windows-based distributed application environments. In Part II, we'll cover some best practices for securing IIS and Microsoft SQL Server specifically. See you then!

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved