SQL SERVER – Unable to bring resource online. Error – Data source name not found and no default driver specified

SQL
1 Comment

One of my blog readers reported a strange issue to me. As per the issue described, they were unable to bring SQL Server resource in cluster online, only on one node of the cluster. I always use for error messages, but they said that SQL resource goes to online pending state and after some time, it goes to failed state. Let us learn how to fix the error where data source name not found.

As a part of troubleshooting, I asked them to generate and share cluster log with me. Here is an earlier blog to generate cluster log. SQL SERVER – Steps to Generate Windows Cluster Log?

Here is what we saw in the cluster log.

00000fd4.000002ac::2016/10/17-14:31:38.672 INFO [RES] SQL Server : [sqsrvres] Connect to SQL Server …
00000fd4.000002ac::2016/10/17-14:31:38.672 ERR [RES] SQL Server : [sqsrvres] ODBC Error: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0)
00000fd4.000002ac::2016/10/17-14:31:38.672 INFO [RES] SQL Server : [sqsrvres] Could not connect to SQL Server (rc -1)
00000fd4.000002ac::2016/10/17-14:31:38.672 INFO [RES] SQL Server : [sqsrvres] SQLDisconnect returns following information
00000fd4.000002ac::2016/10/17-14:31:38.672 ERR [RES] SQL Server : [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
00000fd4.00001030::2016/10/17-14:31:42.010 ERR [RHS] RhsCall::DeadlockMonitor: Call ONLINERESOURCE timed out for resource ‘SQL Server’.
00000fd4.00001030::2016/10/17-14:31:42.010 ERR [RHS] Resource SQL Server handling deadlock. Cleaning current operation.

Since the ERR is taking about “[Microsoft][ODBC Driver Manager]” and error is “Data source name not found and no default driver specified”, I asked them to check ODBC drivers available on the nodes. We can do this by going to Control Panel > Administrative Tools > ODBC Data Sources (as shown below)

Here is what we saw on non-working node.

And we see below on working node.

We can see that SQL Server Native Client 11.0 is missing and may be that’s the reason is gives error about the driver.

SOLUTION / WORKAORUND

We downloaded “sqlncli.msi” from Microsoft SQL Server 2012 Feature Pack page and installed it on bad node. This MSI installed the software which was missing under the ODBC Admin tool. As soon as that is installed, failover worked fine.

Have you ever used cluster log for such troubleshooting?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Menu
Exit mobile version