Secure Your Distributed Windows Apps

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.

Authentication

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

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.

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!