Lesson 4: What Is the Security Architecture?

3 4

SQL Server 2000 uses two levels of security when validating a user. The first level of security is authentication. Authentication determines whether the user has a valid login account to connect to an instance of SQL Server 2000. The second level of security is authorization, which is also called permission validation. Authorization determines what activities the user can perform in which databases after being authenticated by SQL Server 2000. Security is discussed in Chapters 10 and 11.

---

After this lesson, you will be able to

• Describe the SQL Server 2000 authentication process
• Describe the SQL Server 2000 authorization process
• Describe the types of SQL Server 2000 logins, and when each is used

Estimated lesson time: 15 minutes

---

Authentication

A user cannot connect to an instance of SQL Server 2000 without first specifying a valid login identifier (ID). A login ID is the account identifier that controls access to an instance of SQL Server 2000. Instances of SQL Server must verify that the login ID supplied on each connection request is authorized to access the instance. Verification of the login ID is called authentication. SQL Server 2000 uses two types of authentication: Windows authentication and SQL Server authentication. When a user attempts to connect to SQL Server 2000, the user specifies the type of authentication the connection is requesting.

Windows Authentication

A database administrator can grant permission to connect to an instance of SQL Server 2000 to Windows 2000 and Windows NT 4.0 users and groups. If an attempted connection specifies Windows authentication, SQL Server 2000 uses Windows-based facilities to determine the validated network user name. SQL Server verifies the user's identity, and then permits or denies login access based on that network user name alone, without requiring a separate login name and password. This is called a trusted connection.

Windows authentication leverages the Windows 2000 and Windows NT 4.0 security system, including such features as secure validation and encryption of passwords, auditing, password expiration, minimum password length, and account lockout after multiple invalid login requests.

SQL Server Authentication

A database administrator can create SQL Server login accounts and passwords. These accounts are completely independent of any Windows 2000 or Windows NT 4.0 user account or group. If an attempted connection specifies SQL Server authentication, SQL Server 2000 performs the authentication itself by checking to see whether the SQL Server login account specified exists and whether the specified password matches the one previously recorded in SQL Server 2000.

Note

---

SQL Server authentication is required on instances of SQL Server 2000 installed on Windows Millennium Edition and Windows 98, and for connections by clients not being validated by Windows 2000 and Windows NT 4.0.

Authentication Modes

SQL Server 2000 can operate in two authentication modes. The default authentication mode is Windows Authentication Mode. When SQL Server 2000 is operating in this mode, a user can connect to SQL Server 2000 only with a previously authenticated Windows 2000 or Windows NT 4.0 user account. SQL Server 2000 can also operate in Mixed Mode. When SQL Server 2000 is operating in this mode, a user can connect to SQL Server 2000 with either a previously authenticated Windows 2000 or Windows NT 4.0 user account, or with a valid SQL Server login account and password. Figure 1.6 illustrates the SQL Server 2000 security decision tree.

Figure 1.6

SQL Server 2000 security decision tree.

Authorization

Once SQL Server 2000 authenticates a user, SQL Server 2000 determines whether that login ID has been authorized to perform any activities in any databases. A login ID by itself does not give a user permission to access objects in a database. It only allows a user to proceed to the next step, which is authorization or permission validation. This prevents a login from automatically accessing all databases on an instance of SQL Server 2000.

User Accounts

In general, a database administrator must associate a login ID with a user ID in a database before anyone connecting with that login ID can access objects and perform activities in that database. A database administrator applies security permissions for the objects (for example, tables, views, and stored procedures) in a database to user accounts. See Figure 1.7.

Figure 1.7

SQL Server database access security.

Guest User Account

If a login ID has not been explicitly associated with any user ID in a database, it is associated with the guest user ID in that database, if one exists. If a database has a guest user account, the login is limited to those rights granted to the guest user. If a database has no guest user account, a login cannot access the database unless it has been associated with a valid user account. By default, all newly created user databases have no guest user account.

Roles

A database administrator uses roles to collect users into a single unit against which to set permissions. Roles are used in much the same way as Windows 2000 and Windows NT 4.0 groups. SQL Server 2000 provides fixed server roles and fixed database roles with predefined server-wide or database-specific permissions. A database administrator can also create custom user-defined database roles.

Every user in a database belongs to the public database role and has whatever permissions have been assigned to the public role, unless the user has been specifically denied certain permissions. Additional rights must be granted explicitly to the user or to a group to which the user belongs.

Lesson Summary

SQL Server uses two levels of security. The first level of security is authentication, which controls access to SQL Server through login IDs. Two types of authentication may be used: Windows authentication and SQL Server authentication. The second level of security is authorization, which controls access to each database. Login IDs are mapped to user IDs in each database. These user IDs are granted permissions to objects at the database level.