Keyword not supported exception when attempting to use a connection string that points to a ODBC DSN

Question

I created an ODBC DSN for my Asp.Net MVC application's database access. One of the main reasons is it makes it easy to keep database credentials (such as server address, port, username, and password) out of source control without hindering my publishing abilities.

So I changed my connection to be DSN=MyDSN.

Unfortunately, when I run my Entity Framework queries I get Exception Details: System.ArgumentException: Keyword not supported: 'dsn'.

Does anyone know what I am doing wrong?

Answer 1

If you want to use ODBC DSN your connection string must use System.Data.Odbc native provider instead of managed SQL client.

Edit:

So now from theory to practice. It doesn't work because of internal EF implementation. EF internally calls some method which tries to get DbProviderFactory from the created connection. The problem is that this property is defined in DbConnection and it returns null. Only SqlConnection overrides property and returns correct factory. So EF doesn't work with default ODBC provider and here is very clearly described why.