Not using Certificate for library

[jeffshumphreys]

Bug in this day an age. Expecting people to just "Trust" a lib.

[jeffshumphreys]

https://nielsberglund.com/2017/07/02/sql-server-2017-sqlclr-and-permissions/ https://nielsberglund.com/2017/07/01/sqlclr-and-certificates/

Create a certificate, if you don’t already have one. Create a .pfx file from the certificate. Pfx stands for Personal Information Exchange, and we’ll use it to sign your assembly. Sign the dll with the .pfx file. In the database create a SQL Server certificate from the original certificate. In the database create login from the certificate. Grant the login UNSAFE ASSEMBLY. Create the dll in the database.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/35b8bce5-8ea9-4fd4-aa87-1764a2071e92/long-lasting-pfx-file-for-sqlclr-externalaccess-signing?forum=sqlnetfx

"I want to be able to deploy CLR assemblies to SQL Server for SQL CLR procedures and functions that need EXTERNAL_ACCESS. I don't want to have to create a separate master db asymmetric key and associated login for each assembly I deploy. So I tried this: 1) Create an empty SQLCLR class, add a .pfx signing file created by Visual Studio, and compile the dll. 2) Create an asymmetric key in SQL Server master database from the dll. 3) Create a login from the asymmetric key and grant it EXTERNAL_ACCESS. 4) In another Visual Studio project: a) create a SQLCLR class that has methods, b) import the .pfx file I created above for signing and c) compile the project to a dll. 5) Create a SQL Server assembly from the second dll. 6) Create CLR procedures and functions from the SQL Server assembly.

That all worked well about three months ago, and I was happy. Now someone has informed me that .pfx files have a 12 month expiration date. We have a SQLCLR procedure with EXTERNAL_ACCESS that was created several years ago, and I can see that its EXTERNAL_ACCESS ability has not expired. However, I am concerned that after another nine months I will no longer be able to sign new/modified assemblies with my "shared" .pfx file, and I will have to create a new master db asymmetric key and associated login.

Question 1: Will my shared .pfx file stop working for signing assemblies after its expiration date?

Question 2: Is it possible, with Microsoft tools, to create a .pfx file with an expiration date of my choosing that will work for signing Visual Studio 2015 assemblies? (I tried using MakeCert.exe and Pvk2Pfx.exe to make one. I was able to make a .pfx file, but Visual Studio 2015 would not build an assembly with it. Maybe I just used the programs incorrectly.)"

[jeffshumphreys]

"So here's what we do:

Create a self signed cerificate, I created the cert with a password of testPwd:

makecert -r -pe -n "CN=Niels Test Root Authority" -a sha256 -sky signature -cy authority -sv NielsTestPvk.pvk -len 2048 -m 144 NielsTestCert.cer The -m flag defines the valid lifetime in months for the certificate (so 12 years).

We then create a pvx file from the pvk file by using the Pvk2Pfx tool:

PVK2PFX -pvk NielsTestPvk.pvk -spc NielsTestCert.cer -pfx NielsTestPfx.pfx -po testPwd The -pfx flag defines the name of the pfx file you want, and the -po is the password for the cert/pvk."

[jeffshumphreys]

"USE master; GO

CREATE CERTIFICATE NielsTestCert FROM FILE = 'D:\CertTest\cert\NielsTestCert.cer' GO The certificate file has to be somewhere where SQL Server can get to it.

We then create a login from the SQL certificate and grant unsafe access to the login (I went with UNSAFE, as most of our SQLCLR assemblies are UNSAFE):

CREATE LOGIN login_NielsTestCert FROM CERTIFICATE NielsTestCert GO

GRANT UNSAFE ASSEMBLY TO login_NielsTestCert; GO My unsafe assembly looks something like this:

using System.Threading;

namespace SqlClrTest { public class Functions { public static int fn_clr_LongRunning() { Thread.Sleep(10000); return 999; } } } Notice in the code how we use threads, which is definitely not safe. And if we were to try and deploy it as it stands straight after building it, we would get told that the assembly is not meeting the requirements for being UNSAFE. So what we want to do is to sign the assembly with the previously created pfx file, and we use the SignTool.exe for this:

signtool sign /f NielsTestPfx.pfx /p testPwd SqlClrTest.dll Having done this we can now deploy the assembly, create the function and test it:

CREATE ASSEMBLY SqlClrTest FROM 'D:\CertTest\cs\SqlClrTest\bin\Release\SqlClrTest.dll' WITH PERMISSION_SET = UNSAFE; GO

CREATE FUNCTION fn_clr_LongRunning() RETURNS INT EXTERNAL NAME SqlClrTest.[SqlClrTest.Functions].fn_clr_LongRunning GO

SELECT dbo.fn_clr_LongRunning() GO"