SQL SERVER – Be Careful with Logon Triggers – Don’t use Host_Name

Before you read further, let me warn you that Logon triggers are potentially evil and might also end up locking everyone out of the instance. Be careful! Don’t just copy paste the code and create the trigger, you might end up in downtime to fix the issue.

Recently, one of my blog readers sent an email to me asking a quick suggestion. He informed me that he is using a LOGON trigger to control the access to the server. Here is the code for the trigger.

CREATE TRIGGER RestrictAccessPerHostname 
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF
(
HOST_NAME() NOT IN ('HostName1','HostName2','HostName3','HostName4','HostName5')
)
BEGIN
RAISERROR('You cannot connect to SQL Server from this machine', 16, 1);
ROLLBACK;
END
END 

This code would check host_name for incoming connection and if its not in the list login would not be allowed.

He asked if this method is OK to restrict the server access? By looking at the code and logic it seems very logical that we are checking the host_name with the predefined list.

Solarwinds

MY SUGGESTION

In my opinion, we shouldn’t use logon trigger as a mechanism to stop login to SQL Server when the filter can be bypassed. What if someone changes his/her laptop IP or name to match the allowed IP or name? You should ideally use a firewall for this purpose. In remaining part of the blog, you can see that how easy it is to spoof host_name sent to SQL Server.

If I create the trigger and try to login from my laptop (which is not in the listed hostname) I would get an error as below.

SQL SERVER - Be Careful with Logon Triggers - Don’t use Host_Name logon-trig-01

Now, here is the trick to bypass this specific trigger. In SSMS, we can add “Workstation ID = HostName1” as shown below under “Additional Parameter”.

SQL SERVER - Be Careful with Logon Triggers - Don’t use Host_Name logon-trig-02

This would allow us to connect from any server even if the server name is not HostName1 (or any other name in the trigger). This is not what you might have thought while designing the trigger.

Are you using such triggers to restrict server access? What filter you are using? Can that be bypassed like I showed above? Please comment and let me know.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , , ,
Previous Post
SQL SERVER – Error: 1067 – Unable to Bring Analysis Service Online in Cluster
Next Post
SQL SERVER – Unable to Add Node – Could not find subkey System \ CurrentControlSet \ Services \ MSSQLFDLauncher. Error code 0x851B0001

Related Posts

1 Comment. Leave new

  • I am using the logon triggers to prevent brute force attacks. But when the trigger prevent the login of the hacker, the hacker will not login but he already knows the password.

    Is there any control mechanism for brute force before the logon?

    Reply

Leave a Reply

Menu