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!
Best Answer
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 :)