Accessing SQL2005 as a Second Instance to SQL2000

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Mike
    #1

    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$S QL2005" and was able to connect to database engine
    "computername\S QL2005" 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$SQ L2005" 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$SQ L2005" it
    defaults to the SQL 2000 instance. How do I access the SQL2005
    instance from a remote server?

    Any help would be appreciated.

    Mike

    Tags: None
    • Stu
      #2
      Re: Accessing SQL2005 as a Second Instance to SQL2000

      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$S QL2005" and was able to connect to database engine
      "computername\S QL2005" 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$SQ L2005" 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$SQ L2005" it
      defaults to the SQL 2000 instance. How do I access the SQL2005
      instance from a remote server?
      >
      Any help would be appreciated.
      >
      Mike

        Comment

        • Mike
          #3
          Re: Accessing SQL2005 as a Second Instance to SQL2000

          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$S QL2005" and was able to connect to database engine
          "computername\S QL2005" 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$SQ L2005" 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$SQ L2005" it
          defaults to the SQL 2000 instance. How do I access the SQL2005
          instance from a remote server?

          Any help would be appreciated.

          Mike

            Comment

            • Erland Sommarskog
              #4
              Re: Accessing SQL2005 as a Second Instance to SQL2000

              Mike (mjorlando@camp systems.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, esquel@sommarsk og.se

              Books Online for SQL Server 2005 at
              http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

              Books Online for SQL Server 2000 at
              http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                Comment

                • Mike
                  #5
                  Re: Accessing SQL2005 as a Second Instance to SQL2000

                  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 (mjorlando@camp systems.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, esquel@sommarsk og.se
                  >
                  Books Online for SQL Server 2005 at
                  http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

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

                    Comment

                    • Erland Sommarskog
                      #6
                      Re: Accessing SQL2005 as a Second Instance to SQL2000

                      Mike (mjorlando@camp systems.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, esquel@sommarsk og.se

                      Books Online for SQL Server 2005 at
                      http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

                      Books Online for SQL Server 2000 at
                      http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                        Comment

                        Previous template Next
                        Working...