Creating a Manageable Security Plan

In revamping the way SQL Server implements security in the 7.0 release, Microsoft created a flexible and powerful method for managing users' access both to the SQL Server system and to the databases. The changes also created confusion as administrators tried to upgrade from SQL Server 6.5 security. The confusion about the differences between SQL Server 7.0 and SQL Server 6.5 security led many administrators either to leave their security implementation alone or to inadvertently leave holes through which unauthorized users could get to the data. With the approach this article presents, you can build a security plan for SQL Server 7.0 (or SQL Server 2000) that is not only manageable and flexible but also verifiably secure.

Authentication Choices
In this article, I distinguish between the terms authentication and authorization. Authentication refers to verifying a user's identity; authorization refers to what a user is allowed to do. For this discussion, authentication occurs when the user logs in to SQL Server, and authorization occurs whenever a user attempts to access data or execute a command.

The first step in building a security plan is to decide how SQL Server will authenticate users. SQL Server authentication matches an account and password to a list stored in the Sysxlogins table of the Master database. Windows NT/2000 authentication asks a domain controller to validate the user's credentials. In general, always use Windows NT/2000 authentication for installations where the server has access to a domain controller. The domain controller can be either a Win2K or an NT server because in both cases SQL Server receives an access token, which is a special list built during authentication that contains the user's SID and a list of SIDs for the groups of which the user is a member. As I show later in this article, SQL Server assigns database access and permissions based on those SIDs. Note that how the OS builds the access token is unimportant. SQL Server uses the SIDs only in the access token, so whether you use SQL Server 2000, SQL Server 7.0, Win2K, or NT is unimportant. The result is the same.

The biggest benefit of using SQL Server authenticated logins is that they're simple to implement through Enterprise Manager. The major drawback is that SQL Server authenticated logins are local to a particular server, which means that they're difficult to manage in a multiserver environment. A lesser but still significant drawback is that with SQL Server authentication, you have to manage permissions on each database individually. If a user needs the same permissions in two databases, you must either assign the permissions manually or build a script to assign them. If you have a small number of users, say fewer than 25, and their permissions change infrequently, SQL Server authenticated logins might work for you. In almost all other cases (exceptions include an application that manages security directly), the administrative overhead in managing these logins outweighs their benefits.

Web Authentication
One situation that often breaks down even the best security plans is the use of SQL Server data in Web pages. The typical way to handle authentication in such a situation is to embed a SQL Server login and password in a Web-server-based program, such as an Active Server Pages (ASP) or Common Gateway Interface (CGI) script. The Web server takes responsibility for authenticating the user, then the program uses its own login (either the systems administrator—sa—account or a login account in the Sysadmin server role for convenience) to access the data for the user.

This arrangement has several drawbacks, the most significant of which are an inability to audit actions on the server, dependence on the Web program to authenticate users properly, and a lack of differentiation among users when SQL Server determines permissions. If you're using Microsoft IIS 5.0 or IIS 4.0, you have four options for authenticating users. The first option is to create an NT account for anonymous users for each site and virtual directory. All programs will then use that security context when they log in to SQL Server. By granting the anonymous NT account the appropriate permissions, you can improve auditing and authentication functionality.

The second option is to have each site use Basic authentication, in which users must type a valid account name and password into a dialog box before IIS permits them to view a page. IIS validates the login credentials against an NT security database that can reside on either the local server or a domain controller. When the user runs a program or script that accesses SQL Server, IIS sends the server the login credentials the user provided to view the page. If you use this option, remember that the transfer of the account and password between IIS and the browser usually isn't encrypted; therefore, you need to implement Secure Sockets Layer (SSL) on any Web site that uses Basic authentication.

If you use Microsoft Internet Explorer (IE) 5.0, IE 4.0, or IE 3.0, you have a third option. You can enable NT authentication on both Web sites and virtual directories. IE will send IIS the credentials the user logged on to the computer with, and IIS will use those credentials whenever that user tries to log in to SQL Server. In this simple way, you can authenticate users in a remote site's domain who log on to a domain that has a trust relationship with the domain the Web server uses.

Finally, if your users have personal digital certificates, you can map those certificates to NT accounts in the local domain. Based on the same technology that server digital certificates use, a personal certificate validates a user's identity and therefore can replace NT's Challenge/Response authentication algorithm. With this option, a user doesn't need to log on to a domain that trusts or even recognizes the domain where IIS resides. Both Netscape Navigator and IE will send certificate information automatically to IIS with every request for a page. IIS includes a tool that lets administrators map those credentials to an NT account, which means that the digital certificate replaces the usual login process of supplying an account name and password.

You have many options for authenticating users with NT accounts, even when the users connect to SQL Server across the Internet through IIS. Therefore, plan to use NT authentication as your principal means of authenticating users.

Users Travel in Groups
The next step in building the security plan is to identify which groups your users fall into. Typically, every organization or application has broad categories of users who need a specific type of access to data. Accounting applications usually have users who are data entry operators, data entry managers, report writers, accountants, auditors, and accounting managers. Each group needs a different kind of database access.

The easiest way to handle data access permissions for the different groups is to create a domain global group that matches each group of users. You can either create the groups separately for each application or create the groups to cover broad categories that apply throughout the organization. I prefer to create groups that are specific to each application so that I can know exactly what the members need to be able to do. Using the accounting example, you would create groups named Accounting Data Entry Operators, Accounting Data Entry Managers, and so on. Remember that for easy management, it pays to give your groups names that clearly relate to their purposes.

Besides application-specific groups, you need a few basic groups whose members manage your servers. As a rule, I recommend creating groups with names such as SQL Server Administrators, SQL Server Users, SQL Server Denied Users, SQL Server DB Creators, SQL Server Security Operators, SQL Server Database Security Operators, SQL Server Developers, and DB_Name Users (where DB_Name is the name of a database on the server). You can create other groups as you need them.

After you create the global groups, you can grant them access to SQL Server. First, create an NT authenticated login for SQL Server Users and grant login rights. Make Master the default database, and don't grant access to any other database or make the login account a member of any server role. Next, repeat the process with SQL Server Denied Users, but in this case, deny login access. After you've created these two groups, you have a simple way to permit and deny users access to the server. SQL Server lets users log in if any of the groups in the users' access token has appropriate permission; it rejects a login if any group is denied access, as does the No Access permission in NTFS.