Logins
To connect to an instance of Microsoft® SQL Server™ 2000, you typically give an application only two or three pieces of information:
- The network name of the computer on which the SQL Server instance is running.
- The name of the instance (optional, required only if you are connecting to a named instance).
- Your login identifier (ID).
A login ID is the account identifier that controls access to any SQL Server 2000 system. SQL Server 2000 does not complete a connection unless it has first verified that the login ID specified is valid. Verification of the login is called authentication.
One of the properties of a login is the default database. When a login connects to SQL Server, this default database becomes the current database for the connection, unless the connection request specifies that another database be made the current database.
A login ID only enables you to connect to an instance of SQL Server. Permissions within specific databases are controlled by user accounts. The database administrator maps your login account to a user account in any database you are authorized to access. For more information, see Logins, Users, Roles, and Groups.
Authenticating Logins
Instances of SQL Server must verify that the login ID supplied on each connection request is authorized to access the instance. This process is called authentication. SQL Server 2000 uses two types of authentication: Windows Authentication and SQL Server Authentication. Each has a different class of login ID.
Windows Authentication
A member of the SQL Server 2000 sysadmin fixed server role must first specify to SQL Server 2000 all the Microsoft Windows NT® or Microsoft Windows® 2000 accounts or groups that can connect to SQL Server 2000. When using Windows Authentication, you do not have to specify a login ID or password when you connect to SQL Server 2000. Your access to SQL Server 2000 is controlled by your Windows NT or Windows 2000 account or group, which is authenticated when you log on to the Windows operating system on the client.
When you connect, the SQL Server 2000 client software requests a Windows trusted connection to SQL Server 2000. Windows does not open a trusted connection unless the client has logged on successfully using a valid Windows account. The properties of a trusted connection include the Windows NT and Windows 2000 group and user accounts of the client that opened the connection. SQL Server 2000 gets the user account information from the trusted connection properties and matches them against the Windows accounts defined as valid SQL Server 2000 logins. If SQL Server 2000 finds a match, it accepts the connection. When you connect to SQL Server 2000 using Windows 2000 Authentication, your identification is your Windows NT or Windows 2000 group or user account.
The Microsoft Windows 98 operating system does not support the server side of the trusted connection API. When SQL Server is running on Windows 98, it does not support Windows Authentication. Users must supply a SQL Server login when they connect. When SQL Server is running on Windows NT or Windows 2000, Windows 95 and Windows 98 clients can connect to it using Windows 2000 Authentication.
SQL Server Authentication
A member of the sysadmin fixed server role first specifies to SQL Server 2000 all the valid SQL Server 2000 login accounts and passwords. These are not related to your Microsoft Windows account or network account. You must supply both the SQL Server 2000 login and password when you connect to SQL Server 2000. You are identified in SQL Server 2000 by your SQL Server 2000 login.
SQL Server Authentication Modes
When SQL Server 2000 is running on Windows NT or Windows 2000, members of the sysadmin fixed server role can specify one of two authentication modes:
- Windows Authentication Mode
Only Windows Authentication is allowed. Users cannot specify a SQL Server 2000 login ID. This is the default authentication mode for SQL Server 2000. You cannot specify Windows Authentication Mode for an instance of SQL Server running on Windows 98, because the operating system does not support Windows Authentication.
- Mixed Mode
If users supply a SQL Server 2000 login ID when they log on, they are authenticated using SQL Server Authentication. If they do not supply a SQL Server 2000 login ID, or request Windows Authentication, they are authenticated using Windows Authentication.
These modes are specified during setup or with SQL Server Enterprise Manager.
Login Delegation
If you use Windows Authentication to log on to an instance of SQL Server 2000 running on Windows 2000, and the computer has Kerberos support enabled, SQL Server 2000 can pass your Windows login credentials to other instances of SQL Server. Delegation of your credentials from one instance to another is sometimes called impersonation, typically when both instances of SQL Server are running on the same computer.
For example, if Instance A and Instance B are running on separate computers using Windows 2000, you can connect to Instance A and execute a distributed query that references tables on Instance B. When Instance A connects to Instance B to retrieve the required data, Instance A can use your Windows account credentials for the connection. Instance B has visibility to your specific account, and can validate your individual permissions to access the data requested.
Without delegation, administrators have to specify the login that Instance A uses to connect to Instance B (or any other instance). This login is used regardless of which user executes a distributed query on Instance A, and prevents Instance B from having any knowledge of the actual user executing the query. The administrators of Instance B cannot define permissions specific to individual users coming in from Instance A, they must define a global set of permissions for the login account used by Instance A. The administrators also cannot audit which specific users perform actions in Instance B. Using delegation with Windows Authentication on Windows 2000 allows administrators greater control over user permissions and gives auditors greater visibility to the actions of individual users.
Connections that use delegation are authenticated using a Kerberos ticket. Each ticket has a timeout period defined by the Windows 2000 security administrator. If a connection remains idle for a long period and the Kerberos ticket times out, all subsequent attempts to execute a distributed query will fail until the user disconnects and reconnects.