Failed to connect using SQL Server 2012 Native Client

Symptom

The FME log file message starts reporting with:

• "MS SQL Server Reader: Failed to connect using SQL Server 2012 Native Client, trying to use SQL Server 2008 Native Client."
  This message is shown when there is a problem with the connection being bad with the SQL Server database.
• FME Warning: SQL Server Non-Spatial Writer: Failed to connect using Microsoft OLE DB Driver for SQL Server. Provider error 'Description: Class not registered'
  this message is shown when FME can't find SQL Server 2012 Native Client or the client is not installed
  
  FME warnings or errors can be followed by numerous Provider Error messages. Some of those reported are:
• Provider error '(-2146824582) Provider cannot be found.
• Provider error '(-2147217843) Login failed for user '<some user name>'.'.
• Provider error '(-2147217843) Invalid authorization specification'.
• Provider error '(-2147467259) Named Pipes Provider:
• Provider error '(-2147467259) TCP Provider:
• Provider error '(-2147467259) SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
• **Microsoft SQL Server Spatial Writer: Failed to get the SQL Server native binary form for column 'GEOM' (not related to MS SQL Server Reader but valid to share here)

Cause

There are several causes for the Provider Error messages:

1. Bad Credentials... for example incorrect password.
2. Bad Permissions for the user connection
3. Login "Permission to connect to database engine set to Deny"
4. Bad Server Name.
5. Database is offline.
6. Incorrect bit version - 32bit vs 64bit.
7. Missing Drivers.
8. Known issue in the Joiner transformer.
9. **Issue with bit format of Microsoft System CLR Types for SQL Server 2012.
10. Bad SQL Server Instance Name.
11. Bad SQL Server port. The default is 1433, but can be configured to be different.
12. SQL Server 2012 Native Client not installed

Resolution

For causes 1-5: Ensure the user can connect to the SQL Server system with another client application and ensure they can access the table(s).

For cause 6: Ensure that the drivers for SQL Server are in the same bit version of FME. This is less likely to be the issue as usually 32bit and 64bit drivers are installed on 64bit Microsoft Systems.

For cause 7: Ensure you visit the link found in the log message and install the drivers. If you get a message that the drivers are already installed consider testing the opposite FME bit version then the one installed or find the other bit version of the SQL Server Native Client drivers.

For causes 6 and 7 you'll need to install a SQL Server Native Client. You can confirm the version of your existing Native Client by looking for the CurrentVersion information in the registry. If you have a 64 bit client it will be here - HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server Native Client xx.x A 32 bit client will be here HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server Native Client xx.x

For cause 9 (**) you'll need to get the correct bit version installed of Microsoft System CLR for SQL Server 2012 or Microsoft System CLR for SQL Server 2012 (x64). Both can be installed.

FME Desktop and FME Server should install the appropriate SQL Server Native Client. If a specific version of the SSNC is needed then this link https://www.microsoft.com/en-us/search/result.aspx... returns a list of SQL Server Feature Packs.
[At the time of creating this article the URL above returns the list of SQL Server Feature Packs in the image. Let us know via the comments if the URL stops working.]

Click the appropriate version and locate the Install Instructions button


In the expanded list locate the SQL Server Native Client you wish to install. Consider the bit version of your FME software you are using and download that SQL Server Native Client software. Not all Clients have separate installers for 32bit and 64bit software.

Here is an example of the SQL Server 2012 Native Client information:

For cause 8: The known issue in the Joiner has been fixed in FME 2015.1.1. Download FME Build 15515 or greater.

NOTES: There is no SQL Server 2014 Native Client. Use the SQL Server 2012 Native Client for connecting to SQL Server 2014 instances.