Maintaining a User-Level Security Strategy

The majority of access to SQL Server takes place by users who need to perform queries that retrieve or update data. As a result, the user security policy that you adopt will likely change and evolve over time to meet the changing needs of your users.

SQL Server 2005 has expanded upon the security policies in previous versions in a number of ways. For one thing, schemas have been added, more granular permissions are available on objects, and new policies are available for accounts. The following sections will identify many of these changes and provide guidelines on how to best include them in your policy.

Verifying the Existence and Enforcement of Account Policies

SQL Server 2005 offers two authentication schemes, as it has for many versions in the past: Windows-authenticated logins and SQL Server–authenticated logins. Each of these has its advantages and disadvantages, and likely both will be used in most locations.

Using Windows Authentication

Windows-authenticated logins take advantage of the Active Directory authentication that exists in a Windows forest or domain. SQL Server uses the authentication of the domain to check whether a particular user is allowed to connect to SQL Server and accepts the authorization of the Windows account. This does not mean every Windows account can access SQL Server; specific Windows users and groups must be added as logins to SQL Server before they are allowed access. This just means that the user is authorized by Windows and does not need to reenter their username and password.

This also means the password policies and restrictions will be applied at the domain level and that SQL Server will abide by those same restrictions. The accounts accessing SQL Server are subject to all the restrictions of Windows accounts: lockout, password changes, and so on. If you have automated processes or services that access SQL Server through Windows authentication, be aware that these restrictions apply and that passwords may expire and need to be changed.

Using SQL Server Authentication

In the past, SQL Server logins have been authenticated by SQL Server itself, comparing the credentials submitted to those stored in the master database, but there have been no restrictions or requirements applied to passwords. With SQL Server 2005, this can now be changed. If installed on Windows 2003 or later, SQL Server logins can now be subject to the following Windows policies on that server:

• Password policy

• Password expiration

• Immediate password change

These are now check boxes in the Login Properties dialog box (shown in Figure 10.2), and you can set them individually for each login. We’ll discuss each in the following sections.

Figure 10.2: SQL Server Login Properties dialog box

Enforce Password Policy

If this option is checked for a login, then the password is checked against the NetValidatePasswordPolicy API call. This function will verify that the password meets maintain the following rules:

• The password must be at least eight characters long.

• The password must not contain all or part of the username. This happens when three or more consecutive alphanumeric characters delimited by whitespace, a comma, a period, or a hyphen match the username.

• The password must contain characters from three of the four following areas:

  • Uppercase letters (A through Z)

  • Lowercase letters (a through z)

  • Base 10 numbers (0-9)

  • Nonalphanumeric characters such as the exclamation point (!), at symbol (@), pound sign (#), dollar sign ($), and so on.

If a password does not meet these requirements, then it will not be accepted in either the new login dialog box or in the properties dialog box for an existing login. If this option is set for the login, then these requirements are also enforced when a login changes its password using SQLCMD or another application.

Enforce Password Expiration

The password expiration follows the same setting as for the host Windows server logins. When a password is set or changed, the date is noted in the master.sys.server_principals system view, and this is checked on each login to determine whether the password has expired. If the password has expired, then the user is prompted to enter a new password before continuing with the session.

The Enforce Password Policy and Enforce Password Expiration check boxes are not necessarily related. You can check either of them, both, or neither, depending on your particular requirements.

User Must Change Password at Next Login

This option is checked by default whenever a new password is entered. This forces a password change by the login as soon as the next session is established but before any batches can be processed. Since many users neglect to change passwords unless prompted, this prevents the administrator who set the password from knowing the user’s password indefinitely. It is a good security policy that prevents the administrator from gaining unauthorized access. It also ensures the user will choose their own password, which increases the likelihood they will remember it.

Comparing Windows and SQL Server Authentication

So, which is better-Windows authentication or SQL Server authentication? The answer is that it depends on your situation. Both types of logins have their positive attributes and their negative detractors. Both are also suited to different types of applications.

Microsoft recommends Windows authentication where possible because no additional administrative effort is required to manage a separate set of passwords and because the users do not need to remember a second password. However, this requires that you have an Active Directory domain set up and that you can easily map your Windows groups to SQL Server security.

SQL Server logins work in heterogeneous environments with Unix, MacOS, or other operating system clients. They also have the advantage of requiring that users be specifically set up in SQL Server and no clients “sneak into” the SQL Server because of membership in a Windows group.

Verifying SQL Server Login Authentication

You should check user accounts accessing SQL Server to ensure that they are properly set up and that their configuration does not compromise the security of the SQL Server instance. This should entail checking the permission assignments as well as the policies that are in place.

The strength of passwords in either Windows-authenticated logins or SQL Server– authenticated logins comes from the Windows operating system. In the former case, the Active Directory policy in a domain will determine what restrictions and requirements are being mandated. In the case of the latter, the local Windows host on which SQL Server is installed determines the password policies that are applied. In the case of a domain, it is possible that the domain determines the local Windows settings.

In either case, the DBA should ensure that a strong policy is in place that follows security best practices. Microsoft recommends the following:

• Password length   A strong password should be at least eight characters and preferably more.

• Minimum password age   This determines how long a password must be used before it can be changed. A value of at least two days is recommended.

• Maximum password age   This determines how long a password can exist before it must be changed. It is recommended that this value be set to 42 days.

• Password history   This determines the number of unique passwords a user must use before an old one can be reused. This should be set to 24 passwords.

• Password complexity   This determines the makeup of the password. The list given earlier in the “Enforce Password Policy” section should be used here.

If you are using SQL Server–authenticated logins, some of these may not apply, but you should ensure that your domain meets or exceeds these requirements for strong security.

Note 

These recommendations are published on Microsoft’s TechNet website at http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/directory/activedirectory/stepbystep/strngpw.mspx.

Verifying Permissions on SQL Server Roles and Accounts

Each user who accesses an object in SQL Server 2005 has their permissions checked to be sure they are allowed to access that object. SQL Server 2005 greatly expands the number and scope of permissions, allowing granular control of what rights a particular user or role has against objects or groups of objects.

Warning 

SQL Server 2005 has changed the paradigm with additional permissions types that were not available previously. You should revisit all guidelines developed on previous versions of SQL Server to be sure they still provide strong security under this new paradigm.

Inside SQL Server, there are two levels of access: server-level and database-level. You will examine each of these and the different types of security that apply at each level.

Securing Server Logins

In addition to service accounts, you must secure one more class of accounts at the database server level. Each client or process that wants to access the SQL Server instance does so through a login account. These accounts give the user or role the right to access server-level objects. Each particular object or operation still requires its own permission, but the scope of rights being granted at this level is for the entire server.

Before any user can access a resource in SQL Server, the user must have a login on the server and be authenticated. A system administrator creates a login and assigns it rights and privileges. The logins can be either Windows users, Windows groups, or SQL Server–authenticated logins. The CREATE LOGIN command adds a login to the SQL Server. The syntax is as follows:

 CREATE LOGIN loginame WITH [options] 

where the options include the default database, the default language, and the various password options. For example, to create a new user named Delaney with a password of D#L@Ney7 and the requirement it be changed on the next login, the syntax would be as follows:

 CREATE LOGIN Delaney WITH PASSWORD = 'D#L@Ney7' MUST_CHANGE

With Windows users and groups, no password is specified because the Windows credentials are used to authenticate the user.

In addition to assigning specific users, you can use certificates to authenticate other services, such as Service Broker connections. The certificate must be created and then specified in the CREATE LOGIN command.

Credentials

SQL Server 2005 includes one additional server-level feature to associate with logins. A credential is a mapping to a Windows account that is associated with a login in SQL Server. The security context of the credential is used when accessing resources outside SQL Server, such as Windows resources.

A credential must be created and then associated with a login using the CREDENTIAL = credential_name option of the CREATE LOGIN command.

Server-Level Permissions

Each user or role that accesses SQL Server resources is referred to as a security principal. A login is a server-level principal and is granted rights that apply to the server. These include the ability to connect to SQL Server (through the CONNECT permission as well as being enabled), the ability to use resources under another user’s context (through the IMPERSONATE permission), and the ability to affect the operation of the server (through membership in server-level roles).

Server-level permissions are controlled with the GRANT, DENY, and REVOKE statements. The GRANT statement bestows the permission on the object to the user, and the DENY statement prevents the user from using that permission. The REVOKE command is used to undo the GRANT or DENY permission. Any object on which permissions can be granted is a securable.

These commands have two additional options. The WITH GRANT option allows the login that receives the permission to in turn grant it to other logins or users. The CASCADE option will apply the action to all other users or logins that were granted the permission by the user or login specified.

At the server level, the permissions described in Table 10.3 exist. In general, you will not be granting these permissions directly if possible, but rather using server roles, which are discussed in the next section.

Table 10.3: Server-Level Permissions
Open table as spreadsheet

Permission	Description
ALTER	Alters the properties of an object
CONTROL	Controls all aspects of the object such as the object owner
CONNECT	Connects to SQL Server
CREATE	Creates objects
VIEW	Views server metadata

Note 

Rights granted at the server level do not necessarily imply that any rights exist at the database level.

If you do need to grant these permissions, you would use one of the three statements along with the permission and the object on which it is being granted as well as the user receiving the permission. As always, the least amount of privileges necessary should be granted to a login. For example, to allow the user Kendall to create a database, you would run the following command:

 GRANT CREATE DATABASE TO Kendall

Using Server Roles

Several server roles are built into SQL Server 2005 and cannot be removed or altered. These roles are designed to allow you to grant common sets of permissions to logins and allow them to administer part of the server without requiring them to have permissions to every aspect of the server. Table 10.4 lists the roles and their permissions.

As always when granting permissions with these roles, grant only the minimum permission needed to perform the particular task. For example, if the login Kyle needs to create databases, he should be granted the dbcreator role only.

Table 10.4: Server-Level Roles
Open table as spreadsheet

Role	Rights	Server-Level Permission
bulkadmin	Members of this role can run the BULK INSERT command.	ADMINISTER BULK OPERATIONS
dbcreator	Members of this role can create, alter, drop, and restore any database.	CREATE DATABASE
diskadmin	Members of this role can manage disk files.	ALTER RESOURCES
processadmin	Members of this role can terminate processes running on SQL Server.	ALTER ANY CONNECTION, ALTER SERVER STATE
securityadmin	Members of this role can manage server logins, grant server-level and database-level permissions, and reset passwords for logins.	ALTER ANY LOGIN
serveradmin	Members of this role can change server configuration options and shut down the server.	ALTER ANY ENDPOINT, ALTER RESOURCSE, ALTER SERVER STATE, ALTER SETTINGS, SHUTDOWN, VIEW SERVER STATE
setupadmin	Members of this role can add and remove linked servers and execute certain system stored procedures.	ALTER ANY LINKED SERVER
sysadmin	This role grants rights to perform any function on the SQL Server instance.	CONTROL SERVER (WITH GRANT option)

An administrator can add or remove logins from these roles with SQL Server Management Studio or by executing T-SQL commands. You can use three main system stored procedures to manage roles and their members:

• sp_addsrvrolemember   This stored procedure adds a login to a particular server role.

• sp_helpsrvrolemember   This stored procedure displays a list of logins that are members of a particular role.

• sp_dropsrvrolemember   This stored procedure removes a login from a server role.

In SQL Server Management Studio, you can use the Security folder under a SQL Server instance to view server roles and their members.

Enabling or Disabling Logins

One of the new features of SQL Server 2005 is the ability to enable or disable a login, thereby allowing or denying access to the SQL Server instance. This allows you to temporarily prevent a particular login from accessing the server without removing their permissions. This helps ease administrative effort and allows you to tighten security for those accounts that are used rarely or sporadically.

This is useful in quite a few places, some of which are listed here:

• An employee on vacation or leave

• Consultants working part-time or on a semiregular basis

• Technical support personnel from Microsoft or some other vendor who might periodically need access to your server

You can enable or disable a login in two ways. You can choose the Status tab from a login’s properties and check the appropriate box, or you can use T-SQL. The following command will disable access for the login Tia:

 ALTER LOGIN Tia DISABLE

In prior versions of SQL Server, you could not disable the sa account unless Windows-only authentication was set. In SQL Server 2005, you can disable the sa account. In addition, you can rename the sa account, providing one small additional layer of defense against hackers.

Auditing Login Events

One of the key aspects of ongoing security monitoring is the auditing of resource usage on your SQL Server. Auditing logins is crucial since every access to a resource inside SQL Server requires a login of some sort. This section will examine the various ways in which you can audit login activity.

The most basic way of auditing logins is to set the server to log successful, failed, or both login events. Figure 10.3 shows the Security Properties dialog box for the server. If you choose to audit events in this way, they will be written to the SQL Server error log and the Windows Event Log.

Figure 10.3: SQL Server Security Properties dialog box

The next method of auditing logins is to use the SQL Trace stored procedures to create a Profiler trace that captures these events. You can use a number of stored procedures to create a trace and add events and filters. You can set a trace to start when the server starts and log to a file or table. More details on SQL Trace are available in Chapter 4, “Designing a Strategy to Monitor a Database Solution.”

To ensure a comprehensive audit of all login events, you should add all the login events, including creating new logins, to the trace. Here’s a list of events:

• Audit Add Login to Server Role Event

• Audit Addlogin Event

• Audit Broker Login Event

• Audit Login event

• Audit Login Failed event

• Audit Logout event

The last method of auditing logins is to set an Event Notification for the login classes. The notifications are sent to a Service Broker queue for processing, so you must have a Service Broker queue on the same or a separate server for receiving the events. The events are similar to those shown previously for SQL Trace.

Implementing Database Security

The next level of security below the server is the database level. You now will examine the basics of how security works inside a SQL Server 2005 database because this is where the majority of your security will be applied to your data.

A user is the term for any principal that has access to a database. Each login at the server level must be mapped to a user in a particular database in order for that connection to access the database. The names are typically the same for administrative ease, but there is no requirement that a user in a database be named the same as the login to the server.

Note 

If the guest user exists inside a database and a login attempts to access a database for which they have not been mapped to a login, they will be mapped to the guest user and receive the rights that this user has.

Figure 10.4 shows the users set up in the database Mydb2. By navigating to a particular database and selecting the Security folder and then the Users folder, you can get a list of all users allowed to access a database.

Figure 10.4: Users in a database

Each user inside a database must be granted explicit permissions to access any objects inside the database such as tables, views, stored procedures, CLR functions, and so on. You grant permissions using the same GRANT, REVOKE, and DENY commands discussed earlier, but with a different set of permissions at the database level.

There are two ways of receiving permissions: through membership in a role or explicit assignment. The recommended method of granting rights inside a database is the same as at the server level or Windows domain level: add the user to a group or role, and grant explicit permissions to the role. The next section will explain the roles inside a database.

Database Roles

Just as there are predefined server-level roles, there are also predefined, or fixed, database roles. However, unlike the security model at the server level, at the database level two additional types of roles can be user-defined. Table 10.5 lists the predefined roles, and you should use them when granting database-level administrative permissions.

Table 10.5: Fixed Database Roles
Open table as spreadsheet

Fixed Database Role	Permissions
db_accessadmin	This role allows its members to add or remove access for logins and groups.
db_backupoperator	This role allows its members to back up a database.
db_datareader	This role allows its members to read (SELECT) data from all user tables in the database.
db_datewriter	This role allows its members to update information (INSERT, UPDATE, and DELETE) in all user tables in the database.
db_ddladmin	This role allows its members to run any Data Definition Language (DDL) command in the database.
db_denydatareader	This role is equivalent to issuing a DENY SELECT on all user tables.
db_denydatawriter	This role is equivalent to issuing a DENY INSERT, UPDATE, and DELETE on all user tables.
db_owner	This role allows its members to perform any configuration or maintenance commands inside the database.
db_securityadmin	This role allows its members to change role membership and modify permissions.

Two other types of roles exist: user-defined roles and application roles. Both are created by an administrator and contain custom sets of permissions as defined by the administrator to particular objects.

User-defined roles are groups created by the administrator and to which permissions are assigned. These can be any set of permissions on any of the objects inside the database. A user who is a member of a user-defined role receives the permissions granted to the role in addition to any permissions that have been explicitly granted to the user. If a user is a member of two or more roles, they receive the union of the permission sets.

Application roles are similar to user-defined roles in that an administrator creates the role and assigns it the permissions needed by the role. This role is not assigned to a particular individual. Instead, any user can “invoke” the role by using the sp_setapprole stored procedure and supplying a password. When the user invokes the application role, the user loses their existing permissions and executes everything under the context of the role.

Tip 

Application roles are useful when you want to ensure a particular application is used to access your database. By invoking the role in the application and not assigning rights to the data except with the application role, you can force the application to be used to update or read data.

Schemas

Prior versions of SQL Server did not include the concept of a schema. Instead, every object was owned by a particular user, and security permissions were not easily changed if that user needed to be removed. Because of this administrative burden, in most databases the dbo user owned all objects to simplify permission assignment.

SQL Server 2005 introduced the concept of the schema, which is a construct in many other relational database systems. Instead of an owner grouping objects inside the database, the schema is used. This provides a namespace that allows objects in different schemas to have the same name. Objects can still be owned by users as can schemas, but a user can receive permissions to a schema, allowing them to receive those permissions on all objects in the schema. This is particularly useful if groups of objects can be separated by function or even application. It also allows the permissions and naming to remain the same if a user that owns the schema is removed. The schema can be transferred to a new user rather than having to rename all objects.

Note 

The four-part naming scheme from prior versions of SQL Server changes with the introduction of the schema. Instead of it being server.database .owner.object, it is now server.database.schema.object.

Impersonation

In SQL Server 2000, only system administrators could alter their execution context in the middle of a session. In SQL Server 2005, any user has the ability to change context to that of any other user for which they have IMPERSONATE permissions.

This is accomplished with two different users of the EXECUTE AS command. A user can change their context in a session by calling the EXECUTE AS statement in a batch. All subsequent batches executed will be in the security context of the user named in the EXECUTE AS statement. A user can return to their previous context by executing a REVERT statement, disconnecting and reconnecting to the server, or using another EXECUTE AS command.

The second way of changing context for a user is for an object to be compiled with the EXECUTE AS clause in its header. In this form, the individual compiling the object can specify a specific user account or the object owner to be used as the execution context for the module. A user executing the function, stored procedure, trigger, or other module does not need permissions to the objects referenced in the module; the user needs only the permissions to execute the module.

By using this second form of the EXECUTE AS command, a user can be given access to objects referenced inside a module without getting permissions to the objects themselves. This allows a minimal level of permissions to be assigned to users.

Verifying Permissions

Now that you have examined how permissions work inside SQL Server for users, you’ll look at the methods for verifying that permissions and rights are properly assigned.

Just as with service accounts and service-level permissions, the user-level permissions should be assigned with the principle of least privilege. This means that only those rights needed by a user to complete a task or job should be assigned to that user.

You can use a number of tools within SQL Server to assist you in verifying permissions. Management Studio makes it easy to check on server-level or database-level role membership. By navigating to the Database Roles folder and selecting a role, you can easily check membership. Exercise 10.3 shows how to check on server role membership.

Exercise 10.3: Auditing Server Administrators

This exercise will show how to conduct an audit. In this particular case, you will check to see which accounts are sysadmins on an instance of SQL Server 2005.

1. Start SQL Server Management Studio from the Start menu by clicking Start All Programs SQL Server 2005 SQL Server Management Studio.

2. Connect to your instance of SQL Server 2005 with the proper credentials.

3. Expand the Security folder, as shown here in Object Explorer. In this case, the instance is named SS2K5; yours may be named something else.

   

4. Expand the Server Roles folder, and right-click the sysadmin role, as shown here. Select the Properties menu item.

   

5. You will receive a dialog box like the one shown here, but you should have a different list of administrators. In this case, you can see that six accounts have administrator access.

   

6. Since this is too many accounts and the documentation does not list the mysys account, click the mysys account, and then click the Remove button at the bottom to remove this account. You can see here that this account has now been removed and the audit is complete.

   

Inside a database, you can also check the permissions of a particular user by using the sp_helpuser stored procedure. This procedure reports on permission and role membership for any or all users. By running this stored procedure within a particular database, you will display all of their role memberships.

To see explicit permissions, you would need to check each user or object to verify which explicit permissions are assigned. Since explicit permissions should not be assigned to users, it is easy to query the catalog views inside SQL Server for explicit permission assignments not made to roles. This data is contained in the sys.database_principals and sys.database_ permissions catalog views. You can then easily move those permissions to roles.

Just as with the server level, you should document all permissions that are assigned according to your strategy and store them in a safe location. As you assign users to roles, document their membership so that a security audit can easily determine whether the current permissions match what is expected.

Domain and Directory Permissions

Rarely does a SQL Server exist in an isolated environment in today’s world. Instead, a SQL Server is a part of a larger set of computers usually contained in an Active Directory forest or domain. In this situation, global policies will be applied to OUs, which contain computers or users. These policies override the local policies that are set up on local computes. Therefore, the security of your SQL Server depends on the security of the overall Active Directory environment.

Several tools are available to Active Directory administrators that allow them to examine the effect of their policies on different parts of the domain. The Result Set of Policy (RSoP) tool allows an administrator to see the sum of all the various policies applied at different levels of the Active Directory, including the local computers, to determine the final set of settings for a particular computer. As a SQL Server DBA, you should be sure that the results of all policies at higher levels do not compromise the security of the data you are protecting.

Another tool that you might be able to run is the Security Analysis and Configuration snap-in for the Microsoft Management Console. You can use this tool to create templates for a particular type of computer, such as a mail server or database server. A DBA can develop a consistent template for their SQL Servers that meets the security requirements of their organization. The DBA can then use this template to analyze the various servers and determine whether they are correctly configured. The DBA can also use the templates to apply the template’s settings to computers if you have the proper rights.

It is important that SQL Server DBAs work closely with other system administrators to maintain a consistent security framework. Since all SQL Server computers will likely require similar security settings, you may want to request a special OU be created for all SQL Servers and that all servers running SQL Server 2005 be placed in this OU with administrative permissions set specifically to meet your security requirements. Also, be sure that as you continue to refine your security policy that each of you is aware of any changes made by the other.