What is the difference between Trusted_Connection and Integrated Security in a connection string?

Question

I'm curious what the difference between the token "Trusted_Connection" and "Integrated Security" in SQL Server connection strings (I believe other databases/drivers don't support these). I understand that they are equivilent.

Answer 1

They are synonyms for each other and can be used interchangeably.

In .Net, there is a class called SqlConnectionStringBuilder that is very useful for dealing with SQL Server connection strings using type-safe properties to build up parts of the string. This class keeps an internal list of synonyms so it can map from one value to another:

+----------------------+-------------------------+
| Value                | Synonym                 |
+----------------------+-------------------------+
| app                  | application name        |
| async                | asynchronous processing |
| extended properties  | attachdbfilename        |
| initial file name    | attachdbfilename        |
| connection timeout   | connect timeout         |
| timeout              | connect timeout         |
| language             | current language        |
| addr                 | data source             |
| address              | data source             |
| network address      | data source             |
| server               | data source             |
| database             | initial catalog         |
| trusted_connection   | integrated security     |
| connection lifetime  | load balance timeout    |
| net                  | network library         |
| network              | network library         |
| pwd                  | password                |
| persistsecurityinfo  | persist security info   |
| uid                  | user id                 |
| user                 | user id                 |
| wsid                 | workstation id          |
+----------------------+-------------------------+

_{(Compiled with help from Reflector)}

There are other similar classes for dealing with ODBC and OleDb connection strings, but unfortunately nothing for other database vendors - I would assume the onus is on a vendor's library to provide such an implementation.

Answer 2

They are the same.

Unfortunately, there are several variations like this, including:

Server/Data Source

Database/Initial Catalog

I'm not sure of the origins of the variations, I assume some are meant to be generic (not database-centric so your connection string would look very similar if connecting to a RDBMS vs connecting to a directory service, etc.)

Answer 3

So a little bit later I discovered the origins of the name clash. A set of tokens were used by ODBC and a different set defined for OLEDB. For Sql Server for legacy reasons they still support both interchangeably.

Trusted_Connection=true is ODBC and Integrated Security=SSPI was OLEDB.

Answer 4

In my case I have discovered a difference between "Trusted_Connection" and "Integrated Security". I am using Microsoft SQL Server 2005. Originally I used Windows logon (Integrated Security=SSPI). But when I replaced the Windows authentification by SQL Server authentification adding User ID and password, replacing SSPI by "False" failed. It returned a "Multiple-step OLE DB operation generated error". However, when I replaced "Integrated Security=False" by "Trusted_Connection=no", it worked.