While attempting to setup a linked server in SQL Server 2008 I found that I could not make a connection through an ODBC data source.  Everything I was reading showed SQL Server having an ODBC provider right out of the box but it wasn’t in mine.  I ran the setup thinking it was a feature I could add and didn’t find it there either.  After some poking around I found this page from Microsoft for an OLEDB provider for ODBC called MSDASQL (too many acronyms).

http://www.microsoft.com/en-us/download/details.aspx?id=20065

After downloading the x64 version and installing it I now have “Microsoft OLE DB Provider for ODBC Drivers” as a provider type when creating a new linked server.

I suspect this has something to do with 32bit vs 64bit but haven’t investigated to verify this.