Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am deploying a SQL Server CLR project to my database and am usure of something. To give the function in the project external access I am creating an asymetric key from the projects assembly, creating a login from that key and granting access to that login. From MSDN:

USE master 
GO  

CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'path\to\my\assembly.dll'   
CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey   
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin 
GO 

My question is, do I need to keep the assembly on the file system after the key is created for the login to work? I know I could test this by doing the above then removing the assembly and seeing if things break but the only environment I have this type of access to right now is a production one and I don't want to muck around with things there. Thus, I asked here. Could anyone tell me if I need to keep the assembly after the key is created? Thanks!

share|improve this question

1 Answer 1

up vote 2 down vote accepted

No need to keep the file. The file is read by the CREATE ASYMMETRIC KEY statement, the key is extracted and saved in the database and that's it, all further references to it will use the key from the database.

As a general rule databases try to avoid dependencies on file system because they have to work on backup restore scenarios, there could be involved in log shipping or database mirroring etc. For example when you create an assembly in SQLCLR the assembly is loaded into the database and this becomes the master copy, not the one in the file system. Is all about making the database a self contained entity. Now of course I know in the real world this story has some holes, but the CREATE ASYMMETRIC KEY is not one of them :)

share|improve this answer
    
Exactly what I needed to know, thanks for the response, accepted answer and will + when I get enough rep. –  TheMethod Aug 23 '12 at 21:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.