Wednesday, September 29, 2010

What exactly is a trusted connection?

Trusted connections are only opened by the SQL Server client software (such
as the ADO.NET, OLE DB, or ODBC provider/driver) if you request a Windows
Authentication (used to be called Integrated Security) connection. The SQL
Server client software then opens a specific kind of network connection that
can only be opened by a process that has already successfully logged on to
Windows using a valid Windows login and password. Since those types of
connections are only possible after Windows has validated the login and
password, the Database Engine software does not have to re-validate the
login and password, it can "trust" that authentication was already performed
by Windows. The security ID of the Windows account is passed as part of the
information concerning the connection.

The above just controls whether the you can open a connection to the
database engine (authentication). After you have connected, you can only
perform actions for which the proper permissions have been assigned to
either your login or any user your login has been mapped to. Part of what a
DBA has to do in Windows Authentication environments is define to the
Databae Engine which Windows accounts and groups used as logins map to
different users in each database, and which permissions are granted to each
user or login.

Alan Brewer [MSFT]
SQL Server Documentation Team