Accessing SQL2005 as a Second Instance to SQL2000

[Mike]

I'm having trouble accessing SQL2005 Standard Edition as a second
instance of SQL Server where the first instance is SQL 2000 Enterprise
Edition. I installed SQL 2005 as a named instance "SQL2005". The
server is running Windows 2000 SP4 ON A 32-Bit machine. When I look in
Services I see the SQL Server (SQL2005), SQL Server Agent (SQL2005)
services there. I went into SQL Server Configuration manager and
disabled the named pipes protocol and only have TCP/IP and by default
Shared Memory enabled to rule out that named pipes is causing the
problem. In SQL Server Configuration Manager I created an alias
"computername$SQL2005" and was able to connect to database engine
"computername\SQL2005" through SQL Server Management Studio WHILE ON
THAT BOX.
But when trying to access the 2005 instance from another server
(64-bit Windows 2003 SP1) I'm having trouble. On the 64-bit machine I
disabled the named pipes protocol for both "SQL Native Client
Configuration (32-bit)" and "SQL Native Client Configuration". Under
32-bit I created the alias "machinename$SQL2005" to point to the IP
address of the 32-bit machine. In SQL Server Management Studio when
trying to connect to the database engine "machinename$SQL2005" it
defaults to the SQL 2000 instance. How do I access the SQL2005
instance from a remote server?

Any help would be appreciated.

Mike

[Stu]

This question seems to crop up a lot; I'm surprised that Microsoft
didn't make a bigger deal about it. By default, SQL Server 2005 does
not allow remote access; you have to turn it on.

Use the Surface Area Configuration tool for SQL Server 2005; under
database engine, select Remote Connections and make sure that local and
remote connections are selected.

HTH,
Stu

Mike wrote:

I'm having trouble accessing SQL2005 Standard Edition as a second
instance of SQL Server where the first instance is SQL 2000 Enterprise
Edition. I installed SQL 2005 as a named instance "SQL2005". The
server is running Windows 2000 SP4 ON A 32-Bit machine. When I look in
Services I see the SQL Server (SQL2005), SQL Server Agent (SQL2005)
services there. I went into SQL Server Configuration manager and
disabled the named pipes protocol and only have TCP/IP and by default
Shared Memory enabled to rule out that named pipes is causing the
problem. In SQL Server Configuration Manager I created an alias
"computername$SQL2005" and was able to connect to database engine
"computername\SQL2005" through SQL Server Management Studio WHILE ON
THAT BOX.
But when trying to access the 2005 instance from another server
(64-bit Windows 2003 SP1) I'm having trouble. On the 64-bit machine I
disabled the named pipes protocol for both "SQL Native Client
Configuration (32-bit)" and "SQL Native Client Configuration". Under
32-bit I created the alias "machinename$SQL2005" to point to the IP
address of the 32-bit machine. In SQL Server Management Studio when
trying to connect to the database engine "machinename$SQL2005" it
defaults to the SQL 2000 instance. How do I access the SQL2005
instance from a remote server?

Any help would be appreciated.

Mike

[Mike]

Thanks for your response Stu, After checking SQL Server 2005 Surface
Area Configuration it looks like the option for "Local and Remote
Connections" was already selected. I checked under "Database Engine"
under both "View by Instance" and "View by Component" and it seems the
same. I even clicked on "Local connections only" then back to "Local
and Remote Connections" just to make sure the setting was recognized
when hitting "Apply". Then restarted SQL Server (2005) service. Still
unable to access this instance from the other server. Do I need to use
another tcp/ip port for the second instance? I remember reading this
somewhere, but when I tried to use port 1435 because UDP port 1434 is
supposedly disabled in SQL 2005, I still cannot connect.

When I try connecting remotely I'm still getting the SQL 2000 instance.
Any other ideas?

Thanks,

Mike

Stu wrote:

This question seems to crop up a lot; I'm surprised that Microsoft
didn't make a bigger deal about it. By default, SQL Server 2005 does
not allow remote access; you have to turn it on.

Use the Surface Area Configuration tool for SQL Server 2005; under
database engine, select Remote Connections and make sure that local and
remote connections are selected.

HTH,
Stu

Mike wrote:

I'm having trouble accessing SQL2005 Standard Edition as a second
instance of SQL Server where the first instance is SQL 2000 Enterprise
Edition. I installed SQL 2005 as a named instance "SQL2005". The
server is running Windows 2000 SP4 ON A 32-Bit machine. When I look in
Services I see the SQL Server (SQL2005), SQL Server Agent (SQL2005)
services there. I went into SQL Server Configuration manager and
disabled the named pipes protocol and only have TCP/IP and by default
Shared Memory enabled to rule out that named pipes is causing the
problem. In SQL Server Configuration Manager I created an alias
"computername$SQL2005" and was able to connect to database engine
"computername\SQL2005" through SQL Server Management Studio WHILE ON
THAT BOX.
But when trying to access the 2005 instance from another server
(64-bit Windows 2003 SP1) I'm having trouble. On the 64-bit machine I
disabled the named pipes protocol for both "SQL Native Client
Configuration (32-bit)" and "SQL Native Client Configuration". Under
32-bit I created the alias "machinename$SQL2005" to point to the IP
address of the 32-bit machine. In SQL Server Management Studio when
trying to connect to the database engine "machinename$SQL2005" it
defaults to the SQL 2000 instance. How do I access the SQL2005
instance from a remote server?

Any help would be appreciated.

Mike

[Erland Sommarskog]

Mike (mj*******@campsystems.com) writes:

Thanks for your response Stu, After checking SQL Server 2005 Surface
Area Configuration it looks like the option for "Local and Remote
Connections" was already selected. I checked under "Database Engine"
under both "View by Instance" and "View by Component" and it seems the
same. I even clicked on "Local connections only" then back to "Local
and Remote Connections" just to make sure the setting was recognized
when hitting "Apply". Then restarted SQL Server (2005) service. Still
unable to access this instance from the other server. Do I need to use
another tcp/ip port for the second instance? I remember reading this
somewhere, but when I tried to use port 1435 because UDP port 1434 is
supposedly disabled in SQL 2005, I still cannot connect.

Have you checked that the SQL Browser service is running? You control it
from the SQL Configuration Management tool.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Mike]

Yes, the SQL Browser service is running. I restarted it just to make
sure. Has there been updates to BOL since May 2006? That is the date
of my SQL 2005 installation. I will download the latest, thanks,
Mike
Erland Sommarskog wrote:

Mike (mj*******@campsystems.com) writes:

Thanks for your response Stu, After checking SQL Server 2005 Surface
Area Configuration it looks like the option for "Local and Remote
Connections" was already selected. I checked under "Database Engine"
under both "View by Instance" and "View by Component" and it seems the
same. I even clicked on "Local connections only" then back to "Local
and Remote Connections" just to make sure the setting was recognized
when hitting "Apply". Then restarted SQL Server (2005) service. Still
unable to access this instance from the other server. Do I need to use
another tcp/ip port for the second instance? I remember reading this
somewhere, but when I tried to use port 1435 because UDP port 1434 is
supposedly disabled in SQL 2005, I still cannot connect.

Have you checked that the SQL Browser service is running? You control it
from the SQL Configuration Management tool.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Mike (mj*******@campsystems.com) writes:

Yes, the SQL Browser service is running. I restarted it just to make
sure. Has there been updates to BOL since May 2006? That is the date
of my SQL 2005 installation. I will download the latest, thanks,

Yes, there is a new version. (They come out once a quarter.) URL below.

I went back to your original post, and it appears that you have defined
alias which points to the IP Address on the machine with SQL 2000 and
SQL 2005 on. But it is not clear that you specifiec the instance name
in the alias definition. If you only use the IP address you will end up
at the default instance, which I understand is running SQL 2000.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx