Featured Post

SQL Saturday #51: Nashville ( #sqlsat51 )

Well, I will be seeing everyone in Nashville at the SQL Saturday #51. My session will be on Monitoring Data Changes With Change Data Capture so I am already checking into trying to do some kind of really crazy type of demo to really underpin the technology and the speed and resourcefulness that it brings to the DBAs arsenal…Mwahahahahaha!..(cough)..excuse me. Anywho, Perpetual Technologies, Inc (PTI), my employer is also a sponsor of the event so look for me to be by the table at least part of the day….I am thinking that I will maybe blog all day about the event in a series of miniblogs …..have to tie that in to twitter maybe by using the hashtag in the title…Hmmmm…. Or maybe the lunch room will be free and I will just do some ad hoc stuff…..that would be pretty cool as well….in any case, it should be a really good time….So if you haven’t signed up yet then be sure to do so or risk being labeled a LAMO! Here’s the whole schedule below….A little messy but hey you can only do so much with cut and paste:) Start Time Business Intelligence Cafeteria Database Administration Database Development Professional Development Session Mix 08:30 AM Rafael Salas Planning your ETL architecture with SSIS Thomas LaRock DBA Survivor Aaron Nelson The Dirty Dozen: PowerShell Scripts for Busy DBAs Daniel Evans EPM using R2 and SharePoint 2010 John Welch Creating Custom Components for SSIS Alan Brewer SQL Server 2008 R2 Utility and Data-tier Apps 9:45 AM Douglas McDowell Realizing ROI for Business Intelligence Projects Allen White Gather SQL Server Performance Data with PowerShell William Pearson Design and Implement Like Edison! Stuart Ainsworth The Social DBA: Resources for Career Building Andy Leonard Database Design for Developers 11:00 AM Wayne Snyder Information Visualization – Designing great Charts Jason Strate Are You Following Your Own Best Practices? Hope Foley Working with Spatial Data in SQL Server 2008 Mark Tabladillo Data Mining with PowerPivot 2010 Jeremiah Peschka Fundamentals of SQL Server Internals 12:15 PM Kevin Kline Keynote 01:00 PM Craig Utley Analysis Services 2008 End-to-End Arie Jones Monitoring Data Changes with Change Data Capture Dan Crawford SQL Server Service Broker Jack Cannon From Access To SQL Server Glenn Berry DMV Emergency Room! 02:15 PM Jessica Moss Make Reporting Services Work For You Kendra Little Take the Awesomeness Home: the Data Collector Kevin Boles Common TSQL Mistakes Andy Warren Building a Professional Development Plan Brian Kelley Fortress SQL Server 03:30 PM Barry Ralston Introduction to MDX for SQL Programmers Thomas LaRock What Are You Waiting For? Robert Cain Data Dude – Making DB developers more productive Drew Minkin Data Mining in Action: A case study Andy Warren Introduction to SQL Server Statistics 4:45 PM Experts Panel Stump the "Experts" Cheers, AJ                          

Read More

Error Message from CLR assembly on SQL Server 2005 Restored Database

Posted by Arie | Posted in SQL Server | Posted on 17-07-2009

Tags: , , , ,

0

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

Write a comment