Solved

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

Posted on 2007-03-19
15
165 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now