Added .Net assembly to SQL CLR without turning TRUSTWORTHY ON

6 votes
1 answer
5064 views
sql-server sql-server-2008 best-practices sql-clr signature
                          I'm trying to add "System.Messaging.dll" using the guidance provided by Solomon Rutzky in his post Assembly deployment with permission UNSAFE or EXTERNAL_ACCESS using asymmetric key  but I'm failing at the first hurdle.

The first part of the script is to create a certificate from the assembly;

    CREATE CERTIFICATE [MS.NETcer] FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll';
    GO

However, when I execute this I get the error;

    Msg 15208, Level 16, State 1, Line 1
    The certificate, asymmetric key, or private key file does not exist or has invalid format.

The account I'm using to execute the command has the 'sysadmin' server role. This is on a SQL Server 2008 instance.

Please does anyone have any ideas regarding why this is failing?

**---------- Update 1 -----------**

I've taken Solomon's advice and amended my script so it looks like this;

    CREATE ASYMMETRIC KEY [Key.System.Messaging] FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll';
    GO

    CREATE LOGIN [CLR.Login.System.Messaging] FROM ASYMMETRIC KEY [Key.System.Messaging];
    GO

    GRANT UNSAFE ASSEMBLY TO [CLR.Login.System.Messaging];
    GO

All good so far. I now run the CREATE ASSEMBLY command to add the assembly to the SQLCLR;

    CREATE ASSEMBLY [System.Messaging]
    FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
    WITH PERMISSION_SET = UNSAFE
    GO

but this fails with the error;

*CREATE ASSEMBLY for assembly 'System.Messaging' failed because assembly 'System.Windows.Forms' is not authorized for PERMISSION_SET = UNSAFE.  The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.*

I've tried creating an asymetric key for 'System.Windows.Forms'

    CREATE ASYMMETRIC KEY [Key.System.Windows.Forms] FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll';
    GO

but this fails with;

*Msg 15468, Level 16, State 1, Line 1
An error occurred during the generation of the asymmetric key.*

so I tried;

    CREATE CERTIFICATE [Cer.System.Windows.Forms] FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll';
    GO

and this fails with; 

*Msg 15208, Level 16, State 1, Line 1
The certificate, asymmetric key, or private key file does not exist or has invalid format.*

So now I'm stuck again and wondering if its possible to use System.Messaging.dll in the SQL CLR without having to enable the TRUSTWORTHY database property *(and also why on earth does System.Messaging.dll need to have a dependency on System.Windows.Forms.dll!)*

Any thoughts gratefully received
Asked by user1443986 (63 rep)
Mar 18, 2019, 09:54 AM
Last activity: May 10, 2019, 08:04 PM
Added .Net assembly to SQL CLR without turning TRUSTWORTHY ON

Related Questions
