Microsoft SQL Server Security

Microsoft SQL Server 2000 is used by many portal implementations as data storage for personalization and user profile information. Many portals incorporate enterprise data residing in SQL Server databases. A detailed discussion of Microsoft SQL Server features is beyond the scope of this chapter, but this section does briefly cover the main points on SQL Server security.

SQL Server 2000 security is based on Windows security. SQL Server provides two authentication modes:

• Windows authentication mode

• Mixed authentication mode

Windows Authentication Mode

Windows authentication mode is the default and most secure authentication mode. As its name implies, it bases SQL Server security on existing Windows user or group accounts. In this mode, if a user wants to access the SQL Server database, the network administrator must first create a valid domain user account. After a Windows account has been created, the SQL Server administrator creates an SQL Server account mapped to the Windows account and grants access to some of the databases contained within the SQL Server to this Windows account. The walkthrough at the end of this chapter shows in detail how to create a SQL Server account using Windows authentication mode.

Windows authentication is the recommended authentication mode. It is more secure than the Mixed mode and allows for transparent login to SQL Server after Windows has authenticated the user. Figure 6.11 displays the SQL Server Enterprise Manager interface for creating SQL Server logins. You open this dialog box by expanding the Security node of the selected server, right-clicking the Logins item, and selecting New Login. In Figure 6.11, user Alice from the Windows domain Proto is being given access to the Nwind database. This mode of communication is known as trusted mode.

Figure 6.11. Creating SQL Server Login

Mixed Authentication Mode

In Mixed authentication mode, users requesting a connection to an SQL Server database can be authenticated either by Windows or by SQL Server. If an administrator were to click the SQL Server Authentication radio button during login creation (as was done for Alice in Figure 6.11), SQL Server would store an encrypted username and password internally. When Windows cannot authenticate the user, SQL Server would require explicit user credentials, which then would be compared to those stored internally. This mode of connection is called nontrusted.

Microsoft recommends using Mixed mode when you are dealing with legacy applications that cannot support Windows authentication or when you are working with SQL Server installations on the Windows 98 family of operating systems, which do not support NTLM or Kerberos.