Cannot connect remotely to SQL Server 2005/2008

06-28-2012, 10:28 AM

Mike

Contributor

Join Date: Apr 2001

Location: Montreal, Canada

Posts: 428

Cannot connect remotely to SQL Server 2005/2008

Hi folks,

I don't understand what's going on here. I use the following connection code:

Code:

 
    Dim conMasterDB as ADODB.Connection   
    Set conMasterDB = New ADODB.Connection
    With conMasterDB
        .Provider = "SQLOLEDB"
        .Properties("Data Source").Value = MyServer\SQLExpress08        .Properties("User ID").Value = "MyUserID"
        .Properties("Password").Value = "MyPwd"
        .Properties("Initial Catalog").Value = "Master"
        .Open
     End With

And this works fine if I try to connect to my local SQL Server 2008. But if I try to connect from another station on my local area network, with all firewalls down, I cannot connect. Typically, I know that firewalls may block ports 1433 and 1434 to impede a connection, but it can't be the case here because there's no firewall on either side. Can anyone provide any guidance?

Thanks!

Mike

07-04-2012, 03:46 AM

Orca44

Centurion

Join Date: Nov 2004

Location: Cape Town, South Africa

Posts: 124

Can you connect to the remotely using Management Studio?

07-04-2012, 05:42 AM

DrPunk

Senior Contributor

* Expert *

Join Date: Apr 2003

Location: Never where I want to be

Posts: 1,272

SQLExpress by default doesn't allow remote connections. You have to enable them.

With the management studio connected, right click the server in the object explorer and select properties. Then on the Connections page make sure "Allow remote connections to this server" is ticked.

The SQL Browser service can also cause problems if not started by default, but I think that's more of a 2005 issue than a 2008 one.

__________________
There are no computers in heaven!