Load assembly from a certificate in SQL Server
5
votes
1
answer
1616
views
We received an assembly from external vendor as
----- It worked this way. Is this correct method?------
1. Downloaded Visual Studio Community Edition 2019
2. From the above set of files, opened the project file
3. Build CALCproject
4. It created a .dll file
5. In the properties of the project, signing tab
created a new strong name key file
**Did not select the checkbox "Protect my key file with a password"
ANd chosen sha256RSA as the Signature Algorithm
Save the Project and Build, it creates a .snk file
5. Copy the DLL and snk file to the SQL Server box
6. Run the below script
CREATE ASSEMBLY MYCALC_DLL
AUTHORIZATION dbo
FROM 0x42A728....
WITH PERMISSION_SET = UNSAFE
GO
And this DLL is been called from an SP.
We would not like to set TRUSTWORTHY ON for the database to load this assembly into SQL Server DB and was exploring options. Came across these links
https://nielsberglund.com/2017/07/01/sqlclr-and-certificates/
https://sqlquantumleap.com/2017/08/09/sqlclr-vs-sql-server-2017-part-2-clr-strict-security-solution-1/
https://sqlquantumleap.com/2017/08/16/sqlclr-vs-sql-server-2017-part-3-clr-strict-security-solution-2/
However these links use tools like MAKECERT (replaced by new-selfsignedcertificate), pvk2pfx.exe and signtool.exe are not existing on our win2016 servers.
Also would the DLL be called with this approach
Enable TRUSTWORTHY in the DB ->
Install the assembly in the DB AS WITH PERMISSION_SET = UNSAFE->
Disable TRUSTWORTHY in the DB
I suppose that's a stupid question, however i'm loading assembly as UNSAFE, then what's the point to disable TRUSTWORTHY? Correct me...
We would like to use certificates. How to proceed further? And these DB's will be soon configured for AlwaysOn, would there be any complications? Thanks
Now the developer gave these files


USE [Master]
GO
CREATE ASYMMETRIC KEY CLR_SP_Key
FROM EXECUTABLE FILE = 'H:\CLR_SP\CALC.dll'
GO
CREATE LOGIN CLR_SP_Login FROM ASYMMETRIC KEY CLR_SP_Key
GO
GRANT UNSAFE ASSEMBLY TO CLR_SP_Login
GO
USE [target_database]
GO
CREATE USER CLR_SP_Login FOR LOGIN CLR_SP_Login
GO
CREATE ASSEMBLY DBCALC_DLL FROM 'H:\CLR_SP\CALC.dll'
WITH PERMISSION_SET=UNSAFE
GO
CREATE function dbo.Proc_CLR_SP
(
@name as nvarchar(200) ,
@name2 as nvarchar(200)
)
RETURNS nvarchar(200)
AS EXTERNAL NAME DBCALC_DLL
Asked by Kris
(452 rep)
Oct 4, 2021, 05:41 PM
Last activity: Oct 5, 2021, 06:58 PM
Last activity: Oct 5, 2021, 06:58 PM