473,386 Members | 1,766 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,386 software developers and data experts.

Accessing SQL2005 as a Second Instance to SQL2000

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

Aug 2 '06 #1
5 3889
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
Aug 2 '06 #2
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
Aug 4 '06 #3
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
Aug 4 '06 #4
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
Aug 7 '06 #5
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
Aug 8 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Benny Ng | last post by:
The error is shown by the following connection string.£¨But it can be runs properly in SQL2000£© PROVIDER=MSDATASHAPE;DRIVER={SQL SERVER};SERVER=ibmdept;DATABASE=CY_20060208;UID=sa;PWD=111111; ...
2
by: PointMan | last post by:
i make a project in vs2005 that use ASPNETDB.MDF and use <asp:LoginView ID="LoginView1" runat="server" > server control for vs2005 in this case , i'd like to use hosting service but this...
8
by: Radu | last post by:
Hi. This code worked fine with SQL2005, but has a problem with SQL2000.... Which one ???? cnSQL = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"))...
2
by: itfetish | last post by:
I've just upgraded our WIndows 2003 (with SBS server) web server to .Net framework 2.0 (it had 1.1 before) As I have been working on a web parts page on my laptop, hosting it there, working...
4
by: imranabdulaziz | last post by:
Dear All, I am using asp.net2.0, C#, sql2005 using Visual studio 2005 Let Me explain the scenario I have checkboxlist containg 15 field. Based on no of checked field . I created...
0
by: ckmoied | last post by:
Hi guys, I want to know if there is any checklist available that one can follow before moving the databases from one server to another one. I am a newbie in SQL stuff and facing the following...
3
by: Tawfiq | last post by:
Hi, We have shifted one of our data processing to a new instance of SQL 2005. For this a new DB was created in SQL2005 and it is not an upgrade of SQL200 DB. The data processing application...
5
by: Virendra | last post by:
I need to figure out programmatically if an instance is a cluster or non-cluster. Also, if it is a cluster what are nodes and associated cluster directories in the instance. All I know is the...
0
by: nic829 | last post by:
Hi, I was upgrading a instance from SQL2000 to SQL2005. During SQL2005 setup I accidently allow the files to be installed in C drive, but I should have them all installed in D drive. My databases...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.