Integrated security integrates SQL Server login security with Windows NT security. This allows a network user to log in to a SQL Server without supplying a separate login ID or password. Users can maintain one login ID and password for both Windows NT and SQL Server.
Integrated security lets SQL Server applications take advantage of Windows NT security features, including encrypted passwords, password aging, domain-wide user accounts, and Windows-based user administration. (For more information about these features, see your documentation for Windows NT.)
SQL Server achieves login security integration with Windows NT by using the Windows NT security attributes of a network user to control login access to SQL Server. A user's network security attributes are established at network login time and are validated by Windows NT through a sophisticated password encryption mechanism. When a network user tries to connect to SQL Server, SQL Server uses Windows NT – based facilities to determine with certainty the validated network username of the client. SQL Server then permits or denies login access based on that network username alone, without requiring a separate login name and password to be sent in the client connection request.
Login security integration operates over network protocols that support authenticated connections between clients and servers. Such connections are referred to as trusted connections.
Trusted connections include multi-protocol and named pipes sessions from other Windows NT – based workstations, from Windows for Workgroups–based clients, from Windows 95-based clients, and from Microsoft LAN Manager clients running under the Microsoft Windows or MS-DOS operating systems. Using the multi-protocol Net-Library, trusted connections can also be made over the NWLink IPX/SPX protocol from Windows 3.1 clients using Novell software. (In this case, the user will be prompted for a Windows NT username and password at SQL Server connect time.) Other network protocols do not support authenticated connections, so clients on these protocols must be handled according to SQL Server standard security.
When a user of integrated security logs in to SQL Server over a trusted connection, the user is mapped to a SQL Server login ID, to a default SQL Server login ID (usually called guest), or–if the user has administrative privileges–to SA.
Once the login process is complete, all other SQL Server security checks are handled as usual. Access to individual SQL Server tables, for example, is managed through the permissions granted within a SQL Server database.
If multiple SQL Server computers participate in a domain managed by a Windows NT Server, a single network login for each user is sufficient to enable access for all of the SQL Servers. For users defined on other domains, SQL Server provides map characters to map the domain name and username to a SQL Server login ID (for example, salesdomain_Chris). These map characters also provide a way to map Windows NT usernames that are not valid SQL Server login IDs to valid SQL Server login IDs.
The recommended way to implement SQL Server security integration is to create two or more locally defined Windows NT groups and then provide the users of those groups with access to SQL Server. The two recommended Windows NT groups are:
For more information about the interaction of Windows NT security with SQL Server security, see Windows NT Security and SQL Server. For instructions on setting up integrated security (beginning with an overview of each task required to do so), see Chapter 9, Managing Security.