SQL Server logon triggers are a powerful feature introduced in SQL Server 2005 with SP2. They allow instance administrators to perform certain actions at the time of user logon. This can be useful for implementing restrictions on user access during specific times of the day or allowing connections only from certain addresses.
One common use case for logon triggers is tracking the number of users connected from different client machines. By developing a logon trigger, you can easily store connection-related data without granting insert permissions to your logging database and table. However, it’s important to be aware of potential issues that can arise when implementing logon triggers.
In a real-life scenario, a DBA implemented a logon trigger successfully on their development and QA instances. They were able to track the number of clients hitting their instances and store all connection-related data. However, when they rolled out the logon trigger to their production environments, they encountered unexpected problems.
Shortly after the rollout, the first production instance crashed, triggering a series of alerts from the monitoring system. The error messages indicated that processes were attempting to unlock resources they did not own, and there were also communication link failures and login timeouts.
The DBA quickly realized that the logon trigger was causing these issues. They attempted to disable the trigger using a fallback scenario, but it failed because the trigger itself was the problem. Fortunately, they were able to use the Dedicated Administrator Connection (DAC) to turn off the trigger and restore normal operation.
Upon investigation, the DBA discovered that the issue was related to the isolation level being used by some connections. The developers responsible for the applications were unaware of the isolation level being used, as they had not encountered any problems in the development and QA environments. This highlighted the importance of thoroughly testing all scenarios and configurations before implementing logon triggers in production.
To address the issue and find a more reliable solution, the DBA turned to SQL Server’s Event Notifications feature. By using login events and processing them asynchronously through Service Broker, they were able to achieve similar functionality to the logon trigger without the associated problems. The only limitation was that they couldn’t retrieve the IP address of the connecting device.
In conclusion, logon triggers can be a valuable tool for managing user access and tracking connections in SQL Server. However, it’s crucial to thoroughly test and consider the potential impact on production environments before implementing them. Additionally, exploring alternative features like Event Notifications can provide more reliable and scalable solutions.
Lessons learned from this experience include:
- Never assume a server has a typical usage. Double-check with all development teams to test different scenarios.
- Always prepare fallback scenarios and familiarize yourself with the Dedicated Administrator Connection (DAC) for emergency situations.
- Ensure that the scope of any trigger, including logon triggers, is kept as small as possible to minimize potential issues.
- Consider whether real-time processing is necessary or if alternative asynchronous solutions like Event Notifications can be used.
By following these best practices and being aware of potential pitfalls, you can effectively leverage logon triggers in SQL Server to enhance security and gain valuable insights into user connections.