When using Trusted_Connection=true and SQL Server authentication, will this affect performance?

Solution 1:

Not 100% sure what you mean:

Trusted_Connection=True;

IS using Windows credentials and is 100% equivalent to:

Integrated Security=SSPI;

or

Integrated Security=true;

If you don't want to use integrated security / trusted connection, you need to specify user id and password explicitly in the connection string (and leave out any reference to Trusted_Connection or Integrated Security)

server=yourservername;database=yourdatabase;user id=YourUser;pwd=TopSecret

Only in this case, the SQL Server authentication mode is used.

If any of these two settings is present (Trusted_Connection=true or Integrated Security=true/SSPI), then the Windows credentials of the current user are used to authenticate against SQL Server and any user iD= setting will be ignored and not used.

For reference, see the Connection Strings site for SQL Server 2005 with lots of samples and explanations.

Using Windows Authentication is the preferred and recommended way of doing things, but it might incur a slight delay since SQL Server would have to authenticate your credentials against Active Directory (typically). I have no idea how much that slight delay might be, and I haven't found any references for that.

Summing up:

If you specify either Trusted_Connection=True; or Integrated Security=SSPI; or Integrated Security=true; in your connection string

==> THEN (and only then) you have Windows Authentication happening. Any user id= setting in the connection string will be ignored.

If you DO NOT specify either of those settings,

==> then you DO NOT have Windows Authentication happening (SQL Authentication mode will be used)

Solution 2:

When you use trusted connections, username and password are IGNORED, because SQL Server using windows authentication.

Solution 3:

This will probably have some performance costs when creating the connection but as connections are pooled, they are created only once and then reused, so it won't make any difference to your application. But as always: measure it.

UPDATE:

There are two authentication modes:

1. Windows Authentication mode (corresponding to a trusted connection). Clients need to be members of a domain.
2. SQL Server Authentication mode. Clients are sending username/password at each connection

Solution 4:

If your web application is configured to impersonate a client, then using a trusted connection will potentially have a negative performance impact. This is because each client must use a different connection pool (with the client's credentials).

Most web applications don't use impersonation / delegation, and hence don't have this problem.

See this MSDN article for more information.