Checking IP Against CIDR Netmask with T-SQL

I’ve been in the process of improving the functionality in ASLB, one of the improvements I have been working on adding is functionality for ASLB to be smart enough to understand geo-location in a large network.

However in doing so I went through many iterations on how to identify which network a user is in for picking the server priority. The result was to create a network table and a scalar function which returned a bit if the provided IP address was in the supplied sub network. Below is the function I used to accomplish this. With a little more work I may modify the function to accept the netmask in CIDR notation instead of two separate columns.

-- Author: Anthony Mattas
-- Create date: 1-28-2012
-- Description:    This function takes an IP address, CIDR network, and CIDR netmask
--        (i.e. 192.168.0.0/16) and checks to see if the IP address is in the 
--              network
-- ==================================================================================
CREATE FUNCTION [dbo].[udf_CheckNetmask]  
(
    @Address VARCHAR(16),
    @Network VARCHAR(16),
    @Netmask INT
)
RETURNS BIT  
AS  
BEGIN  
    DECLARE @Octet1 INT
    DECLARE @Octet2 INT
    DECLARE @Octet3 INT
    DECLARE @Octet4 INT

    DECLARE @BAddress BINARY(4)
    DECLARE @BNetwork BINARY(4)

    DECLARE @Return BIT

    SELECT @BAddress = CAST( CAST( PARSENAME( @Address, 4 ) AS INTEGER) AS BINARY(1))
            + CAST( CAST( PARSENAME( @Address, 3 ) AS INTEGER) AS BINARY(1))
            + CAST( CAST( PARSENAME( @Address, 2 ) AS INTEGER) AS BINARY(1))
            + CAST( CAST( PARSENAME( @Address, 1 ) AS INTEGER) AS BINARY(1)) 

    SELECT @BNetwork = CAST( CAST( PARSENAME( @Network, 4 ) AS INTEGER) AS BINARY(1))
        + CAST( CAST( PARSENAME( @Network, 3 ) AS INTEGER) AS BINARY(1))
        + CAST( CAST( PARSENAME( @Network, 2 ) AS INTEGER) AS BINARY(1))
        + CAST( CAST( PARSENAME( @Network, 1 ) AS INTEGER) AS BINARY(1)) 

    SELECT @Return =
        CASE
            WHEN
            0 = (cast(@BAddress as bigint) ^ cast(@BNetwork as bigint))
                &
                ~(power(CAST(2 AS bigint), 32 - @Netmask) - 1)
            THEN 1
            ELSE 0
        END

    RETURN @Return

END

GO  

Anthony Mattas

I work for Microsoft! All site content is my personal view. I'm a music lover, big data evangelist, photography enthusiast, and most of all proud dad and husband.

Cincinnati, OH
comments powered by Disqus