SQL Configure: tcp

Showing posts with label tcp. Show all posts

Sunday, 25 March 2012

Connect to specific TCP/IP endpoint?

Hello All,
I'd read in BOL that it is possible to connect "explicitly" to a
specific TCP/IP endpoint on the server. Does anyone know how to do
this? I can't find an example anywhere.
Thanks in advance.
-KJIn Enterprise manager, when you connect to a new server in a server group
enter the IP address of your server instead of the server's network name. If
your server is behind a firewall, you would have to implement NAT to the
actual server's Internal server's private IP address using port 1433 (the
default). Enter your EXTERNAL IP address as the server's address in the Ent
mgr in this case.
This way you can control a sql server from anywhere, but be aware that you
are opening a dangerous door. You should have a firewall that allows you to
filter the origin of the calls on port 1433 and only allow those requests
that originate from your safe Ip addresses to go through, that means that
your Ip address from which you make the calls should be a fixed IP. (costs
more)
HTH
Bob
"KJ" <n_o_s_p_a__m@.mail.com> wrote in message
news:1151340328.957952.169780@.c74g2000cwc.googlegroups.com...
> Hello All,
> I'd read in BOL that it is possible to connect "explicitly" to a
> specific TCP/IP endpoint on the server. Does anyone know how to do
> this? I can't find an example anywhere.
> Thanks in advance.
> -KJ
>|||I should have stated that I am actually looking for information on
connecting to TCP/IP endpoints (default or user-created) in SQL Server
2005.
Thanks Bob for your reply.
Bob wrote:
> In Enterprise manager, when you connect to a new server in a server group
> enter the IP address of your server instead of the server's network name.
If
> your server is behind a firewall, you would have to implement NAT to the
> actual server's Internal server's private IP address using port 1433 (the
> default). Enter your EXTERNAL IP address as the server's address in the En
t
> mgr in this case.
> This way you can control a sql server from anywhere, but be aware that you
> are opening a dangerous door. You should have a firewall that allows you t
o
> filter the origin of the calls on port 1433 and only allow those requests
> that originate from your safe Ip addresses to go through, that means that
> your Ip address from which you make the calls should be a fixed IP. (costs
> more)
> HTH
> Bob
>
> "KJ" <n_o_s_p_a__m@.mail.com> wrote in message
> news:1151340328.957952.169780@.c74g2000cwc.googlegroups.com...sql sql

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!
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>

Saturday, 25 February 2012

Confused about Configuration Manager's TCP/IP settings

I need another opinion about the SQL Server Configuration Manager's TCP/IP settings...

Recreation steps:
Start, (All) Programs, SQL Server 2005, Configuration Tools, SQL Server Configuration Manager.
Expand the "SQL Server 2005 Network Configuration"
Select the "Protocols for x" where "x" is the named-instance or "MSSQLServer" for default.
On the right-hand pane, right-click "TCP/IP" and select "Properties."
Select the "IP Addresses" tab.

Visually on the screen I have this:
- IP1
Active Yes
Enabled No
IP Address 106.xx.xxx.xx
TCP Dynamic Ports
TCP Port 1433

- IP2
Active Yes
Enabled No
IP Address 127.0.0.1 <-loopback
TCP Dynamic Ports
TCP Port 1433

-IPAll
TCP Dynamic Ports
TCP Port 1433

Questions:
1. If I want to change the listening port, I change all three at the same time?
1a. Does a value in the IPAll section override the individual IPx sections?
2. What does "enabled" mean? How can IP1 and IP2 not be enabled?
3. What does "active" mean? How can something be both active yet not enabled?
(4. Any plans to change the "NULL=False, 0=True" values for the Dynamic Ports?)

Hey Jeff,

answers to some of your questions (from Configuration Manager "help"):

Use the TCP/IP Properties (IP Addresses Tab) dialog box to configure the TCP/IP protocol options for a specific IP address. Only TCP Dynamic Ports and TCP Port can be configured for all addresses at once by selecting IP All.

Active

Indicates that the IP address is active on the computer. Not available for IPAll.

Enabled

If the Listen All property on the TCP/IP Properties (Protocol Tab) is set to No, this property indicates whether SQL Server is listening on the IP address. If the Listen All property on the TCP/IP Properties (Protocol Tab) is set to Yes, the property is disregarded. Not available for IPAll

|||

Thanks Kevin. Hey, we could use someone like you on our team!

But this is still a miscommunication issue. Follow-up was posted on the MS Feedback Center...

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=151162

Conflicting SQL Client Settings

Seems like SQL Server 2000 likes SQL Client to use TCP/IP (when doing
backups), but Microsoft Content Management Server 2002 SP1A likes Named
Pipes for MCMS Server Side Site Deployment Object export to work properly!
I am faced with a big dilemma!
See
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
I have been experiencing frequest "General network Error"s, when I use
Server side SDO to export the CMS repository (as a backup, before importing
a SDO file from another test server). This article recommends the use of
Named Pipes in preference over TCP/IP for SQL Client Network settings.
However, there has been some recent failures in SQL Server Flat file backup
(SQL SErver maintenance plan to do full backup all application databases
with verify). Invesigation reveal (quite consistently) that the flat file
backup of the MCMS 2002 SP1A database will always fail when Named Pipes to
use. As soon as SQL Client is changed to use TCP/IP, backup function as
expectd. The error that are logged when Named pipes are used as follows
*1) The error logged in the error log file is :
2004-08-21 21:25:14.07 spid60 BackupDiskFile::OpenMedia: Backup device
'd:\MSSQL\BACKUP\DBLive\DBLive_db_200408212125.BAK ' failed to open.
Operating system error = 32(error not found).
2004-08-21 21:25:14.20 backup BACKUP failed to complete the command
BACKUP DATABASE [YJBLive] TO DISK =
N'd:\MSSQL\BACKUP\DBLive\DBLive_db_200408212125.BA K' WITH INIT , NOUNLOAD
, NOSKIP , STATS = 10, NOFORMAT
*2) If I change the job to just do the backup without verify, then the job
completes, but
*2.1) Normally, the BAK file used to be 3xxMB large, but now the BAK file
which only succeeded in backing up without verifying is 1xxMB large
*3) The Job History indicates
Executed as user: WEBSERVER\SQLServer. sqlmaint.exe failed. [SQLSTATE 42000]
(Error 22029). The step failed.
The job failed. The Job was invoked by Schedule 2 (Schedule 1). The last
step to run was step 1 (Step 1).
*4) The backup file appear corrupted:
*4.1) If I look at the Maintenance Plan history, I found:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'd:\MSSQL\BACKUP\DBLive\DBLive_db_200408231407.BAK '. Device error or device
off-line. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
terminating abnormally.
*4.2) If I try to restore this (corrupted) 1xxMB BAK file to another DB, it
doesn't work. After the popup to say restore has completed, Enterprise
Manager shows the database as grey with "(Loading)" appended to the database
name (even after 5mins)
*5) The user group Everyone has full permission on d:\MSSQL\BACKUP
*6) DBCC CheckDB, DBCC CheckFileGroup, DBCC CheckIdent reveal no errors
So, I am really stuck here, could some SQL Server / MCMS expert help me out
here! If I use Named Pipes, SQL Backup is not going to work. If I use
TCP/IP, then my MCMS export/import would not work most of the time!
Hi Patrick,
1) MCMS likes all protocols if SP1 or SP1a are installed
2) creating an ALIAS in SQL tools for the different application would allow
to configure the protocols for all apps individually.
Cheers,
Stefan.
This posting is provided "AS IS" with no warranties, and confers no rights.
MCMS FAQ:
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm
MCMS Blog: http://blogs.msdn.com/stefan_gossner/category/4983.aspx
MCMS Sample Code:
http://www.gotdotnet.com/community/u...emen t+Server
MCMS Whitepapers and other docs:
http://blogs.msdn.com/stefan_gossner.../07/41859.aspx
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:esccSyRiEHA.2908@.TK2MSFTNGP10.phx.gbl...
> Seems like SQL Server 2000 likes SQL Client to use TCP/IP (when doing
> backups), but Microsoft Content Management Server 2002 SP1A likes Named
> Pipes for MCMS Server Side Site Deployment Object export to work properly!
> I am faced with a big dilemma!
> See
>
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
> I have been experiencing frequest "General network Error"s, when I use
> Server side SDO to export the CMS repository (as a backup, before
importing
> a SDO file from another test server). This article recommends the use of
> Named Pipes in preference over TCP/IP for SQL Client Network settings.
> However, there has been some recent failures in SQL Server Flat file
backup
> (SQL SErver maintenance plan to do full backup all application databases
> with verify). Invesigation reveal (quite consistently) that the flat file
> backup of the MCMS 2002 SP1A database will always fail when Named Pipes to
> use. As soon as SQL Client is changed to use TCP/IP, backup function as
> expectd. The error that are logged when Named pipes are used as follows
> *1) The error logged in the error log file is :
> 2004-08-21 21:25:14.07 spid60 BackupDiskFile::OpenMedia: Backup device
> 'd:\MSSQL\BACKUP\DBLive\DBLive_db_200408212125.BAK ' failed to open.
> Operating system error = 32(error not found).
> 2004-08-21 21:25:14.20 backup BACKUP failed to complete the command
> BACKUP DATABASE [YJBLive] TO DISK =
> N'd:\MSSQL\BACKUP\DBLive\DBLive_db_200408212125.BA K' WITH INIT ,
NOUNLOAD
> , NOSKIP , STATS = 10, NOFORMAT
>
> *2) If I change the job to just do the backup without verify, then the job
> completes, but
> *2.1) Normally, the BAK file used to be 3xxMB large, but now the BAK file
> which only succeeded in backing up without verifying is 1xxMB large
> *3) The Job History indicates
> Executed as user: WEBSERVER\SQLServer. sqlmaint.exe failed. [SQLSTATE
42000]
> (Error 22029). The step failed.
> The job failed. The Job was invoked by Schedule 2 (Schedule 1). The last
> step to run was step 1 (Step 1).
> *4) The backup file appear corrupted:
> *4.1) If I look at the Maintenance Plan history, I found:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Cannot open backup device
> 'd:\MSSQL\BACKUP\DBLive\DBLive_db_200408231407.BAK '. Device error or
device
> off-line. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> terminating abnormally.
> *4.2) If I try to restore this (corrupted) 1xxMB BAK file to another DB,
it
> doesn't work. After the popup to say restore has completed, Enterprise
> Manager shows the database as grey with "(Loading)" appended to the
database
> name (even after 5mins)
> *5) The user group Everyone has full permission on d:\MSSQL\BACKUP
> *6) DBCC CheckDB, DBCC CheckFileGroup, DBCC CheckIdent reveal no errors
> So, I am really stuck here, could some SQL Server / MCMS expert help me
out
> here! If I use Named Pipes, SQL Backup is not going to work. If I use
> TCP/IP, then my MCMS export/import would not work most of the time!
>
|||Thanks stefan,
Can I just check if the FAQ in regards to "General Network Error" when using
server side SDO to do export/import apply to MCMS2002 with SP1A (the CD
comes with SP1A) or not? If not, could you suggest any other remedies? I
have tried all the remedies listed in the FAQ at
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
and still we are getting quite frequent "General Network Error" when
performing export on a repository (whose SQL DB is 1GB ish and whose SDO
file is 80MB). e.g. timeouts/maxium request size are much larger than that
required already!!
"Stefan [MSFT]" <stefang@.online.microsoft.com> wrote in message
news:elC3D2RiEHA.396@.TK2MSFTNGP12.phx.gbl...
> Hi Patrick,
> 1) MCMS likes all protocols if SP1 or SP1a are installed
> 2) creating an ALIAS in SQL tools for the different application would
allow
> to configure the protocols for all apps individually.
> Cheers,
> Stefan.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> MCMS FAQ:
>
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm
> MCMS Blog: http://blogs.msdn.com/stefan_gossner/category/4983.aspx
> MCMS Sample Code:
>
http://www.gotdotnet.com/community/u...emen t+Server[vbcol=seagreen]
> MCMS Whitepapers and other docs:
> http://blogs.msdn.com/stefan_gossner.../07/41859.aspx
> --
>
> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
> news:esccSyRiEHA.2908@.TK2MSFTNGP10.phx.gbl...
properly!
>
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B[vbcol=seagreen]
> importing
of[vbcol=seagreen]
> backup
file[vbcol=seagreen]
to[vbcol=seagreen]
device[vbcol=seagreen]
> NOUNLOAD
job[vbcol=seagreen]
file[vbcol=seagreen]
> 42000]
last
> SQL
> device
> it
> database
> out
>
|||Hi Patrick,
you should surely test if using named pipes solves the problem or not. We
had customers indicating that this solved their problem even after SP1a -
although we assume that the problem was related to their network
architecture as we haven't been able to repro this inhouse.
Using an ALIAS allows to do this easily without affecting any other
applications.
From my understanding backup should work with all protocols - so this
sounds as if there are indeed problems with your network architecture. You
should follow up on this in an SQL related newsgroup.
Cheers,
Stefan.
This posting is provided "AS IS" with no warranties, and confers no rights.
MCMS FAQ:
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm
MCMS Blog: http://blogs.msdn.com/stefan_gossner/category/4983.aspx
MCMS Sample Code:
http://www.gotdotnet.com/community/u...emen t+Server
MCMS Whitepapers and other docs:
http://blogs.msdn.com/stefan_gossner.../07/41859.aspx
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:eG8LodbiEHA.3608@.TK2MSFTNGP09.phx.gbl...
> Thanks stefan,
> Can I just check if the FAQ in regards to "General Network Error" when
using
> server side SDO to do export/import apply to MCMS2002 with SP1A (the CD
> comes with SP1A) or not? If not, could you suggest any other remedies? I
> have tried all the remedies listed in the FAQ at
>
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
> and still we are getting quite frequent "General Network Error" when
> performing export on a repository (whose SQL DB is 1GB ish and whose SDO
> file is 80MB). e.g. timeouts/maxium request size are much larger than
that
> required already!!
> "Stefan [MSFT]" <stefang@.online.microsoft.com> wrote in message
> news:elC3D2RiEHA.396@.TK2MSFTNGP12.phx.gbl...
> allow
> rights.
>
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm
>
http://www.gotdotnet.com/community/u...emen t+Server[vbcol=seagreen]
Named
> properly!
>
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B[vbcol=seagreen]
> of
databases[vbcol=seagreen]
> file
Pipes[vbcol=seagreen]
> to
as[vbcol=seagreen]
follows[vbcol=seagreen]
> device
> job
> file
> last
[Microsoft][ODBC[vbcol=seagreen]
DB,[vbcol=seagreen]
Enterprise[vbcol=seagreen]
errors[vbcol=seagreen]
me[vbcol=seagreen]
use
>
|||Actually:
1) Tests reveal that we cannot consistently prove whether Server side SDO
export/import would work with TCP/IP or Named-pipes
2) Tests reveal that Flat file backups
2.1) for the MCMS 2002 SP1A Repository consistently fail when SQL Client
Network utility is set to use Named Pipes.
2.2) For other databases (including a 3GB database, as opposed to a mere 1GB
MCMS SQL DB) all works with named pipes
3) The SQL Server, IIS and CMS are all on the same box! So, named pipes
should be using (I believe) the internal kernel, which is meant to be super
fast?
"Stefan [MSFT]" <stefang@.online.microsoft.com> wrote in message
news:evymkDciEHA.4020@.TK2MSFTNGP10.phx.gbl...
> Hi Patrick,
> you should surely test if using named pipes solves the problem or not. We
> had customers indicating that this solved their problem even after SP1a -
> although we assume that the problem was related to their network
> architecture as we haven't been able to repro this inhouse.
> Using an ALIAS allows to do this easily without affecting any other
> applications.
> From my understanding backup should work with all protocols - so this
> sounds as if there are indeed problems with your network architecture. You
> should follow up on this in an SQL related newsgroup.
> Cheers,
> Stefan.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> MCMS FAQ:
>
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm
> MCMS Blog: http://blogs.msdn.com/stefan_gossner/category/4983.aspx
> MCMS Sample Code:
>
http://www.gotdotnet.com/community/u...emen t+Server[vbcol=seagreen]
> MCMS Whitepapers and other docs:
> http://blogs.msdn.com/stefan_gossner.../07/41859.aspx
> --
>
> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
> news:eG8LodbiEHA.3608@.TK2MSFTNGP09.phx.gbl...
> using
I
>
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
> that
>
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm
>
http://www.gotdotnet.com/community/u...emen t+Server[vbcol=seagreen]
doing
> Named
>
http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B[vbcol=seagreen]
use[vbcol=seagreen]
use[vbcol=seagreen]
settings.[vbcol=seagreen]
> databases
flat[vbcol=seagreen]
> Pipes
function[vbcol=seagreen]
> as
> follows
command[vbcol=seagreen]
the[vbcol=seagreen]
[SQLSTATE[vbcol=seagreen]
The
> [Microsoft][ODBC
> DB,
> Enterprise
> errors
> me
> use
>
|||Hi Patrick,
Just wondering if you found a solution to your backup problem. We have a
similiar issue and are banging our head trying to figure out what the problem
is.
"Patrick" wrote:

> Actually:
> 1) Tests reveal that we cannot consistently prove whether Server side SDO
> export/import would work with TCP/IP or Named-pipes
> 2) Tests reveal that Flat file backups
> 2.1) for the MCMS 2002 SP1A Repository consistently fail when SQL Client
> Network utility is set to use Named Pipes.
> 2.2) For other databases (including a 3GB database, as opposed to a mere 1GB
> MCMS SQL DB) all works with named pipes
> 3) The SQL Server, IIS and CMS are all on the same box! So, named pipes
> should be using (I believe) the internal kernel, which is meant to be super
> fast?
>
> "Stefan [MSFT]" <stefang@.online.microsoft.com> wrote in message
> news:evymkDciEHA.4020@.TK2MSFTNGP10.phx.gbl...
> rights.
> http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm
> http://www.gotdotnet.com/community/u...emen t+Server
> I
> http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
> http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm
> http://www.gotdotnet.com/community/u...emen t+Server
> doing
> http://download.microsoft.com/downlo...a/MCMS+2002+-+(complete)+FAQ.htm#B01B7B38-C97A-4935-8957-DC346719CE9B
> use
> use
> settings.
> flat
> function
> command
> the
> [SQLSTATE
> The
>
>

Sunday, 25 March 2012

Connect to specific TCP/IP endpoint?

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

Saturday, 25 February 2012

Confused about Configuration Manager's TCP/IP settings

Conflicting SQL Client Settings

SQL Configure

Blog Archive

About Me