Problem description
ApexSQL Log is unable to remotely connect to Microsoft SQL Server 2008/2008 R2 (x86/x64/IA64) editions.
When trying to remotely connect via SQL log tool to any of the aforementioned SQL Server editions, the following error is encountered:
Connection to the <database_name> database on the server <SQL_Server_name> failed. Possible reasons are:
- Connection parameters for the specified SQL Server are invalid
- SQL Server is not running
- Network connection is lost
- Selected database is offline
Cause
Microsoft SQL Server 2008/2008 R2 (x86/x64/IA64) editions are missing the connection provider, i.e. the Microsoft SQL Server 2008 and SQL Server 2008 R2 Native Client and it needs to be manually installed.
“Microsoft SQL Server 2008/2008 R2 Native Client (SQL Server Native Client – SNAC) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs to connect to Microsoft SQL Server 2000, 2005, or 2008. In other words, SNAC is the technology that can be used to access data in a SQL Server database.”
Source: SNAC lifecycle explained
An ODBC driver uses the Open Database Connectivity (ODBC) interface by Microsoft that allows applications to access data in database management systems (DBMS) using SQL as a standard for accessing the data.
Furthermore, the Microsoft OLE DB Provider for SQL Server, SQLOLEDB, allows ADO, ActiveX Data Objects, interface to access Microsoft SQL Server. However, the Microsoft OLE DB Provider for SQL Server (SQLOLEDB) remains deprecated and it is not recommended to use it for new development work.
Resolution
Installing SNAC is a fairly simple and straightforward task. Just head over to the official link, choose the version matching your SQL Server edition, run the setup, and after a few steps, all is done.
Also, it’s mandatory that after this installation, SQL Server must be rebooted in order for changes to be applied.
Afterward, run the SQL log tool, connect to the remote SQL Server 2008/2008 R2 and the issue should be resolved.
Note: This problem can also be solved by installing Microsoft SQL Server 2012 Native Client. Perks of choosing to install this one is simply because it can be used to create new applications or enhance the existing ones that need to take advantage of new SQL Server 2012 features.
