Install a CLR for SQL Server Linux edition

Question

I have a C# file that compiles and installs perfectly fine on Windows. I can compile it without error on Linux with mcs using the command:

mcs -reference:System.Data.dll -target:library -out:tests/RegEx.dll tests/regex.cs

I have verified that the file exists and has permissions 775 in the same directory as the source. Next I try to install it on the server with the following:

/opt/mssql-tools/bin/sqlcmd -P Password12! -S localhost -U SA -Q "CREATE ASSEMBLY Regex from '$TRAVIS_BUILD_DIR/tests/RegEx.dll' WITH PERMISSION_SET = SAFE"

However, I received the error:

CREATE ASSEMBLY failed because it could not open the physical file '/home/travis/build/Beakerboy/sqlsrv/tests/RegEx.dll': 2(The system cannot find the file specified.).

I was worried that paths may need to be “Windows format” and found a suggestion that even ‘C:\’ may be required. I tried this next, but the file still was not found:

/opt/mssql-tools/bin/sqlcmd -P Password12! -S localhost -U SA -Q "CREATE ASSEMBLY Regex from 'c:\home\travis\build\Beakerboy\sqlsrv\tests\RegEx.dll' WITH PERMISSION_SET = SAFE"

Anyone have a suggestion on how this needs to be formatted? The full travis script for my server install is on GitHub

Answer 1

From what I remember, unless something has changed in the past year or to, I believe you are only able to create assemblies using the hex bytes / VARBINARY option, not from the file system. So far I am unable to find the documentation for that, but I do remember reading it when SQL Server for Linux came out (restrictions were no loading from file system, only SAFE assemblies, etc)._{(O.P. was able to get it loaded from the DLL, so either something did change or I misremembered).}

If compiling on Windows, I created a command-line utility for transforming the DLL into the proper format: BinaryFormatter . I've been meaning to update the project so that it runs on Linux natively, but haven't gotten around to that yet (might could use a little help there if anyone has the time 😸).

Regardless, if you are wanting RegEx functions (plus a whole lot more), you can do so more easily by downloading and installing the SQL# library that I created as it does work on SQL Server on Linux. It has most, if not all, of the RegEx methods available in .NET, plus a few extra. And, it handles security properly in that all assemblies are signed, thus not requiring either enabling TRUSTWORTHY (a bad practice) or disabling 'clr strict security'.

Answer 2

@David Browne had the correct advice, to try a different file path.

Here is my Dockerfile:

FROM mcr.microsoft.com/mssql/server:2019-CU10-ubuntu-20.04

RUN set -eux; \
    curl -fSL "wget https://github.com/Beakerboy/drupal-sqlsrv-regex/releases/download/1.0/RegEx.dll"; \
    mv RegEx.dll /var/opt/mssql/data/; \

And the CLR is loaded with:

sqlcmd -P Password12! -S localhost -U SA -d mydrupalsite -Q "EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'clr strict security', 0; RECONFIGURE; EXEC sp_configure 'clr enable', 1; RECONFIGURE"

sqlcmd -P Password12! -S localhost -U SA -d mydrupalsite -Q "CREATE ASSEMBLY Regex from '/var/opt/mssql/data/RegEx.dll' WITH PERMISSION_SET = SAFE"

sqlcmd -P Password12! -S localhost -U SA -d mydrupalsite -Q "CREATE FUNCTION dbo.REGEXP(@pattern NVARCHAR(100), @matchString NVARCHAR(100)) RETURNS bit EXTERNAL NAME Regex.RegExCompiled.RegExCompiledMatch"