Solved

SQL Server Connection failed

Posted on 2014-07-29
13
241 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)
[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 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

739 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