Security Modes

[Previous] [Next]

SQL Server relies on Windows 2000 to perform many functions, including security. SQL Server supports two authentication modes—integrated security and mixed security—which we'll discuss in the following sections.

Integrated Security Mode

For a secure installation, you should run SQL Server in integrated security mode. Integrated security means that SQL Server will use Windows 2000 authentication, which is implemented by calling the Security Support Provider Interface (SSPI). When a client attempts to connect to SQL Server over the network, SQL Server requests that Windows 2000 authenticate the client. Windows 2000 verifies the identity of the user, performing mutual authentication if necessary, and then allows a login to SQL Server to proceed, passing the access token to SQL Server. Mutual authentication is supported only on SQL Server 2000 using Kerberos authentication.

The authentication performed in integrated security mode is based entirely on Windows Security Account Manager (SAM) accounts or Windows 2000 Active Directory members. For each user you want to grant access to SQL Server, you must have a matching SAM or Active Directory user account. SQL Server understands Windows group membership, so you can add groups to SQL Server as well as individual user accounts.

Mixed Security Mode

SQL Server originally came to Microsoft under an agreement with Sybase, and some of the server's code and backward compatibility considerations stem from the original Sybase implementation. Mixed security mode is one of those considerations. Sybase implemented their own security system, and Microsoft SQL Server inherited this system. In earlier releases of SQL Server, mixed security mode was referred to as standard security, and it's still called that sometimes. In this mode, the client must specify a login ID and password other than the client's Windows 2000 credentials. This login ID and password are then passed to SQL Server, which verifies that the login exists and the password is correct for the specified login. This security mechanism is internal to SQL Server—no Windows security APIs are used.

In mixed security mode, logins for both integrated security mode and standard security are allowed. This means it's up to the client either to request a trusted connection or to request a nontrusted connection and specify login credentials for SQL Server. If a trusted connection request is specified, the user's Windows security credentials are used and the user isn't prompted further for security information, thus implementing the "single sign-on" mechanism for SQL Server.

NOTE
A trusted security connection is one where integrated security is used. A nontrusted connection is one that uses standard SQL Server security.

Mixed security mode is not recommended for secure applications. SQL Server's standard security mechanisms have not been enhanced for many releases and will be phased out. These mechanisms don't support even the simplest security strategies, such as enforcing minimum password length and not allowing blank passwords. Windows 2000 security is much stronger, so anyone interested in having SQL Server run securely should be running in integrated mode. Getting the hint?

An authentication troubleshooting tip

If your server is in integrated security mode, the client might not understand this and might believe there's a security hole in the server. Say the user starts a query tool and specifies some standard credentials, such as sa (for system administrator) and a random password (or no password at all). Upon successfully logging in to SQL Server, the user will immediately come to you and claim to have found a security hole. What's probably happened instead is that the user has been logged in with his or her Windows credentials.

Ask the user to run select suser_sname and see whether the results come back as DOMAIN\Username. If they do, a trusted connection was made by using the user's Windows credentials. You can perform this function with the SQL Server Query Analyzer. Figure 6-1 shows an example of the output you'll receive.

click to view at full size.

Figure 6-1. Using SQL Server Query Analyzer to determine whether a connection is trusted.

Setting the Security Mode of SQL Server

Follow these steps to set the mode of SQL Server security:

  1. Start the SQL Server Enterprise Manager.
  2. Drill down in the user interface to your instance of SQL Server.
  3. Right-click your instance of SQL Server, and choose Properties from the context menu.
  4. Click the Security tab, and then select either mixed security mode—that is, SQL Server And Windows NT/2000—or integrated security mode, Windows NT/2000 Only.

You'll need to stop and restart the SQL Server service (MSSQLServer by default) for the change to take effect. The following command line instructions will start and stop SQL Server:

 net stop MSSQLServer net start MSSQLServer 

When you change the authentication scheme, you are changing the following Registry key: HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\LoginMode. The key's values are

  • 0 or 2 - Mixed Security Mode
  • 1 - Integrated Security Mode

This applies to SQL Server 7.0 and a default instance of SQL Server 2000. If you're using a named instance of SQL Server 2000, the Registry location will be HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\<InstanceName>\MSSQLServer\LoginMode and it will have the same values.

NOTE
A named instance is an instance that is distinct from the default instance. A default instance is one that looks (from a service name, Registry key, and so on) like an instance of SQL Server 6.5 or SQL Server 7. The named instance is compliant with the Windows 2000 application standards and can be connected to with a special name of the form Servername\Instancenameinstead of just servername. You can have only one default instance installed at any given time and any number of additional named instances installed at any time.



Designing Secure Web-Based Applications for Microsoft Windows 2000 with CDROM
Designing Secure Web-Based Applications for Microsoft Windows 2000 with CDROM
ISBN: N/A
EAN: N/A
Year: 1999
Pages: 138

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net