Blocking Users by IP

One of the things I like about MySQL is the built-in control over what hosts/IP addresses are allowed to connect into the server. This is granular down to the user (equivalent to SQL Server's login/server principal). For instance, here's an example on one of my MySQL instances of where the root account (equivalent to sa for SQL Server) can log in from:

mysql> use mysql;
Database changed
mysql> select host, user from user where user = 'root';
+-----------+------+
|   host    | user |
+-----------+------+
| localhost | root |
+-----------+------+
1 row in set (0.03 sec)

mysql>

Note that the only host defined is localhost. In other words, if someone were to try and connect to the MySQL server using the root user account from anywhere other than the local machine, MySQL is not going to permit it. They could have the credentials right, but it won't matter, as MySQL will block the connection. Should I try from a different location, I'll get an error similar to:

ERROR 1045 (28000): Access denied for user 'root'@'<client name>' (using password: YES)

If I try and trick it and specify root@localhost, I get the following error:

ERROR 1045 (28000): Access denied for user 'root@localhost'@'<client name>' (using password: YES)

I love this level of control, especially when you have scenarios like the following:

In this particular case, we should know the login coming in from the web server. And if best practices were followed, this login has limited rights to the database(s) for the web application. It should not be a member of the sysadmin fixed server role. And any login attempts coming from the web server should not be made with a login that is a member of the sysadmin fixed server role, and especially not the sa account. Should we see anything like that, that's trouble and we'd like to block it.

Unfortunately, SQL Server doesn't have the same capabilities with respect to specifying what IPs or hosts a login can come in from, at least nothing built-in that's as clear and simple as with MySQL. I'd like to see something similar to the way MySQL handles it. But until then, there is a way to do this using logon triggers which some folks have hit upon. If you're not familiar with logon triggers, they are similar to DDL triggers, except they fire on a logon event (such as when someone connects to SQL Server). They were quietly introduced in SQL Server 2005 SP2, and they give us the ability to rollback a connection, thereby effectively terminating it. If you're still supporting SQL Server 2000 or below servers, you'll have to use another means to control connections.

Using EVENTDATA

The key to all of this working in 2005 SP2 and above is the EVENTDATA() function. EVENTDATA() is a function that returns information about an event (as the name implies) and is accessible within a DDL or logon trigger. The information is in XML, so there's a slight trick to extracting the details but it's not terribly difficult. And when it comes to a logon trigger, one of those details is a field called ClientHost, which happens to contain the IP address of the client in the event of a remote connection, or the string '<local machine>' if the connection was made from the same system where SQL Server was running. To obtain this information, we do the following:

DECLARE @IP NVARCHAR(15);
SET @IP = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'));  

This is assuming an IPv4 based IP address which could be XXX.XXX.XXX.XXX, or up to 15 characters long. By retrieving the IP address of the client we can compare it versus a list of valid or restricted IPs, depending on how we want to enforce based on IP.

Building an IP Table

In my case, I like to whitelist access whenever possible. In other words, all access is blocked unless explicitly permitted (blacklisting is the opposite, where everything is permitted unless explicitly blocked). This is the preferred security practice if you can do it, but understandably may not work in all situations. But assuming we can whitelist, here's a simple table structure:

CREATE TABLE dbo.ValidIP (
  IP NVARCHAR(15),
  CONSTRAINT PK_ValidIP PRIMARY KEY CLUSTERED (IP)
);
GO

In this article I'm simply going to be blocking members of the sysadmin fixed server role from logging on except from specified IP addresses, hence the reason I'm only specifying IPs, and not logins. This is ideal if you can work with your network administrators to assign DHCP reservations for the DBAs' workstations. In this way they'll be assigned the same IP address every time. You could just as easily construct a restricted IP address table (where, if you see a connection from a particular IP address, you block, but otherwise permit), or expand it to include both IPs and logins. You could also use subnets or IP ranges, but those are variants off the main idea, so I won't cover them here. Whatever you do, one thing you'll likely want to do is ensure you include connections from the local system. In the example table, it's as simple as:

INSERT INTO dbo.ValidIP (IP) VALUES ('<local machine>');  

Building the Logon Trigger

The next step is to build the logon trigger itself. Here's what we're looking to do with this example:

• Check to see if the login is a member of the sysadmin fixed server role.
• If that's the case, check to see if the ClientHost value matches one of the valid IPs specified in the ValidIP table.
• If it doesn't, issue a ROLLBACK, thereby ending the connection.

Now I don't like throwing objects in the master database. Therefore, I usually create a work database which I call DBAWork or something similar. The risk is that if, for some reason, the database is unavailable (or the table is), then the logon trigger is going to receive the error and it's going to issue an automatic rollback, thereby terminating the connection. There's a workaround which you can use to disable the trigger, but I'll cover that in a bit. Now that we know what we want to do and we know the location of the table, it's time to put the trigger together:

CREATE TRIGGER tr_logon_CheckIP 
ON ALL SERVER
FOR LOGON
AS
BEGIN
  IF IS_SRVROLEMEMBER('sysadmin') = 1
  BEGIN
   DECLARE @IP NVARCHAR(15);
   SET @IP = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'));
   IF NOT EXISTS(SELECT IP FROM DBAWork.dbo.ValidIP WHERE IP = @IP)
     ROLLBACK;
  END;
END;
GO  

What makes it a logon trigger is the ON ALL SERVER which tells SQL Server it's at the server level and the FOR LOGON option. A DDL trigger can also be at the server level with ON ALL SERVER, so the FOR LOGON differentiates the two. Inside the trigger itself, note that we're looking for a match between the result we get from ClientHost and what's stored in the table. If we get '<local machine>', that has been taken care of. Should a connection be attempted for a login that is a member of the sysadmin fixed server role from other than a valid IP address, an error will be returned. If you're using SQL Server Management Studio, you'll see something like this (note why the connection is being broken highlighted in red):

The login failed because of the trigger execution. In other words, the ROLLBACK killed the login attempt. If you have an error when executing the trigger, you'll see the same sort of dialog window. In that case, it's important to know how to get into SQL Server to fix the issue.

Connecting When a Logon Trigger Is Blocking and It Shouldn't Be

Since the logon trigger will fire for all connections (with one exception) if there is ever a problem, either because the IPs are wrong or because there's an issue with the database or table, then getting in and turning off the trigger becomes paramount to restoring availability. In this case, the Dedicated Administrator Connection (DAC) is your friend. Connections via the DAC do not cause logon triggers to fire. So if a logon trigger is blocking connectivity, then you'll want to come in via the DAC. The default DAC configuration only permits a DAC connection from the local computer. You can configure it for remote access, but if you have logon rights to the server where SQL Server is running (such as through Remote Desktop), it's probably not a good idea to do so, because it creates another point to attack. Better to connect and then establish the DAC connection from the server itself. In either case, to connect using the DAC, if you're using SQLCMD, make sure to specify the -A parameter (case-sensitive) and if you're using SQL Server Management Studio (SSMS), precede the name of the SQL Server with ADMIN: to connect. For instance, if I was trying to connect to MyServer using the DAC in SSMS, I would use ADMIN:MyServer as the connection.

After you are connected in this manner, the next step is to either disable or drop the trigger. To disable the trigger, the syntax is:

DISABLE TRIGGER <trigger name> ON ALL SERVER;

To drop the trigger, the syntax is similar:

DROP TRIGGER <trigger name> ON ALL SERVER;

Closing Thoughts

While it is tempting to implement a very robust solution using logon triggers, keep in mind that a logon trigger will fire for every connection. Therefore, it is best to limit what it does both to reduce the likelihood of blocking connections unintentionally and to reduce overall resource usage. A complex logon trigger will cause every connection to slow down, and this could result in a performance or reliability issue. Therefore, it's best to keep the rules simple and straight-forward. If you need something complex, you might look at operating system solutions such as IPSEC or networking solutions such as the use of firewalls and IDS/IPS. If you keep it simple, using a logon trigger in this manner can help provide an extra layer of security to your SQL Servers without too much additional worry or maintenance.