How does the ODBC driver for SQL handles conflicting security settings between DSN and connection string?

  • Article

My colleague Arvind Shyamsundar asked the following interesting question the other day in an internal forum. I think this may be of the general interest, so I’ve decided to publish it over here.

This is what Arvind wrote (verbatim):

Is there (and if so, what is) a defined behavior if we have conflicting security settings between the connection string and the DSN?

For example, if the DSN states we are to use Integrated Security (this is for SQL ODBC Driver connecting to SQL 2008) but the connection string specifies a UID and PWD, will it use integrated security or SQL authentication?

And how about the reverse (DSN specifies SQL auth but the connection string states trusted_connection=yes).

To answer that question, I must first explain the three different ways you can tell the ODBC driver that you want to connect to a SQL data source using Integrated Security (Windows acount based login). Those methods are the following:

  1. setting the SQL_COPT_SS_INTEGRATED_SECURITY attribute to SQL_IS_ON on the allocated connection object, before the connection is established (i.e. before SQLConnect, SQLDriverConnect, or SQLBrowseConnect are called),
  2. setting the Trusted_Connection connection string keyword in the connection string to yes,
  3. setting the Trusted_Connection connection string keyword in the DSN definition to yes.

Through each of those three mechanisms you can either 1) set the positive value explicitly, 2) set the negative value explicitly, or 3) do not set any explicit value. Therefore, the different possible combinations one can form based on those three mechanisms are 27 (3^3^3). And the outcome for each of those possible combinations are those described in the following table:

xx