Solved

SQL Server Connection failed

Posted on 2014-07-29
13
230 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
Comment Utility
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)
Comment Utility
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
Comment Utility
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
 
LVL 12

Assisted Solution

by:Harish Varghese
Harish Varghese earned 200 total points
Comment Utility
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)
Comment Utility
@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
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Comment Utility
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)
Comment Utility
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
Comment Utility
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
Comment Utility
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
Comment Utility
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)
Comment Utility
Turns out that the firewall settings had been changed, but nobody will confess to having made the changes.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now