Intro

One of the useful things in SQL Server 2017 is the ability to use any Common Language Runtime (CLR) languages to create user-defined functions. This is a good way to bypass some limitations in SQL Server such as the ability to convert an IPv6 address into a 128-bit number. Below we will show you how to create such a function using C# which takes a string containing an IPv6 address and returning the IP number as a string.

Pre-requisites

Our example will be using the below but you may be able to use other versions of those programs:

• Microsoft SQL Server 2017
• Microsoft Visual Studio 2019

The C# code for the IPv6 conversion function

Let’s get started with our code. First of all, open Visual Studio 2019 and create a new project. For the project type, we will use Class Library (.NET Framework).

We’ll call our project IPv6ToNum.

Create a new class called IPv6Converter.cs and paste the following code into it.

using Microsoft.SqlServer.Server;
using System.Net;
using System.Numerics;
using System.Collections.Generic;

namespace IPv6ToNum
{
    public class IPv6Converter
    {
        [SqlFunction(DataAccess = DataAccessKind.None)]
        public static string ToNum(string IPv6)
        {
            BigInteger ipnum;

            if (System.Net.IPAddress.TryParse(IPv6, out IPAddress address))
            {
                byte[] addrBytes = address.GetAddressBytes();

                if (System.BitConverter.IsLittleEndian)
                {
                    List<byte> byteList = new List<byte>(addrBytes);
                    byteList.Reverse();
                    addrBytes = byteList.ToArray();
                }

                if (addrBytes.Length > 8)
                {
                    //IPv6
                    ipnum = System.BitConverter.ToUInt64(addrBytes, 8);
                    ipnum <<= 64;
                    ipnum += System.BitConverter.ToUInt64(addrBytes, 0);
                }
                else
                {
                    //IPv4
                    ipnum = System.BitConverter.ToUInt32(addrBytes, 0);
                }
                return ipnum.ToString();
            }
            else
            {
                return "";
            }
        }
    }
}

Make sure you are signing your assembly.

Then compile the project to generate the IPv6ToNum.dll file.

Register the assembly in SQL Server 2017

Before you can use the user-defined function in SQL Server, you will need to register the assembly.

To grant the permission to register the assembly, run the following Transact-SQL. The full path should be modified to wherever you have created the IPv6ToNum.dll file.

USE master;   
GO    
  
CREATE ASYMMETRIC KEY IPv6ToNumKey FROM EXECUTABLE FILE = 'C:\Users\xxxxx\Documents\Visual Studio 2019\Projects\IPv6ToNum\IPv6ToNum\bin\Debug\IPv6ToNum.dll'
CREATE LOGIN IPv6ToNumLogin FROM ASYMMETRIC KEY IPv6ToNumKey
GRANT UNSAFE ASSEMBLY TO IPv6ToNumLogin;
GO

NOTE: You must create a new login to associate with the asymmetric key. This login is only used to grant permissions; it does not have to be associated with a user, or used within the application.

Next, run the following Transact-SQL to perform the registration of the assembly. As before, modify the full path to your own.

CREATE ASSEMBLY IPv6ToNum
FROM 'C:\Users\xxxxx\Documents\Visual Studio 2019\Projects\IPv6ToNum\IPv6ToNum\bin\Debug\IPv6ToNum.dll'
WITH PERMISSION_SET = UNSAFE;
GO

Create the user-defined function

Finally, we will create the user-defined function that maps to the function in the assembly. Run the following Transact-SQL.

CREATE FUNCTION ConvertIPv6(@IPv6 AS NVARCHAR(50)) RETURNS NVARCHAR(50)
AS EXTERNAL NAME IPv6ToNum.[IPv6ToNum.IPv6Converter].ToNum;
GO

NOTE: The external name consists of the assembly name, namespace name, class name and finally the function name. We are also using NVARCHAR since that maps to a string data type on the CLR side.

Before using the function, we will need to configure SQL Server to allow it to run CLR code. Run the following Transact-SQL to do so.

EXEC sp_configure 'clr enabled' , '1';
RECONFIGURE;

Using the function in your queries

You can call the function with a string containing an IPv6 address and you’ll get back the IP number as a string.

SELECT dbo.ConvertIPv6('2404:6800:4001:c01::93');  
GO

Conclusion

Normally, you won’t have to convert an IPv6 address to its IP number inside SQL Server. Most people would query the SQL Server from another external programming language like C# or VB.NET. They can perform the IPv6 conversion in those languages and then just pass the IP number to the SQL Server for database queries. This is the easier and recommended way.

However, if you are forced to only use SQL Server to perform the conversion then you can jump through all the hoops we’ve mentioned above. It would work just as well.