1

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.

Zenilogix
  • 1,318
  • 1
  • 15
  • 31
  • Sign it with a certificate chain that it will trust. Do you have a Certificate Authority in your AD domain? `EXEC sp_configure 'clr enabled' , '1'; ` is always needed – Charlieface Jun 07 '21 at 15:33
  • @Charlieface Thanks for the info re: clr enabled. Re: cert... I signed the assembly with a PFX (self-signed), and I created a .cer; into which store/branch do i install the .cer so that SQL server will see it? – Zenilogix Jun 07 '21 at 16:28
  • Not sure, perhaps `Computer -> Trusted Publishers`. But ideally you should use a cert signed by a CA (even if it's your own CA) – Charlieface Jun 07 '21 at 19:23

0 Answers0