Sample Header Ad - 728x90

Load assembly from a certificate in SQL Server

5 votes
1 answer
1616 views
We received an assembly from external vendor as
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 Assembly Files ----- 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 Signing 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
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