In most cases, you have to give an application only two pieces of information to connect to a Microsoft® SQL Server™ installation:
Login IDs are the account identifiers that control access to any SQL Server system. SQL Server will not complete a connection unless it has first verified that the login ID you specified is valid. This verification of the login is called authentication.
There are two types of SQL Server Authentication, each of which has a different class of login ID:
A member of the sysadmin fixed server role first specifies to SQL Server all the valid SQL Server login accounts and passwords. These are not related to your Microsoft Windows® account or network account. You must supply both the SQL Server login and password when you connect to SQL Server. You are identified in SQL Server by your SQL Server login.
A member of the SQL Server sysadmin fixed server role must first specify to SQL Server all the Microsoft Windows NT® accounts or groups that can connect to SQL Server. When using Windows NT Authentication, you do not have to specify a login ID or password when you connect to SQL Server. Your access to SQL Server is controlled by your Windows NT account or group, which is authenticated when you log on to the Windows operating system on the client. When connecting, the SQL Server client software requests a Windows trusted connection to SQL Server. Windows NT will not open a trusted connection unless the client has logged on successfully using a valid Windows NT account. The properties of a trusted connection include the Windows NT group and user accounts of the client that opened the connection. Because it is impossible for a trusted connection to be completed without Windows NT having first authenticated the user, SQL Server does not have to do anything to authenticate your accounts. SQL Server gets the user account information from the trusted connection properties and matches them against the Windows NT accounts defined as valid SQL Server logins. If SQL Server finds a match, it accepts the connection. You are identified in SQL Server by your Windows NT group or user account.
When SQL Server is running on Windows NT, members of the sysadmin fixed server role can specify one of two authentication modes:
Only Windows NT Authentication is allowed. Users cannot specify a SQL Server login ID.
If a user supplies a SQL Server login ID when they connect, they are authenticated using SQL Server Authentication. If they do not supply a SQL Server login ID, they are authenticated using Windows NT Authentication.
These modes are specified during setup or with SQL Server Enterprise Manager.
The Windows 95/98 operating system does not support the server side of the trusted connection API. When SQL Server is running on Windows 95/98, it does not support Windows NT Authentication. Every user must supply a SQL Server login when they connect. When SQL Server is running on Windows NT, Windows 95/98 clients can connect to it using Windows NT 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.