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.