CODEWITHSUNDEEP
deploymentcertificatevisual-studio-2022sql-server-data-toolssqlclr
Muflix
Muflix

Reputation: 6798

How to sign DLL with certificate in Visual Studio (SQL Server CLR)

I try to sign the CLR assembly in Visual Studio database project (SSDT) so that I can publish the assembly into the SQL Server but with no success.

What I have done manually and it works

  1. Created certificate in SQL Server and exported it

  2. Then I converted the certificate format PVK to PFX

pvk2pfx.exe -pvk C:\Certs\ClrCert.pvk -spc C:\Certs\ClrCert.cer -pfx C:\Certs\ClrCert.pfx -pi Pa$$w0rd -po Pa$$w0rd
  1. Signed the DLL with the PFX certificate

DLL is located in the BIN folder of the Visual Studio project.

signtool.exe sign /fd SHA256 /f C:\Certs\ClrCert.pfx /p Pa$$w0rd C:\...\bin\Release\ClrTest.dll
  1. Imported signed assembly and executed it
-- Import assembly
CREATE ASSEMBLY CLRTest FROM 'C:\...\CLRTest.dll' WITH PERMISSION_SET = SAFE;
GO

-- Import function from the assembly
CREATE FUNCTION [dbo].[FooFunction] (@year INT NULL)
RETURNS BIT
AS EXTERNAL NAME [CLRTest].[UserDefinedFunctions].[FooFunction]

-- Execute function
select dbo.FooFunction(2022)

Attempt 1 - Sign an assembly in post build event

  1. Setup event

Project > Properties > Build events : Post-build event command line

"C:\Program Files (x86)\Windows Kits\10\bin\10.0.22621.0\x86\signtool.exe" sign /fd SHA256 /f C:\Certs\ClrCert.pfx /p Pa$$w0rd "C:\Workspace\CLRTest\bin\Release\ClrTest.dll"
  1. Hit build

I can see in the Output window that DLL was signed

Done Adding Additional Store Successfully signed:
C:\Workspace\CLRTest\bin\Release\CLRTest.dll
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
  1. Deploy

When I execute publish profile I see this error

CREATE or ALTER ASSEMBLY for assembly 'CLRTest' 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...

why is that?

Attempt 2 - Sign an assembly with Visual Studio wizard

  1. Open window

Project > Properties > SQLCLR > Signing

  1. Select certificate and provide a password

enter image description here

  1. Hit build

I get this error that the key cannot be imported

Cannot import the following key file: ..\..\..\..\..\Certs\ClrCert.pfx. The key file may be password protected.

What am I doing wrong? 🙈

Upvotes: 1

Views: 1253

Answers (1)

diwatu
diwatu

Reputation: 5699

I had the some problem, just couldn't assign the c# CLR project in Visual Studio, even followed all the googled solutions.

It turned out there is very simple way:

Just create the pfx file in visual studio, pick '<New...>' in the "Choose a strong name key file" drop down.

Upvotes: 1

Related Questions