Error while installing on SQL Server 2017

[pbromwelljr]

I'm getting the following error while running on SQL Server 2017:

[2018-02-13 14:32:02] [S0001] An error occured: CREATE or ALTER ASSEMBLY for assembly 'Z.Expressions.Compiler' with the SAFE or EXTERNAL_ACCESS option failed because the 'clr strict security' option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly.

Here's the SQL Server info:
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)
Aug 22 2017 17:04:49
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 16299: )

[JonathanMagnan]

Hello @pbromwelljr ,

Thank you for reporting, we missed this line in our script.

Since SQL Server 2017, a new option must be modified due to new security

EXEC sys.sp_configure N'clr strict security', N'0';
RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'show advanced options', N'0';
RECONFIGURE WITH OVERRIDE;
GO

Let me know if that work when you disable clr strict security

You can find a longer explanation here: https://stackoverflow.com/a/44086646/5619143

Best Regards,

Jonathan

[pbromwelljr]

Thanks, @JonathanMagnan. It installed after that. And I apologize for the late response.

Do you know if there are any plans to provide the items needed to get it installed via the method described here? https://sqlquantumleap.com/2017/08/09/sqlclr-vs-sql-server-2017-part-2-clr-strict-security-solution-1/

[JonathanMagnan]

Hello @pbromwelljr ,

We will look at this article this week and try to choose what will be our plan for SQL Server 2017

Best Regards,

Jonathan

[JonathanMagnan]

Hello @pbromwelljr ,

The script should now be compatible with SQL Server 2017 (about time!).

After reading the article, I believe it will make the deployment/installation of our library way to much complex.

Since the options clr strict security can be turned on after the installation of the script, I believe that' correct for now.

Best Regards,

Jonathan

[pbromwelljr]

Hello @JonathanMagnan,

I tried what you described (turning clr strict security back on after installing the script) and it encountered an error:

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65537. The server may be running out of resources, or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: 
System.IO.FileLoadException: Could not load file or assembly 'z.expressions.sqlserver.eval, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
System.IO.FileLoadException: 
   at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
   at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
   at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)
   at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
   at System.Reflection.Assembly.Load(String assemblyString)

Thanks,

Paul

[JonathanMagnan]

Thank you, Paul for reporting.

We made an error in our test. Unfortunately, clr strick security must stay disabled. That's a pretty bad news for SQL Server 2017.

At this moment, you will need to disable this options but we will work soon to redo the installation to probably use the technic from your post.

Best Regards,

Jonathan

[JonathanMagnan]

Hello @pbromwelljr ,

Sorry for being so slow, we forget to answer you once this request has been made.

Since a few months ago, the library can now be installed using a certificate.

The download is now a zip with a script named SQLNET-Cert.sql that will install it.

I will close this request but if there is anything, just let me know.

Best Regards,

Jonathan