bixb0012

Better Practice:  Connect to SQL Server from ArcGIS Using MS ODBC

Discussion created by bixb0012 Champion on Jan 6, 2015

Several years back, Microsoft made an announcement that it was moving forward with more focus on ODBC and moving away from OLE DB for connecting to SQL Server.  Nearly two years ago, Microsoft introduced the new Microsoft ODBC Drivers for SQL Server.  Beyond introducing these new drivers, Microsoft announced the beginning of the end for the SQL Server Native Client.

What's New in SQL Server Native Client (2012):

SQL Server 2012 contains the final version of SQL Server Native Client. SQL Server Native Client will not be updated in future releases of SQL Server. However, as a convenience to developers, SQL Server 2012 Native Client will ship in future releases of SQL Server.

Recently, I was looking over the Microsoft SQL Server database requirements for ArcGIS 10.3.  Microsoft SQL Server 2012 SP1?  Check.  Microsoft SQL Server 2014?  Check.  As expected, the page addresses the software you need to connect to SQL Server.

Software required to connect to a DBMS

Your client machine (for example, the one running ArcMap) will need to have the appropriate client files installed for the RDBMS you are using. These client files are available from their respective RDBMS vendors, but some are also available on My Esri as a convenience. See Database clients for more information.

So far, so good.  Looking at the database clients page, something did grab my attention.  Specifically, the page lists Microsoft SQL Server native client and Microsoft ODBC driver 11 for SQL Server.  Well, maybe the deprecated native client was included for backwards compatibility with older versions of SQL Server since ArcGIS 10.3 does support back to SQL Server 2008 SP3.  Unfortunately, that argument didn't hold up because the download page for Microsoft ODBC Driver 11 for SQL Server - Windows states that the ODBC driver is backwards compatible to SQL Server 2005 even older than what ArcGIS 10.3 supports.

 

The situation got even worse when I looked at the Help pages for administering geodatabases.  The Connect to SQL Server from ArcGIS page actively instructs users to install a now-deprecated client to connect to SQL Server.

Connect from ArcGIS for Desktop

To connect to a database or geodatabase in SQL Server from ArcGIS for Desktop, install the SQL Server native client on the ArcGIS for Desktop computer and create a connection file in the Catalog tree. First, though, be sure your SQL Server instance can accept connections from remote computers.

The worst part of it all, though, is that the most appropriate driver to use isn't even mentioned once in the whole page talking about connecting to SQL Server.

 

The SQL Server Native Client may very well work for most, if not all, situations involving ArcGIS 10.3, but it seems a better practice to move users over to the more current driver while both drivers still work rather than wait for the next version of SQL Server where connections could stop working.  Microsoft has been encouraging users to switch for years, I think it is time for Esri to finally start.

Outcomes