prolem with "create login from asymmetric key"

Greetings...

I'm trying to create assembly with "PERMISSION_SET = UNSAFE".
For that I've signed assembly's .dll and installed root certificate to “Trusted Root Certificate Authority.”: http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies.aspx
now I'm trying to create login from asymmetric key:

USE master
GO

CREATE ASYMMETRIC KEY SQLCLRTestKey
FROM EXECUTABLE FILE = 'C:\Documents and Settings\All Users\Documents\hunter\StoredProcedures.dll'
CREATE LOGIN SQLCLRTestLogin
FROM ASYMMETRIC KEY SQLCLRTestKey

but I'm receiving error: "Cannot find the asymmetric key 'SQLCLRTestKey', because it does not exist or you do not have permission."

What's wrong?

Best regards...

I recommend you take a look at a sample like the AdventureWorks CLR sample which demostrates all the steps necessary in signing, registering the asymmetric key, registering the login, and finally registering the unsafe assembly. You can find the Yukon SP2 samples here: http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en. Once you've installed the code samples, by default you will find the AdventureWorks CLR sample here: SystemDrive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\AdventureWorks. The readme file for the sample will walk you through each step of the process. You don't need to install any root certificates to use asymmetic keys.

--Bonnie [MSFT]

|||? I'm not a big fan of those instructions. A much, much easier method: First, read this: http://www.sommarskog.se/grantperm.html#certandbulkcopy Follow the instructions therein for setting up a certificate, creating a login using the certificate, etc. But instead of granting bulk copy permissions to the login, grant EXTERNAL ACCESS ASSEMBLY permissions. Backup the certificate and restore it into the database in which you want to create the assembly. Create and catalog the assembly -- no strong name needed (unless you want to use one). Sign your assembly with the same certificate you created in the master DB, and you're good to go. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- ""_hunter"@.discussions..microsoft.com" <"=?UTF-8?B?X2h1bnRlcg==?="@.discussions.microsoft.com> wrote in message news:e74340fa-4ba8-406e-94f4-51698ba38574@.discussions.microsoft.com...Greetings...I'm trying to create assembly with "PERMISSION_SET = UNSAFE".For that I've signed assembly's .dll and installed root certificate to “Trusted Root Certificate Authority.”: http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies..aspxnow I'm trying to create login from asymmetric key:USE master GO CREATE ASYMMETRIC KEY SQLCLRTestKeyFROM EXECUTABLE FILE = 'C:\Documents and Settings\All Users\Documents\hunter\StoredProcedures.dll'CREATE LOGIN SQLCLRTestLoginFROM ASYMMETRIC KEY SQLCLRTestKeybut I'm receiving error: "Cannot find the asymmetric key 'SQLCLRTestKey', because it does not exist or you do not have permission."What's wrong?Best regards...|||

BonnieFe, can You be more specific? Ideally just piont on my mistakes...

I've created key by "sn -k SampleKey.snk"

Then in my project's properties i've selected it in Signing->Sign the assembly->Choose a strong name &key file:-><Browse...>

Build->Rebuild Solution

Then in "SQL Server Management Studio":

EXEC('CREATE ASYMMETRIC KEY UnsafeSample_Key FROM EXECUTABLE FILE = ''C:\\StoredProcedures.dll'';');

CREATE LOGIN UnsafeSample_Login FROM ASYMMETRIC KEY UnsafeSample_Key
and I'm reciving the same error: "Cannot find the asymmetric key 'UnsafeSample_Key', because it does not exist or you do not have permission."

|||

NNTP User, I'm trying such:

USE master

GO

CREATE CERTIFICATE UnsafeSample_Certificate

ENCRYPTION BY PASSWORD = 'All you need is love'

WITH SUBJECT = 'Certificate for example_sp',

START_DATE = '20070201', EXPIRY_DATE = '21000101'

GO

BACKUP CERTIFICATE UnsafeSample_Certificate TO FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.pvk' ,

ENCRYPTION BY PASSWORD = 'Tomorrow never knows',

DECRYPTION BY PASSWORD = 'All you need is love')

GO

CREATE USER UnsafeSample_Login

FROM CERTIFICATE UnsafeSample_Certificate

GO

GRANT EXTERNAL ACCESS ASSEMBLY

TO UnsafeSample_Login

GO

code, but I'm reciving an error "Cannot find the login 'UnsafeSample_Login', because it does not exist or you do not have permission." on GRANT-command.

|||? Look at your code again -- you've created a USER, rather than a LOGIN -- you need to create the login first, then create a user based on the login, in your database (but not in master). -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- ""_hunter"@.discussions..microsoft.com" <"=?UTF-8?B?X2h1bnRlcg==?="@.discussions.microsoft.com> wrote in message news:46c433b9-51c8-4343-95b2-925ce937dbf4_WBRev1_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com NNTP User, I'm trying such: USE master GO CREATE CERTIFICATE UnsafeSample_Certificate ENCRYPTION BY PASSWORD = 'All you need is love' WITH SUBJECT = 'Certificate for example_sp', START_DATE = '20070201', EXPIRY_DATE = '21000101' GO BACKUP CERTIFICATE UnsafeSample_Certificate TO FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.cer' WITH PRIVATE KEY (FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.pvk' , ENCRYPTION BY PASSWORD = 'Tomorrow never knows', DECRYPTION BY PASSWORD = 'All you need is love') GO CREATE USER UnsafeSample_Login FROM CERTIFICATE UnsafeSample_Certificate GO GRANT EXTERNAL ACCESS ASSEMBLY TO UnsafeSample_Login GO code, but I'm reciving an error "Cannot find the login 'UnsafeSample_Login', because it does not exist or you do not have permission." on GRANT-command.