NOT (LinkedServertoSQLAzure) where Provider=’MSDASQL’ and DSN=’SQL Server Native Client’

I recently had two different people suggest the same (unfortunately unsupported) solution to the fact that there is no supported way of doing a linked server to SQL Azure directly from inside of SQL Server. Since the reason the proposed solution is unsupported are subtle in the sense that it is deep down in documentation few people read, I wanted to publicize this limitation a bit more.

The suggestion was to configure the linked server to use the Microsoft OLE DB Provider for ODBC Drivers (MSDASQL) and then use SQL Server Native Client’s ODBC functionality. Unfortunately, while this does indeed circumvent the fact that SQL Azure doesn’t support OLE DB, it runs afoul of the fact that using SQL Server Native Client underneath MSDASQL is not supported. This is documented at https://msdn.microsoft.com/en-us/library/ms131035.aspx. 

So, in summary, a good idea but unfortunately it is not a supported solution.