SQL Server connection string Trusted_Connection=true issue

Question

Hello everyone,

I am using .Net 2.0 + SQL Server 2005 Enterprise + VSTS 2008 + C# + ADO.Net to develop ASP.Net Web application.

My question is, if I am using Trusted_Connection=true with SQL Server authentication mode (not Windows authentication mode, i.e. using sa account and password in connection string in web.config), I am wondering whether Trusted_Connection=true will impact performance of my web application? And why?

thanks in advance, George

Answer 1

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

Answer 2

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)

---

Marc

Answer 3

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