Issue regarding logon triggers and EVENTDATA() when connecting via Python pymssql from Non Windows OS RRS feed

  • Question

  • Wonder if anyone can shed any light on this for me. We have a logon trigger defined that uses the EVENTDATA() function to basically capture logon details and write the details to a table for review later.  Nothing fancy in the trigger at all other than it uses the EVENTDATA() function to capture the events and then log them.

    What we have noticed is that with the logon trigger running and a connection attempt is made via Python pymssql the login is rejected and an error returned to say that Logon failed for login '<username>' due to trigger execution

    If a 3rd party SQL connection tool is used to again try and connect it gets the error "SELECT failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations."

    Now the underlying 3rd party tool and the pymssql connections are using the FreeTDS library for their connections.  I can see if I place a trace for these non windows connections I can clearly see these SET connection parameters being received by SQL and any other logon trigger that does not use the EVENTDATA() function to capture the details allows the logons successfully.  So the issue appears to be that EVENTDATA() is dropping these connection parameters when a connection attempt is made.

    Has anyone got any ideas why this is happening?  I cant see any low level documentation on EVENTDATA() to say this would be the cause but it seems too coincidental.

    There is a work around in that we now issue the SET connection parameters as part of the trigger and it allows the connections but would like to know if EVENTDATA() is the cause. 

    Wednesday, September 9, 2015 3:04 PM

All replies

  • You already have a thread in the Security forum about this issue. I posted an answer to that thread last night.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, September 10, 2015 9:16 AM