M-Files and SQL Server 2017 compatibility
Posted by Ville Karinen (M-Files) on 19 January 2018 10:05 AM
M-Files is compatible with MS SQL Server 2017, but in SQL Server 2017 all .NET assemblies to be run in a database have to be explicitly trusted.
M-Files 2018 / Online
M-Files 2018 and M-Files Online will try to register the assemblies automatically. If it fails for some reason, it will give you an error, in which case you can register the assemblies manually, as explained in the following chapter.
You must manually whitelist the MFMSSQLCLRObjs.dll assembly located in the M-Files Server installation path. Below are two examples on how to whitelist the assembly. Notice that the assembly file must be present on the Microsoft SQL Server machine. If you run M-Files Server on a different machine you must copy the assembly file to the MS SQL Server computer first.
Option 1: compute the hash of the assembly and whitelist it:
-- Load the assembly file from disk. The file must exists on the local disk
DECLARE @AssemblyBytes VARBINARY(MAX)=
(SELECT * FROM OPENROWSET(BULK N'C:\Program Files\M-Files\<version>\Bin\x64\udf\MFMSSQLCLRObjs.dll', SINGLE_BLOB) AS bytes );
-- Compute a SHA2_512 hash of the assembly file.
DECLARE @AssemblyHash VARBINARY(64) = HASHBYTES( 'SHA2_512', @AssemblyBytes );
-- Whitelist the assembly.
EXEC sys.sp_add_trusted_assembly @hash = @AssemblyHash, @description = N'M-Files <version>. Manually whitelisted.';
Option 2: get the hash of the assembly file in any other way and add it as trusted assembly. You can get the hash for example from the error message that reports the lack of trust for the MFMSSQLCLRObjs.dll:
-- Apply the assembly hash to the @hash parameter below
@hash = 0x8204FAF4E3CBEACDD0E6FB1FD3DBF3663A91643EF76F220501789F0F47C445DAC85EF22977113DCA3B89158D3198AD28371A97A60A7B3EF1F3D57947141E3423,
@description = N'M-Files <version>. Manually whitelisted.';