Connecting to SQL 2008 Express from virtual PC 1

[Glasgow]

I have a Windows XP virtual PC (guest) running on a Windows 7 PC (host) with a SQL 2008 Express instance installed.

As things stand, I can only access the host SQL instance from the guest (using SSMSE and sqlcmd) when the host's firewall is completely turned off. Any attempts I make to tweak the firewall and allow access via ports 1433, 1434 and others have no noticeable effect.

I have also tried running the script available from

http://support.microsoft.com/kb/968872

but no luck.

Any suggestions would be appreciated thanks.

 

[gmmastros]

Your SQL 2008 express instance is probably not using port 1433.

Port 1434 is for the SQL Browser service. This is a windows service. Click Start -> Run, type services.msc and make sure the service is set to start automatically and that it is running.

Next, run the SQL Server configuration manager. Click Start -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration manager. First, make sure TCP/IP is enabled. Then Right click on TCP/IP and click properties. Click the "IP Addresses" tab. Scroll down to the bottom and look for "TCP Dynamic Ports". This is the port that SQL Server is using, and it's also the one you will need to allow through your firewall.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Glasgow]

Great, thank you George - that has got me connected via SSMSE and from a VB application.

Can you think of a reason as to why I can't connect via SQLcmd? It persistently claims:

Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

 

[gmmastros]

When you use SQLcmd, you are specifying a server (with the -S switch), right? If you are using computername\instancename try using the IP Address and port, like this:

SQLCMD -S 192.168.1.21,12345

Where the IP Address is that of the SQL Server computer, and "12345" is the port for the instance (that I showed you how to get in my previous reply).

Does it work if you do it like this?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Glasgow]

Yep. adding the port did the trick, thank you so much. I should have thought of that!

 

[gmmastros]

You shouldn't have to add the port, but if you are connecting to a named instance, you need to put that in there.

Ex:

-S ComputerName\InstanceName
-S IPAddres,port

Either should work. There are a couple things that can trip you up with the ComputerName\InstanceName. If you have DNS problems on your network, you may not be able to resolve the IP Address for the computer name. Also, if the SQL Browser service is not started on the server computer, you may not be able to resolve the port number for the instance name.

Personally, I prefer to use ComputerName\InstanceName because it's easier to remember than IP Addresses.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Glasgow]

Thanks again.

I don't like using IP addresses either. I actually connected with computername\instancname,port

Spent virtually all day on this! SqlCmd -L was returning the named instance OK but couldn't connect without port.