If you are receiving the following error related to connection to SQL Server, this blog is for you.
TITLE: Connect to Server
——————————
Cannot connect to Database Server.
——————————
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)
I suggest that you read previous article first as that describes how to fix the problem and has worked in many cases.
SQL SERVER – Fix : Error : 40 – could not open a connection to SQL server
Now if SQL Server can be connected perfectly from local system, but cannot be connected from a remote system, in that case firewall on the server where SQL Server is installed can be issued.
Follow instructions of this article to fix the issue.
Go to control panel >> Firewall Settings >> Add SQL Server’s Port to Exception List.
Now try to connect to SQL Server again. It will allow you to connect to the server successfully. Please leave a comment with your experience.
Reference: Pinal Dave (https://blog.sqlauthority.com)
141 Comments. Leave new
Si,
I have a problem. I have one server 2003and 100 client xp.
server is in workgroup bt client are in all domain.
client can not access server where xp are in domain.
client can not access sql server.
i set odbc in client. bt got an error:
connection failed sqlstate ‘HYT00’, SQL SERVER ERROR :0
plz help me
Msg 28102, Level 16, State 1, Line 2
Getting this message when i execute any Query in New Query Window, and keep on showing ‘Debugging query’ message in status bar of SQL query execution window
Hello Amar,
It seems you are working in SQL server 2008 and instead of executing you are debugging the query. Click on Execute button or press F5. Instead of debug |> button.
Regards,
Pinal Dave
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1327
I am Getting this problem again and again plz zzzzzzzzzzzzzzzz solve my prblm
I upgraded to SQL Srvr Express 2008 and now I can’t access SQL Srvr 2000, or any of the databases. These are all on my local machine. I didn’t detach any of the 2000 databases, and I can’t attach to them using 2008. How can I get 2008 to work with the 2000 databases?
I had SQL Srvr 2005 n this machine also, it ran in harmony with SQl Srvr 2000. I have already spent a couple of days making changes to files to work with 2008, but I must also work with these older applications.
If I try starting the instance of SQL Srvr 2000, it tells me it can’t find the default instance of SQL Srvr.
Any help will be appreciated.
I set up an exception on the firewall, but the firewall is off anyway. This had no effect. I can’t attach to the 2000 databases from 2008, it won’t even let me expand the directories where those files are located. I can’t access them through ASP pages. I can see the database files and copy them with Windows Explorer, but SQL Server 2008 will not use them.
This computer is running Win XP, SP3 and also has Sony Acid on it, which uses a SQL Server 2000 database and I have set for Manual Startup. That appears to be working. But the instance of SQL Srvr 2000 installed years ago for development is dead. And I need to access the data in those databases to maintain older applications.
Any other suggestions? I just need the data, I can use 2008 if I can just attach to the databases.
@Jim,
I am sorry but I am having hard time understanding your question, you said you are not able to connect to SQL Server 2000 database after you upgraded it.
1. Can you please provide error message what shows up on your screen why you try to connect to SQL Server 2000 database.
2. Also what is the size of SQL Server 2000 database, because you are using SQL Server 2008 Express edition, it has a limitation that database size can only be less than or equal to 4 GB.
Stop SQL Server 2008 instance. Copy SQL Server 2000 database files (data files and log files) and move it to a new location. Start SQL Server 2008 instance and then try to attach database files following regular attach database process.
~ IM.
Thanks for the reply. There was no error message, the browser just sat there with the message “Connecting” – but it would never connect. The largest database was about 350 meg.
I could not get it to work after trying all suggestions I could find and I can’t make any money unless I can actually test what I am doing. So I started detaching the databases from other computers to reinstall on my current work machine (the data is old, but at least we can work on the apps). This morning I drove a hundred miles to get copies of the two which need changes to associated web pages. Over the last couple of years, the data files had been modified too much to try to use older versions of the databases. I attached the new copies this evening, now I am just modifying connection strings.
One other note, before I could attach the databases I had to modify the security on the directory for my ID. Even though I have admin rights, I had to give myself create rights to attach the databases.
I am back in business, but it just burns me up that half the time when M$ pushes out a new release we have problems that take hours (sometimes days) to resolve. And many of the issues (like this one) take a while to discover because we are not working on every application every day. I wish I had taken up something simple like brain surgery, this upgrade/update nightmare never ends. Browsers, SQL Server, Visual Studio, the operating system, firewalls, etc,. etc., etc. No upgrades on brains – they either work or they don’t (seems like more don’t than do).
hi!
i can’t register sql servers that haves only TCP/IP protocol in server network utility and raised exception “SQL Server dose not exist or access denied”
Regards,
saval
Hi Pinal,
i need to know that i am having SQL Server 2005 in my local PC . now i need to connect to server ie SQL Server 2008 . but i have full permission for both. if connected i can’t able to create a database.(Error: could not create a database acces denied,do’t have permission to create a database)
can u help u out from this.
Thanks
ravi
Hi, Dave-
I am learning SQL Server 2008. I have installed the complete package on my local PC. I can log on no problem using my default ‘windows authentication’.
Following a study guide on how to create a new user and password I did the following:
USE master
GO
CREATE LOGIN Murray
WITH PASSWORD = ‘ABC$$123’
it seems it processed this successfully, however, when trying to log in, after selecting ‘SQL Server Authentication’, I get the following error dialog:
TITLE: Connect to Server
——————————
Cannot connect to .\Master.
——————————
ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
Now, while logged on under ‘Windows Authentication’, I right clicked on the server and went into properties, Security, there I selected the radio choice for ‘SQL Server and Windows Authentication mode’. I closed out and restarted but Murray password will NOT work at all.
I even used the following command:
USE Master
GO
GRANT CONTROL SERVER TO Murray
But that did not help either…
I will appreciate any help.
Regards
Thanks for this post. It saved what was left of my hair.
I have a real difficult twist on these cases:
I have SQL Server 2008 running on Windows XP Professional
Windows Firewall is OFF
SQL Server Services: SQL Server, SQL Server Browser, SQL Agent are all running.
Protocols: Named Pipes, Shared Memory and TCP/IP are enabled
SQL Server Enterprise Manager running on the same machine cannot see the local SQL Server instance.
The database is set to Windows Authentication – this instance has until today worked without fail.
Can you please help me out with this problem
i have installed sql server 2005 now iam not able to connect to the server what could be the problem and can you say me what is the server name should i give and what type of authentication should i give for this type. My system is a local system connected with net connection and i have added port in the firewall settings.
Can you help me out with this problem please mail to [email modified] as soon as possible.
What should be the Server Name after installation?
Please let me know.
Thanks in advance.
hello
i have sql server 2008 and when i try to log onto my terminal command interpreter,i get the following error message”No port information found for host………..(which is the computer name)”
my sql database is not running
I solved my problem. I uninstalled the SQL Server 2008 piece of crap and reinstalled SQL Server 2005. IT WORKS FINE. It is ridiculous that a Microsoft product will not install on a Microsoft operating system without spending hours on blogs and trying useless attempts to make it work.
Maybe I will try again in a year or two, but at least now I can use my laptop for work.
The irony is that 2008 installed fine on my XP machine (the OS M$ wants to retire) but won’t on my Win7 OS (the next generation???).
Thanks For Above Setting I done it
But Currently problem is occurring that Every one on network can access my sql server but i can’t access other servers so if there any clues to setting then please reply
I am using SQL server 2008 and microsoft server 2008
Thanks…
thanks a lot, It works correctly
Hi,
This is really great help me to solve the problem when This error occur.
But I have One More problems that
Other Network SQL server can access me but i cannot access their server AND I have been Set all the settings that are given above.
So Please Help me out for this problem
Thanks You.
Amit Tank