Accessing Azure SQL DB from an Azure VM Using the External IP

SQL IP address

Address / November 13, 2018

The technically correct way to store IPv4 is Binary(4), since that is what it actually is (no, not even an INT32/INT(4)), the numeric textual form that we all know and love (255.255.255.255) being just the display conversion of it's binary content.

If you do it this way, you will want functions to convert to and from the textual-display format:

Here's how to convert the textual display form to binary:

CREATE FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4) AS BEGIN DECLARE @bin AS BINARY(4) SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1)) RETURN @bin END go

And here's how to convert the binary back to the textual display form:

CREATE FUNCTION dbo.fnDisplayIPv4(@ip AS BINARY(4)) RETURNS VARCHAR(15) AS BEGIN DECLARE @str AS VARCHAR(15) SELECT @str = CAST( CAST( SUBSTRING( @ip, 1, 1) AS INTEGER) AS VARCHAR(3) ) + '.' + CAST( CAST( SUBSTRING( @ip, 2, 1) AS INTEGER) AS VARCHAR(3) ) + '.' + CAST( CAST( SUBSTRING( @ip, 3, 1) AS INTEGER) AS VARCHAR(3) ) + '.' + CAST( CAST( SUBSTRING( @ip, 4, 1) AS INTEGER) AS VARCHAR(3) ); RETURN @str END; go

Here's a demo of how to use them:

SELECT dbo.fnBinaryIPv4('192.65.68.201') -should return 0xC04144C9 go SELECT dbo.fnDisplayIPv4( 0xC04144C9 ) - should return '192.65.68.201' go

Finally, when doing lookups and compares, always use the binary form if you want to be able to leverage your indexes.

UPDATE:

I wanted to add that one way to address the inherent performance problems of scalar UDF's in SQL Server, but still retain the code-reuse of a function is to use an iTVF (inline table-valued function) instead. Here's how the first function above (string to binary) can be re-written as an iTVF:

CREATE FUNCTION dbo.itvfBinaryIPv4(@ip AS VARCHAR(15)) RETURNS TABLE AS RETURN ( SELECT CAST( CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1)) AS BINARY(4)) As bin ) go

Source: stackoverflow.com