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)

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

Here is what we saw on non-working node.

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

And we see below on working node.

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

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)

, ,
Previous Post
Cloud Consulting Pre-Engagement Questionnaire – A Bookmark
Next Post
SQL SERVER – Analysis Services and the Network Performance

Related Posts

1 Comment. Leave new

  • Good post. Should I run into future cluster issues, I’ll be sure to check the cluster log. Thanks Pinal

    Reply

Leave a Reply

Menu