Post reply

Application roles

  • January 6, 2006 at 12:44 pm

    #90383

    Hello all,

    I hope someone have a solution for the problem we are experiencing.  I use application roles in my database.  So when a user connected to the database through VC++ using Microsoft OLEDB.  The application perform an EXEC SP_Setapprole to activate the application role.  My problem is occasionally, OLEDB spawn new connection process(es) on its own.  These leads to permission issue as the new connection are not authenticate by application role and would cause database permission problem.  Currently, my temporary solution is to give datareader and datawritter to all my user.  By this would defeat the application role concept.  I am sure this must be a common problem and someone may be a good solution to it.

    Any help would greatly appreciate it.

    Thanks

    Louis Lam

     

  • January 6, 2006 at 1:28 pm

    #613385

    Why would the application spawn new connections without using the application role? Also, datareader and datawriter and indeed very, very broad. That also gives access to system tables. Can you determine what times the new connection is created which doesn't use sp_setapprole? If so, does it consistently hit against certain objects?

    K. Brian Kelley
    @kbriankelley

  • January 6, 2006 at 2:43 pm

    #613392

    Brain,

    Thanks for the quick follow up.  We are only using datareader and datawritter as a temporary solution now.  We would like to do away with this temporary fix and not give user any permission other than application role.

    Seem like the application only spawn new connection when then original connection is in use or busy and it need to make another database request.  So, OLEDB automatically created a asynchronus connection to the database using the same user id connection but it doesn't know to authenticate to application role.  So this would fail.  When I give that user datareader and datawritter, the application would continue and then I notice the new connection would close on its own after the requested has been made.  We don't know any way of forcing OLEDB not to make extra connection. 

    Thanks.

    Louis

     

  • January 9, 2006 at 4:30 am

    #613497

    Is it possible your problem is to do with connection pooling, application roles mess up connections so they can't be reused. Try turning this off in the odbc settings for the SQL Server driver. If it is creating multiple sessions then perhaps you could have a connection wrapper so that everytime the connection is referenced a check is made on the connection state each time a new recordset is created and it can wait until its ready to continue? I dont really know what your app does but if your using asynchroneous queries wouldnt it be better to wait for it to complete before letting the user carry on doing something else, can imagine testing will be a bit complex otherwise?

     


    Phil Nicholas

  • January 9, 2006 at 7:11 am

    #613524

    The problem there, though, is application roles persist with connection pooling, even if you don't want them to do so. The case here is connections are being made (new ones) but the app role is not being set.

    K. Brian Kelley
    @kbriankelley

  • January 9, 2006 at 9:24 am

    #613611

    When I experienced this (in vb6 ado 2.5) the approle couldnt be reapplied but the connection was still usable if the user had privileges to access the data which does sound like what they might be experiencing. Apologies if this is out of date info .


    Phil Nicholas

  • January 9, 2006 at 9:42 am

    #613616

    I have to believe this must be a very common problem for anyone using application role with VC++ and OLEDB for SQL server.  Is there a way to prevent OLEDB from making these extra connection on its own and force it to use the existing connection that authenticated with application role?  Or can this be done with VC++.  It doesn't seem like there is anything I can do on SQL Server 2000 end to stop this from happening.  Our application also run on Oracle as well, we are not expericing this problem in Oracle with the same code.  So it is crazy.  I hope someone here can give me some of there experience on this issue.

    Thanks.

     

     

  • January 9, 2006 at 9:59 am

    #613621

    Whats the state of the connection when a recordsets created? If its busy does the class library your using attempt to create a new connection if the state doesnt equal open, or is this something you noticed, if so how - in profiler or in c++? If its asynchroneous then has every other recordset/dataset finished fetching the data?

     

     


    Phil Nicholas

  • January 9, 2006 at 10:43 am

    #613631

    We notice this using SQL Server profiler and see that new connections are being open and then close by itself.  Our developer verify that they are not making the new connection themself.  This happen when the connection is busy with serving on recordset and trying executed another query. 

  • January 9, 2006 at 11:21 am

    #613641

    Pretty sure that ado (not sure about oledb) can't open two recordsets simultaneously you have to wait until data is returned, try waiting until query is complete before continuing, asynch queries are more to keep the app responsive.

    found this on the net:

    http://www.kbalertz.com/kb_910696.aspx


    Phil Nicholas

  • January 9, 2006 at 11:21 am

    #613640

    Pretty sure that ado (not sure about oledb) can't open two recordsets simultaneously you have to wait until data is returned, try waiting until query is complete before continuing, asynch queries are more to keep the app responsive.

    found this on the net:

    http://www.kbalertz.com/kb_910696.aspx


    Phil Nicholas

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

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