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.

SSMS login screen

Screenshot of every permission the DbRegex.dll has:

DbRegex.dll permissions

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.

SSMS login screen

Screenshot of every permission the DbRegex.dll has:

DbRegex.dll permissions

• sql-server
• windows
• -assembly
• user-permissions

1 Answer 1

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