Windows

Use IIS, SQL Server 7, and NT groups to design a secure network

Microsoft bundled SQL Server 7 with the ability to apply object-level security using Windows NT groups. In this TechProGuild feature, Joe Barnes shows you how to manage security on an intranet application.

In the "pre-Web" Windows NT environment, how was application security managed? In most cases, homegrown security architecture was established on an application-by-application basis.
This article appears courtesy of TechRepublic's TechProGuild, the subscription Web resource for IT administration and support professionals. Among other great benefits, TechProGuild offers in-depth technical articles, e-books, and weekly chats moderated by industry experts on hot topics such as the latest OS developments and career advancement. Sign up now for a FREE 30-day trial of our TechProGuild service.
This application buried some type of authentication wrapper around a user’s table within a database. This database table held a "role," or definition, for each user, or a common database was used specifically for this purpose, holding not only user data, but rolling it up to a group level. Each user was associated with a group (or groups), and each group had defined rights within an application(s) and database(s). In essence, entire group-based security architecture was created and modeled after the existing NT Group Security bundled within the operating system.

Now with Windows 2000 (which is very much directory services based) on our corporate heels, we are again pushed to think about how to manage application security. Microsoft bundled Structured Query Language (SQL) Server 7 with the ability to apply object-level security using NT groups. Prior to this, SQL Server was managed on a user-by-user basis. This meant that each user established within the NT domain who required access to a SQL Server database had to be added twice.

In addition to the SQL 7 security architecture change, Microsoft provided Internet Information Server (IIS) with the ability to manage site security using NT groups via NT Challenge/Response. NT Challenge/Response is a process by which the IIS Server requests security credentials from the user accessing the site and verifies them with those supplied by an encrypted token within the browser. The root value of this security method is that the server knows who you are and what you have access to (as defined within the file system security of your site).

In this Daily Drill Down, I'll examine how you can use these features to manage security on an intranet application based on NT group accounts, SQL Server 7, and IIS 4.0.

NT group accounts
The foundation of this security model is the use of NT groups to manage permissions on files and resources within your domain. To demonstrate this concept, I'll show you how to create a few user IDs and groups that will ultimately access your application via IIS.

For demonstration purposes, I'll use the FO Track database. FO Track is a consultant's time-tracking tool built on SQL Server 7.

Two types of users will functionally access this application: our FO Track Administrator(s) and the FO Track Consultant(s). To implement access rights (for both the database and the Web site) for these two groups, you must first create the groups within your NT domain.

Begin by opening the User Manager For Domains tool that comes bundled with NT 4.0. Then, select the New Local Group option from the User menu. In the pop-up box that appears, you'll see text boxes for entering the group name and the group description. Name this group FO_Track_Admins.

To complete this task, you have to add users or other global groups to your newly created group. For our purposes, we have a global group that already contains all necessary members of our group—the Domain Admins group. The Domain Admins group is a built-in NT account that contains all designated Administrators of the domain. You may be wondering why you need to create a local group when you have a global group that contains all necessary users. In the event you have to add a user as an Administrator of your application, you can do so by adding that user to your FO_Track_Admins group and not the Domain Admins group. This concept is the backbone of our security model; we strive to manage security on a group level—even if this means creating local groups and leveraging as much as possible in already functional global groups.

Our next account is application specific: the FO_Track_Consultants group. This group contains all users of the application. You perform the same steps as you did with the FO_Track_Admins account. However, because no predefined global group fills the bill, you have to add specific users to this account.

SQL 7 security
With the domain security model in place, you are now able to map a specific SQL Server database to your newly created NT group accounts. First, you register your groups within your SQL Server (in this case, OFFSRV1). Attach to this server via Enterprise Manager (or the Microsoft Management Console) and add these two groups as user accounts on the SQL Server. Keep in mind that you must have Administrative access to the SQL Server to perform this task.

Next, navigate to the Security folder on the server. Expand that folder and right-click the Logins icon. Then, click the New Login menu option to open a dialog box containing specific user data. On the General tab, you'll specify how this new login will access SQL Server. Since you're using NT group security, select this option. You will then designate the domain where your user is coming from. (In this case, you are working with the Tennis Domain.) Now, type in the user (in this case, group) account name—FO_Track_Admins. To complete the entries on the General tab, specify the FO Track database (FOTRACK) as the default for this group, as shown in Figure A. This means that when the user is logging into your SQL Server, by default the user will access the FO Track database.

Figure A
Specify FOTRACK as the default database for your group.


You must also configure the server roles and database access associated with this group by clicking the appropriate tab. For this account, you don't need to designate any special rights on the server level. Just assign this group access to the FO Track database and set the permissions on this database as dbowner. This way, you assign Administrative capability over this database to the FO_Track_Admins group.

While on the Database Access tab, add the FO_Track_Consultants group as well. However, keep in mind that the Consultants group will not have dbowner access. Assign this group only public access rights, as shown in Figure B. Assigning only public access rights gives you the ability to then restrict further the options available to the user or group in the future by creating specific server roles and applying them on a SQL object level. An example of this would be allowing only specific users (or groups) the right to execute stored procedures within a database.

Figure B
Assign the FO_Track_Consultants group only public access rights.


Both groups now have appropriate access within the FO Track database. You have successfully mapped the newly created NT Group accounts to your SQL Server. Next, I'll examine the role IIS plays in completing the connection.

IIS
In order for your Web application to recognize and have the ability to pass the user credentials from the browser to the calling application, you must configure NT Challenge/Response security on your site. This security option, however useful, is applicable only when being used in conjunction with Internet Explorer.

NT Challenge/Response performs a couple of critical functions for your application. First, you have the ability to validate your user against the NTFS security permissions applied on the files your application will access. This option plays a great role in creating intra- and extranet systems. Second, you now have captured and sent to the server the username and password. You can leverage this when using application options such as DSN—there are no specific user credentials passed from calling application to database. (I use this type of configuration to limit the amount of security data stored within my code.)

To implement NT Challenge Response within your site, navigate to the Site Properties sheet within the Internet Service Manager (or again, the Microsoft Management Console). Select the Directory Security tab, choose Anonymous Access And Authentication Control, and press the Edit button to open the Authentication Methods dialog box. Deselect the Allow Anonymous Access option and make sure the Windows NT Challenge/Response check box is selected, as shown in Figure C.

Figure C
Be sure the Windows NT Challenge/Response option is selected.

Joe Barnes is a senior project manager for Fundamental Objects, Inc., working with large-scale infrastructure and software development projects. His specialties include Windows NT, Visual Basic, and Lotus Notes. If you'd like to contact Joe, send him an e-mail.

If you'd like to share your opinion, please post a comment at the bottom of this page or send the editor an e-mail .
0 comments

Editor's Picks