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

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
PantoffelSlippersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rboyd56Commented:
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
PantoffelSlippersAuthor Commented:
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
rboyd56Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PantoffelSlippersAuthor Commented:
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
PantoffelSlippersAuthor Commented:
Should SQL Server 2000 and SQL Server 2005 not be configured to use different ports?
0
NightmanCTOCommented:
Have you checked to see if windows firewall is blocking the ports?
0
rboyd56Commented:
If they are not both running at the same time the port should not matter.
0
PantoffelSlippersAuthor Commented:
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
NightmanCTOCommented:
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
NightmanCTOCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PantoffelSlippersAuthor Commented:
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
NightmanCTOCommented:
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
PantoffelSlippersAuthor Commented:
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
PantoffelSlippersAuthor Commented:
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
PantoffelSlippersAuthor Commented:
Thanks guys
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.