How do I connect to a server with multiple instances of SQL

Question

How do I connect to a server with multiple instances of SQL

Mike Lopilato

SSC Enthusiast

Points: 182
More actions
September 22, 2006 at 2:13 pm

#172770

How do I connect to a server with SQL 2000 and SQL EXPRESS using SQL Management Studio EXPRESS FROM a REMOTE site?
September 25, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Dan Jones-279024 Ten Centuries Points: 1352 More actions · Answer 1

Hi Mike, what are you trying to connect from? Enterprise Manager, SQL Server Management Studio, an ASP.net application, etc.?

Mike Lopilato SSC Enthusiast Points: 182 More actions · Answer 2

I am trying to connect using Microsoft SQL Server Management Studio Express from my PC on DSL.

I have SQL 2000 Ent and SQL 2005 express running on Windows 2000 Adv server.

The problem I'm having is that I"m not sure how to address the instance of SQL Express and not the SQL 2000.

I've tried 'serverIP\SQLEXPRESS' with no luck,

'ServerIP' connects me to the SQL 2000.

Dan Jones-279024 Ten Centuries Points: 1352 More actions · Answer 3

You'll ned to start the SQLBrowser service on the server. You can do this from "SQL Server Configuration Manager". Then you'll be able to connect using 'serverIP'\SQLExpress

Mike Lopilato SSC Enthusiast Points: 182 More actions · Answer 4

The 'SQL Server Browser' is running.

What else could it be?

'Remote Connections' is enabled.

This is the error msg:

TITLE: Connect to Server

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

Cannot connect to xxx.xxx.xxx.xxx\SQLExpress.

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

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

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

BUTTONS:

OK

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

Dan Jones-279024 Ten Centuries Points: 1352 More actions · Answer 5

Sorry for the incomplete answer. Take a look at the following blog entry: http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Cheers,

Dan

Mike Lopilato SSC Enthusiast Points: 182 More actions · Answer 6

Well, here in lies the dilema!

I've done all of that is required in those Documents, prior to posting this.

Although, I'm not running Server 2003, I'm running 2000 no Windows Firewall, but I have a cisco firewall with permission given for 'udp on 1434' & 'tcp on 1433' to the Server IP.

What am I missing?

Enterprise Manager works connecting to SQL 2000 just fine.

Dan Jones-279024 Ten Centuries Points: 1352 More actions · Answer 7

I'm not familiar with configuring Cisco firewalls. However, I can conceptually tell you what to do. The default SQL Server instance listens on port 1433, the named instanace (SQLExpress) in your case is not listening on the default port. Therefore, you have two options:

1) Configure the Cisco firewall to allow sqlbrowser.exe and sqlsvr.exe to accept connections. The port numbers will not be fixed, this is what SQLBrowser controls, it'll listen on 1433 and redirect traffic to the appropriate instance.

or

2) Configure SQL Server (the SQLExpress instance) to listen on a specific port and open that port on the Cisco firewall. You can find information on on configuring SQL Server to listen on a specific port in Books Online.

Dan