SQL Configure: port

Showing posts with label port. 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 DB using another TCP port from SQL management Studio

Hi,
The DBA set database on server to a high TCP port number.
How can I connect to it from Microsoft SQL2005 management Studio.
I can't find the option to set TCP port number in there.
Thanks!You can use tcp:servername,port in the connection dialog although if the SQL
Browser is running you should still be able to use the instance name. As an
alternative you can set up a client alias using SQL Server Configuration
Manager>SQL Native Client Configuration>Aliases
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
<walanta> wrote in message
news:loure2pjt4o6rvq7t5r3rvh4mln76i3pan@.
4ax.com...
> Hi,
> The DBA set database on server to a high TCP port number.
> How can I connect to it from Microsoft SQL2005 management Studio.
> I can't find the option to set TCP port number in there.
> Thanks!|||Where is "connection dialog" ?
I'm using SQL server management studio-> object explore-> connect->
database engine -> ...
I see "servername:", "authentication:", "connect to database:",
"network protocol"...
No place I can put "tcp:192.168.1.1,8800" or somthing like that.
On Thu, 24 Aug 2006 22:08:36 +0100, "Jasper Smith"
<jasper_smith9@.hotmail.com> wrote:

>You can use tcp:servername,port in the connection dialog although if the SQ
L
>Browser is running you should still be able to use the instance name. As an
>alternative you can set up a client alias using SQL Server Configuration
>Manager>SQL Native Client Configuration>Aliases|||Hi,
Go to SQL Server program groups nad select "SQL Server Configuration
Manager"
Thanks
Hari
SQL Server MVP
<walanta> wrote in message
news:ndcse2lg3v26uv0s31sh8sob6rl84l9tq4@.
4ax.com...[vbcol=seagreen]
> Where is "connection dialog" ?
> I'm using SQL server management studio-> object explore-> connect->
> database engine -> ...
> I see "servername:", "authentication:", "connect to database:",
> "network protocol"...
> No place I can put "tcp:192.168.1.1,8800" or somthing like that.
>
>
> On Thu, 24 Aug 2006 22:08:36 +0100, "Jasper Smith"
> <jasper_smith9@.hotmail.com> wrote:
>|||It's in the Server name box i.e. if your server was called FOO and on port
55551 you would use
tcp:FOO,55551
in the Server name box
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
<walanta> wrote in message
news:ndcse2lg3v26uv0s31sh8sob6rl84l9tq4@.
4ax.com...[vbcol=seagreen]
> Where is "connection dialog" ?
> I'm using SQL server management studio-> object explore-> connect->
> database engine -> ...
> I see "servername:", "authentication:", "connect to database:",
> "network protocol"...
> No place I can put "tcp:192.168.1.1,8800" or somthing like that.
>
>
> On Thu, 24 Aug 2006 22:08:36 +0100, "Jasper Smith"
> <jasper_smith9@.hotmail.com> wrote:
>|||If SQL Browser is started you should be redirected automatically. For
security reasons (and the fact that this is not enabled in the setup
process by default) the SQL Browser service can not be running. The
port can be specified manually by putting it after the instancename you
want to connect to:
For default instances: Servername\InstanceName,Portnumber
For named instances: Servername,Portnumber
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

connect to a specific port on sql server/endpoint

Public has grant permissions to the following:
CustomConnection1
TSQL Default VIA
TSQL Local Machine
TSQL Named Pipes
So I'm guessing adba is able to connect usng one of the other protocols?
"Tibor Karaszi" wrote:

> That is not what I'm seeing. Below is what I did, and result of my actions. I'm on sp2, btw:
> CREATE LOGIN adba WITH PASSWORD = 'pwd'
> CREATE ENDPOINT [CustomConnection1]
> STATE = STARTED
> AS TCP
> (LISTENER_PORT = 1444, LISTENER_IP =ALL)
> FOR TSQL() ;
> GRANT CONNECT ON ENDPOINT::[CustomConnection1] to [public]
> GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
> -- Added port 1444 in Network Server Utility, IPAll, TCP Port: 1433,1444
> -- Restarted SQL Server
> REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
> --Login adba able to login on port 1444
> --Login adba not able to login with only server name specified
> --Login adba not able to login on port 1433
> --Windows login able to login on port 1433
> --Windows login able to login with only server name specified
> --(my Windows account is a login member of syadmin)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "adba" <adba@.discussions.microsoft.com> wrote in message
> news:9AF19309-A371-4983-B788-95E67789552C@.microsoft.com...
>
|||> So I'm guessing adba is able to connect usng one of the other protocols?
That seems to be the case. You can check what netlib a connection is using though the catalog views.
And you can disable netlibs using Configuration Manager. Note that the VIA is disabled by default
and Local Machine is only for local access, so the only one you would need to disable is Named
Pipes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"adba" <adba@.discussions.microsoft.com> wrote in message
news:DF4D5051-2D13-4083-9A82-BCC4A597E071@.microsoft.com...[vbcol=seagreen]
> ok i tried your steps like u mentioned them & all of them worked except for
> "Login adba not able to login with only server name specified". The login
> adba is still able to connect with the servername

> Public has grant permissions to the following:
> CustomConnection1
> TSQL Default VIA
> TSQL Local Machine
> TSQL Named Pipes
> So I'm guessing adba is able to connect usng one of the other protocols?
>
> "Tibor Karaszi" wrote:
|||That was it Tibor. Once I disabled the other protocols it couldnt connect.
Thanks so much for your help.
"Tibor Karaszi" wrote:

> That seems to be the case. You can check what netlib a connection is using though the catalog views.
> And you can disable netlibs using Configuration Manager. Note that the VIA is disabled by default
> and Local Machine is only for local access, so the only one you would need to disable is Named
> Pipes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "adba" <adba@.discussions.microsoft.com> wrote in message
> news:DF4D5051-2D13-4083-9A82-BCC4A597E071@.microsoft.com...
>

connect to a specific port on sql server/endpoint

Public has grant permissions to the following:
CustomConnection1
TSQL Default VIA
TSQL Local Machine
TSQL Named Pipes
So I'm guessing adba is able to connect usng one of the other protocols'
"Tibor Karaszi" wrote:

> That is not what I'm seeing. Below is what I did, and result of my actions
. I'm on sp2, btw:
> CREATE LOGIN adba WITH PASSWORD = 'pwd'
> CREATE ENDPOINT [CustomConnection1]
> STATE = STARTED
> AS TCP
> (LISTENER_PORT = 1444, LISTENER_IP =ALL)
> FOR TSQL() ;
> GRANT CONNECT ON ENDPOINT::[CustomConnection1] to [public]
> GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
> -- Added port 1444 in Network Server Utility, IPAll, TCP Port: 1433,1444
> -- Restarted SQL Server
> REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
> --Login adba able to login on port 1444
> --Login adba not able to login with only server name specified
> --Login adba not able to login on port 1433
> --Windows login able to login on port 1433
> --Windows login able to login with only server name specified
> --(my Windows account is a login member of syadmin)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "adba" <adba@.discussions.microsoft.com> wrote in message
> news:9AF19309-A371-4983-B788-95E67789552C@.microsoft.com...
>|||> So I'm guessing adba is able to connect usng one of the other protocols'
That seems to be the case. You can check what netlib a connection is using t
hough the catalog views.
And you can disable netlibs using Configuration Manager. Note that the VIA i
s disabled by default
and Local Machine is only for local access, so the only one you would need t
o disable is Named
Pipes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"adba" <adba@.discussions.microsoft.com> wrote in message
news:DF4D5051-2D13-4083-9A82-BCC4A597E071@.microsoft.com...[vbcol=seagreen]
> ok i tried your steps like u mentioned them & all of them worked except fo
r
> "Login adba not able to login with only server name specified". The login
> adba is still able to connect with the servername

> Public has grant permissions to the following:
> CustomConnection1
> TSQL Default VIA
> TSQL Local Machine
> TSQL Named Pipes
> So I'm guessing adba is able to connect usng one of the other protocols'
>
> "Tibor Karaszi" wrote:
>|||That was it Tibor. Once I disabled the other protocols it couldnt connect.
Thanks so much for your help.
"Tibor Karaszi" wrote:

> That seems to be the case. You can check what netlib a connection is using
though the catalog views.
> And you can disable netlibs using Configuration Manager. Note that the VIA
is disabled by default
> and Local Machine is only for local access, so the only one you would need
to disable is Named
> Pipes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "adba" <adba@.discussions.microsoft.com> wrote in message
> news:DF4D5051-2D13-4083-9A82-BCC4A597E071@.microsoft.com...
>sql sql

connect to a specific port on sql server/endpoint

i have created a new tcp endpoint using the following:
CREATE ENDPOINT [CustomConnection1]
STATE = STARTED
AS TCP
(LISTENER_PORT = 1444, LISTENER_IP =ALL)
FOR TSQL() ;
And I got warning about permissions being revoked for the default tcp
endpoint, but I was okay with that since I really wanted all users to go
through this new endpoint I am creating.
I then granted connect pemissions to a sql login (called test) to connect to
this endpoint.
I added the tcp port 1444 to the IPALL TCP Port list & restarted SQL.
I tried connecting to the sql server with the test login & it gave me a
login failed error 18456.
i then granted connect permissions to public to the default tcp port & tried
conecting with the test user & it connected successfully. So obviously it was
connecting with the default tcp port & not the new port i defined as the
endpoint. to be sure, i stopped the new endpoint & connected with the test
login again & it still worked.
So my question is - what am i doing wrong? i dont connections to go through
the default tcp port , i want them to go through the port i define in the
endpoint.
also, how can i connect to a specific port through management studio?
just fyi - i basically followed the instructions on this link:
http://msdn2.microsoft.com/en-us/library/ms189310.aspx
thanks in advance!Did you specify this new port in the connection string/server name?
It seems you just want to change the port, and for that you don't need to create a new endpoint. You
would just change the port in Server Configuration program.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"adba" <adba@.discussions.microsoft.com> wrote in message
news:04055949-5892-425E-8593-541612857169@.microsoft.com...
>i have created a new tcp endpoint using the following:
> CREATE ENDPOINT [CustomConnection1]
> STATE = STARTED
> AS TCP
> (LISTENER_PORT = 1444, LISTENER_IP =ALL)
> FOR TSQL() ;
> And I got warning about permissions being revoked for the default tcp
> endpoint, but I was okay with that since I really wanted all users to go
> through this new endpoint I am creating.
> I then granted connect pemissions to a sql login (called test) to connect to
> this endpoint.
> I added the tcp port 1444 to the IPALL TCP Port list & restarted SQL.
> I tried connecting to the sql server with the test login & it gave me a
> login failed error 18456.
> i then granted connect permissions to public to the default tcp port & tried
> conecting with the test user & it connected successfully. So obviously it was
> connecting with the default tcp port & not the new port i defined as the
> endpoint. to be sure, i stopped the new endpoint & connected with the test
> login again & it still worked.
> So my question is - what am i doing wrong? i dont connections to go through
> the default tcp port , i want them to go through the port i define in the
> endpoint.
> also, how can i connect to a specific port through management studio?
> just fyi - i basically followed the instructions on this link:
> http://msdn2.microsoft.com/en-us/library/ms189310.aspx
> thanks in advance!
>|||No, I havenâ't specified this new port in my servername â' how do I do that in
management studio? Lets assume my server name is sqltest & the new port
number is 1443 & the endpoint is called custom1. should the servername be
sqltest:1443 ?
What I want to do is create different tcp endpoints for different
applications. That way the default port 1433 would be used only by admins
internally & other users outside the firewall will connect through another
port (new endpoint) than 1433. That way if security is compromised with one
application, I donâ't have to shut the entire server down, I should be able to
shut down that specific port â' does this idea make sense' Iâ'm not having
much luck implementing itâ?¦.
"Tibor Karaszi" wrote:
> Did you specify this new port in the connection string/server name?
> It seems you just want to change the port, and for that you don't need to create a new endpoint. You
> would just change the port in Server Configuration program.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "adba" <adba@.discussions.microsoft.com> wrote in message
> news:04055949-5892-425E-8593-541612857169@.microsoft.com...
> >i have created a new tcp endpoint using the following:
> > CREATE ENDPOINT [CustomConnection1]
> > STATE = STARTED
> > AS TCP
> > (LISTENER_PORT = 1444, LISTENER_IP =ALL)
> > FOR TSQL() ;
> > And I got warning about permissions being revoked for the default tcp
> > endpoint, but I was okay with that since I really wanted all users to go
> > through this new endpoint I am creating.
> > I then granted connect pemissions to a sql login (called test) to connect to
> > this endpoint.
> > I added the tcp port 1444 to the IPALL TCP Port list & restarted SQL.
> > I tried connecting to the sql server with the test login & it gave me a
> > login failed error 18456.
> > i then granted connect permissions to public to the default tcp port & tried
> > conecting with the test user & it connected successfully. So obviously it was
> > connecting with the default tcp port & not the new port i defined as the
> > endpoint. to be sure, i stopped the new endpoint & connected with the test
> > login again & it still worked.
> > So my question is - what am i doing wrong? i dont connections to go through
> > the default tcp port , i want them to go through the port i define in the
> > endpoint.
> > also, how can i connect to a specific port through management studio?
> >
> > just fyi - i basically followed the instructions on this link:
> > http://msdn2.microsoft.com/en-us/library/ms189310.aspx
> >
> > thanks in advance!
> >
> >
>|||I understand about your usage scenario. Yes, that makes sense to me.
To connect to so something else than the "default" port, the client uses below syntax for the server
name. Note that you don't specify an instance name if it is a named instance:
SERVERNAME,PORT#
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"adba" <adba@.discussions.microsoft.com> wrote in message
news:C84D70CE-AEC4-48F3-B56E-86C264C85858@.microsoft.com...
> No, I havenâ't specified this new port in my servername â' how do I do that in
> management studio? Lets assume my server name is sqltest & the new port
> number is 1443 & the endpoint is called custom1. should the servername be
> sqltest:1443 ?
> What I want to do is create different tcp endpoints for different
> applications. That way the default port 1433 would be used only by admins
> internally & other users outside the firewall will connect through another
> port (new endpoint) than 1433. That way if security is compromised with one
> application, I donâ't have to shut the entire server down, I should be able to
> shut down that specific port â' does this idea make sense' Iâ'm not having
> much luck implementing itâ?¦.
>
> "Tibor Karaszi" wrote:
>> Did you specify this new port in the connection string/server name?
>> It seems you just want to change the port, and for that you don't need to create a new endpoint.
>> You
>> would just change the port in Server Configuration program.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "adba" <adba@.discussions.microsoft.com> wrote in message
>> news:04055949-5892-425E-8593-541612857169@.microsoft.com...
>> >i have created a new tcp endpoint using the following:
>> > CREATE ENDPOINT [CustomConnection1]
>> > STATE = STARTED
>> > AS TCP
>> > (LISTENER_PORT = 1444, LISTENER_IP =ALL)
>> > FOR TSQL() ;
>> > And I got warning about permissions being revoked for the default tcp
>> > endpoint, but I was okay with that since I really wanted all users to go
>> > through this new endpoint I am creating.
>> > I then granted connect pemissions to a sql login (called test) to connect to
>> > this endpoint.
>> > I added the tcp port 1444 to the IPALL TCP Port list & restarted SQL.
>> > I tried connecting to the sql server with the test login & it gave me a
>> > login failed error 18456.
>> > i then granted connect permissions to public to the default tcp port & tried
>> > conecting with the test user & it connected successfully. So obviously it was
>> > connecting with the default tcp port & not the new port i defined as the
>> > endpoint. to be sure, i stopped the new endpoint & connected with the test
>> > login again & it still worked.
>> > So my question is - what am i doing wrong? i dont connections to go through
>> > the default tcp port , i want them to go through the port i define in the
>> > endpoint.
>> > also, how can i connect to a specific port through management studio?
>> >
>> > just fyi - i basically followed the instructions on this link:
>> > http://msdn2.microsoft.com/en-us/library/ms189310.aspx
>> >
>> > thanks in advance!
>> >
>> >
>>|||thanks. okay now i wa able to connect to a specific port through management
studio.
but my problem is that to be able to connect to that port, i have to have
connect permissions granted to public on the default tcp port. like when you
create a new endpointm it takes away the permissions on the deafult port. so
i i leave it like that, i cannot connect to the new endpoint i created. if i
grant permissions to public, it kind of defeats the purpose of creating new
endpoints for me.
so i guess my question is can we connect to a new endpoint without granting
connect permissions to public on the default tcp port?
"Tibor Karaszi" wrote:
> I understand about your usage scenario. Yes, that makes sense to me.
> To connect to so something else than the "default" port, the client uses below syntax for the server
> name. Note that you don't specify an instance name if it is a named instance:
> SERVERNAME,PORT#
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "adba" <adba@.discussions.microsoft.com> wrote in message
> news:C84D70CE-AEC4-48F3-B56E-86C264C85858@.microsoft.com...
> > No, I havenâ't specified this new port in my servername â' how do I do that in
> > management studio? Lets assume my server name is sqltest & the new port
> > number is 1443 & the endpoint is called custom1. should the servername be
> > sqltest:1443 ?
> >
> > What I want to do is create different tcp endpoints for different
> > applications. That way the default port 1433 would be used only by admins
> > internally & other users outside the firewall will connect through another
> > port (new endpoint) than 1433. That way if security is compromised with one
> > application, I donâ't have to shut the entire server down, I should be able to
> > shut down that specific port â' does this idea make sense' Iâ'm not having
> > much luck implementing itâ?¦.
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Did you specify this new port in the connection string/server name?
> >>
> >> It seems you just want to change the port, and for that you don't need to create a new endpoint.
> >> You
> >> would just change the port in Server Configuration program.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "adba" <adba@.discussions.microsoft.com> wrote in message
> >> news:04055949-5892-425E-8593-541612857169@.microsoft.com...
> >> >i have created a new tcp endpoint using the following:
> >> > CREATE ENDPOINT [CustomConnection1]
> >> > STATE = STARTED
> >> > AS TCP
> >> > (LISTENER_PORT = 1444, LISTENER_IP =ALL)
> >> > FOR TSQL() ;
> >> > And I got warning about permissions being revoked for the default tcp
> >> > endpoint, but I was okay with that since I really wanted all users to go
> >> > through this new endpoint I am creating.
> >> > I then granted connect pemissions to a sql login (called test) to connect to
> >> > this endpoint.
> >> > I added the tcp port 1444 to the IPALL TCP Port list & restarted SQL.
> >> > I tried connecting to the sql server with the test login & it gave me a
> >> > login failed error 18456.
> >> > i then granted connect permissions to public to the default tcp port & tried
> >> > conecting with the test user & it connected successfully. So obviously it was
> >> > connecting with the default tcp port & not the new port i defined as the
> >> > endpoint. to be sure, i stopped the new endpoint & connected with the test
> >> > login again & it still worked.
> >> > So my question is - what am i doing wrong? i dont connections to go through
> >> > the default tcp port , i want them to go through the port i define in the
> >> > endpoint.
> >> > also, how can i connect to a specific port through management studio?
> >> >
> >> > just fyi - i basically followed the instructions on this link:
> >> > http://msdn2.microsoft.com/en-us/library/ms189310.aspx
> >> >
> >> > thanks in advance!
> >> >
> >> >
> >>
> >>
>|||> but my problem is that to be able to connect to that port, i have to have
> connect permissions granted to public on the default tcp port.
That is not what I'm seeing. Below is what I did, and result of my actions. I'm on sp2, btw:
CREATE LOGIN adba WITH PASSWORD = 'pwd'
CREATE ENDPOINT [CustomConnection1]
STATE = STARTED
AS TCP
(LISTENER_PORT = 1444, LISTENER_IP =ALL)
FOR TSQL() ;
GRANT CONNECT ON ENDPOINT::[CustomConnection1] to [public]
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
-- Added port 1444 in Network Server Utility, IPAll, TCP Port: 1433,1444
-- Restarted SQL Server
REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
--Login adba able to login on port 1444
--Login adba not able to login with only server name specified
--Login adba not able to login on port 1433
--Windows login able to login on port 1433
--Windows login able to login with only server name specified
--(my Windows account is a login member of syadmin)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"adba" <adba@.discussions.microsoft.com> wrote in message
news:9AF19309-A371-4983-B788-95E67789552C@.microsoft.com...
> thanks. okay now i wa able to connect to a specific port through management
> studio.
> but my problem is that to be able to connect to that port, i have to have
> connect permissions granted to public on the default tcp port. like when you
> create a new endpointm it takes away the permissions on the deafult port. so
> i i leave it like that, i cannot connect to the new endpoint i created. if i
> grant permissions to public, it kind of defeats the purpose of creating new
> endpoints for me.
> so i guess my question is can we connect to a new endpoint without granting
> connect permissions to public on the default tcp port?
> "Tibor Karaszi" wrote:
>> I understand about your usage scenario. Yes, that makes sense to me.
>> To connect to so something else than the "default" port, the client uses below syntax for the
>> server
>> name. Note that you don't specify an instance name if it is a named instance:
>> SERVERNAME,PORT#
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "adba" <adba@.discussions.microsoft.com> wrote in message
>> news:C84D70CE-AEC4-48F3-B56E-86C264C85858@.microsoft.com...
>> > No, I havenâ't specified this new port in my servername â' how do I do that in
>> > management studio? Lets assume my server name is sqltest & the new port
>> > number is 1443 & the endpoint is called custom1. should the servername be
>> > sqltest:1443 ?
>> >
>> > What I want to do is create different tcp endpoints for different
>> > applications. That way the default port 1433 would be used only by admins
>> > internally & other users outside the firewall will connect through another
>> > port (new endpoint) than 1433. That way if security is compromised with one
>> > application, I donâ't have to shut the entire server down, I should be able to
>> > shut down that specific port â' does this idea make sense' Iâ'm not having
>> > much luck implementing itâ?¦.
>> >
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Did you specify this new port in the connection string/server name?
>> >>
>> >> It seems you just want to change the port, and for that you don't need to create a new
>> >> endpoint.
>> >> You
>> >> would just change the port in Server Configuration program.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "adba" <adba@.discussions.microsoft.com> wrote in message
>> >> news:04055949-5892-425E-8593-541612857169@.microsoft.com...
>> >> >i have created a new tcp endpoint using the following:
>> >> > CREATE ENDPOINT [CustomConnection1]
>> >> > STATE = STARTED
>> >> > AS TCP
>> >> > (LISTENER_PORT = 1444, LISTENER_IP =ALL)
>> >> > FOR TSQL() ;
>> >> > And I got warning about permissions being revoked for the default tcp
>> >> > endpoint, but I was okay with that since I really wanted all users to go
>> >> > through this new endpoint I am creating.
>> >> > I then granted connect pemissions to a sql login (called test) to connect to
>> >> > this endpoint.
>> >> > I added the tcp port 1444 to the IPALL TCP Port list & restarted SQL.
>> >> > I tried connecting to the sql server with the test login & it gave me a
>> >> > login failed error 18456.
>> >> > i then granted connect permissions to public to the default tcp port & tried
>> >> > conecting with the test user & it connected successfully. So obviously it was
>> >> > connecting with the default tcp port & not the new port i defined as the
>> >> > endpoint. to be sure, i stopped the new endpoint & connected with the test
>> >> > login again & it still worked.
>> >> > So my question is - what am i doing wrong? i dont connections to go through
>> >> > the default tcp port , i want them to go through the port i define in the
>> >> > endpoint.
>> >> > also, how can i connect to a specific port through management studio?
>> >> >
>> >> > just fyi - i basically followed the instructions on this link:
>> >> > http://msdn2.microsoft.com/en-us/library/ms189310.aspx
>> >> >
>> >> > thanks in advance!
>> >> >
>> >> >
>> >>
>> >>
>>|||ok i tried your steps like u mentioned them & all of them worked except for
"Login adba not able to login with only server name specified". The login
adba is still able to connect with the servername :(
Public has grant permissions to the following:
CustomConnection1
TSQL Default VIA
TSQL Local Machine
TSQL Named Pipes
So I'm guessing adba is able to connect usng one of the other protocols'
"Tibor Karaszi" wrote:
> > but my problem is that to be able to connect to that port, i have to have
> > connect permissions granted to public on the default tcp port.
> That is not what I'm seeing. Below is what I did, and result of my actions. I'm on sp2, btw:
> CREATE LOGIN adba WITH PASSWORD = 'pwd'
> CREATE ENDPOINT [CustomConnection1]
> STATE = STARTED
> AS TCP
> (LISTENER_PORT = 1444, LISTENER_IP =ALL)
> FOR TSQL() ;
> GRANT CONNECT ON ENDPOINT::[CustomConnection1] to [public]
> GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
> -- Added port 1444 in Network Server Utility, IPAll, TCP Port: 1433,1444
> -- Restarted SQL Server
> REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
> --Login adba able to login on port 1444
> --Login adba not able to login with only server name specified
> --Login adba not able to login on port 1433
> --Windows login able to login on port 1433
> --Windows login able to login with only server name specified
> --(my Windows account is a login member of syadmin)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "adba" <adba@.discussions.microsoft.com> wrote in message
> news:9AF19309-A371-4983-B788-95E67789552C@.microsoft.com...
> > thanks. okay now i wa able to connect to a specific port through management
> > studio.
> > but my problem is that to be able to connect to that port, i have to have
> > connect permissions granted to public on the default tcp port. like when you
> > create a new endpointm it takes away the permissions on the deafult port. so
> > i i leave it like that, i cannot connect to the new endpoint i created. if i
> > grant permissions to public, it kind of defeats the purpose of creating new
> > endpoints for me.
> > so i guess my question is can we connect to a new endpoint without granting
> > connect permissions to public on the default tcp port?
> >
> > "Tibor Karaszi" wrote:
> >
> >> I understand about your usage scenario. Yes, that makes sense to me.
> >> To connect to so something else than the "default" port, the client uses below syntax for the
> >> server
> >> name. Note that you don't specify an instance name if it is a named instance:
> >> SERVERNAME,PORT#
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "adba" <adba@.discussions.microsoft.com> wrote in message
> >> news:C84D70CE-AEC4-48F3-B56E-86C264C85858@.microsoft.com...
> >> > No, I havenâ't specified this new port in my servername â' how do I do that in
> >> > management studio? Lets assume my server name is sqltest & the new port
> >> > number is 1443 & the endpoint is called custom1. should the servername be
> >> > sqltest:1443 ?
> >> >
> >> > What I want to do is create different tcp endpoints for different
> >> > applications. That way the default port 1433 would be used only by admins
> >> > internally & other users outside the firewall will connect through another
> >> > port (new endpoint) than 1433. That way if security is compromised with one
> >> > application, I donâ't have to shut the entire server down, I should be able to
> >> > shut down that specific port â' does this idea make sense' Iâ'm not having
> >> > much luck implementing itâ?¦.
> >> >
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Did you specify this new port in the connection string/server name?
> >> >>
> >> >> It seems you just want to change the port, and for that you don't need to create a new
> >> >> endpoint.
> >> >> You
> >> >> would just change the port in Server Configuration program.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "adba" <adba@.discussions.microsoft.com> wrote in message
> >> >> news:04055949-5892-425E-8593-541612857169@.microsoft.com...
> >> >> >i have created a new tcp endpoint using the following:
> >> >> > CREATE ENDPOINT [CustomConnection1]
> >> >> > STATE = STARTED
> >> >> > AS TCP
> >> >> > (LISTENER_PORT = 1444, LISTENER_IP =ALL)
> >> >> > FOR TSQL() ;
> >> >> > And I got warning about permissions being revoked for the default tcp
> >> >> > endpoint, but I was okay with that since I really wanted all users to go
> >> >> > through this new endpoint I am creating.
> >> >> > I then granted connect pemissions to a sql login (called test) to connect to
> >> >> > this endpoint.
> >> >> > I added the tcp port 1444 to the IPALL TCP Port list & restarted SQL.
> >> >> > I tried connecting to the sql server with the test login & it gave me a
> >> >> > login failed error 18456.
> >> >> > i then granted connect permissions to public to the default tcp port & tried
> >> >> > conecting with the test user & it connected successfully. So obviously it was
> >> >> > connecting with the default tcp port & not the new port i defined as the
> >> >> > endpoint. to be sure, i stopped the new endpoint & connected with the test
> >> >> > login again & it still worked.
> >> >> > So my question is - what am i doing wrong? i dont connections to go through
> >> >> > the default tcp port , i want them to go through the port i define in the
> >> >> > endpoint.
> >> >> > also, how can i connect to a specific port through management studio?
> >> >> >
> >> >> > just fyi - i basically followed the instructions on this link:
> >> >> > http://msdn2.microsoft.com/en-us/library/ms189310.aspx
> >> >> >
> >> >> > thanks in advance!
> >> >> >
> >> >> >
> >> >>
> >> >>
> >>
> >>
>|||> So I'm guessing adba is able to connect usng one of the other protocols'
That seems to be the case. You can check what netlib a connection is using though the catalog views.
And you can disable netlibs using Configuration Manager. Note that the VIA is disabled by default
and Local Machine is only for local access, so the only one you would need to disable is Named
Pipes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"adba" <adba@.discussions.microsoft.com> wrote in message
news:DF4D5051-2D13-4083-9A82-BCC4A597E071@.microsoft.com...
> ok i tried your steps like u mentioned them & all of them worked except for
> "Login adba not able to login with only server name specified". The login
> adba is still able to connect with the servername :(
> Public has grant permissions to the following:
> CustomConnection1
> TSQL Default VIA
> TSQL Local Machine
> TSQL Named Pipes
> So I'm guessing adba is able to connect usng one of the other protocols'
>
> "Tibor Karaszi" wrote:
>> > but my problem is that to be able to connect to that port, i have to have
>> > connect permissions granted to public on the default tcp port.
>> That is not what I'm seeing. Below is what I did, and result of my actions. I'm on sp2, btw:
>> CREATE LOGIN adba WITH PASSWORD = 'pwd'
>> CREATE ENDPOINT [CustomConnection1]
>> STATE = STARTED
>> AS TCP
>> (LISTENER_PORT = 1444, LISTENER_IP =ALL)
>> FOR TSQL() ;
>> GRANT CONNECT ON ENDPOINT::[CustomConnection1] to [public]
>> GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
>> -- Added port 1444 in Network Server Utility, IPAll, TCP Port: 1433,1444
>> -- Restarted SQL Server
>> REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
>> --Login adba able to login on port 1444
>> --Login adba not able to login with only server name specified
>> --Login adba not able to login on port 1433
>> --Windows login able to login on port 1433
>> --Windows login able to login with only server name specified
>> --(my Windows account is a login member of syadmin)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "adba" <adba@.discussions.microsoft.com> wrote in message
>> news:9AF19309-A371-4983-B788-95E67789552C@.microsoft.com...
>> > thanks. okay now i wa able to connect to a specific port through management
>> > studio.
>> > but my problem is that to be able to connect to that port, i have to have
>> > connect permissions granted to public on the default tcp port. like when you
>> > create a new endpointm it takes away the permissions on the deafult port. so
>> > i i leave it like that, i cannot connect to the new endpoint i created. if i
>> > grant permissions to public, it kind of defeats the purpose of creating new
>> > endpoints for me.
>> > so i guess my question is can we connect to a new endpoint without granting
>> > connect permissions to public on the default tcp port?
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> I understand about your usage scenario. Yes, that makes sense to me.
>> >> To connect to so something else than the "default" port, the client uses below syntax for the
>> >> server
>> >> name. Note that you don't specify an instance name if it is a named instance:
>> >> SERVERNAME,PORT#
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "adba" <adba@.discussions.microsoft.com> wrote in message
>> >> news:C84D70CE-AEC4-48F3-B56E-86C264C85858@.microsoft.com...
>> >> > No, I havenâ't specified this new port in my servername â' how do I do that in
>> >> > management studio? Lets assume my server name is sqltest & the new port
>> >> > number is 1443 & the endpoint is called custom1. should the servername be
>> >> > sqltest:1443 ?
>> >> >
>> >> > What I want to do is create different tcp endpoints for different
>> >> > applications. That way the default port 1433 would be used only by admins
>> >> > internally & other users outside the firewall will connect through another
>> >> > port (new endpoint) than 1433. That way if security is compromised with one
>> >> > application, I donâ't have to shut the entire server down, I should be able to
>> >> > shut down that specific port â' does this idea make sense' Iâ'm not having
>> >> > much luck implementing itâ?¦.
>> >> >
>> >> >
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Did you specify this new port in the connection string/server name?
>> >> >>
>> >> >> It seems you just want to change the port, and for that you don't need to create a new
>> >> >> endpoint.
>> >> >> You
>> >> >> would just change the port in Server Configuration program.
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >>
>> >> >>
>> >> >> "adba" <adba@.discussions.microsoft.com> wrote in message
>> >> >> news:04055949-5892-425E-8593-541612857169@.microsoft.com...
>> >> >> >i have created a new tcp endpoint using the following:
>> >> >> > CREATE ENDPOINT [CustomConnection1]
>> >> >> > STATE = STARTED
>> >> >> > AS TCP
>> >> >> > (LISTENER_PORT = 1444, LISTENER_IP =ALL)
>> >> >> > FOR TSQL() ;
>> >> >> > And I got warning about permissions being revoked for the default tcp
>> >> >> > endpoint, but I was okay with that since I really wanted all users to go
>> >> >> > through this new endpoint I am creating.
>> >> >> > I then granted connect pemissions to a sql login (called test) to connect to
>> >> >> > this endpoint.
>> >> >> > I added the tcp port 1444 to the IPALL TCP Port list & restarted SQL.
>> >> >> > I tried connecting to the sql server with the test login & it gave me a
>> >> >> > login failed error 18456.
>> >> >> > i then granted connect permissions to public to the default tcp port & tried
>> >> >> > conecting with the test user & it connected successfully. So obviously it was
>> >> >> > connecting with the default tcp port & not the new port i defined as the
>> >> >> > endpoint. to be sure, i stopped the new endpoint & connected with the test
>> >> >> > login again & it still worked.
>> >> >> > So my question is - what am i doing wrong? i dont connections to go through
>> >> >> > the default tcp port , i want them to go through the port i define in the
>> >> >> > endpoint.
>> >> >> > also, how can i connect to a specific port through management studio?
>> >> >> >
>> >> >> > just fyi - i basically followed the instructions on this link:
>> >> >> > http://msdn2.microsoft.com/en-us/library/ms189310.aspx
>> >> >> >
>> >> >> > thanks in advance!
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >>
>> >>
>>|||That was it Tibor. Once I disabled the other protocols it couldnt connect.
Thanks so much for your help.
"Tibor Karaszi" wrote:
> > So I'm guessing adba is able to connect usng one of the other protocols'
> That seems to be the case. You can check what netlib a connection is using though the catalog views.
> And you can disable netlibs using Configuration Manager. Note that the VIA is disabled by default
> and Local Machine is only for local access, so the only one you would need to disable is Named
> Pipes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "adba" <adba@.discussions.microsoft.com> wrote in message
> news:DF4D5051-2D13-4083-9A82-BCC4A597E071@.microsoft.com...
> > ok i tried your steps like u mentioned them & all of them worked except for
> >
> > "Login adba not able to login with only server name specified". The login
> > adba is still able to connect with the servername :(
> >
> > Public has grant permissions to the following:
> > CustomConnection1
> > TSQL Default VIA
> > TSQL Local Machine
> > TSQL Named Pipes
> >
> > So I'm guessing adba is able to connect usng one of the other protocols'
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> > but my problem is that to be able to connect to that port, i have to have
> >> > connect permissions granted to public on the default tcp port.
> >>
> >> That is not what I'm seeing. Below is what I did, and result of my actions. I'm on sp2, btw:
> >>
> >> CREATE LOGIN adba WITH PASSWORD = 'pwd'
> >>
> >> CREATE ENDPOINT [CustomConnection1]
> >> STATE = STARTED
> >> AS TCP
> >> (LISTENER_PORT = 1444, LISTENER_IP =ALL)
> >> FOR TSQL() ;
> >>
> >> GRANT CONNECT ON ENDPOINT::[CustomConnection1] to [public]
> >>
> >> GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
> >>
> >> -- Added port 1444 in Network Server Utility, IPAll, TCP Port: 1433,1444
> >> -- Restarted SQL Server
> >>
> >> REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
> >>
> >> --Login adba able to login on port 1444
> >> --Login adba not able to login with only server name specified
> >> --Login adba not able to login on port 1433
> >> --Windows login able to login on port 1433
> >> --Windows login able to login with only server name specified
> >> --(my Windows account is a login member of syadmin)
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "adba" <adba@.discussions.microsoft.com> wrote in message
> >> news:9AF19309-A371-4983-B788-95E67789552C@.microsoft.com...
> >> > thanks. okay now i wa able to connect to a specific port through management
> >> > studio.
> >> > but my problem is that to be able to connect to that port, i have to have
> >> > connect permissions granted to public on the default tcp port. like when you
> >> > create a new endpointm it takes away the permissions on the deafult port. so
> >> > i i leave it like that, i cannot connect to the new endpoint i created. if i
> >> > grant permissions to public, it kind of defeats the purpose of creating new
> >> > endpoints for me.
> >> > so i guess my question is can we connect to a new endpoint without granting
> >> > connect permissions to public on the default tcp port?
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> I understand about your usage scenario. Yes, that makes sense to me.
> >> >> To connect to so something else than the "default" port, the client uses below syntax for the
> >> >> server
> >> >> name. Note that you don't specify an instance name if it is a named instance:
> >> >> SERVERNAME,PORT#
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "adba" <adba@.discussions.microsoft.com> wrote in message
> >> >> news:C84D70CE-AEC4-48F3-B56E-86C264C85858@.microsoft.com...
> >> >> > No, I havenâ't specified this new port in my servername â' how do I do that in
> >> >> > management studio? Lets assume my server name is sqltest & the new port
> >> >> > number is 1443 & the endpoint is called custom1. should the servername be
> >> >> > sqltest:1443 ?
> >> >> >
> >> >> > What I want to do is create different tcp endpoints for different
> >> >> > applications. That way the default port 1433 would be used only by admins
> >> >> > internally & other users outside the firewall will connect through another
> >> >> > port (new endpoint) than 1433. That way if security is compromised with one
> >> >> > application, I donâ't have to shut the entire server down, I should be able to
> >> >> > shut down that specific port â' does this idea make sense' Iâ'm not having
> >> >> > much luck implementing itâ?¦.
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> Did you specify this new port in the connection string/server name?
> >> >> >>
> >> >> >> It seems you just want to change the port, and for that you don't need to create a new
> >> >> >> endpoint.
> >> >> >> You
> >> >> >> would just change the port in Server Configuration program.
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >> >>
> >> >> >>
> >> >> >> "adba" <adba@.discussions.microsoft.com> wrote in message
> >> >> >> news:04055949-5892-425E-8593-541612857169@.microsoft.com...
> >> >> >> >i have created a new tcp endpoint using the following:
> >> >> >> > CREATE ENDPOINT [CustomConnection1]
> >> >> >> > STATE = STARTED
> >> >> >> > AS TCP
> >> >> >> > (LISTENER_PORT = 1444, LISTENER_IP =ALL)
> >> >> >> > FOR TSQL() ;
> >> >> >> > And I got warning about permissions being revoked for the default tcp
> >> >> >> > endpoint, but I was okay with that since I really wanted all users to go
> >> >> >> > through this new endpoint I am creating.
> >> >> >> > I then granted connect pemissions to a sql login (called test) to connect to
> >> >> >> > this endpoint.
> >> >> >> > I added the tcp port 1444 to the IPALL TCP Port list & restarted SQL.
> >> >> >> > I tried connecting to the sql server with the test login & it gave me a
> >> >> >> > login failed error 18456.
> >> >> >> > i then granted connect permissions to public to the default tcp port & tried
> >> >> >> > conecting with the test user & it connected successfully. So obviously it was
> >> >> >> > connecting with the default tcp port & not the new port i defined as the
> >> >> >> > endpoint. to be sure, i stopped the new endpoint & connected with the test
> >> >> >> > login again & it still worked.
> >> >> >> > So my question is - what am i doing wrong? i dont connections to go through
> >> >> >> > the default tcp port , i want them to go through the port i define in the
> >> >> >> > endpoint.
> >> >> >> > also, how can i connect to a specific port through management studio?
> >> >> >> >
> >> >> >> > just fyi - i basically followed the instructions on this link:
> >> >> >> > http://msdn2.microsoft.com/en-us/library/ms189310.aspx
> >> >> >> >
> >> >> >> > thanks in advance!
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>

Thursday, 22 March 2012

connect to named instances

connect to named instances

connect to named instances

Tuesday, 20 March 2012

Connect to DB using another TCP port from SQL management Studio

connect to a specific port on sql server/endpoint

connect to a specific port on sql server/endpoint

connect to a specific port on sql server/endpoint

SQL Configure

Blog Archive

About Me