Solved

Cant do remote connection do SQL Server 2005  - Server does not exist or access is denied

Posted on 2007-03-19
15
167 Views
Last Modified: 2010-03-19
Hi Experts,

I'm writing a VB.Net app based on a SQL Server 2005 Express database.  I also have the Management Studio Express installed on my laptop that I use for developement.  I've set up the application on my PC to simulate a client machine as a test but I cant get a connection to SQL Server running on my laptop.  

I can give you the following information:
-Both my PC and laptop run Windows XP Pro SP2.
-I'm using SQL Server 2005 Express on my laptop.
-I'm using a named instance.
-I am able to connect my application to the sever and database locally only (from my laptop).
-I checked that "Allow remote connections to this server" is checked in Management Studio Express.
-I created a UDL file on my laptop that connects successfully to my SQL Server 2005 instance using SQL Authentication. When copying this UDL file to my PC and then test the connection it fails.
-When installing SQL Server Express on my PC, restoring the database and connecting to it from my laptop (ie, just doing the whole test the other way around), I also cant connect so it must be something to do with my settings....

The error message on my PC when trying to connect using the UDL file copied from my laptop is the usual Server does not exist or access denied.

Anything I'm missing?

Thanks
0
Comment
Question by:PantoffelSlippers
  • 8
  • 4
  • 3
15 Comments
 
LVL 16

Expert Comment

by:rboyd56
ID: 18748185
By default, TCP/IP is not enabled on SQL Server Express. You will need to enable it. Use the SQL Server Configuration Tool to do this.

Check the SQL Server Network settings. Enable TCP/IP if it is not already and restart SQL Server Express.
0
 

Author Comment

by:PantoffelSlippers
ID: 18748478
Thanks rboyd56,

I used the SQL Server Configuration Manager.  Not too sure what the difference is between the two nodes called "SQL Server 2005 Network Configuration" and "SQL Native Client Configuration"....

I enabled TCP/IP on both and restarted SQL Server Express.  Connection failed with same error message.

I then disabled all protocols except TCP/IP under both nodes and restarted my SQL Server instance again. Connection failed again.

Are there perhaps any TCP/IP settings that can make a difference?  Does it make a difference if SQL Server 2000 is also installed on my laptop as the default instance?

Thanks
0
 
LVL 16

Assisted Solution

by:rboyd56
rboyd56 earned 50 total points
ID: 18748635
Does it make a difference if SQL Server 2000 is also installed on my laptop as the default instance?

Yes it can.

You also need to make sure SQL Browser is running. If that does not help, stop SQL Server 2000 and see if it works. There is an issue with SQL Server 2000 that oprevents connections to SQL Server 2005 named instances if SQL Server 2000 is running. This was fixed in SP4 for SQL Server 2000.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:PantoffelSlippers
ID: 18750133
rboyd56,

Thanks for the quick response.  

The SQL Browser was running.  
My SQL Server 2000 does have SP4 installed and I always stop it when I use my SQL Server 2005 anyway (to free RAM and virtual Memory).

Getting a bit urgent now - I'll double the points.  Thanks for effort so far.
0
 

Author Comment

by:PantoffelSlippers
ID: 18750173
Should SQL Server 2000 and SQL Server 2005 not be configured to use different ports?
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18750410
Have you checked to see if windows firewall is blocking the ports?
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18750445
If they are not both running at the same time the port should not matter.
0
 

Author Comment

by:PantoffelSlippers
ID: 18751251
Sorry for the dumb question Nightman but how do I check if WIndows is blocking a specific port?
Thanks

rboyd56:
>>>If they are not both running at the same time the port should not matter.
Thanks.
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18751272
Do you have windows firewall running?
If so, go to control panel --> Windows Firewall and add the TCP port to the exceptions list (it will be blocked by default if Windows Firewall is running).
0
 
LVL 29

Accepted Solution

by:
Nightman earned 200 total points
ID: 18751292
Here are a couple of articles that cover pretty much all of the common connectivity issues:

http://support.microsoft.com/kb/914277
http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx
0
 

Author Comment

by:PantoffelSlippers
ID: 18751404
Nightman,

I do use Windows firewall but TCP port 1433 was on the exceptions list with the name SQL Server.

I'll have a look at your links and report back ASAP.

Thanks
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18751452
Is SQL running on port 1433? You can check this from the SQL Server Configuration Manager.

Also, you should ensure that port 1434 (Default for SQL Browser) is also open.
0
 

Author Comment

by:PantoffelSlippers
ID: 18754322
Nightman,

SQL Server is running on port 1433.
I added port 1434 to the exceptions list.  Port 1433 was already on the exceptions list.

The connection failed but it succeeded once I decided to turn the windows firewall off completely.
Turning it on again, causes the connection to fail again.  

I'm 100% sure that SQL Server 2005 uses Port 1433 and that both Port 1433 and 1434 are on the execeptions list of the windows firewall.  For both ports, the scope is set to Any Computer.

Is there maybe another port of service that is required?  Between my laptop and PC I can access shared folders, internet connection sharing works if I set it up and also print sharing works so I I'm reasonably sure there isnt anything major wrong with the connection between the two machines.

I'm not sure which ports and services etc are all required for a remote connection like this to work....
Perhaps I'll find the answer in those two links that you sent.  I'll be able to have a look at those within the next 18 hours.

Thanks
0
 

Author Comment

by:PantoffelSlippers
ID: 18762844
OK, I found the answer.

For future newbies like me with the same problem, I'll summarize the discussion as best I can:

Adding the correct port numbers to the windows firewall exception list did not work.  They are port 1433 as the default SQL Server port and port 1434 as the default SQL Server Browser port.  The browser service, as far as I can tell, exposes all instances running on a machine to the network.  My solution is adding sql server and the browser to the windows firewall exceptions list as programs instead of the port numbers.

According to Microsoft's website, remote connections can use either the information provided to the network by the SQL Server Browser service or they can use the TCP Port directly.  Dont know if I understood this correctly, I dont really understand this concept.

To summarize, for at least one method to allow remote SQL Server2005  connections, you will need the following:
-Obviously a functional network connection and everything required for a local connection.
-The applicable instance need to allow remote connections. (Server properties in Ent Manager)
-The SQL Server Browser service needs to be running.
-The SQL Server Browser service needs to be active. (Browser properties in Config Manager.)
-Add the SQL Server to the windows firewall exceptions list as a program. (Add program, not port)
-Add the SQL Server Browser to the firewall exceptions list as a program. (Add program, not port)
0
 

Author Comment

by:PantoffelSlippers
ID: 18762847
Thanks guys
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL date incremented 11 30
SQL Server 2012 - Merge Replication Issue 1 20
T-SQL: "HAVING CASE" Clause 1 24
SQL SELECT query help 7 37
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

776 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