SQL Configure: named

Showing posts with label named. 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

Connect to named instance on different domain

I am having trouble connecting to a named instance on a different domain. For example, I am in the domain mydomain.com, the machine is in ops.mydomain.com.

In Query Analyzer I have tried to connect (without success) to:

machinename\instance

machinename

i.p. address

i.p. address\instance

Within the same domain I can successfully connect to

machinename\instance

Any ideas? Also, this machine is part of a cluster...not sure if that matters. I have full permissions to connect to this machine.

ThanksNever mind...the instance was setup to a port different than 1433. We changed it to 1433, rebooted the server, and now I can connect.

One issue though: I can only connect by specifying the i.p. address, i.e.

255.255.255.0\instancename

Using the server name or setting up an alias doesn't seem to work.

Originally posted by banderson
I am having trouble connecting to a named instance on a different domain. For example, I am in the domain mydomain.com, the machine is in ops.mydomain.com.

In Query Analyzer I have tried to connect (without success) to:

machinename\instance

machinename

i.p. address

i.p. address\instance

Within the same domain I can successfully connect to

machinename\instance

Any ideas? Also, this machine is part of a cluster...not sure if that matters. I have full permissions to connect to this machine.

Thanks

connect to named instance in a cluster

Windows 2003 N+1 cluster, SQL2005 enterprise + sp2
I have a N+1 cluster and installed default sql instance and one named
instance. SQL Browser service is running on the node which owns the named
instance; UDP port 1434 is opened on the firewall.
I had problem connecting to the named instance. Then I found the port number
in the sql error log which the named instance listens on. Opening this port
in firewall solved the connection problem.
My questions:
1. What's the best/secure way to connect to the named instance in a cluster?
Why does it not work with UDP opened and SQL Browser running before the
specific port is opened?
2. Will the port number change when the sql instance restarts? If yes, how
to assign a static port number?
Thanks.
Claudia
The short answer is that the behavior you observed is perfectly normal.
SQL will decide on a port when the service restarts unless it is a default
instance or you have locked SQL to a specific port number.
You can use the SQL Server Configuration Tool to "lock" the instance to a
specific port. This is done whenever a client needs a static port to
connect to.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:17766F0D-BF9B-4DA1-9D1A-126B57E422B4@.microsoft.com...
> Windows 2003 N+1 cluster, SQL2005 enterprise + sp2
> I have a N+1 cluster and installed default sql instance and one named
> instance. SQL Browser service is running on the node which owns the named
> instance; UDP port 1434 is opened on the firewall.
> I had problem connecting to the named instance. Then I found the port
> number
> in the sql error log which the named instance listens on. Opening this
> port
> in firewall solved the connection problem.
> My questions:
> 1. What's the best/secure way to connect to the named instance in a
> cluster?
> Why does it not work with UDP opened and SQL Browser running before the
> specific port is opened?
> 2. Will the port number change when the sql instance restarts? If yes, how
> to assign a static port number?
> Thanks.
> Claudia

Monday, 19 March 2012

Connect SQL Server 2005 through SQL-DMO

Hi,
I have installed in one computer a named instance of SQL Server 2005
Developer Edition. In that computer there is also a SQL Server 2000 instance,
which is the default. For SQL Server 2005 I have allowed local and remote
connections through TCP/IP. The SQL Browser service is active too. During the
installation I also installed the backward compatibility tools. The operating
system of the server is Windows 2000 Professional.
I am trying to execute an VB.NET app that uses SQL-DMO in order to create a
database and several logins and users in the SQL Server 2005 instance. When I
execute it on the server, the app works perfectly, but the problems appear
when I try the same from another computer. The client computer is another
Windows 2000 Professional, but just with the SQL Server 2000 client tools
installed, no SQL Server 2005 client tools.
I think that there may be missing some extra steps to configure the server
in order to allow remote connections throug SQL-DMO.
Any suggestions would be greatly appreciated.
Jose Antonio.
Jose Antonio wrote:
> Hi,
> I have installed in one computer a named instance of SQL Server 2005
> Developer Edition. In that computer there is also a SQL Server 2000
> instance, which is the default. For SQL Server 2005 I have allowed
> local and remote connections through TCP/IP. The SQL Browser service
> is active too. During the installation I also installed the backward
> compatibility tools. The operating system of the server is Windows
> 2000 Professional.
> I am trying to execute an VB.NET app that uses SQL-DMO in order to
> create a database and several logins and users in the SQL Server 2005
> instance. When I execute it on the server, the app works perfectly,
> but the problems appear when I try the same from another computer.
> The client computer is another Windows 2000 Professional, but just
> with the SQL Server 2000 client tools installed, no SQL Server 2005
> client tools.
> I think that there may be missing some extra steps to configure the
> server in order to allow remote connections throug SQL-DMO.
> Any suggestions would be greatly appreciated.
> Jose Antonio.
I believe that SQL 2005 comes with DMO for backward compatibility only
and that in order to use DMO on a SQL 2005 serve, you are going to need
either the DMO distributable files from SQL 2005 or have the SQL 2005
client tools installed.
David Gugick
Quest Software
www.quest.com
|||Hi David,
Yes, installing the backward compatibility package on the client computer
solved the problem.
Thanks.
Jose Antonio.
"David Gugick" wrote:

> Jose Antonio wrote:
> I believe that SQL 2005 comes with DMO for backward compatibility only
> and that in order to use DMO on a SQL 2005 serve, you are going to need
> either the DMO distributable files from SQL 2005 or have the SQL 2005
> client tools installed.
> --
> David Gugick
> Quest Software
> www.quest.com
>

Thursday, 8 March 2012

Connect a report to an application database

I added a database named appdata to the app_data folder to hold temporary application data. I want to show this data in a report. I created a dataset and an object to select the data. I then created an report.fdlc and a report.aspx with a ReportViewer on it pointing to the dataset object. If I right click on the report.aspx and View in Browser, it work perfectly. But when I upload it to the server I get and browse to it I get (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified). The server does allow remote connections. So I am thinking it's my connection string of the fact that the server doesn't have Sql Server Express.?

Here's my connection string. What am I missing here?

addname="appdataConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\appdata.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient" />

Set the Data Source attribute to the full SQL Instance name like 'machinename\SQLEXPRESS' and try again.

Thursday, 22 March 2012

connect to named instances

connect to named instances

connect to named instances

Connect to named instance on different domain

connect to named instance in a cluster

Monday, 19 March 2012

Connect SQL Server 2005 through SQL-DMO

Connect SQL Server 2005 through SQL-DMO

Thursday, 8 March 2012

Connect a report to an application database

SQL Configure

Blog Archive

About Me