SQL Configure: instances

Showing posts with label instances. Show all posts

Thursday, 22 March 2012

connect to named instances

How can I connect to a named instance from a client. I believe the problem
is bcos of the port number.. When i connect to computername\instancename it
says SQL Server does not exist or access denied. However when i register an
alias in client network utility with the instance name and the actual port
number it works fine, but i have to use the alias.
Can i not register with the instance name directly without setting it up
thru the client network utility ? Please advice..Your client system likely has an older version of MDAC. Install MDAC 2.6 or
higher to connect to a named instance without requiring an alias.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ud6L39KLEHA.2100@.TK2MSFTNGP10.phx.gbl...
> How can I connect to a named instance from a client. I believe the problem
> is bcos of the port number.. When i connect to computername\instancename
it
> says SQL Server does not exist or access denied. However when i register
an
> alias in client network utility with the instance name and the actual port
> number it works fine, but i have to use the alias.
> Can i not register with the instance name directly without setting it up
> thru the client network utility ? Please advice..
>|||> Your client system likely has an older version of MDAC. Install MDAC 2.6
> or
> higher to connect to a named instance without requiring an alias.
Is this documented somewhere Geoff? I don't remember *exactly* when MDAC
2.6 was released, but I know I was able to connect to named instances before
I ever worried about what version of MDAC was installed, and that was some
time ago.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Does that mean that with the latest MDAC, the client would dynamically
determine the port of the named instance ?
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23GGZf4LLEHA.3944@.tk2msftngp13.phx.gbl...
> > Your client system likely has an older version of MDAC. Install MDAC
2.6
> > or
> > higher to connect to a named instance without requiring an alias.
> Is this documented somewhere Geoff? I don't remember *exactly* when MDAC
> 2.6 was released, but I know I was able to connect to named instances
before
> I ever worried about what version of MDAC was installed, and that was some
> time ago.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>|||From BOL "Each instance of Microsoft® SQL ServerT 2000 listens on a unique
set of network address so that applications can connect to different
instances"
What does this mean ? What is this n/w address ? If i have 2 instances( one
default and the other named ) , what are the 2 network addresses ? It cant
be IP right ? So what is it ? Thanks
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23GGZf4LLEHA.3944@.tk2msftngp13.phx.gbl...
> > Your client system likely has an older version of MDAC. Install MDAC
2.6
> > or
> > higher to connect to a named instance without requiring an alias.
> Is this documented somewhere Geoff? I don't remember *exactly* when MDAC
> 2.6 was released, but I know I was able to connect to named instances
before
> I ever worried about what version of MDAC was installed, and that was some
> time ago.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>|||I am not sure it MDAC 2.6 is the breakover point, but it is around that
version. I will try and see if I can find it.
SQL listens on all IP addresses that exist at service startup. Each
instance has its own port. A clustered instance has its own IP address and
port number, since the IP address is unique to the virtual server. MDAC 2.6
and above does resolve names and port numbers automagically.
GNH
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ejJR8QMLEHA.2692@.tk2msftngp13.phx.gbl...
> From BOL "Each instance of Microsoft® SQL ServerT 2000 listens on a unique
> set of network address so that applications can connect to different
> instances"
> What does this mean ? What is this n/w address ? If i have 2 instances(
one
> default and the other named ) , what are the 2 network addresses ? It cant
> be IP right ? So what is it ? Thanks
> "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
> news:%23GGZf4LLEHA.3944@.tk2msftngp13.phx.gbl...
> > > Your client system likely has an older version of MDAC. Install MDAC
> 2.6
> > > or
> > > higher to connect to a named instance without requiring an alias.
> >
> > Is this documented somewhere Geoff? I don't remember *exactly* when
MDAC
> > 2.6 was released, but I know I was able to connect to named instances
> before
> > I ever worried about what version of MDAC was installed, and that was
some
> > time ago.
> >
> > --
> > Aaron Bertrand
> > SQL Server MVP
> > http://www.aspfaq.com/
> >
> >
>|||It is indirectly documented here:
INF: How to Connect to an SQL Server 2000 Named Instance with the Previous
Version's Client Tools
http://support.microsoft.com/default.aspx?scid=kb;en-us;265808&Product=sql2k
The MORE INFORMATION section begins with a statement that MDAC 2.6 is
required to connect to a named instance in \\computer_name\instance_name
format. The article then goes on to describe a workaround.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23GGZf4LLEHA.3944@.tk2msftngp13.phx.gbl...
> > Your client system likely has an older version of MDAC. Install MDAC
2.6
> > or
> > higher to connect to a named instance without requiring an alias.
> Is this documented somewhere Geoff? I don't remember *exactly* when MDAC
> 2.6 was released, but I know I was able to connect to named instances
before
> I ever worried about what version of MDAC was installed, and that was some
> time ago.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>|||Ah, I see, this wasn't really specified, as I use computername\instance_name
(not \\computername\instance_name) over TCP/IP all the time, and
http://www.aspfaq.com/2126 has recommended the \instance_name syntax since
long before MDAC 2.6 (1999 or 2000 sometime). I'm talking about connecting
to SQL Server from, for example, ASP or VB, over TCP/IP, not named pipes.
Sorry about the confusion.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OJzZU3MLEHA.3808@.TK2MSFTNGP12.phx.gbl...
> It is indirectly documented here:
> INF: How to Connect to an SQL Server 2000 Named Instance with the Previous
> Version's Client Tools
> http://support.microsoft.com/default.aspx?scid=kb;en-us;265808&Product=sql2k
> The MORE INFORMATION section begins with a statement that MDAC 2.6 is
> required to connect to a named instance in \\computer_name\instance_name
> format. The article then goes on to describe a workaround.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
> "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
> news:%23GGZf4LLEHA.3944@.tk2msftngp13.phx.gbl...
>> > Your client system likely has an older version of MDAC. Install MDAC
> 2.6
>> > or
>> > higher to connect to a named instance without requiring an alias.
>> Is this documented somewhere Geoff? I don't remember *exactly* when MDAC
>> 2.6 was released, but I know I was able to connect to named instances
> before
>> I ever worried about what version of MDAC was installed, and that was
>> some
>> time ago.
>> --
>> Aaron Bertrand
>> SQL Server MVP
>> http://www.aspfaq.com/
>>
>|||Let me rephrase, 2000 sometime. I thought MDAC 2.6 was introduced much
later than SQL Server 2000, but I must be remembering incorrectly.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||2.6 was the release with RTM SQL 2000
--snip
Release Version Number
MDAC 2.6 RTM (2.60.6526.3), September 2000
Release Function
This release provides, directly to the public, the same version of MDAC
released with Microsoft SQL ServerT 2000.
--snip
Snipped from:
Release Manifest for MDAC 2.6 RTM (2.60.6526.3)
http://support.microsoft.com/default.aspx?scid=/support/servicepacks/mdac/2.6/rtmmanifest.asp
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23TxVKBNLEHA.3012@.tk2msftngp13.phx.gbl...
> Let me rephrase, 2000 sometime. I thought MDAC 2.6 was introduced much
> later than SQL Server 2000, but I must be remembering incorrectly.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>|||Thanks, at least it's just my memory that's messed up. Math and depth
perception are still functioning okay. ;-)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
> 2.6 was the release with RTM SQL 2000|||Memory is fine here. Sanity is another question, but that has been an open
issue for a very long time.
GNH
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:urFfumNLEHA.2396@.TK2MSFTNGP12.phx.gbl...
> Thanks, at least it's just my memory that's messed up. Math and depth
> perception are still functioning okay. ;-)
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> > 2.6 was the release with RTM SQL 2000
>|||So i have to specify the port number then to connect thru EM from a client.
Am i right ? I tried this computername\instancename,port and it worked
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:u7SZgkMLEHA.2660@.TK2MSFTNGP09.phx.gbl...
> I am not sure it MDAC 2.6 is the breakover point, but it is around that
> version. I will try and see if I can find it.
> SQL listens on all IP addresses that exist at service startup. Each
> instance has its own port. A clustered instance has its own IP address
and
> port number, since the IP address is unique to the virtual server. MDAC
2.6
> and above does resolve names and port numbers automagically.
> GNH
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ejJR8QMLEHA.2692@.tk2msftngp13.phx.gbl...
> > From BOL "Each instance of Microsoft® SQL ServerT 2000 listens on a
unique
> > set of network address so that applications can connect to different
> > instances"
> >
> > What does this mean ? What is this n/w address ? If i have 2 instances(
> one
> > default and the other named ) , what are the 2 network addresses ? It
cant
> > be IP right ? So what is it ? Thanks
> >
> > "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
> > news:%23GGZf4LLEHA.3944@.tk2msftngp13.phx.gbl...
> > > > Your client system likely has an older version of MDAC. Install
MDAC
> > 2.6
> > > > or
> > > > higher to connect to a named instance without requiring an alias.
> > >
> > > Is this documented somewhere Geoff? I don't remember *exactly* when
> MDAC
> > > 2.6 was released, but I know I was able to connect to named instances
> > before
> > > I ever worried about what version of MDAC was installed, and that was
> some
> > > time ago.
> > >
> > > --
> > > Aaron Bertrand
> > > SQL Server MVP
> > > http://www.aspfaq.com/
> > >
> > >
> >
> >
>|||MDAC 2.6 and higher can determine the port number from a
computername\instancename request. 2.5 and lower cannot. You can specify a
port number with any MDAC version if it makes you more comfortable. If you
turn on port hiding on the server, you must use the port number.
SQL uses port 1434 to handle name-port resolution. That is where the
Slammer worm hit.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23R5K20TLEHA.3216@.TK2MSFTNGP12.phx.gbl...
> So i have to specify the port number then to connect thru EM from a
client.
> Am i right ? I tried this computername\instancename,port and it worked
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:u7SZgkMLEHA.2660@.TK2MSFTNGP09.phx.gbl...
> > I am not sure it MDAC 2.6 is the breakover point, but it is around that
> > version. I will try and see if I can find it.
> >
> > SQL listens on all IP addresses that exist at service startup. Each
> > instance has its own port. A clustered instance has its own IP address
> and
> > port number, since the IP address is unique to the virtual server. MDAC
> 2.6
> > and above does resolve names and port numbers automagically.
> >
> > GNH
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:ejJR8QMLEHA.2692@.tk2msftngp13.phx.gbl...
> > > From BOL "Each instance of Microsoft® SQL ServerT 2000 listens on a
> unique
> > > set of network address so that applications can connect to different
> > > instances"
> > >
> > > What does this mean ? What is this n/w address ? If i have 2
instances(
> > one
> > > default and the other named ) , what are the 2 network addresses ? It
> cant
> > > be IP right ? So what is it ? Thanks
> > >
> > > "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
> > > news:%23GGZf4LLEHA.3944@.tk2msftngp13.phx.gbl...
> > > > > Your client system likely has an older version of MDAC. Install
> MDAC
> > > 2.6
> > > > > or
> > > > > higher to connect to a named instance without requiring an alias.
> > > >
> > > > Is this documented somewhere Geoff? I don't remember *exactly* when
> > MDAC
> > > > 2.6 was released, but I know I was able to connect to named
instances
> > > before
> > > > I ever worried about what version of MDAC was installed, and that
was
> > some
> > > > time ago.
> > > >
> > > > --
> > > > Aaron Bertrand
> > > > SQL Server MVP
> > > > http://www.aspfaq.com/
> > > >
> > > >
> > >
> > >
> >
> >
>|||Any way to find out what version of MDAC I have ? Ive got SQL 2K Developer
Edition with SP3a installed
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uJEdE6ULEHA.1052@.TK2MSFTNGP12.phx.gbl...
> MDAC 2.6 and higher can determine the port number from a
> computername\instancename request. 2.5 and lower cannot. You can specify
a
> port number with any MDAC version if it makes you more comfortable. If
you
> turn on port hiding on the server, you must use the port number.
> SQL uses port 1434 to handle name-port resolution. That is where the
> Slammer worm hit.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23R5K20TLEHA.3216@.TK2MSFTNGP12.phx.gbl...
> > So i have to specify the port number then to connect thru EM from a
> client.
> > Am i right ? I tried this computername\instancename,port and it worked
> >
> >
> > "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> > news:u7SZgkMLEHA.2660@.TK2MSFTNGP09.phx.gbl...
> > > I am not sure it MDAC 2.6 is the breakover point, but it is around
that
> > > version. I will try and see if I can find it.
> > >
> > > SQL listens on all IP addresses that exist at service startup. Each
> > > instance has its own port. A clustered instance has its own IP
address
> > and
> > > port number, since the IP address is unique to the virtual server.
MDAC
> > 2.6
> > > and above does resolve names and port numbers automagically.
> > >
> > > GNH
> > >
> > >
> > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > news:ejJR8QMLEHA.2692@.tk2msftngp13.phx.gbl...
> > > > From BOL "Each instance of Microsoft® SQL ServerT 2000 listens on a
> > unique
> > > > set of network address so that applications can connect to different
> > > > instances"
> > > >
> > > > What does this mean ? What is this n/w address ? If i have 2
> instances(
> > > one
> > > > default and the other named ) , what are the 2 network addresses ?
It
> > cant
> > > > be IP right ? So what is it ? Thanks
> > > >
> > > > "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
> > > > news:%23GGZf4LLEHA.3944@.tk2msftngp13.phx.gbl...
> > > > > > Your client system likely has an older version of MDAC. Install
> > MDAC
> > > > 2.6
> > > > > > or
> > > > > > higher to connect to a named instance without requiring an
alias.
> > > > >
> > > > > Is this documented somewhere Geoff? I don't remember *exactly*
when
> > > MDAC
> > > > > 2.6 was released, but I know I was able to connect to named
> instances
> > > > before
> > > > > I ever worried about what version of MDAC was installed, and that
> was
> > > some
> > > > > time ago.
> > > > >
> > > > > --
> > > > > Aaron Bertrand
> > > > > SQL Server MVP
> > > > > http://www.aspfaq.com/
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||HOW TO: Check for MDAC Version
http://support.microsoft.com/default.aspx?scid=kb;en-us;301202&Product=mdac
I believe SP3a shipped with MDAC 2.7.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e5aRYEVLEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Any way to find out what version of MDAC I have ? Ive got SQL 2K Developer
> Edition with SP3a installed
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uJEdE6ULEHA.1052@.TK2MSFTNGP12.phx.gbl...
> > MDAC 2.6 and higher can determine the port number from a
> > computername\instancename request. 2.5 and lower cannot. You can
specify
> a
> > port number with any MDAC version if it makes you more comfortable. If
> you
> > turn on port hiding on the server, you must use the port number.
> >
> > SQL uses port 1434 to handle name-port resolution. That is where the
> > Slammer worm hit.
> >
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:%23R5K20TLEHA.3216@.TK2MSFTNGP12.phx.gbl...
> > > So i have to specify the port number then to connect thru EM from a
> > client.
> > > Am i right ? I tried this computername\instancename,port and it worked
> > >
> > >
> > > "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> > > news:u7SZgkMLEHA.2660@.TK2MSFTNGP09.phx.gbl...
> > > > I am not sure it MDAC 2.6 is the breakover point, but it is around
> that
> > > > version. I will try and see if I can find it.
> > > >
> > > > SQL listens on all IP addresses that exist at service startup. Each
> > > > instance has its own port. A clustered instance has its own IP
> address
> > > and
> > > > port number, since the IP address is unique to the virtual server.
> MDAC
> > > 2.6
> > > > and above does resolve names and port numbers automagically.
> > > >
> > > > GNH
> > > >
> > > >
> > > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > > news:ejJR8QMLEHA.2692@.tk2msftngp13.phx.gbl...
> > > > > From BOL "Each instance of Microsoft® SQL ServerT 2000 listens on
a
> > > unique
> > > > > set of network address so that applications can connect to
different
> > > > > instances"
> > > > >
> > > > > What does this mean ? What is this n/w address ? If i have 2
> > instances(
> > > > one
> > > > > default and the other named ) , what are the 2 network addresses ?
> It
> > > cant
> > > > > be IP right ? So what is it ? Thanks
> > > > >
> > > > > "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
> > > > > news:%23GGZf4LLEHA.3944@.tk2msftngp13.phx.gbl...
> > > > > > > Your client system likely has an older version of MDAC.
Install
> > > MDAC
> > > > > 2.6
> > > > > > > or
> > > > > > > higher to connect to a named instance without requiring an
> alias.
> > > > > >
> > > > > > Is this documented somewhere Geoff? I don't remember *exactly*
> when
> > > > MDAC
> > > > > > 2.6 was released, but I know I was able to connect to named
> > instances
> > > > > before
> > > > > > I ever worried about what version of MDAC was installed, and
that
> > was
> > > > some
> > > > > > time ago.
> > > > > >
> > > > > > --
> > > > > > Aaron Bertrand
> > > > > > SQL Server MVP
> > > > > > http://www.aspfaq.com/
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Hassan,
Try Component checker tool (url may wrap)
'MDAC Utility: Component Checker'
http://www.microsoft.com/downloads/details.aspx?familyid=8f0a8df6-4a21-4b43-bf53-14332ef092c9&displaylang=en
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e5aRYEVLEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Any way to find out what version of MDAC I have ? Ive got SQL 2K Developer
> Edition with SP3a installed
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uJEdE6ULEHA.1052@.TK2MSFTNGP12.phx.gbl...
> > MDAC 2.6 and higher can determine the port number from a
> > computername\instancename request. 2.5 and lower cannot. You can
specify
> a
> > port number with any MDAC version if it makes you more comfortable. If
> you
> > turn on port hiding on the server, you must use the port number.
> >
> > SQL uses port 1434 to handle name-port resolution. That is where the
> > Slammer worm hit.
> >
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:%23R5K20TLEHA.3216@.TK2MSFTNGP12.phx.gbl...
> > > So i have to specify the port number then to connect thru EM from a
> > client.
> > > Am i right ? I tried this computername\instancename,port and it worked
> > >
> > >
> > > "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> > > news:u7SZgkMLEHA.2660@.TK2MSFTNGP09.phx.gbl...
> > > > I am not sure it MDAC 2.6 is the breakover point, but it is around
> that
> > > > version. I will try and see if I can find it.
> > > >
> > > > SQL listens on all IP addresses that exist at service startup. Each
> > > > instance has its own port. A clustered instance has its own IP
> address
> > > and
> > > > port number, since the IP address is unique to the virtual server.
> MDAC
> > > 2.6
> > > > and above does resolve names and port numbers automagically.
> > > >
> > > > GNH
> > > >
> > > >
> > > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > > news:ejJR8QMLEHA.2692@.tk2msftngp13.phx.gbl...
> > > > > From BOL "Each instance of Microsoft® SQL ServerT 2000 listens on
a
> > > unique
> > > > > set of network address so that applications can connect to
different
> > > > > instances"
> > > > >
> > > > > What does this mean ? What is this n/w address ? If i have 2
> > instances(
> > > > one
> > > > > default and the other named ) , what are the 2 network addresses ?
> It
> > > cant
> > > > > be IP right ? So what is it ? Thanks
> > > > >
> > > > > "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
> > > > > news:%23GGZf4LLEHA.3944@.tk2msftngp13.phx.gbl...
> > > > > > > Your client system likely has an older version of MDAC.
Install
> > > MDAC
> > > > > 2.6
> > > > > > > or
> > > > > > > higher to connect to a named instance without requiring an
> alias.
> > > > > >
> > > > > > Is this documented somewhere Geoff? I don't remember *exactly*
> when
> > > > MDAC
> > > > > > 2.6 was released, but I know I was able to connect to named
> > instances
> > > > > before
> > > > > > I ever worried about what version of MDAC was installed, and
that
> > was
> > > > some
> > > > > > time ago.
> > > > > >
> > > > > > --
> > > > > > Aaron Bertrand
> > > > > > SQL Server MVP
> > > > > > http://www.aspfaq.com/
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||> Any way to find out what version of MDAC I have ?
Many ways.
http://www.aspfaq.com/2057

connect to named instances

Tuesday, 20 March 2012

Connect to a 2nd instance of SQL server while UDP 1434 is blocked

I have two instances of SQL 2000 on one of our servers. I can connect to the
first instance that has the machine name and port 1434 from anywhere I want.
But when I try to connect to the second instance on the same machine with
then name of machinename\G3 from a server on a second network where UDP 1434
port is blocked by the firewall, the connection fail and the second instance
is not available. I followed this article on Microsoft knowledge base
http://support.microsoft.com/kb/265808/ where I created an alias for the G3
server on the client machine using the IP address and the assigned port
number, but that didnâ't help and I still could not connect to the server. I
basically followed those steps:
2. Configure the server alias on the client computer.
SQL Server 2000a. Start the Client Network Utility.
b. On the General tab, verify that TCP/IP appears in the list under Enabled
protocols by order.
c. Click the Alias tab, and then click Add.
d. Under Network libraries, select TCP/IP.
e. In the Server name text box, type the IP address of the server that is
running SQL Server 2005.
Note The IP address that you type here is the one that is configured to use
the TCP/IP port number.
f. Click to clear the Dynamically determine port check box, and then type
the port number of the instance of SQL Server 2005 in the Port number text
box.
g. Type a name in the Server alias text box, and then click OK.
I asked the IT department to unblock the UDP 1434 temporarily to see if I
can connect to the G3 server, and I was able to connect with no problem.
Unfortunately, they are required to block UDP 1434 so I have to deal with
that.
Does anybody have any idea that could help? Please let me know.
ThanksHi Danny
A named instance will use a dynamic port by default and UDP 1434 is used to
transmit the name of the instance name back to the client. You will need to
make the port fixed on the server and the corresponding port configured on
the client.
Check out http://support.microsoft.com/kb/287932.
John
"Danny Mansour" wrote:
> I have two instances of SQL 2000 on one of our servers. I can connect to the
> first instance that has the machine name and port 1434 from anywhere I want.
> But when I try to connect to the second instance on the same machine with
> then name of machinename\G3 from a server on a second network where UDP 1434
> port is blocked by the firewall, the connection fail and the second instance
> is not available. I followed this article on Microsoft knowledge base
> http://support.microsoft.com/kb/265808/ where I created an alias for the G3
> server on the client machine using the IP address and the assigned port
> number, but that didnâ't help and I still could not connect to the server. I
> basically followed those steps:
> 2. Configure the server alias on the client computer.
> SQL Server 2000a. Start the Client Network Utility.
> b. On the General tab, verify that TCP/IP appears in the list under Enabled
> protocols by order.
> c. Click the Alias tab, and then click Add.
> d. Under Network libraries, select TCP/IP.
> e. In the Server name text box, type the IP address of the server that is
> running SQL Server 2005.
> Note The IP address that you type here is the one that is configured to use
> the TCP/IP port number.
> f. Click to clear the Dynamically determine port check box, and then type
> the port number of the instance of SQL Server 2005 in the Port number text
> box.
> g. Type a name in the Server alias text box, and then click OK.
>
> I asked the IT department to unblock the UDP 1434 temporarily to see if I
> can connect to the G3 server, and I was able to connect with no problem.
> Unfortunately, they are required to block UDP 1434 so I have to deal with
> that.
> Does anybody have any idea that could help? Please let me know.
> Thanks
>|||One way to avoid the dependency on UDP 1434 is to specify the port number
explicitly on the connection string. So assume that the server name is
ServerA, its IP address is A.B.C.D, and the instance listens on TCP port
1234, you connect to the instance as follows:
ServerA,1234
or
A.B.C.D,1234
When you connect to an instance using the ServerName\InstanceName
convention, the client needs to retrieve the port number first, and it talks
to UDP 1434 to get the port number.
Linchi
"Danny Mansour" wrote:
> I have two instances of SQL 2000 on one of our servers. I can connect to the
> first instance that has the machine name and port 1434 from anywhere I want.
> But when I try to connect to the second instance on the same machine with
> then name of machinename\G3 from a server on a second network where UDP 1434
> port is blocked by the firewall, the connection fail and the second instance
> is not available. I followed this article on Microsoft knowledge base
> http://support.microsoft.com/kb/265808/ where I created an alias for the G3
> server on the client machine using the IP address and the assigned port
> number, but that didnâ't help and I still could not connect to the server. I
> basically followed those steps:
> 2. Configure the server alias on the client computer.
> SQL Server 2000a. Start the Client Network Utility.
> b. On the General tab, verify that TCP/IP appears in the list under Enabled
> protocols by order.
> c. Click the Alias tab, and then click Add.
> d. Under Network libraries, select TCP/IP.
> e. In the Server name text box, type the IP address of the server that is
> running SQL Server 2005.
> Note The IP address that you type here is the one that is configured to use
> the TCP/IP port number.
> f. Click to clear the Dynamically determine port check box, and then type
> the port number of the instance of SQL Server 2005 in the Port number text
> box.
> g. Type a name in the Server alias text box, and then click OK.
>
> I asked the IT department to unblock the UDP 1434 temporarily to see if I
> can connect to the G3 server, and I was able to connect with no problem.
> Unfortunately, they are required to block UDP 1434 so I have to deal with
> that.
> Does anybody have any idea that could help? Please let me know.
> Thanks
>|||Thanks a bunch for the reply.
I followed the article you sent me and configured a static port and I
configured the client machine to point to the port. Somehow this still
didn't work for me. The article mentions to set a static Port before
configuring the firewall. I wonder if I need to open the new static port on
the firewall to make it work?
Please let me know what you think.
Thanks,
Danny
"John Bell" wrote:
> Hi Danny
> A named instance will use a dynamic port by default and UDP 1434 is used to
> transmit the name of the instance name back to the client. You will need to
> make the port fixed on the server and the corresponding port configured on
> the client.
> Check out http://support.microsoft.com/kb/287932.
> John
>
> "Danny Mansour" wrote:
> > I have two instances of SQL 2000 on one of our servers. I can connect to the
> > first instance that has the machine name and port 1434 from anywhere I want.
> > But when I try to connect to the second instance on the same machine with
> > then name of machinename\G3 from a server on a second network where UDP 1434
> > port is blocked by the firewall, the connection fail and the second instance
> > is not available. I followed this article on Microsoft knowledge base
> > http://support.microsoft.com/kb/265808/ where I created an alias for the G3
> > server on the client machine using the IP address and the assigned port
> > number, but that didnâ't help and I still could not connect to the server. I
> > basically followed those steps:
> >
> > 2. Configure the server alias on the client computer.
> >
> > SQL Server 2000a. Start the Client Network Utility.
> > b. On the General tab, verify that TCP/IP appears in the list under Enabled
> > protocols by order.
> > c. Click the Alias tab, and then click Add.
> > d. Under Network libraries, select TCP/IP.
> > e. In the Server name text box, type the IP address of the server that is
> > running SQL Server 2005.
> >
> > Note The IP address that you type here is the one that is configured to use
> > the TCP/IP port number.
> > f. Click to clear the Dynamically determine port check box, and then type
> > the port number of the instance of SQL Server 2005 in the Port number text
> > box.
> > g. Type a name in the Server alias text box, and then click OK.
> >
> >
> > I asked the IT department to unblock the UDP 1434 temporarily to see if I
> > can connect to the G3 server, and I was able to connect with no problem.
> > Unfortunately, they are required to block UDP 1434 so I have to deal with
> > that.
> >
> > Does anybody have any idea that could help? Please let me know.
> >
> > Thanks
> >|||thank you for your reply. The intersting thing is that I have been trying to
connect to the second instance through the Enterprise Manager using an alias
name through the Client Network Utility where I specified the IP address and
the static port number, but I was unable to make a connection. On the other
hand, trying to connect using OSQL by doing "OSQL /E servername,port as you
suggeted works fine and I'm able to query the server. I wonder what the
difference is in the way the Enterprise Manger tries to make the connection.
I'm trying to establish replication between servers thus I need the
Enterprise Manager to be able to connect.
Please let me know what you think.
Thanks a million.
Danny
"Linchi Shea" wrote:
> One way to avoid the dependency on UDP 1434 is to specify the port number
> explicitly on the connection string. So assume that the server name is
> ServerA, its IP address is A.B.C.D, and the instance listens on TCP port
> 1234, you connect to the instance as follows:
> ServerA,1234
> or
> A.B.C.D,1234
> When you connect to an instance using the ServerName\InstanceName
> convention, the client needs to retrieve the port number first, and it talks
> to UDP 1434 to get the port number.
> Linchi
> "Danny Mansour" wrote:
> > I have two instances of SQL 2000 on one of our servers. I can connect to the
> > first instance that has the machine name and port 1434 from anywhere I want.
> > But when I try to connect to the second instance on the same machine with
> > then name of machinename\G3 from a server on a second network where UDP 1434
> > port is blocked by the firewall, the connection fail and the second instance
> > is not available. I followed this article on Microsoft knowledge base
> > http://support.microsoft.com/kb/265808/ where I created an alias for the G3
> > server on the client machine using the IP address and the assigned port
> > number, but that didnâ't help and I still could not connect to the server. I
> > basically followed those steps:
> >
> > 2. Configure the server alias on the client computer.
> >
> > SQL Server 2000a. Start the Client Network Utility.
> > b. On the General tab, verify that TCP/IP appears in the list under Enabled
> > protocols by order.
> > c. Click the Alias tab, and then click Add.
> > d. Under Network libraries, select TCP/IP.
> > e. In the Server name text box, type the IP address of the server that is
> > running SQL Server 2005.
> >
> > Note The IP address that you type here is the one that is configured to use
> > the TCP/IP port number.
> > f. Click to clear the Dynamically determine port check box, and then type
> > the port number of the instance of SQL Server 2005 in the Port number text
> > box.
> > g. Type a name in the Server alias text box, and then click OK.
> >
> >
> > I asked the IT department to unblock the UDP 1434 temporarily to see if I
> > can connect to the G3 server, and I was able to connect with no problem.
> > Unfortunately, they are required to block UDP 1434 so I have to deal with
> > that.
> >
> > Does anybody have any idea that could help? Please let me know.
> >
> > Thanks
> >|||I wonder if I need to open the new static port on
> the firewall to make it work?
Yes, whatever port SQL Server is listening on must be open in order for
clients to connect over the network.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Danny Mansour" <DannyMansour@.discussions.microsoft.com> wrote in message
news:6F17AAA9-D82F-41CB-B1DA-2AD431FC86AA@.microsoft.com...
> Thanks a bunch for the reply.
> I followed the article you sent me and configured a static port and I
> configured the client machine to point to the port. Somehow this still
> didn't work for me. The article mentions to set a static Port before
> configuring the firewall. I wonder if I need to open the new static port
> on
> the firewall to make it work?
> Please let me know what you think.
> Thanks,
> Danny
> "John Bell" wrote:
>> Hi Danny
>> A named instance will use a dynamic port by default and UDP 1434 is used
>> to
>> transmit the name of the instance name back to the client. You will need
>> to
>> make the port fixed on the server and the corresponding port configured
>> on
>> the client.
>> Check out http://support.microsoft.com/kb/287932.
>> John
>>
>> "Danny Mansour" wrote:
>> > I have two instances of SQL 2000 on one of our servers. I can connect
>> > to the
>> > first instance that has the machine name and port 1434 from anywhere I
>> > want.
>> > But when I try to connect to the second instance on the same machine
>> > with
>> > then name of machinename\G3 from a server on a second network where UDP
>> > 1434
>> > port is blocked by the firewall, the connection fail and the second
>> > instance
>> > is not available. I followed this article on Microsoft knowledge base
>> > http://support.microsoft.com/kb/265808/ where I created an alias for
>> > the G3
>> > server on the client machine using the IP address and the assigned port
>> > number, but that didn't help and I still could not connect to the
>> > server. I
>> > basically followed those steps:
>> >
>> > 2. Configure the server alias on the client computer.
>> >
>> > SQL Server 2000a. Start the Client Network Utility.
>> > b. On the General tab, verify that TCP/IP appears in the list under
>> > Enabled
>> > protocols by order.
>> > c. Click the Alias tab, and then click Add.
>> > d. Under Network libraries, select TCP/IP.
>> > e. In the Server name text box, type the IP address of the server that
>> > is
>> > running SQL Server 2005.
>> >
>> > Note The IP address that you type here is the one that is configured to
>> > use
>> > the TCP/IP port number.
>> > f. Click to clear the Dynamically determine port check box, and then
>> > type
>> > the port number of the instance of SQL Server 2005 in the Port number
>> > text
>> > box.
>> > g. Type a name in the Server alias text box, and then click OK.
>> >
>> >
>> > I asked the IT department to unblock the UDP 1434 temporarily to see if
>> > I
>> > can connect to the G3 server, and I was able to connect with no
>> > problem.
>> > Unfortunately, they are required to block UDP 1434 so I have to deal
>> > with
>> > that.
>> >
>> > Does anybody have any idea that could help? Please let me know.
>> >
>> > Thanks
>> >

Monday, 19 March 2012

Connect to a 2nd instance of SQL server while UDP 1434 is blocked

Sunday, 12 February 2012

configuring Active/Active...

You would need to install multiple instances of SQL Server
to do what you want. So Ins1 would own DB1, and Ins2 DB2
and DB3. (Or Two instances ... whatever you want). The
way failover clustering works is that upon a failure, the
whole SQL instance will fail over to another node, so if
you had a failure on Ins1, it would go to Node 2 (or 3 or
whatever) automatically.
So I'm not sure what it is you're asking since you get
that functionality.
Your apps would connect to the SQL virutal server/instance
that has the DBs. On a cluster, a SQL virtual server is
an instance.
Heelo AA,
Here are some links that you may find useful learning about SQL Server 20000 Failover Clustering
Microsoft Whitepaper - SQL Server 2000 Failover Clustering
http://www.microsoft.com/technet/pro.../failclus.mspx
Microsoft SQL Server 2000 High Availability Series
http://www.microsoft.com/technet/pro...y/sqlhalp.mspx
Introduction to Microsoft SQL Server 2000 Clustering
http://support.microsoft.com/default...lurb051001.asp
Microsoft SQL Server 2000 Virtual Server: Things You Should Know
http://support.microsoft.com/default...lurb032602.asp
Microsoft SQL Server 2000 Virtual Server Basic Setup, Maintenance, and Service Pack http://support.microsoft.com/default...lurb061002.asp
Microsoft SQL Server 2000 Failover Clustering Disaster Recovery Procedures
http://support.microsoft.com/default...lurb101802.asp
Microsoft SQL Server 2000 High Availability (MS Press)
ISBN 0-7356-1920-4
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Friday, 10 February 2012

Configure TCP/IP port during unattended instance installation

Due to security reason, I need to restrict the port number that SQL Express instances listen on. I know I can do it through the SQL Server Configuration Manger, but I like to do it programmatically. If there a way to specify the port number in conjunction with the

“SECURITYMODE=SQL” name-value pair during the instance installation with the “setup.exe”?

Thanks,

Peter

The folks in the Setup forum will have an answer for this one.

Mike

|||

Yea I got the same problem. Im doin a silent SQL installation and i set DISABLENETWORKPROTOCOLS=2 so TCP\IP is enabled after installation.
When I open up the config managed TCP\IP is enabled but IP1 and IP2 are both disabled. I need a way to enable these and change the port numbers programatically so the user will not have to do this themselves.

Configure TCP/IP port during unattended instance installation

“SECURITYMODE=SQL” name-value pair during the instance installation with the “setup.exe”?

Thanks,

Peter

The folks in the Setup forum will have an answer for this one.

Mike

|||

Thursday, 22 March 2012

connect to named instances

connect to named instances

connect to named instances

Tuesday, 20 March 2012

Connect to a 2nd instance of SQL server while UDP 1434 is blocked

Monday, 19 March 2012

Connect to a 2nd instance of SQL server while UDP 1434 is blocked

Sunday, 12 February 2012

configuring Active/Active...

Friday, 10 February 2012

Configure TCP/IP port during unattended instance installation

Configure TCP/IP port during unattended instance installation

SQL Configure

Blog Archive

About Me