Sign in Subscribe

Error Message from CLR assembly on SQL Server 2005 Restored Database

Arie D. Jones

• 2 min read

Okay,  you may run into the situation where you restore a database from Server A onto Server B and the database contains CLR assemblies. When you try to execute a given CLR assembly it blows an error similar to the one below.

Msg 10314, Level 16, State 11, Line 2
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. 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 ‘AssemblyName, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null’ or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A) System.IO.FileLoadException:
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)

 

This occurs even though you have the database Trustworthy property set to ON and the assembly is set to either external_access or unsafe for the permission level. Additionally, it will not matter if you are the sysadmin or not. So what gives since the assembly worked just fine on the first machine?

Well, it seems that the packages are pretty attached to their owners. So if the owner whom originally created the package on Server A does not have an account on Server B then it goes haywire. Microsoft has kind of a hokey way to get around this in copying over the account and then matching up the SID and giving the account the proper permissions on the database instance.

This is outlined in the following kb article

http://support.microsoft.com/kb/918040

However, a much easier workaround is just to simply script out the dropping of the procedures and functions that rely on the assembly as well as the assembly itself. Then script out a set of create statements to recreate them. Put it all together and run it under a new account on the new server and you are good to go.

In practice a better method of handling this is to ensure that your assemblies are deployed with an AD service account with permissions on the SQL Server. The AD accounts are easier to handle in this scenario because you just have to make sure that the service account is on the new database instance with the proper permissions. No synching of SIDS is required.

Hopefully, this post saves someone a little bit of time down the road…..

Cheers,
AJ

Sign up for more like this.

Enter your email
Subscribe