The Pitfall of Logon Trigger

Start with SQL Server 2008, it provides us a feature called Logon Trigger that can be used to audit and control login sessions. Some database administrators use it to restrict login from valid client host IPs or host names. But in fact such restriction can be cheated, and if there is any program bug inside your Logon Trigger code, it will end up locking everyone out of the instance, and you must rely on Dedicated Administrator Connection (DAC) to resume your database service.

Below is an example of such logon trigger:

When someone try to connect to your sql server using SSMS from an invalid host, below error message will be prompted out:

In my opinion, logon trigger should NOT be used as a mechanism to restrict login when it can be cheated. What if someone changes its laptop IP or name to match the allowed ones? You should use a firewall for this purpose.