Setting the Server Security Options

The security options determine the server's login security mode, login auditing level, and mapping of characters in Windows NT usernames to SQL Server characters. To set or change the security options, use either SQL Setup or SQL Enterprise Manager. The procedures in this section assume you are using SQL Enterprise Manager.

To set server security options

From the Microsoft SQL Server 6.0 program group, start SQL Enterprise Manager.
From the Server Manager window, open a server group and select the server to be administered.
From the Server menu, choose Configurations.
From the Server Configuration dialog box, choose the Security Options tab.
The Security Options window displays the current setting of the options.
Review and change the options as necessary.
An explanation of each option is provided following this procedure.
When you finish configuring the security options, choose the OK button.

SQL Server 6.0 offers you the choice of three login security modes. The login security mode you choose significantly affects how the server handles security. The information that follows only summarizes each login security mode (standard, integrated, or mixed). For a comprehensive discussion of the login security modes, see Chapter 8, Security Concepts.

Standard: Standard mode is the default. In Standard mode, SQL Server manages its own login validation process for all connections (except client applications that explicitly request integrated security over the trusted connections).
Note The server can be set up to deny client-requested trusted connections, but this will restrict applications and features that use forced trusted connections (such as SQL Enterprise Manager, and replication). For more information, see Denying Client-Requested Trusted Connections, earlier in this chapter.
Windows NT Integrated: Integrated mode uses Windows NT - based authentication mechanisms for all connections. Only trusted connections are allowed into SQL Server. The login name and SQL Server password submitted in the login request from a DB-Library or Open Database Connectivity (ODBC) client application are always ignored by SQL Server. Network users who were assigned user-level privileges to SQL Server log in using their network username or the default login ID (if the network username is not found in syslogins). Network usernames that are assigned system administrator - level privilege log in as sa.
With this option, only multi-protocol and named pipe clients are supported. If you have installed additional Net-Libraries, this option is not available. In addition, if you are using this option, you cannot install additional server Net-Libraries.
Mixed: Mixed mode allows both trusted and nontrusted connections¾it is a combination of integrated and standard modes. For trusted (multi-protocol or named pipes) connections, SQL Server examines the requested login name as specified by the client DB-Library or ODBC application. If this login name matches the user's network username, or if the login name is null or spaces, SQL Server first tries the Windows NT integrated login rules as described above. If this fails, SQL Server uses the standard rules. If the requested login name is any other value, the user must supply the correct SQL Server password, and SQL Server handles the login using the standard rules described above. All login requests from nontrusted connections are handled using the standard rules.
Mixed mode offers users the convenience of login security integration without forcing all clients and applications to use this mechanism. For example, existing applications that embed a hard-coded login name and password for all users continue to operate as before. Non-PC clients (such as Apple® Macintosh® - and UNIX-based workstations) can also access a SQL Server running in mixed mode. Users accessing SQL Server over trusted connections can avoid a separate SQL Server password validation by entering their network username or blanks in their login request.

In all login security modes, server-to-server communications (remote stored procedures) are handled using the standard SQL Server mechanisms.

SQL Server also lets you specify the following parameters:

Default Login

The SQL Server login name used by an authorized user on trusted connections when the user's network username does not appear in syslogins. If this box is left blank, users without an entry for their network usernames in syslogins will be denied access to the server, even if they have been given user privileges as described below.

Note that the SA must add a login ID of the name specified as the default login. Entering a name in the Default Login box does not automatically create the login ID.

This parameter is not used in standard mode, except for client applications that explicitly request a trusted connection.

Default Domain

The Windows NT domain name that is the default for matching network usernames to SQL Server login names. Because the same network username can be defined in two different domains for two different users and both can be authorized to access SQL Server, SQL Server must distinguish between the two names in the login process for a trusted connection. For network usernames defined in domains other than the specified default, SQL Server adds the domain name and a map character such as an underscore ( _ ) to the network username before attempting the lookup in syslogins.

For example, suppose the domain MARKETING is defined as the SQL Server default, and network username john is used by two different users ¾ one in the MARKETING domain and the other in the ENGINEERING domain. If John in Marketing is using a trusted connection, he accesses SQL Server using a login name of john (assuming such an entry exists in syslogins). John in Engineering accesses the same SQL Server using a login name of ENGINEERING_ john. If there is one domain to which the majority of your users belong, set the Default Domain to that domain name. If your server computer does not participate in a domain, set the Default Domain to the server computer name.

This parameter is not used in standard mode, except for client applications that explicitly request a trusted connection.

Set Host Name to User Name

Determines whether the host name from the client login record is replaced with the Windows NT network username for users under trusted connections. If this is selected, for example, the network username will appear in the output of the sp_who system procedure. The default for this option is cleared (deselected).

Audit Level

Attempted user accesses can be recorded along with other SQL Server log information. Auditing can be enabled for all three security modes and will record information on both trusted and nontrusted connections.

You can choose no auditing (the default), or you can record successful login attempts, failed login attempts, or both successful and failed attempts. Log records for these events appear in the Windows NT event log or the SQL Server error log, or in both, depending on how you configure logging for your SQL Server. For information on setting up SQL Server logging, see Chapter 3, Configuring Servers.

Mappings

Windows NT usernames include certain characters that are not valid in SQL Server login IDs (for example, hyphens, spaces, and periods). When you're using any of the integrated security options or client-requested trusted connections, mappings let you indicate how to map these characters in SQL Server. For example, the Windows NT username t-johns, which would be invalid to SQL Server, can be mapped to t_johns by mapping the SQL Server underscore (_) to the Windows NT hyphen (-). Possible values for map characters are domain separator (\), space ( ), hyphen (-), period (.), single quotation mark ('), exclamation point (!), "at" sign (@), percent sign (%), caret (^), ampersand (&), and "not used" (which indicates no mapping).

SQL Server lets you map special characters to an underscore (_), a pound sign (#), or a dollar sign ($), as follows:

Map _: Determines which Windows NT character will be mapped to the valid SQL Server character underscore (_). The default is domain separator (\).
Map #: Determines which Windows NT character will be mapped to the valid SQL Server character pound sign (#). The default is hyphen (-).
Map $: Determines which Windows NT character will be mapped to the valid SQL Server character dollar sign ($). The default is space ( ).