SQL Server 2000 and Security Introduction to SQL Server 2000 and Security	3.0/5.0 (2 votes total) Rate: Select 1 - Terrible 2 - Poor 3 - Average 4 - Good 5 - Outstanding
Team uCertify July 23, 2007

1. Authentication

Authentication is the process of validating that the user attempting to connect to SQL Server is authorized to do so. The authentication stage identifies the user by using a login account and verifies his ability to connect to an instance of SQL Server. If authentication is successful, the user connects to an instance of SQL Server. The user then needs permissions to access databases on the server. This is done by granting access to an account in each database, which is mapped to the user login.

The permissions validation stage controls the activities that the user is allowed to perform in the SQL Server database.

Authentication Modes

SQL Server can operate in one of the following two authentication modes:

1. Windows Authentication Mode (Windows Authentication): Windows Authentication Mode allows a user to connect to an instance of SQL Server through a Windows NT 4.0 or Windows 2000 user account.
   
   
2. Mixed Mode (Windows Authentication and SQL Server Authentication): Mixed Mode allows users to connect to an instance of SQL Server through Windows Authentication or SQL Server Authentication. Users who connect through a Windows NT 4.0 or Windows 2000 user account can make use of trusted connections in either Windows Authentication Mode or Mixed Mode.

Windows Authentication

When a user connects to an instance of SQL Server through a Windows NT 4.0 or Windows 2000 user account, SQL Server revalidates his account name and password by calling back to Windows NT 4.0 or Windows 2000 for the information.

SQL Server attains login security integration with Windows NT 4.0 or Windows 2000 by using the security attributes of a network user to control login access. The network security attributes of a user are established at the time of network login and are validated by a Windows domain controller. When a network user tries to connect, SQL Server uses Windows-based facilities to determine the validated network user name. Then, SQL Server permits or denies login access based on the network user name without requiring a separate login name and password. Login security integration can operate over any supported network protocol in SQL Server.

If a user attempts to connect to an instance of SQL Server providing a blank login name, SQL Server uses Windows Authentication to authenticate that user. Moreover, if a user attempts to connect to an instance of SQL Server configured for Windows Authentication Mode by specifying a login, the login is ignored and Windows Authentication is used.

Windows Authentication Mode is not available when an instance of SQL Server is running on Windows 98 or Windows Millennium Edition.

SQL Server Authentication

When a user connects to an instance of SQL Server with a specified login name and password from a non-trusted connection, SQL Server performs the authentication itself by checking if a SQL Server login account has been set up and if the specified password matches the previously recorded password. If a login account has not been set, authentication fails and the user receives an error message.

SQL Server Authentication is provided for backward compatibility because applications written for SQL Server version 7.0 or earlier may require the use of SQL Server logins and passwords. Furthermore, SQL Server Authentication is required when SQL Server is running on Windows 98, as Windows Authentication Mode is not supported on Windows 98. Therefore, SQL Server uses Mixed Mode when running on Windows 98. SQL Server Authentication is also required for connections with clients other than Windows NT 4.0 and Windows 2000 clients.



Note: Due to its integration with Windows NT 4.0 and Windows 2000 security system, Windows Authentication provides certain advantages over SQL Server Authentication. Windows NT 4.0 and Windows 2000 security provides more features, such as secure validation and encryption of passwords, auditing, password expiration, minimum password length, and account lockout after certain number of invalid login attempts.

1. Permissions Validation (Authorization)
   
   After a user has been authenticated and allowed to connect to an instance of SQL Server, he needs a separate user account in each database that he wants to access. Requiring a user account in each database prevents users from accessing all the databases on a server. For example, if a server contains two databases named Sales and Marketing, and a user is required to access only the Sales database, the user would have a user account created only in the Sales database.
   
   The user account in each database is used to apply security permissions for the objects (e.g., tables, views, stored procedures, etc.) in that database. The user account can be mapped from the Windows NT 4.0 and Windows 2000 user accounts, the Windows NT 4.0 and Windows 2000 groups in which the user is a member, or the SQL Server login accounts. If the database has no account mapped for the user, the user may be allowed to work in the database under the guest account, if one exists. The activities a user is allowed to perform in a database are controlled by the permissions applied to the user account through which he connects to the database.
   
   After a user has gained access to a database, the SQL Server accepts his commands sent to the database. A user interacts with a database through Transact-SQL statements. When an instance of SQL Server receives a Transact-SQL statement, it ensures that the user has the required permission to execute the statement in the database. If the user does not have the permission to execute the statement or access an object used by the statement, SQL Server returns a permissions error.