Systems Engineering and RDBMS

Prevent connections from certain IP Address(es)

Posted by decipherinfosys on January 1, 2010

We have talked about Logon Triggers in SQL Server which were first introduced in SQL Server 2005 Service Pack 2.  We had also covered the usage of the EVENTDATA() function in another blog post here.  One of the questions that a reader had asked recently was to have the capability of preventing any connections coming from a specific IP Address and one of the ways of doing that (if we want to do it from the DB Side) in SQL Server is to make use of the logon triggers and the eventdata() function.  We can use the function to get the IP address of the client machine and then prevent the connection via the trigger logic.  Let’s check this out using an example:

As per the BOL, here is how the event data schema looks like for the logon event:

<EVENT_INSTANCE>
<EventType>event_type</EventType>
<PostTime>post_time</PostTime>
<SPID>spid</SPID>
<ServerName>server_name</ServerName>
<LoginName>login_name</LoginName>
<LoginType>login_type</LoginType>
<SID>sid</SID>
<ClientHost>client_host</ClientHost>
<IsPooled>is_pooled</IsPooled>
</EVENT_INSTANCE>

The one that we are interested in is “ClientHost” which as per the definition from BOL: “Contains the host name of the client from where the connection is made. The value is ‘<local machine>‘ if the client and server name are the same. Otherwise, the value is the IP address of the client.

So, armed with that information now, we have two ways of achieving our aim:

a) Either we allow connections from specific IP addresses only thus blocking away all the rest of the connections OR,

b) We block only specific connections.

Method (a) is more secure since permissions should be given on an as needed basis in this case.  Regardless of the method, we would need to store that list of IP addresses someplace – can be a configuration file on the DB Server filesystem that we can read via a linked server query or better yet, this information can be stored securely in a table in the schema within the database.   In our database, we have a schema called SecuritySchm and all the security related tables are created in it.  So, assume we have a table in it called Allowed_IP_Address and the column IP_Address is the one where we will check the existence of the incoming IP Address of the client.  Besides the allowed IP addresses, do not forget to put in the <local machine> in it (as mentioned in BOL above since it is needed for local connections).

And here is how the trigger will look like:

create trigger AuditLogin_Demo
/* server means instance level*/
on all server
with execute as 'sa'
/* We specify the logon event at this stage
– If the IP address of the client is not a valid one,
– Issue a rollback*/
for logon
as
begin
/*declare the local variable*/
declare @IP nvarchar(30)
/*Now, get the IP Address value*/
SELECT @IP = (SELECT EVENTDATA().value ('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(30)'))

/*Check and rollback if not a valid IP value*/
IF NOT EXISTS (Select 1 from SECURITYSCH.Allowed_IP_Address where IP_Address = @IP)
ROLLBACK;

end
go

Now that we have it, let’s try connecting from a machine who’s IP is not listed in our table and we will get an error like this one:

NOTE:   We wanted to mention here that while playing with the code for this post, in the first iteration we ended up creating a bad logon trigger (oops!) and if you end up in the same situation, then if you already have a valid connection open, you can drop the logon trigger by doing:

drop trigger <trigger_name> On All Server;

And if you do not have any current valid connection open, you can connect through DAC (Dedicated Admin Connection) and then execute the command from above.  We had discussed DAC in one of our previous posts here.

Resources:

  • BOL Information on EventData() and Logon Trigger – here.

One Response to “Prevent connections from certain IP Address(es)”

  1. […] a reader on how to prevent connections from certain IP addressess – you can read that post here.   As you know, in order to capture the XML data about LOGON events, we will make use of the […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: