Question

ShaunNOwens on Thu, 05 Feb 2015 03:55:29


I am doing a SQL migration from SQL 2008 to SQL 2014, I just performed a backup and restore to move a DB from SQL 2008 to SQL 2014.  On both servers the OS is the same Server 2008R2.  After moving the database I receive the following error when executing a stored procedures.  The SP's merge data from a remote Oracle DB to my DB.

Msg 10314, Level 16, State 11, Line 12

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65552. 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 'or.databaseutilities, Version=1.0.4212.34369, Culture=neutral, PublicKeyToken=24de98c7e3e2deeb' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

System.IO.FileLoadException:

   at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)

   at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection, Boolean suppressSecurityChecks)

   at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)

   at System.Reflection.Assembly.Load(String assemblyString)

Any help on how to resolve this problem is appreciated.

Shaun


Replies

Bob Beauchemin on Thu, 05 Feb 2015 18:22:26


Hi Shaun,

Have you tried recompiling your assembly under the version of .NET that's on the server? And, if you're accessing Oracle directly through OCI, is Oracle client stack installed on your new server?

Cheers, Bob

ShaunNOwens on Fri, 06 Feb 2015 00:56:40


I did a search for assembly's on the SQL server, I didn't see any.  I am accessing the Oracle DB through a SSIS package.  Yes the Oracle client is installed on the server.

Bob Beauchemin on Fri, 06 Feb 2015 02:42:58


So, if you do a "select * from sys.assemblies" from the SQL Server 2008 instance (the original instance) you see no assemblies in the database? How do you invoke the SPs that produce the error message? And are the SPs the produce the error written in .NET?

If this is an SSIS problem, rather than a SQLCLR (stored procedures, UDFs etc written in .NET rather than T-SQL) problem, I can move it to the SSIS forum, where you'll get a better response.

Cheers, Bob

ShaunNOwens on Fri, 06 Feb 2015 16:08:45


The only assembly that shows up is the default MS one.  The SP are invoked via a SQL job that calls the SSIS first, I have confirmed that the SSIS is working correctly, the next step is to call 4 SP's, three of those SP's fail with the same error.

This is the SP.

MERGE dbo.MAS_UserTitles AS Target
USING XOX.PAYROLL.CTLTCI AS Source
ON Source.TCI_TITLE_CODE = Target.ust_userTitleKey
WHEN MATCHED AND (
Target.ust_name IS NULL
OR Target.ust_name != Core.Utilities.RegExReplace(Source.TCI_TITLE_NAME, '\s{2,}', ' ')
) THEN
UPDATE SET
 ust_name = Core.Utilities.RegExReplace(Core.Utilities.ProperCase(Source.TCI_TITLE_NAME), '\s{2,}', ' ')
, ust_dateUpdated = GETDATE()
, ust_updatedBy = 'PPS'
WHEN NOT MATCHED BY Target THEN
INSERT (
 ust_userTitleKey
, ust_name
, ust_dataSourceKey
, ust_updatedBy
)
VALUES (
 Source.TCI_TITLE_CODE
, Core.Utilities.RegExReplace(Core.Utilities.ProperCase(Source.TCI_TITLE_NAME), '\s{2,}', ' ')
, 'SYS'
, 'PPS'

Thanks for your help.

Shaun

Bob Beauchemin on Fri, 06 Feb 2015 18:06:09


Hi Shaun,

I'm not "an SSIS guy", so maybe I'm missing something here but where does "Core.Utilities.RegExReplace" come from? Is it part of your SQL Server instance, part of SSIS, or something else?

I've not run into it before and it looks like the SQL statement is looking for a database named "Core", schema named "Utilities" and function "RegExReplace". Based on your reported error message, I'm wondering if you had a database named "Core" in the old SQL Server instance and the MERGE SQL statement is looking for it in the new system, and perhaps that is where the assembly that the error message refers to lives. And Regex functionality is something that folks use SQLCLR for...

Cheers, Bob

ShaunNOwens on Fri, 06 Feb 2015 18:57:38


Yes Core is another DB on my server and that DB exists on the new server as well.  All the dependence DB's have been moved over to the new SQL server.  I'm not a developer so I am scratching my head on this. 

Shaun

Tom Phillips on Fri, 06 Feb 2015 19:09:54


This sounds like you are using functions from the SSMA for Oracle.  This needs to be installed on the SQL 2014 instance.  Just copying the database is not enough.

Please see:

http://www.microsoft.com/en-us/download/details.aspx?id=43689

Bob Beauchemin on Fri, 06 Feb 2015 19:16:15


Hmmm. And I'm guessing that, in the Core database, Utilities refers to an assembly named "or.databaseutilities" and that shows up in sys.assemblies as assembly ID 65552.

Do you have the right SQL Server permissions in place for cross-database calls (i.e. would a call to a T-SQL function in Core database from a different database work?) Who are the owners of the Core database and the "calling" (local) database? This almost sounds like a "blank database owner because the equivalent owner identity doesn't resolve on the new instance" problem. You might also check the assembly owner in the Core database. And the right permissions for the identity running the SSIS job that calls the stored procedures. That's what I'd check next.

I think you could work around this by moving a copy of the assembly into the "local" database (if a call to a T-SQL function in Core database does work *and* the security/database owners are properly set up). And changing the code to refer the to local copy. But that might require more code changes (in possibly multiple databases) than you'd want to make.

Hope this helps, Bob

Erland Sommarskog on Fri, 06 Feb 2015 19:58:12


Run this query:

SELECT * FROM Core.sys.assemblies

Is any of the assemblies listed to have a permission different from SAFE_ACCESS?

In such case you need make arrangements on server level.

ShaunNOwens on Fri, 06 Feb 2015 23:01:44


There is an assembly in the Core DB and it's ID = 65552 the same as the error I am getting.   The permission_set_desc = EXTERNAL_ACCESS.  What do you mean by "make arrangements on the server level"?

Thanks

Shaun

Erland Sommarskog on Sat, 07 Feb 2015 10:44:32


To be able to use an assemnbly with EXTERANAL_ACCESS or UNSAFE, one of the following must be true:

1) The assembly is signed with an asymmetric key, this key has been created in the master database and a login created from the key has been granted the permisssion EXTERNAL ACCESS ASSEMBLY. (Or UNSAFE ASSEMBLY) for an unsafe assembly.

2) The database has been marked as trustworthy, and the database owner has the permission EXTERNAL ACCESS ASSEMBLY.

My preference is strongly for the former, since setting a database owned by a sysadmin user (which commonly is the case) to trustworthy can open a security hole for permission elevation. Then again, in your case, you will have to stick with what have. Go back to the original server, and see if there are any entries in master.sys.asymmetric_keys. If there is not, you will probably have to set the Core database as trustworthy.

Erland Sommarskog on Sat, 07 Feb 2015 10:48:45


By the way, to make it clear, this is not a change between SQL 2008 and SQL 2014, but it is the way it always has been. You would have experienced the same problem if you had move the databases from one SQL 2008 instance to another SQL 2008 instance.

It is one of these small things that have "always worked", which you easily forget about. Or may not even be aware of if you did not install the assemblies originally.

ShaunNOwens on Tue, 10 Feb 2015 02:44:44


Erland, thanks for your help, I have been able to resolve my issue with your help and my new build out is complete.

Best,

Shaun