9

I am trying to be able to run a .Net dll through SQL using the CLR - I am doing so unsuccessfully.

I am following the instructions here

So I am doing the following:

CREATE ASYMMETRIC KEY AKEY_SqlClr FROM EXECUTABLE FILE = 'C:\dlls\mySqlClr.dll'

Which works fine and creates the Key, then I try to do the following:

CREATE LOGIN SQLCLR_AsymKeyLogin FROM ASYMMETRIC KEY AKEY_SqlClr

And I get the error:

Cannot find the asymmetric key 'AKEY_SqlClr', because it does not exist or you do not have permission.

How could I not have permissions to this? I have verified that I have CREATE LOGIN permissions. Any ideas?

Anthony Faull
  • 17,549
  • 5
  • 55
  • 73
naspinski
  • 34,020
  • 36
  • 111
  • 167
  • 2
    My memory on this is a bit hazy... Are you creating it in the `master` database? Make sure to `USE master;` before creating your key. – vcsjones Sep 21 '11 at 16:59

1 Answers1

13

Logins are server principals and as such they cannot be created from keys stored in user databases. You must create the key from assembly in master database:

use master;
CREATE ASYMMETRIC KEY AKEY_SqlClr FROM EXECUTABLE FILE = 'C:\dlls\mySqlClr.dll';
CREATE LOGIN SQLCLR_AsymKeyLogin FROM ASYMMETRIC KEY AKEY_SqlClr;
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • One thing I found from this answer was that trying to create the key in the master database from an SNK file (using `FROM FILE` instead of `FROM EXECUTABLE FILE`) instead of the DLL results in: `Please create a master key in the database or open the master key in the session before performing this operation.`. However, using the DLL is better for me anyway as it means I don't have to move an snk file around with it to have it be registerable in databases. – Sphynx Mar 19 '20 at 09:32