MS SQL CLR Notes

 1. Create Solution

2. Code function

3. Compile and get .DLL file

4. Import DLL to database

5. Suppose to see the error

Cause

SQL Server 2017 introduced a new server-level configuration option called "CLR strict security", and it is enabled by default.

This option requires that all Assemblies, even the SAFE ones, should be signed with a certificate or a strong name key. The Certificate (or the Asymmetric Key) used to do the signing needs to be loaded into the [master] and have a Login created from it. This Login in turn needs to have been granted the UNSAFE ASSEMBLY permission.

You can run the following commands against the SQL server, better if from the Management Studio:

EXEC sp_configure 'show advanced options', 1 
RECONFIGURE; 
EXEC sp_configure 'clr strict security', 0; 
RECONFIGURE;

6. Create function in database

7. Run for testing and suppose to see the error

8. Run the following commands to enable "clr enable"

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

9. Succeed

10. Update Assembly

alter assembly MakeMoney from 'D:\MakeMoney\bin\Debug\MakeMoney.dll'