A while back, I posted an article about creating a WhiteList for access to SQL Server. Since then I have received a bit of feedback that it was not working as designed. My apologies for taking so long, but I believe I have come up with a fix.
The main issue is the trigger will block some or even all access to the server after it’s created. As it turns out, the issues were really permission being denied. To see it in action, let’s create everything using the original code from here.
We’ll add 1 row to the WhiteList table should allow all users from the workstation, ECHOBASE1, access, regardless of its IP address.
USE master; GO INSERT dbo.WhiteList(LoginName,HostName,HostIpAddress) VALUES ('*','ECHOBASE1','*'); GO
Next, we’ll create a SQL login with only connect permission to the server and nothing else.
CREATE LOGIN LogonTriggerTest WITH PASSWORD = 'Password1'; GO
Finally, we’ll open a new query window using that login.
As you can see, we are denied access to the server because of the logon trigger. If we look in the Errorlog, we can see that we lack the VIEW SERVER STATE permission.
This was my first mistake. I did my initial testing using an administrative login. Most users are not admins; therefore, they will not have the permission required to view sys.dm_exec_connections. I was using this DMV to get the IP address of the client connection, and it requires the VIEW SERVER STATE permission. To get around this, I can use the CONNECTIONPROPERTY function, as it does not require any additional permissions.
SELECT CONNECTIONPROPERTY(‘client_net_address’); GO
Now let’s try to connect again.
Again, we failed. This would be my second mistake. I failed to grant SELECT access to the WhiteList table. By default, a user is will have public permission to the master database, but no permission to the table. To solve this, we can grant permission to the public database role. This will allow any authenticated user to read from the WhiteList table.
USE master; GO GRANT SELECT ON dbo.WhiteList TO public; GO
Finally, our connection to SQL Server is successful. Using the same code from the trigger, we can compare it what’s in the WhiteList table.
USE master; GO SELECT ORIGINAL_LOGIN() AS 'LoginName' ,HOST_NAME() AS 'HostName' ,CONNECTIONPROPERTY('client_net_address') AS 'HostIpAddress'; GO SELECT * FROM dbo.WhiteList; GO
If I had followed my own rules, I could have discovered most of these issues before posting the original article.
The fully updated code is below. Please let me know if you run into any other issues with this new version. I also added another column to the WhiteList table that can be used for hold comments. The idea is to provide some documentation about what the white-listed item is attempting to do.
USE master; GO IF OBJECT_ID('dbo.WhiteList') IS NOT NULL DROP TABLE dbo.WhiteList; GO CREATE TABLE dbo.WhiteList( Id INT IDENTITY(1,1) PRIMARY KEY ,LoginName VARCHAR(255) ,HostName VARCHAR(255) ,HostIpAddress VARCHAR(50) ,Comments VARCHAR(2000) ); GO GRANT SELECT ON dbo.WhiteList TO PUBLIC; GO INSERT dbo.WhiteList(LoginName,HostName,HostIpAddress,Comments) VALUES ('*','ECHOBASE1','*','Any user from the workstation "ECHOBASE1" is allowed to connect, regardless of IP address.') ,('WebSiteLogin','webserver1','192.168.100.55','Only the WebSiteLogin from webserver1 with an IP of 192.168.100.55 is allowed access.'); GO CREATE TRIGGER WhiteListTrigger ON ALL SERVER FOR LOGON AS BEGIN DECLARE @LoginName VARCHAR(255) = ORIGINAL_LOGIN() ,@HostName VARCHAR(255) = HOST_NAME() ,@HostIpAddress VARCHAR(50) = CONVERT(VARCHAR(50),CONNECTIONPROPERTY('client_net_address')); IF( SELECT COUNT(*) FROM dbo.WhiteList WHERE ((LoginName = @LoginName) OR (LoginName = '*')) AND((HostName = @HostName) OR (HostName = '*')) AND((HostIpAddress = @HostIpAddress) OR (HostIpAddress = '*')) ) = 0 ROLLBACK; END; GO
ok how can i witelist renge of ip for example 192.168.0.0/16?
You would need to add a line item for each IP in that range to the WhiteList table. Using * for the LoginName and HostName columns will allow any user from any computer name to login.
Hi,
I had used used your original article to create a new whilelist table, ets.. on one of my computes.
I tried your upadted script on the same machine. Now I can’t login to the instance with the error: TITLE: Connect to Server
——————————
Cannot connect to MARS\SQLSERVER2016.
——————————
ADDITIONAL INFORMATION:
Logon failed for login ‘sa’ due to trigger execution.
Changed database context to ‘master’.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-17892-database-engine-error
——————————
BUTTONS:
OK
——————————
What can I do now?
Use the Dedicated Admin Connection to connect to the SQL so you can disable the trigger. Once disabled, you will need to review the trigger code for why it is failing.
https://learn.microsoft.com/en-us/sql/relational-databases/triggers/logon-triggers?redirectedfrom=MSDN&view=sql-server-ver16#disabling-a-logon-trigger
Hi again,
Thanks for your propmpt reply. I still get the same error. I tried your suggestion and also what was suggested on the by others; none of them worked. It was working well (using your provious article) and I could connect to my database from google sheets but after I implemented this update I am unable to connec to the instance. Please provide me with a more specific instrucutins as i am struggling. Many thanks.
Please use the Contact Me form to send me your info.