I am trying to add an assembly to the SQL Server for my companies project to incorporate regex. Since I never added a user defined function, I followed this guide.
When I'm trying to run:
CREATE ASSEMBLY DbRegex AUTHORIZATION [dbo]
FROM '..\CLR_UDF_DLLs\DbRegex.dll'
WITH PERMISSION_SET = SAFE
I get this error message:
CREATE ASSEMBLY failed because it could not read from the physical file '..\CLR_UDF_DLLs\DbRegex.dll': 50
I'm trying the steps from here to set up a signature, the call:
USE master
GO
CREATE ASYMMETRIC KEY ClrSign FROM EXECUTABLE FILE = 'C:\Repos\Dev\Setup\ProgramData\Database\CLR-UDF-DLLs\DbRegex.dll';
GO
results in this error:
The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it
To get permission, I looked at this documentation which references this one in turn.
However none of the files from the second document exist, and when going into the dialog from the first one, I cannot select any role or user that would add permission to SQL Server.
What step am I missing or doing wrong, trying to add this assembly?
We are using Microsoft SQL Server Management Studio 20.2 on Windows 11.
The code of the C# project is worked on in Microsoft Visual Studio Community 2022.
Server is run as a Database Engine, here the login screen if it helps.
Screenshot of every permission the DbRegex.dll has:
Thanks to the people in the comments I made it work.
The queries linked by @ThomA gave me NT AUTHORITY\NETWORKSERVICE
as the user that was used in order to access the files.
SELECT value_data
FROM sys.dm_server_registry
WHERE value_name = 'ObjectName'
AND registry_key = 'HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVER'
OR
SELECT DSS.servicename, DSS.startup_type_desc,
DSS.status_desc, DSS.last_startup_time,
DSS.service_account, DSS.is_clustered,
DSS.cluster_nodename, DSS.filename,
DSS.startup_type, DSS.status, DSS.process_id
FROM sys.dm_server_services AS DSS;
Since my PC is set to German, I translated it to Netzwerkservice
and gave access to that user(group) by following the steps described here. This solved the issue I was having.
Since your SSMS might be configured differently, I would suggest running the queries on your own setup again.
Alternatively
Although it was not applicable to my case, if you can place the assembly file on your server directly the suggestion by @siggemannen is also an solution, since this removes the need to provide access to the user:
"Run:
select * from master.sys.sysfiles
and just put your files into the folder where master db is
However I cannot guarantee that this is considered best practice