can't install a CLR stored procedure

I have written 2 CLR stored procedures. I have a local copy of our database to which I was able to successfully deploy them. When I changed the connection string to our production database, it fails when I try to deploy. It gives me an error... EXTERNAL ACCESS ASSEMBLY permission denied on object 'server', database 'master'. I've logged onto the database server and set trustworthy on for that particular database. I also tried...

CREATE ASSEMBLY UpdateJobAdSearch FROM 'I:\DLL\Candidate.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

Candidate.dll is the stored procedure dll. I also read on http://msdn2.microsoft.com/en-us/library/ms345106.aspx about doing something like....

USE master
GO

CREATE ASYMMETRIC KEY CandidateKey FROM EXECUTABLE FILE = 'I:\DLL\Candidate.dll'
CREATE LOGIN CandidateLogin FROM ASYMMETRIC KEY CandidateKey
GRANT EXTERNAL ACCESS ASSEMBLY TO CandidateLogin

but I get the errors...

Msg 15208, Level 16, State 1, Line 2
The certificate, asymmetric key, or private key file does not exist or has invalid format.
Msg 15151, Level 16, State 1, Line 3
Cannot find the asymmetric key 'CandidateKey', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 4
Cannot find the login 'CandidateLogin', because it does not exist or you do not have permission.

I'm no db admin, so I'm not sure what I need to do to get these installed. I'm not clear on what they mean about creating a login mapped to an asymetric key. Can anyone clear this up in more plain english? Thanks!

Did you sign the Candidate.dll assembly? You can do that with Visual Studio or the .NET Framework 2.0 SDK.

|||

Not originally. I found the Sign option in properties so I checked off for it to sign it and gave it a new key name. But when I tried to deploy, I still got...

Error 1 EXTERNAL ACCESS ASSEMBLY permission denied on object 'server', database 'master'. JobAd

|||

After you signed the assembly, did you successfully create the asymetric key?

|||Bonnie, that's where I'm lost. I assume I have to create the asymetric key on the server? But when I go to Security>Asymetric Keys and right click, the only option I have is refresh. There's nothing there for me to create a new one. So, I'm not sure how to do that? Thanks!|||

You can create asymmetric keys using the DDL you mentioned above (CREATE ASYMMETRIC KEY). See the HandlingLOBUsingCLR sample for step by step instructions for how to install and use an assembly that requires external access. The latest samples MSI is located at http://msdn.microsoft.com/sql/downloads/samples/default.aspx. After you install the Samples MSI, by default you'll find the HandlingLOBUsingCLR sample at drive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\HandlingLOBUsingCLR. In that folder will be a readme file that will walk you through it.

--Bonnie

|||

Thanks Bonnie, but I found this earlier and it worked great. Thanks for your help!

http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies.aspx