Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

SQL Server Connection failed

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
Dale Fye
Asked:
Dale Fye
  • 6
  • 4
  • 2
  • +1
2 Solutions
 
Anthony PerkinsCommented:
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
 
Dale FyeAuthor Commented:
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
 
Anthony PerkinsCommented:
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Harish VargheseProject LeaderCommented:
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
 
Dale FyeAuthor Commented:
@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
 
Harish VargheseProject LeaderCommented:
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
 
Dale FyeAuthor Commented:
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
 
Harish VargheseProject LeaderCommented:
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
 
Dale FyeAuthor Commented:
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
 
Harish VargheseProject LeaderCommented:
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
 
Alpesh PatelAssistant ConsultantCommented:
0
 
Dale FyeAuthor Commented:
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
 
Dale FyeAuthor Commented:
Turns out that the firewall settings had been changed, but nobody will confess to having made the changes.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now