Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Location
    Rio de Janeiro, Brazil
    Posts
    20

    Unanswered: How to connect, after all ?

    Hi All,

    i have a problem, so lets to it.

    Situation:

    I have a COM that connects to SQL2K with that string connection:
    "Provider=SQLOLEDB;Persist Security Info = False;DATABASE=MyDatabase;SERVER=MyServer;User Id=myuser;Password=123456;"

    that works fine, but we have a problem: the person that compiles de DLL knows user and password, so we decided to change the way the application connects to SQL, and start using AppRoles.

    Things we did:
    1-I created an approle and gave the permissions to the role.

    2-I grant connect permission to domain\user1, only connect, that will be used when registering the COM as domain\user responsible to be used to stablish the connection.

    3-Developers changed string connection to: "Provider='SQLOLEDB';Data Source='MyServer';Initial Catalog='Mydatabase';Integrated Security='SSPI';"

    New behavior:

    The connection goes OK, but the way we did before, when traced with profiler, the sp_reset_connection has been invoked by sqlserver, and with the knew connect string was no more executed.

    Another problem is that when the COM is called in a second time, the previous connection is logged out and it stops, not connecting again.

    Any ideas ????


    thanks a lot.


    Leandro.
    Oracle 9i & SQL Server 2000 - DBA
    Rio de janeiro - Brazil
    lsantos.rj@globo.com

  2. #2
    Join Date
    Nov 2002
    Posts
    24
    What's the source code of sp_reset_connection ( are you sure this is a standard MS sp)
    TECMAN

  3. #3
    Join Date
    Sep 2002
    Location
    Rio de Janeiro, Brazil
    Posts
    20
    i dont know, sp_reset_connection is invoked by sqlserver that controls pooling connection.

    the fact is that with the string connection used before the sp_reset... has been invoked, and with the new configuration was no more.

    we setup a domain\user responsible to the COM and granted connect permission to it and the code of the COM is executing sp_setapprole.

    the first time the COM is called it works fine, but the second time the sp_reset_connection is not invoked, and yhe connection is logged out.

    the third time the COM is invoked all works fine again, when i trace the connection the select runs OK, ando so on.

    any ideas ?
    Oracle 9i & SQL Server 2000 - DBA
    Rio de janeiro - Brazil
    lsantos.rj@globo.com

  4. #4
    Join Date
    Jan 2003
    Location
    Surrey, UK
    Posts
    23

    Re: How to connect, after all ?

    I would try adding "Persist Security Info = False;" back into your connection string
    Andy


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •