Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server Connection failed

Posted on 2014-07-29
13
Medium Priority
?
250 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 48

Author Comment

by:Dale Fye
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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 12

Assisted Solution

by:Harish Varghese
Harish Varghese earned 800 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 48

Author Comment

by:Dale Fye
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 48

Author Comment

by:Dale Fye
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 48

Author Comment

by:Dale Fye
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 1200 total points
ID: 40229026
0
 
LVL 48

Author Comment

by:Dale Fye
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 48

Author Comment

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

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

704 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