SSIS OLEDB Connection: SSPI or True

For an OLEDB Connection, if you happen to use Inetegrated Security, what should the value of the key "Integrated Security" be:

a) SSPI

b) True

c) Any of the above

d) None of the above

Till this day I was always under the impression, that it should have been c) as this article on ConnectionString reiterates. But this article emphasizes on something which I might have missed earlier and that is:

Integrated Security -or- Trusted_Connection

'false'

When false, User ID and Password are specified in the connection. When true, the current Windows account credentials are used for authentication. Recognized values are true, false, yes, no, and sspi (strongly recommended), which is equivalent to true.

So whats my point here.

Try executing one of your packages which uses OLEDB connection and modify the connection string at runtime as

"Data Source=[SVR_NAME];Initial Catalog=[DBNAME];Provider=SQLNCLI.1;Integrated Security=True;"

You will get an error something similar to:

Failed to acquire connection "CONNECTION NAME". Connection may not be configured correctly or you may not have the right permissions on this connection.

after seeing which I treaded down the wrong path of trying to figure out why the network account I was using doesn't have the permissions to the requisite database. I tried almost everything, even opened up the SQL Server to the network user I was using, but it still didn't resolve the issue. That is when I started paying close attention to the steps I was using to run the package and was able to figure out the issue.

It was a simple switch in the connection string that did the trick, but nevertheless it was a valuable lesson learnt for me.

"Data Source=[SVR_NAME];Initial Catalog=[DBNAME];Provider=SQLNCLI.1;Integrated Security=SSPI;"

Let me know if anyone else faced any such issues with Integrated Security in a different context maybe.