Features, discussions, tips, tricks, questions, problems and feedback

Adroit Report Suite: SQL server 2017 (14.x) and later

SQL server 2017 onwards introduced an advanced option called ‘ clr strict security’

CLR strict security controls the interpretation of the SAFE , EXTERNAL ACCESS , UNSAFE permission in SQL Server.

The new layer of security will affect the deployment and running of update scripts required by our report packs, Adroit, AMA, ASI, EMS and OEE.

This feature is enabled by default, when enabled it causes the Database Engine to ignore the PERMISSION_SET information on the assemblies, and always interpret them as UNSAFE . Enabled is the default value, starting with SQL Server 2017 (14.x).

This feature disabled Provides for backwards compatibility. Disabled value is not recommended.

This feature is relevant to Adroit report Suite because the application uses update SQL scripts that create an assembly, which this process will be halted when the security feature is not disabled.

To disable ‘CLR Strict Security’ – Not Recommended by Microsoft

Open SQL Server Management Studio and log on to Database engine with SQL Admin permission,

image

Once logged in, open query page and type the script to disable strict security feature,

image

On the query page, type the following code and execute,

EXEC sp_configure ‘clr strict security’,0;

GO

RECONFIGURE;

GO

image

When it is done and successful, there will confirmation message stating the changes

Important:

After enabling strict security, any assemblies that are not signed will fail to load. You must either alter or drop and recreate each assembly so that it is signed with a certificate or asymmetric key that has a corresponding login with the UNSAFE ASSEMBLY permission on the server.

References: Microsoft website link: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/clr-strict-security?view=sql-server-ver15

1 Like