I wrote recently an article about Logon Triggers for Simple-Talk.You can read it here: Logon Triggers.

While doing the research for this article I learned a few things:

  1. Common Criteria Certification does not guarantee that a product is secure but guarantees that a product has certain security functionalities. For example Windows 2000 Server is certified Common Critearia EAL4+ and I wouldn't call it exactly secure.
  2. What the + sign means in EAL4+ - read the article.
  3. That if you activate the common criteria compliance option in SQL Server 2005 SP2 you comply to the following requirements of the Common Criteria Certification:
  4. [quote]
    Criteria Description

    Residual Information Protection (RIP)

    RIP requires a memory allocation to be overwritten with a known pattern of bits before memory is reallocated to a new resource. Meeting the RIP standard can contribute to improved security; however, overwriting the memory allocation can slow performance. After the common criteria compliance enabled option is enabled, the overwriting occurs.

    The ability to view login statistics

    After the common criteria compliance enabled option is enabled, login auditing is enabled. Each time a user successfully logs in to SQL Server, information about the last successful login time, the last unsuccessful login time, and the number of attempts between the last successful and current login times is made available. These login statistics can be viewed by querying the sys.dm_exec_sessions dynamic management view.

    That column GRANT should not override table DENY

    After the common criteria compliance enabled option is enabled, a table-level DENY takes precedence over a column-level GRANT. When the option is not enabled, a column-level GRANT takes precedence over a table-level DENY.

  5. I found 2 bugs in Logon Triggers:
    1. Logon Triggers close connections if an error with a severity of 14 occurs
    2. CLR Logon Triggers - SMO Script Generation
  6. I found out that the script necessary to make SQL Server 2005 SP2 compliant to Common Criteria EAL4+ is missing: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2113556&SiteID=1
  7. I learned the hard way that inside a stored procedure activated by an Event Notification you have the security context of an user and not of a login, so if you need to access different databases it can be a real pain. Fortunatelly Remus Rusanu came in to save my day: Why does feature ... not work under activation? , Signing an activated procedure.
  8. And more ...