Intermittent failed test connections to SQL ODBC

Posted on 2013-10-17
Medium Priority
Last Modified: 2013-11-05
Got a strange problem with an ODBC connection from an app server to a database on a SQL server. The app server is 2003 server. The DB server is 2008R2 with SQL 2008.
When I create the odbc connection and test it, it works the first time, but then the next connection attempts all fail with the error:
Attempting connection
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
Disconnecting from server

A constant ping from the app server shows no dropped packets.
When I try the same odbc from a different app server and the same db server, it connects ok every time.
Any ideas on what might cause this?
Question by:Cashbuddies
  • 2
  • 2
LVL 38

Expert Comment

by:Jim P.
ID: 39581761
On the SQL server side do you have Named Pipes turned off?

NP depends on constant access to the original DC to keep the SID active. Change it to TCP/IP and see how it goes.

Author Comment

ID: 39582209
But it works fine form the other 2003 app server, wouldn't that setting affect it too?
LVL 38

Accepted Solution

Jim P. earned 1400 total points
ID: 39582597
The way Named Pipes works is that the app server first connects to the DC. Then the DC provides a token and redirects it to the SQL server. If the app or SQL server is losing connectivity to that particular DC the individual token is no longer valid.

With TCP/IP the app server goes directly to the SQL Server and says "Hi, I am appserver1. I want to get data. Here's my SID." The SQL server then says to any DC "Here's a SID. Is it valid?" The DC then says "Yes" or "No".  But if connectivity drops from appserver1 then SQL will go to any DC the next time with the question again.

Author Closing Comment

ID: 39623710
Thanks jimpen, named pipes fixed it

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question