98

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.

casperOne
  • 73,706
  • 19
  • 184
  • 253
Peter Oehlert
  • 16,368
  • 6
  • 44
  • 48

4 Answers4

117

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.

adrianbanks
  • 81,306
  • 22
  • 176
  • 206
  • Side note for future spectators of this: Trusted Connection doesn't work for me in a Sql Server 2014. Integrated Security does, though, so I'm going with that! – Stachu Dec 03 '14 at 19:20
  • 4
    On SQL Express 2014 I am using trusted_connection and it is working... note the underscorec please and you have to use "yes" rather than "true" when using trusted_connection – Zoran P. May 17 '15 at 12:28
13

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.)

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • The term `catalog` is part of the relational database nomenclature, and doesn't refer to anything but an RDBMS. It is explained quite well in this SO answer: http://stackoverflow.com/questions/7022755/whats-the-difference-between-a-catalog-and-a-schema-in-a-relational-database – ProfK Sep 24 '16 at 04:05
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.

Peter Oehlert
  • 16,368
  • 6
  • 44
  • 48
  • 1
    http://www.connectionstrings.com/sql-server seems to say either works for OLEDB. Do you have a different resource? – AlignedDev Jan 31 '12 at 03:10
1

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.

Cordis Audax Agrinova
  • 181
  • 1
  • 8
  • 1
    If you are using SQL Server authentication and you specify user id and password you don't need to mention "integrated security" or "trusted_connections". – grahamesd May 15 '17 at 18:11