We talked about whitelisting IPs before, but what about the opposite, blacklisting IPs that may never access your SQL Server.
Blacklisting them well enough that they can’t even go beyond the initial attempt to login.
Sounds like fun!
Oh No You Don’t!
So, if you want more details about what a Blacklist vs. Whitelist is, go check out our other post on how to create a Whitelist, it has those details there.
This post is to get down to the nitty-gritty and help you block certain IPs from getting into your SQL Server while letting all others in.
The first thing we are going to need is a table to store or list of blacklisted IP addresses.
CREATE TABLE master.dbo.IPBlackList ( IPAddress VARCHAR(15) ); GO
Once we have this, we would want to populate it with IP addresses not allowed to access our environment.
Just as in the Whitelist post, you will want to make sure to not block your personal systems while developing this, less you get locked out of your own system.
Then you’d have to use a different machine to connect and remove your own systems from the blacklist. At least it isn’t as bad as forgetting to put yourself on the whitelist if using that method. For now we’ll just use some “made up” IP Addresses.
INSERT INTO IPBlackList (IPAddress) VALUES ('127.1.0.1'), ('69.153.222.*'),('122.168.100.*');
GO
Now that we have the IPs added, we want to make sure the login process, from anyone, is able to read the table.
GRANT SELECT ON master.dbo.IPBlackList TO PUBLIC; GO
Just like the Whitelist functions tracked failed attempts, we will add a logging table to keep track of attempts to login from the blacklisted IPs.
To do this we will need the table to store the information.
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
Now we are ready for the fun part, the trigger to check the IP and block the connection.
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 check IP addresses without the immediate need to involve our network admins and gives us some extra insight to our own system’s activity.
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 blacklisted*/
IF NOT EXISTS ( SELECT IPAddress
FROM master.dbo.IPBlackList
WHERE IPAddress = @ClientIP )
BEGIN
/* IPAddress is not in Blacklist, logon allowed*/
PRINT 'IP Address Allowed'
END
ELSE
BEGIN
/* now check for a range in our IPBlackList 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.IPBlackList
WHERE IPAddress = @IPRange )
BEGIN
/* IPAddress Range is not in Blacklist, logon allowed*/
PRINT 'IP Address Allowed'
END
ELSE
/* The IP is in our Blacklist 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
The NOT EXISTS check is being used instead of an EXISTS check with differnet logic since a blacklist is usually not a very long list.
This works in a very similar way to the WhiteList trigger, with some minor changes to flip the logic of being on the list or not.
As always when it comes to this type of problem and code, we advise working with your network admin to ensure your DNS is configured properly with its whitelist and blacklist options, but now you have yet another tool in your DBA belt to help keep things locked down a little tighter.