Help - connecting to remote db using SQL Server Mgmt Studio Express

[twofive]

Hi, I'm new to MS SQL Server, and I can't find the way to connect to a remote SQL Server 2k db using SQL Server Management Studio Express, or its older equivalent Enterprise Manager. Please advise.

Note, I am able to connect to it in VB with the following code:

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=sqloledb;Data Source=[b]dbserver.domain.com[/b];Initial Catalog=[b]db_name[/b];User Id=[b]user[/b];Password=[b]pass[/b];"

The error I get from SQL Server Management Studio Express is

Cannot connect to dbserver.domain.com

Additional information:

An error has occurred while establishing a connection to the server. 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. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

Lastly, the info I'm providing to SQL Server Management Studio Express is Server name: dbserver.domain.com, Authentication: SQL Server Authentication, Login:user, Password:pass. It doesn't seem to ask for the database name anywhere, which may be the problem...

Thanks in advance.

 

[Roadki11]

Was the SQL server setup to accept network connections? You say sql 2k, i assume you mean full sql not msde. i know the default install of 2k msde didnt enable network connections.

RoadKi11

 

[kmkeshav]

What is the error you get when you try to connect to the SQL Server 2000 database from the Enterprise Manager?

 

[twofive]

Roadki11:

I assume so, but the server itself is remote--on the web host's servers. This is my problem. I'm sorry I don't have much information, besides the fact that I *can* connect to the db by ftping asp files w/ sql code in them to the web server, and then viewing the asp pages in a web browser.

-------------------------------

kmkeshav:

First error >

The SQL Server \\DBSERVER.DOMAIN.COM is not known to be running. Are you sure you wish to connect?

(It is odd that it has the \\ in front of the server name..)

Second error >

A Connection could not be established to DBSERVER.DOMAIN.COM.

Reason: SQL Server does not exist or access denied.
ConnectionOpen(Connect())

Please verify SQL Server is running and check your SQL Server registration properties (by right-clicking on the DBSERVER.DOMAIN.COM node) and try again.

 

[Roadki11]

I bet its not configured for network access. The reason the asp connects work is because the connection is local at that point, asp and sql is on the same box. you need to get local access and change the sql config to allow network connections.

RoadKi11

 

[kmkeshav]

Some reasons for "SQL Server does not exist or access denied":

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

Hope this helps.

-Keshav