I have a SQL Server database project (sqlproj file) in which I have referenced an assembly containing a user-define aggregate function per https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration-database-objects-user-defined-functions/clr-user-defined-functions?view=sql-server-2017
I was unable to deploy or use my function until I did this:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'clr strict security', 0;
RECONFIGURE;
...and also:
EXEC sp_configure 'clr enabled' , '1';
RECONFIGURE;
Along the way, I tried signing the assembly (generated a self-signed cert in a pfx), but could not figure out how to get SQL Server to trust the certificate.
As best I understand it, what I've done is acceptable for a local development deployment, but this is not the right way to deploy to a live environment (or to push to an Azure database service).
I've seen advice which suggests deploying to an older version of SQL Server in order to extract a hash of the assembly to use in a command which will establish trust; this is not useful to me because I only have SQL Server 2017 and have no intention of installing something like 2008 just to extract a hash.
What is the sequence of steps I must perform to successfully deploy my custom assembly with my database and establish trust "the right way", and what exactly does each of those steps accomplish? Part of my problem is a lack of clarity around the need, purpose, side-effects, and "meaning" of each required step.