Lesson 1:Overview of SQL Server 2000 Security | MCSE Training Kit (Exam 70-229): Microsoft SQL Server(TM) 2000 Database Design and Implementation (Pro Certification)

3 4

In this lesson, you will learn about SQL Server security by using a six-layer model. The lowest layer is physical security. Physical security includes securing access to the facilities that contain the internal network and server equipment used to support SQL Server. Network protocol security is the second layer. This layer includes items such as transport protocol isolation and packet encryption. The third layer is domain security. Domain security is implemented in a Microsoft network by using Active Directory (AD) directory services and domains. The fourth layer is local computer security. This layer includes operating system auditing, file permissions, registry access, and file encryption services. The fifth layer is SQL Server security. SQL Server security includes authentication, authorization, encryption, and auditing services. The sixth layer is application security. An application can enhance SQL Server security by providing its own security features.

After this lesson, you will be able to:

Describe SQL Server security architecture by using a six-layer paradigm.

Estimated Lesson time: 35 minutes

Physical Security

Physical security applies to all forms of computer data protection, not just to SQL Server database protection. This vital layer of security is overlooked in many networks and leaves a company vulnerable to data theft, vandalism, and environmental calamities. Physical security includes securing vital server and network hardware by placing it in limited-access spaces. For very large networks, this space could be an entire building or campus of buildings, and for medium-sized networks it could be a data center. For small networks, it could be a closet. To protect against environmental calamities, regular backups must be scheduled and stored off site.

NOTE

For more information about security basics, refer to Microsoft's security Web site at http://www.microsoft.com/security/.

Network Protocol Security

Data traveling from a client to SQL Server can be encrypted so that anyone who is intercepting the packets from the network using a packet analyzer will not be able to read the contents of the packets. Packet encryption is implemented between the client and SQL Server (application layer encryption) by using the Secure Socket Layer (SSL) protocol or remote procedure call (RPC) encryption. Packet encryption is implemented between two computers running Windows 2000 by using IP Security (IPSec). IPSec is also supported in many modern routers. Use the SQL Server 2000 Client Network Utility to configure SSL for Net-Libraries. Figure 13.1 shows how to enable encryption in the Client Network Utility.

figure 13.1-enabling packet encryption for all enabled protocols.

Figure 13.1 Enabling packet encryption for all enabled protocols.

SSL also requires SQL Server to be assigned a certificate. For more information, refer to the Windows 2000 Server Resource Kit.

Enabling encryption slows SQL Server network performance. Create a performance baseline before enabling encryption. After enabling encryption, determine whether the performance degradation warrants enabling this feature. Some of the Net-Library protocols contain their own security features. For example, the Multiprotocol Net-Library uses RPCs and the RPC encryption application programming interface (API), so enabling SSL packet encryption for this protocol is unnecessary.

There are other protocol-specific security features. For example, use a unique port rather than the default of 1433 for Transmission Control Protocol/Internet Protocol (TCP/IP), and use IPSec as the underlying security protocol for TCP/IP. Stop Named Pipes server announcements so that the server does not appear in graphical interfaces such as the SQL Server drop-down list box in Query Analyzer. Disable Mailslots broadcast so that the server's NetBIOS name does not appear on the network.

Another common network protocol security method is to implement a firewall solution, such as Microsoft Internet Security and Acceleration Server, or a dedicated hardware solution. A simple security measure is to create a protocol firewall by using a protocol on the internal network that differs from the external network protocol, such as IPX/SPX internally and TCP/IP externally. The internal network can then be accessed from the outside by a virtual private network (VPN) connection.

NOTE

Refer to the Windows 2000 Server Resource Kit for more information about network security technologies.

Domain Security

A SQL Server 2000 computer that is a member server of a Windows NT or Windows 2000 domain can authenticate users by the security identifiers (SIDs) associated with the user. To accomplish Windows authentication, a database administrator assigns Windows NT/Windows 2000 user or group accounts with privileges to access and perform tasks in SQL Server. Windows domain user accounts provide secure validation and account lockout. Windows domain passwords are encrypted to prevent unauthorized network interception, are case-sensitive to make them difficult to determine, and include policies such as password expiration and minimum password length. A password assigned to a SQL Server login ID is also case-sensitive if a case-sensitive sort order is specified when SQL Server is installed. Windows domain passwords are always case-sensitive when logon occurs from a Windows NT or Windows 2000 client. The SQL Server facility that makes it possible for Windows domain accounts to establish a connection with SQL Server is discussed in the SQL Server Security section of this lesson.

Security account delegation is another powerful feature available to Windows 2000 networks that are running Active Directory directory services and the Kerberos mutual authentication protocol. This feature enables a user's identity to be verified when one computer connects to another computer on the network. The user logs on once and the first computer verifies the user's identity to the second computer on the user's behalf.

NOTE

For more information about configuring security account delegation, refer to "Security Account Delegation" in SQL Server Books Online and refer to "Impersonation" and "Service Principal Names" in the Windows 2000 Server Resource Kit.

Local Computer Security

SQL Server 2000 runs on top of Windows 98, Windows Millennium Edition (ME), Windows NT, and Windows 2000. Windows NT Server, Windows 2000 Server, and more advanced editions of these two operating system products provide the highest level of local computer security available to SQL Server 2000. For this reason, the minimum operating system requirement for SQL Server 2000 Enterprise Edition is Windows NT Server or Windows 2000 Server. Windows NT Server and Windows 2000 Server provide security auditing through the Event Log service so that you can track events such as user logons and attempts to access file objects (such as the database files). File object auditing is available for all partitions running the New Technology File System (NTFS). NTFS is a requirement for running SQL Server 2000 on Windows NT or Windows 2000. NTFS provides additional security features, including local directory and file permissions and encryption services. Finally, SQL Server Setup automatically configures restrictions on sensitive SQL-related registry settings. Whenever you change your operating system's security, carefully test SQL Server operation to verify that performance levels are maintained and that the restrictions you configure will not disable SQL Server functions.

SQL Server Security

SQL Server provides a robust set of security services to protect the databases it maintains. The four categories of SQL Server security are authentication, authorization, auditing, and encryption. Database access occurs in two stages, first by connecting to SQL Server (authentication) and second by accessing a database and its objects (authorization). Object permissions either authorize or deny a user the ability to perform actions against database objects such as tables or views. Statement permissions either authorize or deny a user the ability to create objects or back up database and log files. Activity that occurs within the database is tracked through SQL Server auditing. This auditing mechanism is specifically designed to audit database objects, unlike the Windows NT and Windows 2000 Event Log service. Optionally, database objects such as stored procedures can be encrypted within the database to protect their contents.

Authentication

SQL Server 2000 supports two modes of authentication: Windows Authentication and SQL Server Authentication. Windows Authentication enables a local user account or a user account in a Windows domain (Windows NT or Windows 2000) to establish a connection with SQL Server. SQL Server Authentication enables a user to establish a connection with SQL Server by using a SQL Server login ID. Configure the server for an authentication method during SQL Server installation or afterwards from the SQL Server Properties (Configure) dialog box in Enterprise Manager, as shown in Figure 13.2.

figure 13.2-configuring sql server and windows authentication in enterprise manager.

Figure 13.2 Configuring SQL Server and Windows authentication in Enterprise Manager.

The authentication mode options are either to use both Windows Authentication and SQL Server Authentication, or to use just Windows Authentication. Configuring the server for both authentication modes is called Mixed Mode.

Using Windows Authentication achieves logon security integration because SQL Server depends on the local computer operating system or a domain controller to validate and maintain the user account. For example, if User01 is a local user account created on a standalone SQL Server named Server01, you can grant or deny SERVER01\User01 the connect right to SQL Server. If User02 is a domain user account created on a domain controller in Domain01, you can grant or deny DOMAIN01\User02 the connect right to SQL Server.

You can also grant or deny Windows NT and Windows 2000 groups authentication privileges. Members of the group inherit the grant or deny privilege configured for the group. A deny privilege supersedes a connect privilege assigned to a user or to any other groups of which the user might be a member. There are two types of groups in Windows NT and Windows 2000 that are granted the connect privilege: local computer groups and domain groups. Local computer groups are stored in the operating system on the computer running SQL Server. The two types of local computer groups are built-in local groups and user-created local groups. Domain groups are stored on Windows domain controllers. The three types of domain groups are domain local groups, global groups, and universal groups. Lesson 3 describes how to assign each type of group in SQL Server.

NOTE

Windows user and group accounts are generically referred to as Windows accounts.

Authentication succeeds if the account or group of which the user is a member is granted the connect right to SQL Server. Windows Authentication enables a user to log on to the local SQL Server computer or to the domain without having to separately log on to SQL Server. If SQL Server is configured for Mixed Mode, Windows Authentication is the default connection method.

In certain circumstances, SQL Server Authentication is the only way to establish a connection with SQL Server. The following situations require SQL Server Authentication in order to successfully establish a SQL Server connection:

When the client and the server are not part of the same logon namespace

If the computer running SQL Server is configured as a standalone server and the client is logged on to a domain, the client must use SQL Server authentication. If SQL Server is configured as a standalone server, the client is logged on locally, and the same user account and password combination exists on both the client and the server, then the client can use Windows authentication.

When SQL Server is running on Windows 98 or Windows ME

SQL Server Authentication is the only authentication mode supported by SQL Server that runs on Windows 98 and Windows ME.

When applications are written specifically to use SQL Server Authentication

Several SQL Server account objects are created when SQL Server is installed. The system administrator (sa) SQL Server login ID is created when SQL Server is installed. This account is assigned to the SysAdmin fixed server role and cannot be changed or deleted. A special user account object, the database owner (dbo), is mapped to sa and is created in each database. This mapping makes dbo a member of SysAdmin, as well. You will learn about server roles in the next section of this lesson. Another special account is the guest account, which enables any SQL Server authenticated user to access a database.

Authorization

Authentication alone is not enough to allow a user account to access a database. You must also assign permissions to an authenticated user account, group or role, object, or statement. Authorization is primarily assigned within the context of a database. This approach limits the scope of a user's access. For example, assigning permission to access a table within the Pubs database does not allow the user to access objects in the Master database. However, there are special administrative assignments whose scope is SQL Server.

Groups and Roles

Assigning permissions to individual users is time-consuming and difficult to maintain in databases that have a moderate to large number of users. SQL Server 2000 supports Windows groups and SQL Server roles to ease the administrative burden of assigning per-user permissions. The same groups available for authentication are also available for authorization. For example, you can assign a domain global group, GlobalGroup01 in Domain01, privileges to establish a connection with SQL Server (authentication) and to run a SELECT statement against a specific table or view in the database. Any domain users that are members of GlobalGroup01 will be able to run the SELECT statement providing that the Deny permission state isn't assigned elsewhere. You will learn about the Deny permission state later in this lesson.

Roles are similar to groups, but they are created and maintained within SQL Server. There are two types of roles: standard roles and application roles. Standard roles are assigned privileges that users inherit through role membership. Groups can contain Windows users, and depending on the group type, they can also contain other Windows groups. In contrast, a standard role can contain all security account types: Windows user and group accounts, SQL Server login IDs, and other standard roles.

Group and standard role nesting (groups containing other groups and roles containing groups or other roles) enable you to build a privilege hierarchy because privileges are cumulative. For example, if User01 is a member of Role02 and Role02 is a member of Role01, Role02 is subordinate to Role01. If you then assign a privilege to Role01 and a privilege to Role02, User01 is assigned both privileges. Figure 13.3 demonstrates this hierarchical relationship.

figure 13.3-a hierarchical relationship showing that user01 inherits the privileges assigned to role01 and role02.

Figure 13.3 A hierarchical relationship showing that User01 inherits the privileges assigned to Role01 and Role02.

SQL Server includes predefined standard roles to simplify server and database administration. The two primary categories of predefined roles are fixed server and fixed database. Fixed server roles enable role members to administer the server. For example, if you make a user a member of the ServerAdmin fixed server role, the user can configure server-wide settings. SQL Server Setup adds the Windows Administrators group (BUILTIN\Administrators) as a member of the SysAdmin fixed server role. Fixed database roles enable role members to administer a specific database. For example, if you make a user a member of the db_BackupOperator fixed database role, the user can backup the database. To see the privileges assigned to each fixed database role, run the sp_dbfixedrolepermission fixed_db_role_name system stored procedure. To see the privileges assigned to each fixed server role, run the sp_srvrolepermission fixed_server_role_name system stored procedure.

NOTE

For a list of predefined roles, refer to "Adding a Member to a Predefined Role" in SQL Server Books Online.

The Public role automatically contains all database users, groups, and roles. This special role is similar to the Everyone special group in Windows NT and Windows 2000. You cannot add or remove members from this role or delete the role. The Public role is contained in every database and is assigned default permissions to each database. You can revoke permissions from the Public role to secure the database. Create standard roles in the database if Windows groups do not meet your administrative requirements.

There are times when an application requires permissions that are incongruent with user permissions. In this case, using SQL Server permissions to configure application security is impractical. SQL Server includes application roles to accommodate application-specific permissions. Application roles contain a password and do not contain members. This special role is designed to control what privileges are available to all users accessing the database from a specific application. The application role is assigned permissions in a database. After a user is authenticated to SQL Server, you activate the application role by using the sp_setapprole system stored procedure. The password for the application role can be encrypted by sp_setapprole before the password is sent to SQL Server. When an application role is active, all other user permissions are revoked until the session or application is closed. If an application must access another database when an application role is active, permission to the other database is only available through the guest user account.

Permission States

There are three permission states: Grant, Deny, and Revoke. When you Grant a permission, the user, group, or role (security account) is explicitly assigned the permission. When you Revoke a permission, the permission is removed from the security account. When you Deny a permission, the security account is explicitly restricted from the permission. A user's permissions are the sum of all permissions that are assigned directly to the user or to a group or role of which the user is a member. SQL Server processes Deny permission states first, which negates the permissions granted elsewhere. Therefore, if a user inherits this permission state or if it is directly assigned to the user, all other permissions are irrelevant. For example, suppose that User01 is granted the SELECT permission to Table01 and User01 is a member of Group01, which is granted the INSERT permission to Table01. As a result, the user account's effective rights are SELECT and INSERT for Table01. However, if Group01 is a member of Role01, which is assigned Deny for the SELECT and INSERT permissions on Table01, User01 cannot query or add to Table01.

Object and Statement Permissions

There are three types of permissions in SQL Server: object, statement, and implied. Object permissions apply to database objects and vary by object. For example, you can assign EXECUTE permission for a stored procedure and SELECT permission for a table. Statement permissions apply to the CREATE and BACKUP Transact-SQL statements that are run against a database. For example, the CREATE TABLE statement permission allows a user to create tables in a database. The following list shows object and statement permissions for various database objects:


Permission	For
CREATE (statement permission)	database, table, view, stored procedure, default, rule and function creation
BACKUP (statement permission)	database and log file backup
SELECT and UPDATE (object permission)	query and modify tables, views, and columns in tables and views
INSERT and DELETE (object permission)	add and remove tables, views, and records in tables and views
EXECUTE (object permission)	run stored procedures

Implied permissions are assigned to object owners, fixed server roles, and fixed database roles and cannot be revoked from the owners or roles. Some implied statement permissions are assigned only through fixed role membership. For example, a user must be a member of the ServerAdmin fixed server role to execute the SHUTDOWN statement.

Auditing

General operating system auditing is a feature made possible in Windows NT and Windows 2000 by the Event Log service. SQL Server includes SQL Server login and security event auditing. Login authentication success and failure is a server option that you configure with the xp_loginconfig extended stored procedure or by using the Security tab of the SQL Server Properties (Configure) dialog box of Enterprise Manager (refer to Figure 13.2).

In order to audit events, use SQL Profiler to configure security audit events. The security events are logged to a file or to a database table. Configure the events to audit, the data columns to be traced, and the storage properties for the trace file or trace table. After tracing security events with SQL Profiler, you can open the trace file (.trc) in SQL Profiler or query on a trace table. To see an event class and data column matrix for security events, refer to "Security Audit Event Category" in SQL Server Books Online.

Object Encryption

Encrypting data keeps information confidential by storing it in a format that is difficult to decipher. Programs decipher encrypted data by using a decryption algorithm or decryption key. SQL Server 2000 contains a decryption algorithm to decipher and parse database objects that are encrypted in SQL Server. Passwords and encrypted objects in SQL Server are not viewable by any database user (including members of the SysAdmin fixed server role). SQL Server 2000 automatically encrypts passwords associated with SQL Server login IDs, and you can optionally encrypt the contents of stored procedures, user-defined functions, views, and triggers by specifying the WITH ENCRYPTION clause when creating these database objects.

Application

Applications accessing a database can call the sp_setapprole system stored procedure to activate an application role. In addition, applications can implement their own security that is outside of database control. Applications can use data access APIs supported by SQL Server, such as ActiveX Data Objects (ADO), OLE DB, and Open Database Connectivity (ODBC), in order to insulate the application from data access details. Applications should include security features when accessing data in SQL Server. For example, Internet Explorer includes Security Zone settings to protect against malicious scripts being run against the database.

Lesson Summary

One way to conceptualize database security is by using a six-layer model. The first layer is physical security. Vital servers, including the server running SQL Server 2000, and critical network equipment, such as backbone hardware, should be placed in a secure location. The second layer is network protocol security. Network protocol security provides encryption, mutual authentication, multiprotocol support, and firewall services in order to enhance database security. Encryption is implemented by using network security protocols such as IPSec or by using database Net-Library encryption services. Mutual authentication, multiprotocol support, and firewall services are implemented outside SQL Server network services. The third layer is domain security. SQL Server security is integrated with and benefits from Microsoft domain security. Domain user accounts and groups are assigned permissions in SQL Server to securely connect to and access the database. The fourth layer of security is local computer security. The local computer operating system provides security auditing, file and registry protection, and file encryption services. The fifth layer is SQL Server security. Authentication, authorization, auditing, and encryption services are built into SQL Server. The sixth layer is application security. Applications can use SQL Server application roles and implement their own security features to enhance database security.