4

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

Improve this question
1
  • 1
    Perhaps filesystem ACLS. Try dropping the .dll in a folder that you know the SQL Server instance can read from. And you an always CREATE ASSEMBLY from a hex string instead of a file, which would involve deploying the assembly to a SQL Instance on Windows then generating the DDL script for the Assembly. Commented Jan 11, 2020 at 1:18

2 Answers 2

Reset to default
2

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'.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I just had to move the file to a different location. The permissions and the compilation were fine.
@KevinNowaczyk Awesome! Good to know that there's file system access. You should post an answer to clarify what the issue was, showing an example of what worked (i.e. did you use the linux path or the "Windows" style that you said might try?). I still do recommend my SQL# project as it handles security properly (e.g. assemblies are signed so you don't need to disable security like your travis script is doing), uses best practices within the code, and has A LOT of additional functionality (more options/params to control the RegEx, and more functions).
I’ll add it to my list. My first step in my project was just coming up with something to fulfill the Drupal requirements of emulating the REGEXP Operator in MySQL. What I really want to do is move the feature to a separate module so users can choose their library.
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"
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.