When Using Trusted_Connection=True and SQL Server Authentication, Will This Affect Performance

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

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)

---

Disable Windows Authentication while connecting into database

SOLUTION :

Just remove from your connection string:

Trusted_Connection=True; Integrated Security=True;

Thanks to : Schadensbegrenzer
Explanation : When using Trusted_Connection=true and SQL Server authentication, will this effect performance?

Cannot connect to SQL Server 2012 (connection string issue?)

You need to either use the proper SQL Server Express - then use this connection string:

server=(local)\\SQLEXPRESS;Database=MyDB;Integrated Security=SSPI;

(use just (local) - not (localdb))

or you use LocalDB (which is a "run-on-demand" version of SQL Server Express), in that case, use:

server=(localdb)\\v11.0;Database=MyDB;Integrated Security=SSPI;

The difference:

• SQL Server Express proper is a server-based solution, which is installed and runs as a Windows service all the time

• SQL Server Express LocalDB is a run-on-demand version of SQL Server Express, which only gets started up when you need it (e.g. in debugging inside Visual Studio 2012/2013)

SQL Server connection string Asynchronous Processing=true

Just having the Asynchronous Processing=True in your connection string just simply enables you to write asynchronous queries - I don't see how having that setting in your connection string should affect your performance, if you don't change anything else.

You will hopefully begin to see a positive effect on your performance when you start doing asynchronous processing of your database queries. But just specifying that one option shouldn't have any (positive or negative) impact on your app.

Marc

---

Related Topics