This week, while moving a client’s VM to Windows Azure, I was also required to upgrade their database from SQL 2005 to SQL Azure.  While not extremely difficult, there were a few gotcha’s along the way.  These “gotacha’s” were primarily features that had been deprecated.  Those deprecated features aren’t the purpose of blog post, however.  This post is addressing a connection string issue.

My client’s web application was originally written using the OLEDB .NET database libraries.  Those libraries presented some issues when trying to connect to SQL Azure.  I had two options: 1) replace 10k+ lines of code to use the SQL Client libraries; or, 2) force the application to use the client libraries as is.  I (and the client) opted for the second option.

There are to steps that must be implemented in order to use SQL Azure:

  1. Ensure that the SQL Native Client is installed on the local machine.
    You can download the client as part of the Microsoft SQL Server 2012 Features Pack or download the libary only from here: x86 | x64
  2. Then add the following to the beginning of your connection string: 
    Provider=SQLNCLI11;DataTypeCompatibility=80;

NOTE: the “DataTypeCompatibility=80” forces backwards compatibility for certain datatypes.  For instance, when trying to insert an OLE DBDateTime into the database, this will cause an error if the table attribute (column) type is DateTime because the DBDateTime datatype will attempt to store seconds which will cause an overflow.  While you could change all of the table attributes in the migrated database to use the new DateTime2, using “DataTypeCompatibility=80” forces truncation on the seconds, thus not requiring any code changes.