Blacklist vs Whitelist
I think just about everyone is familiar to some degree with the concepts of a blacklist and whitelist.
If not, the easiest way I can summarize them is as follows:
A blacklist is a list of what can not access something you are trying to protect.
If you travel, this can be thought of as the TSA no fly list. If you are on the list, you are not getting anywhere fast.
A whitelist, on the other hand, is just the opposite. It is a list of what is permitted to access something. If whatever is attempting access is not on the list, it isn’t getting in.
This is like your typical bouncer at that hip club with the clipboard that won’t let you in because you are not on “the list”.
There is a lot of debate of which is a better practice, to blacklist or whitelist, but I feel it is a matter of preference and how much maintenance you want to do.
What does this have to do with SQL Server?
Well, the other night I once again heard the question of: “Does SQL Server allow you to Blacklist/Whitelist certain IP addresses?”.
It is a good, and valid, question and I had to think a bit before answering that I was not aware of an easy way to do that.
Others in the room concurred that you pretty much would have to configure your DNS service to create the blacklist and/or whitelist.
Now, to me it makes sense that there are times you want to lock down access to your server to specific IPs, and other times you just want to stop other IPs from even getting near your server.
When I got home I started doing some research and was able to find a couple of articles written online about using a feature that came with SQL Server starting in SQL Server 2005: Logon triggers
One script that caught my eye was created in 2009 by
Welcome to the Whitelist!
Because of the question of creating a whitelist was what was asked, I am going to go through that process. In a different post I will detail the process for creating a blacklist of IP addresses.
Before we are able to put a logon trigger in place for a whitelist though, we are going to need to create a place to store our list. This will be our bouncer’s clipboard.
There isn’t much we really need, just a simple table to store the list of IPs allows to access our system (and 1 non-IP address, but we’ll get to that in a moment).
I am going to use the master database for this and just create a table with a varchar(15) field to hold the IP addresses.
CREATE TABLE master.dbo.IPWhiteList ( IPAddress VARCHAR(15) ); GO
Once we have the table created I will want to populate it with a few IP addresses I know will need access to the SQL Server. You will need to get the proper list together, but I include some commonly found ranges for test environments in this script.
Take note of the last value I am putting in the table ‘<local machine>’. If you are doing this from the machine on which you are going to place the logon trigger, it will not use the network to loop around to itself, but rather uses internal networking. If you leave off ‘<local machine>’ you may get locked out. Trust me on this. I found out the hard way on one of my test systems.
INSERT INTO IPWhiteList (IPAddress) VALUES ('127.0.0.1'), ('192.168.0.*'),('122.168.100.*'),('<local machine>');
GO
*Note: While you create this trigger and test it I strongly advise you do not disconnect your session. We are creating a very restrictive access logon trigger that will prevent anyone, even the DBA / admin from accessing the server from the wrong IP address. If you leave your session connected the logon trigger is not fired and you can undo the trigger. Once you know it is all working, you can safely close the connection. If you mess up and get locked out, you can get back in, bypassing the trigger, as a member of the sysadmin role and using the dedicated admin access or restart the server in single user mode.
Now that I have some IPs added to my list, I need to make sure that anyone is able to read from the list. To do this I grant select access to the Public role. This may seem counter-intuitive, but if the connection is not from one of these IP addresses, it won’t be able to really do anything with the data found since the connection will be refused.
GRANT SELECT ON master.dbo.IPWhiteList TO PUBLIC; GO
Now for some extra function in the trigger, I want to record the IP address from failed attempts along with the number of times that IP address tries to access my system before I fully enable the blocking capability. Be aware that this number may be inflated as SSMS does connect multiple times to the server. This step is purely optional, but worth having in my opinion, to help you test and be a little more responsive with any addresses you may have missed or forgotten, but also to identify any IP addresses making high access attempts. You could then have the network admin update the DNS server blacklist with the information you provide. It is a very simple table, just like the whitelist table, but includes a counter to be incremented each time a denied IP address attempts to connect. Permissions are granted to allow the trigger to use the table correctly to read, insert and update the values.
CREATE TABLE master.dbo.IP_Attempts ( IPAddress VARCHAR(15), Attempts int ); GO GRANT INSERT ON MASTER.dbo.IP_Attempts TO PUBLIC; GRANT SELECT ON MASTER.dbo.IP_Attempts TO PUBLIC; GRANT UPDATE ON MASTER.dbo.IP_Attempts TO PUBLIC; GO
Once all our tables are created and ready to go, we can implement the logon trigger.
But Triggers are bad!
I know, I know. “But it’s a trigger! Triggers are bad!”. Just like most things in life, there is a potential for triggers to be bad. It is like having hidden code in your system that you have to remember about. But like all those things that are “bad”, there are times that it may be the correct solution for an issue you have. There may be a blacklist and whitelist, but in general things are kind of grey. In this case, the trigger it lets us create a way to validate IP addresses without the immediate need to involve our network admins and gives us some extra insight to our own systems activity.
Anyhow, here is the basic code for the trigger.
Notice the ROLLBACK is commented out for the denied connections. This is so the IP_Attempts table can be populated for test / development purposes. When you add the ROLLBACK statement back in, the trigger will function as intended, but will not be able to record the denied IP addresses since the connection and trigger transaction are all rolled back. Once you have enough of a list of the attempts that are not on your whitelist in the IP_Attempts table you can make adjustments to your whitelist and add the ROLLBACK functionality back in by un-commenting the line.
You can change it up however you feel works better for you, and please share in the comments if you find a better way to do this.
CREATE TRIGGER IPAddress_Check ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @ClientIP NVARCHAR(15);
SET @ClientIP = ( SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]',
'NVARCHAR(15)')
);
/* first we check the actual IP address to see if it is whitelisted*/
IF EXISTS ( SELECT IPAddress
FROM master.dbo.IPWhiteList
WHERE IPAddress = @ClientIP )
BEGIN
/* IPAddress is in whitelist, logon allowed*/
PRINT 'IP Address Allowed'
END
ELSE
BEGIN
/* now check for a range in our IPWhiteList Table and if the IP is in that range*/
DECLARE @IPRange VARCHAR(15)
SELECT @IPRange = SUBSTRING(@ClientIP, 1,
LEN(@ClientIP) - CHARINDEX('.',
REVERSE(@ClientIP)))
+ '.*'
IF EXISTS ( SELECT IPAddress
FROM master.dbo.IPWhiteList
WHERE IPAddress = @IPRange )
BEGIN
/* IPAddress Range is in whitelist, logon allowed*/
PRINT 'IP Address Allowed'
END
ELSE
/* The IP is not in our whitelist therefore we go ahead log the IP. We can deny the connection by adding the commented out ROLLBACK
statement back in, but then the logging will be rolled back as well.*/
MERGE MASTER.dbo.IP_Attempts IPA
USING
(SELECT @CLIENTIP) AS CIP (IPAddress)
ON IPA.IPAddress = CIP.IPAddress
WHEN NOT MATCHED THEN
INSERT (IPAddress, Attempts)
VALUES (CIP.IPAddress,1)
WHEN MATCHED THEN
UPDATE SET Attempts = Attempts + 1;
PRINT 'Your IP Address (' +ISNULL(@IPRange, @CLIENTIP)+ ') is blocked, Contact Administrator'
-- ROLLBACK
END
END
GO
Let’s step through this trigger in a little more detail:
First we state we are going to create a logon trigger called IPAddress_Check.
We declare a variable to hold the client IP address that is connecting, and we collect that IP address from the EVENT_INSTANCE/ClientHost data using the EVENT_DATA().value function.
Next we perform a simple comparison to see if the IP address is in our whitelist table. If it is we go ahead and “print ‘IP Address Allowed'” since we have to perform some action, and this is not visible to the end user. The logon then continues like normal.
If we don’t find an exact match for the incoming IP address, we perform a simple range check. We remove the last part of the IP address and replace it with a * and compare that to the values in our table (I’ve kept things simple in the table, but you could easily adapt it for more detailed ranges). Again, if there is a match, we print the “IP Address Allowed” message and continue with a regular logon.
Things start to get a little interesting if there is not match. I have opted to use a merge statement (SQL 2008 and later only, sorry, but you can change the code freely) to add or increment the IP_Attempts table accordingly, then rollback the connection attempt. The rollback action is what prevents any further action being taken by the connection from the wrong IP address.
That’s it. Nice, clean and simple.
I do advise working with your network admin to ensure your DNS is configured properly with its whitelist and blacklist options, but now you have an additional tool in your DBA belt to help keep things locked down a little tighter.
Try it out and let us know how it all works out in the comments below!