locked
Managing connections from a specific client application RRS feed

  • Question

  •  I have been working on a project that involves migrating end users from an MS Access solution that used linked tables in their MDBs to using SSMS.

    We have done training, supplied documentation and have helped to translate queries.  Now we want to stop the use of Access. We don't want to remove the application from desktops because it is part of the Office suite and doing so would be a big helpdesk undertaking. We don't want to search and destroy MDBs because there is query text that users might need and its not a very elegant solution.

    The MDBs have an autoexec macro that uses ADO to link the tables with their Windows authentication. And all the users have to maintain their Windows authentication connections because they need to connect with SSMS.

    Watching traces in profiler, I can differentiate the activity by seeing the value of the ApplicationName field, “2007 MS Office System” vs. “SQL Server Management Studio”. So is there a way to deny connections or otherwise stop returning results where the application name is “2007 MS Office System”? Or is there a better way to handle it?
    Tuesday, December 16, 2008 4:03 PM

Answers

  • Hi,

    Starting with SQL Server 2005 there is something called Logon Triggers that you can use. However, things can go horribly wrong with those if you are not careful (actually it can go wrong even then - if you are using or ever will use the serializable ISOLATION LEVEL)so I would personally probably stay away from them.

    A thing that will work, but maybe lack in elegance, is to poll sys.dm_exec_sessions and basically KILL (documented in BoL) the spids with a program_name of 2007 MS Office System.

    HTH

    /Elisabeth

    Elisabeth Rédei | www.sqlserverland.com | http://www.linkedin.com/elisabethredei
    • Marked as answer by MarkWB Tuesday, December 16, 2008 6:57 PM
    Tuesday, December 16, 2008 4:41 PM

All replies

  • Hi,

    Starting with SQL Server 2005 there is something called Logon Triggers that you can use. However, things can go horribly wrong with those if you are not careful (actually it can go wrong even then - if you are using or ever will use the serializable ISOLATION LEVEL)so I would personally probably stay away from them.

    A thing that will work, but maybe lack in elegance, is to poll sys.dm_exec_sessions and basically KILL (documented in BoL) the spids with a program_name of 2007 MS Office System.

    HTH

    /Elisabeth

    Elisabeth Rédei | www.sqlserverland.com | http://www.linkedin.com/elisabethredei
    • Marked as answer by MarkWB Tuesday, December 16, 2008 6:57 PM
    Tuesday, December 16, 2008 4:41 PM
  • Thanks, Elisabeth.  I was looking at another thread about limiting specific users to a specific number of connections and the suggestion was to use a logon trigger.  Looking into it more, it seems like my only realistic option. 

    Aside from the serializable ISOLATION LEVEL, what else should I be carful about?
    Tuesday, December 16, 2008 6:52 PM
  •  
    Just make sure you test it thoroughly in a realistic environment so you don't end up locking out everyone. Be aware that COM+ managed transactions (and thus Biztalk) by default will use the serializable isolation level so even if that is not a problem today, it can become a problem at any time in the future.

    /Elisabeth
    Elisabeth Rédei | www.sqlserverland.com | http://www.linkedin.com/elisabethredei
    Tuesday, December 16, 2008 7:03 PM
  • So, one last question Elisabeth: what exactly is the problem with the combination of a serializable isolation level and a logon trigger?  I Googled the two together but haven't come up with anything.  I see that serializable is very restrictive, but not sure what that has to do with logon triggers.

    Thanks
    Tuesday, December 16, 2008 7:52 PM
  •  
    The effect depends on which tables you are touching but basically everything you touch will be locked for the duration of the trigger. Here is one example:

    http://www.sqlservercentral.com/articles/Administration/64974/

    Actually in that post there are links at the bottom to yet som options you can look at.

    /Elisabeth
    Elisabeth Rédei | www.sqlserverland.com | http://www.linkedin.com/elisabethredei
    Tuesday, December 16, 2008 8:05 PM
  • Wow, good article.  Timely too - like yesterday :)

    I do have Biztalk servers in my environment - so I'll definitely keep an eye on that.  However, I won't be making any data changes.  Only rolling back the login if I find the connection is associated with the undesirable application.  So hopefully I won't have as much impact on locking as Johan did.

    I don't know if what I'm trying to do will work asynchroneously.  Seems like my user's queries might be in and out before before I get to the scene.  I am trying to stop them from proceeding as opposed to recording what they are doing.

    The other thing that I am wondering now is whether or not the sys.dm_exec_sessions record exists when the logon trigger is executing.  It looks like I need that record to determine the application name.  Bol refers to the use of EVENTDATA() to get more info inside the logon trigger, but its schema doesn't include application name.
    Tuesday, December 16, 2008 9:29 PM
  • Hi,

    The eventdata does not hold information about the program name but you will have an entry in sys.dm_exec_sessions when the logon trigger is fired:

    CREATE TRIGGER trLogTracker  
    ON ALL SERVER   
    WITH EXECUTE AS 'sa'  
    FOR LOGON  
     AS  
    BEGIN  
    INSERT INTO samplesdb..logtracker  
    SELECT session_id, login_time, program_name FROM sys.dm_exec_sessions  
    WHERE session_id = @@spid  
    END 

    The thing is, the logon trigger will start off an implicit transaction and when you are in serializable isolation level, all shared (range) locks are held as well for the duration of the transaction. Looking at the trigger from the post, I would guess it's not the insert into the userdefined table that is causing the problem but rather the shared locks on exec_sessions and exec_connections.

    /Elisabeth


    Elisabeth Rédei | www.sqlserverland.com | http://www.linkedin.com/elisabethredei
    Wednesday, December 17, 2008 7:27 AM
  • Ok, I have a working trigger that does what I need it to do.  Works great on my machine.  I've sent it off to others to test in other environments - I also linked this post and Johan's to give them a head's up on the other issues.

    Thanks again!
    Thursday, December 18, 2008 4:37 PM