Monday, March 9, 2009

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).

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.

No comments:

Post a Comment