Network access to Sql Server 2005 Express

I have an application written in VB Express and uses SQL Server 2005 Express that runs on my local machine (name JERRY). I published it onto a CD and installed it on another computer (JKNETWORK) on my home network.

I've already modified SQL Server Configuration Manager to enable TCP/IP and Shared Memory. I have also added sqlservr.exe to the exceptions in the Microsoft firewall exceptions list.

The application opens with a login form that asks for username and password and uses the following connection string:

modUserName = txtUserName.Text

modPassWord = txtPassWord.Text

Dim ConnectionStringMaster As String =

_"Server=jerry\SQLEXPRESS;" & _

"DataBase=master;" & _

"user ID=" & modUserName & ";password=" & modPassWord

This all works great on JERRY but doesn't work from JKNETWORK. I get an error message that contains:

...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. ....

Can someone help me figure out how to get the remote access to work?

Thanks,

JerryK

Did you enable Remote Connections? If not, see Surface Area Configuraiton tool for more info: http://msdn2.microsoft.com/en-us/library/ms161956.aspx.

|||

Hi Greg,

Yes, I did enable Remote Connections on the Surface Area Configuraiton tool. It's set for Local and Remote and with TCP/IP and Named Pipes.

If you have any other ideas, I'd certainly appreciate the help.

Thanks,

JerryK

|||

Can you access sql server on your machine from a remote machine using osql.exe? If so, then it's something with your app. If not, then it's some machine/sql configuration. Are you using winxp sp2? See if the suggestions in this thread can help. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=192102&SiteID=1

|||

Greg,

I looked at the thread and have this question: What are Server Client Tools, where do I get them from to put them on the client?

I tried copying osql.exe to the client, but it looks like I need more than that because I got an error message about a missing file.

I might add that the server machine (JERRY) is a Window XP Home machine and not the Professional. But, the client machine is Windows XP Professional. Also, MSDE is still on the server machine. Could either of those circumstances have anything to do with it?

Thanks again,

JerryK

|||

Hi Jerry,

Your problem is likely caused by not having SQL Browser turned on and making an Exception for Browser in the firewall. When you are trying to connect to a named instance such as JerryK\sqlexpress, you need to have SQL Browser running on the server in order for the instance name to be recognized unless you are connecting to the server using a specific port number. Since SQL Browser lisents on it's own port, you also have to make the Exception for Browser in the firewall.

Once you've done this, you should be good to go.

Regards,
Mike Wachal
SQL Express

|||

Hi Mike,

I made sure that the browser is turned on and sqlservr.exe is in the exclude list. But, same error is reported. If I turn the Microsoft firewall off, there is no problem. If I turn on the Norton firewall it is ok too.

Are there any other settings related to the Microsoft firewall I should be concerned about?

My home network is a cable modem connected to a LinkSys router. Could there be some conflict here?

Thanks,

JerryK

|||

Jerry,

In addition to sqlservr.exe you need to add %Program Files%\Microsoft SQL Server\90\Shared\sqlbrowser.exe to the exception list.

Check out the following blog for more information: https://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Cheers,
Dan

|||

Thank you Dan. That was the missing piece..

Thanks again,

jerryK