Solved

SQL Server Connection failed

Posted on 2014-07-29
13
240 Views
Last Modified: 2014-07-30
Have had no problem connecting to a SQL Server on a clients computer remotely for over a year.  All of a sudden today, I and several of the workers at the client site are getting error messages when trying to connect to the SQL Server.Named Pipes ErrorThis occurred after one of the client personnel ran a script that added a new database to the server.  After the script ran (successfully), the user then tried to delete the database, got an error, and used the following script to drop the table.  

USE [master]
GO
ALTER DATABASE [yourdbname] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [yourdbname]

This appeared to work until I and several other users attempted to access the SQL Server via ODBC connections.  Access databases which reside on the server are still able to connect to the server via the established connections, but applications residing on client computers are unable to connect to the SQL Server.
0
Comment
Question by:Dale Fye (Access MVP)
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40228108
Perhaps the users have the default database set to the dropped yourdbname causing the error when logging on.  Can you verify this?  If you can, the fix should be pretty easy.
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40228186
No, the database that was dropped was the one that was added with the script, and that script did not change the default for any of the users that are having difficulties.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40228252
Is there any reason you are using Named Pipes instead of TCP/IP?  I do realize that you probably always connected that way so this should not be the root cause.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Assisted Solution

by:Harish Varghese
Harish Varghese earned 200 total points
ID: 40228442
Try if you are able to connect using IP Address/Machine name and port. If you are using a named instance of SQL Server like MachineName\Instance, then the port may not be standard. You can find the port where the server is listening on by executing below command on the server.
exec master..xp_readerrorlog 0, 1, N'Server is listening on' 

Open in new window

Look for the port number that is listening on 'any' and try to connect using machine name or IP followed by a comma and portnumber.
Eg. Machinename,49598
or 10.0.20.24,49598

You can also get same information from SQL Server Configuration Manager. You will also need to enable Named Pipes and TCP/IP on the server using SQL Server Configuration Manager (which you would have done already).
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40228727
@Anthony,

"is there any reason ..."?  Ans: don't know any better.  I have connected to SQL Server quite frequently in the past.  But I guess this is the way I have always done it.  Is there a chance that the script that was run disabled Named Pipes?  If so, how would I re-enable it?  

Ok, so I made my way to Configuration Manager and the "SQL Native Client 10.0 Configureation"  indicates that Named Pipes is enabled, but when I clicked on "Aliases" it indicates, no items to show in this view.  Then, when I drilled down on SQL Server Network Configuration, under Protocols for MSSQLSERVER, it indicates NamedPipes is disabled, so do I just need to change that to 'Yes'

@Harish,

That script indicates "server is listening on [ 'any' <ipv4>1433]   - same form <ipb6>
0
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 40228731
Yes, you need to enable the Named Pipes under "Protocols for <YourSQLServer>". 1433 is the default SQL Server port and that shows that you are running the default instance. In case you are not able to connect even after enabling Named Pipes, use MachineName,1433 as datasource to connect from client machines and try.
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40228752
Thanks,

When I change that setting, it indicated I need to stop and restart the service.

Can I simply do that in Task Manager, but stopping SQLSERVERAGENT, and then restarting the service, or do I need to stop the MSSQLSERVER or one of the other MSSQLSERVER... services?
0
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 40228756
You can do it from task manager. You can also open Services window by typing services.msc in Run window. Go to SQL Server (MSSQLSERVER) service and then restart. If it is a production system, note that all current users will get disconnected from the server
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40228779
OK,

So, I'm no longer getting the Named Pipes error, but still cannot connect  to the server and am now getting this error:unable to complete loginI'm using the following DSN-less connection string to connect from Access to the server.  

ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=ServerName;Trusted_Connection=Yes;APP=SSMA;DATABASE=WHR_System_Tables;

and am getting the same error when I change that to:

ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=ServerName,1433;Trusted_Connection=Yes;APP=SSMA;DATABASE=WHR_System_Tables;
0
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 40228802
One possibility for this error is that there is a change in server IP address and the local machine that you are using is resolving the server name incorrectly. Find out the IP of the server and try changing the connection string as SERVER=IPAddress,1433 or just SERVER=IPAddress
In case the issue is due to name resolution, you may execute below command in command prompt on your client machine:

ipconfig /flushdns
0
 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 300 total points
ID: 40229026
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40229066
I was advised the SQL Browser needs to be running, but that didn't seem to fix the problem either.  I also noticed that in Task Manager, the MSSQLServerADHelper100 is stopped, should that be running?
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40229201
Turns out that the firewall settings had been changed, but nobody will confess to having made the changes.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

679 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