SQL Configure: specific

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

connect to specific session using AMO?

The following works, but it uses AdomdClient to connect to a specific session. I'm wondering if there's a way to use AMO to connect to a specific session that already exists.

Dim server As New Microsoft.AnalysisServices.Server()

server.Connect("Data Source=myserver")

Dim sOldSession As String = server.SessionID

'do something

server.Disconnect(False) 'disconnect without killing the session

Dim conn As New Microsoft.AnalysisServices.AdomdClient.AdomdConnection("Data Source=myserver")

conn.SessionID = sOldSession

conn.Open() 'works

hello,

there should be a second overload of Server.Connect that accepts sessionId as a second argument.

so something like below should work:

Dim server As New Microsoft.AnalysisServices.Server()

server.Connect("Data Source=localhost")

Dim sOldSession As String = server.SessionID

'do something

server.Disconnect(False) 'disconnect without killing the session

Dim server2 As New Microsoft.AnalysisServices.Server()

server.Connect("Data Source=localhost", sOldSession)

'do something

server2.Disconnect()

hope this helps,

Tuesday, 20 March 2012

Connect to Analysis Services with specific username

Hello,

We are trying to develop an ASP .Net web application that connects to an SQL Server 2005 analysis services and executes MDX statements. We want the connection to be made using a specific user name that we specify in the code rather than having the web application inherit the logged on domain user account.

We tried to specifiy the user name in the connection string as follows, but it is still taking the logged in user account rather than the one specified.

Microsoft.AnalysisServices.Server s = new Microsoft.AnalysisServices.Server();

s.Connect("Provider=MSOLAP.3;Password=123;Persist Security Info=True;User ID=domain\username;Data Source=<server ip>;Initial Catalog=<analysis DB>");

Is this possible?

Note that if we are using the OLEDB 9.0 provider for Analysis Services to connect an excel sheet to the analysis database we can easily specify the user name and password, so is there a similar way to do the same from a .Net application?

Any feedback is appreciated, thanks,

Grace

If you want to use a Windows Indentity, then you can′t. For other connection options refer to the connection properties which are shown e.g. here:

http://www.ssas-info.com/ssas_articles/ssas_articles/analysis_services_2000_and_2005_connection_string_properties.html

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens,

The link helped a lot. We were able to connect using specific Roles and that is what we were looking for exactly but didn't know it exists.

Grace

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

Sunday, 25 March 2012

Connect to specific TCP/IP endpoint?

connect to specific session using AMO?

Tuesday, 20 March 2012

Connect to Analysis Services with specific username

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