Security

SQL Server 2000 security provides two basic methods for authenticating logins: Windows Authentication and SQL Server Authentication. In Windows Authentication, SQL Server login security is integrated directly with Windows NT/2000 security, allowing the operating system to authenticate SQL Server users. Windows Authentication allows SQL Server to take advantage of the security features of the operating system, such as password encryption, password aging, and minimum and maximum length restrictions on passwords. In SQL Server Authentication, an administrator creates SQL Server login accounts within SQL Server, and any user connecting to SQL Server must supply a valid SQL Server login name and password.

Windows Authentication makes use of trusted connections, which rely on the impersonation feature of Windows NT/2000. Through impersonation, SQL Server can take on the security context of the Windows NT/2000 user account initiating the connection and test whether the security identifier (SID) has a valid privilege level. Windows NT/2000 impersonation and trusted connections are supported by any of the available network libraries (Net-Libraries) when connecting to SQL Server running under Windows NT/2000.

Under Windows 2000 only, SQL Server 2000 can use Kerberos to support mutual authentication between the client and the server, as well as the ability to pass a client's security credentials between computers so that work on a remote server can proceed using the credentials of the impersonated client. With Windows 2000, SQL Server 2000 uses Kerberos and delegation to support Windows Authentication as well as SQL Server Authentication.

The authentication method (or methods) used by SQL Server is determined by its security mode. SQL Server can run in one of two different security modes: Windows Authentication Mode (which uses Windows Authentication) and Mixed Mode (which uses both Windows Authentication and SQL Server Authentication). Windows Authentication Mode is available only for instances of SQL Server running on Windows NT/2000. When you connect to an instance of SQL Server configured for Windows Authentication Mode, you cannot supply a SQL Server login name and your Windows NT/2000 username determines your level of access to SQL Server.

Under Mixed Mode, Windows NT/2000-based clients can connect using Windows Authentication, and connections that don't come from Windows NT/2000 clients or that come across the Internet can connect using SQL Server Authentication. In addition, when a user connects to an instance of SQL Server that has been installed in Mixed Mode, the connection can always explicitly supply a SQL Server login name. This allows a connection to be made using a login name distinct from the username in Windows NT/2000. Because Windows 98 doesn't support Windows Authentication, SQL Server on Windows 98 runs only in Mixed Mode and only supports SQL Server Authentication.

Monitoring and Managing Security

SQL Server makes it easy to monitor logon successes and failures. Using SQL Enterprise Manager, administrators can simply select the appropriate Audit Level option on the Security tab of the Properties dialog box for a particular installation of SQL Server. When logon monitoring is enabled in this way, each time a user successfully or unsuccessfully attempts to log on to SQL Server, a message is written to the Windows NT/2000 event log, the SQL Server error log, or both, indicating the time, date, and user who tried to log on.

SQL Server has a number of facilities for managing data security. You can grant or deny access privileges (select, insert, update, and delete) to users or groups of users for objects such as tables and views. You can grant execute privileges for normal and extended stored procedures. For example, to prevent a user from directly updating a specific table, you can write a stored procedure that updates the table and then cascades those updates to other tables as necessary. You can grant the user access to execute the stored procedure, thereby ensuring that all updates take place through the stored procedure, eliminating the possibility of integrity problems arising from ad hoc updates to the base table.