Hi all,
I need to access a DLL (C#) from SQL trigger for one of our requirements. I have compiled the DLL and could load it in SQL server space by using the "Create Assembly" and then create a trigger for it.
The I have set the database to be trust worthy by setting the TRUSTWORTHY flag ON.
ALTER DATABASE %DBName% SET TRUSTWORTHY ON;
But after realizing that this is unsafe way of doing it, I have signed the DLL with a key (.snk file) and tried to create an asymmetric key and then create a login based on the key, for whom I can grand external access. Either without setting DB as trustworthy or unless I create a user who can access the DLL using the key, I cannot use the DLL because it performs a file access. Hence the DLL has to be loaded with an external access permission. But while doing the below query to create the asymmetric key, I am getting an error which follows the query,
USE [master];
GO
CREATE ASYMMETRIC KEY [ClrPermissionsKey]
AUTHORIZATION [dbo]
FROM EXECUTABLE FILE = 'D:\Trigger\TriggerManager.dll'
"Msg 15208, Level 16, State 1, Line 2
The certificate, asymmetric key, or private key file does not exist or has invalid format."
The .snk file lies in the same path as the DLL. Requesting your help here.
If this is not the correct way of doing it, please suggest.