Thursday, January 24, 2013

FIX : CLR .NET Assemblies security error after Database Restore

Recently I had to migrate a massive database from one hosting provider to another (Awesome Amazon Web Services). After backup and restore I noticed all the Stored Procedures calling CLR assemblies fails with a security error. 

"Msg 10314, Level 16, State 11, Procedure xxx_xxxxxx, Line 285
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65627. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly" 

This was really strange as it was a direct restore and all the assemblies resides in side the DB itself. So here is what you have to do to fix this issue;

1. Query the table sys.assembly_files and sys.assemblies make sure your assemblies are present in the restored DB. If not you have to re register the assemblies.
2.  Make sure your restored DB has a owner (Ideally sa)
3. Make sure the login who owned the db in the previous server is created in the new db server, if it is a SQL Server login the new login should have the same SID as the previous servers login, Windows logins just having the same name would sufficient.
4. Then run the below command to make your DB trustworthy.

ALTER DATABSE [DB Name]
SET TRUSTWORTHY ON  --after restoration database has this option equal to off
GO

This should simply solve your issue. Please share with me if you find any trouble doing this workaround.




No comments:

Post a Comment