December 16, 2020 at 3:42 pm
Hi there
We have a SQL 2019 server which has strict security enabled.
On this server , we have a SQL 2008 R2 database which has assemblies . Now we want to be able to execute
the CLR functionality of the 2008 R2 database but obviously the assemblies need to be signed with a certificate.
Because we don't have a Visual Studio database project to sign the assemblies, one thing we tried was to script out the binary for the assemblies and then recreate them.
However we get the following error
Msg 10327, Level 14, State 1, Line 45
CREATE ASSEMBLY for assembly 'Apps.Database.CLR' failed because assembly 'Apps'Database.CLR' is not trusted. The assembly is trusted when either of the following is true: the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission, or the assembly is trusted using sp_add_trusted_assembly.
Now what I then attempted to do , was to create a certificate using https://www.openssl.org/
And then I tried to create a certificate using the following
USE master;
GO
CREATE CERTIFICATE CLRShareviewCert FROM FILE = 'C:\ShareviewCLR\sql-clr-shareview.cer';
GO
I got the error message
Msg 15468, Level 16, State 6, Line 3
An error occurred during the generation of the certificate
How can I create a certificate on my server, so that I can run the CLR functionality of the SQL 2008 R2 database
I want to avoid turning Trustworthy ON. How do I also sign the assemblies
December 16, 2020 at 4:53 pm
My understanding of this - the assembly needs to be signed outside of SQL Server. If you have the source code to the assembly, then you should be able to sign it from within Visual Studio.
Steps to sign the assembly with an asymmetric key:
another blog on signing assemblies for SQL Server:
https://www.sqlservercentral.com/blogs/how-to-sign-a-sql-server-clr-assembly-in-visual-studio-2015
Now, if you don't have the source to the assembly, you will have no way to sign it. How can you trust the assembly if you can't see the code to know EXACTLY what it does?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 16, 2020 at 5:35 pm
Hi Brian
The problem is that we are regularly restoring the SQL Server 2008 database from a backup
We don't have a visual studio project for the database but we can script out the binary for the assembly?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply