Post reply

How can create more listener?

  • October 13, 2011 at 10:10 am

    #246474

    My database is SQL 2008, my server has only one network adapter, that means one IP address. Now I'm using port 1433 as listener. For some reasons, I want to create more port, ex port 1455 as listener, so workstations can connect to my server via port 1433 and 1455

    How to create like that ?

    Thanks

  • October 13, 2011 at 10:29 am

    #1394206

    You need to create endpoints, not listeners (that's Oracle).

    See the article here -> http://technet.microsoft.com/en-us/library/ms181591.aspx

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • October 16, 2011 at 7:35 pm

    #1395125

    Ok, I've done.

    But I don't understand why when I create a new Transact-SQL endpoint, connect permissions for public are revoked for the default TDS endpoint and I have to reassign permissions for public to default endpoint ?

  • October 17, 2011 at 12:40 am

    #1395145

    That's right. http://msdn.microsoft.com/en-us/library/ms187811.aspx


    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • October 17, 2011 at 1:56 am

    #1395169

    thang_ngo_2002 (10/16/2011)

    Ok, I've done.

    But I don't understand why when I create a new Transact-SQL endpoint, connect permissions for public are revoked for the default TDS endpoint and I have to reassign permissions for public to default endpoint ?

    Also consider following:

    AUTHORIZATION login

    Specifies a valid SQL Server or Windows login that is assigned ownership of the newly created endpoint object. If AUTHORIZATION is not specified, by default, the caller becomes owner of the newly created object.

    To assign ownership by specifying AUTHORIZATION, the caller must have IMPERSONATE permission on the specified login.

    STATE = { STARTED | STOPPED | DISABLED }

    Is the state of the endpoint when it is created. If the state is not specified when the endpoint is created, STOPPED is the default.

    STARTED

    Endpoint is started and is actively listening for connections.

    DISABLED

    Endpoint is disabled. In this state, the server listens to port requests but returns errors to clients.

    STOPPED

    Endpoint is stopped. In this state, the server does not listen to the endpoint port or respond to any attempted requests to use the endpoint.

    GRANT Endpoint Permissions (Transact-SQL)

    http://msdn.microsoft.com/en-us/library/ms187811.aspx

  • October 17, 2011 at 4:29 am

    #1395220

    AUTHORIZATION login

    Specifies a valid SQL Server or Windows login that is assigned ownership of the newly created endpoint object. If AUTHORIZATION is not specified, by default, the caller becomes owner of the newly created object

    To assign ownership by specifying AUTHORIZATION, the caller must have IMPERSONATE permission on the specified login.

    When you create or alter for instance a stored procedure, you can specify the execution context for this object. This can be either self (the user creating or altering the stored procedure), caller (the one calling the stored procedure), a specific user or owner. If you were able to specify an owner without having to have impersonate permission on it, we would have a back door where everyone could create stored procedures to run as dbo.

    STATE = { STARTED | STOPPED | DISABLED }

    Is the state of the endpoint when it is created. If the state is not specified when the endpoint is created, STOPPED is the default.

    Personally, I find this sane. You create an endpoint first, then you assign the correct permissions to it, then you start it. I would definitely prefer to have the permissions set correctly before I start an endpoint, and I cannot assign permissions on it before it is created.

    As for the automatic revoke of connect permission on the default endpoint. I realize this has come as a surprise for quite a few, although it is well documented. However, when you create another endpoint, it is sane to believe that you would like to set permissions on the to control which user connects to what endpoint, right? Otherwise you could only add another IP address to the SQL Server and configure it to listen on this IP address as well. And by the way, as default SQL Server does indeed listen on all IP addresses.


    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • October 17, 2011 at 4:45 am

    #1395228

    Personally, I find this sane.

    Did I say it's insane? 😛 In fact it's not the topic of discussion (for now).

    I would definitely prefer to have the permissions set correctly before I start an endpoint

    In my last post I just made a point don't forget to start the endpoint because by default it's stopped. Mere granting the permissions is not sufficient.

  • October 17, 2011 at 5:01 am

    #1395232

    Note to self: Check whether you answer thread starter or other who just tries to help 😛


    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • October 17, 2011 at 8:58 am

    #1395389

    even you only one physical nic, you still can configure (virtually) the multi-homed sql server to listen on multiple ports. In some situation you might want to do it.

  • October 17, 2011 at 10:19 am

    #1395435

    Dev @ +91 973 913 6683 (10/17/2011)

    Personally, I find this sane.

    Did I say it's insane? 😛 In fact it's not the topic of discussion (for now).

    Yes, I don't mean instance but it's perfect if you have solution to connect by difference instance name, difference port but to the same database. Or can one database is controlled by 2 instances ?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply