1

I have had an UNSAFE assembly running on an internal only database and web application but the IT dept. might have changed something as we noticed it just stopped working and trying to figure out why.

The error:

Msg 10314, Level 16, State 11, Line 12
An error occurred in the Microsoft .NET Framework while trying to load assembly id xxxxx. 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 'mytestassembly, Version=0.0.0.0, Culture=neutral, PublicKeyToken=xXXXXXXXXXXXXx' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0xXXXXXXXA)

System.IO.FileLoadException:

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

A couple of tidbits/questions:

  • Something happened in the last week and it just started getting these errors above
  • The assembly is UNSAFE because JSON.Net was added to it years ago
  • As far as I can tell via IT talks, trustworthy is ON (I know the security but this is only internal)
  • Do the .pfx keys expire?
  • Is there any query I can run to check all the permissions to see if anything is correct? (I myself might not have full access but someone can check them)
  • I don't believe it uses a certificate but in the past we use .pfx with a certain login to access the assembly.
  • I believe this was created in SQL Server 2012 but runs on SQL Server 2016 now

Any help would be greatly appreciated.

Improve this question

1 Answer 1

Reset to default
0

Certificate expiration dates are ignored by module signing, especially for validation (which is what a certificate is used for in this context, as opposed to signing something). However, if you aren't using the certificate (by loading it into the [master] database and creating a login from that certificate which is then granted the UNSAFE ASSEMBLY permission), then this part is irrelevant anyway.

Below are two queries to start investigating with:

USE [db_name_containing_the_assembly];

SELECT db.name, db.is_trustworthy_on, db.owner_sid, USER_SID(1) AS [OwnerSID-DbLevel]
FROM   sys.databases db
WHERE  db.database_id = DB_ID();

SELECT perm.[permission_name], perm.[state_desc]
FROM   sys.server_permissions perm
WHERE perm.grantee_principal_id = <db.owner_sid from first query>;

The first query will:

  1. confirm whether or not TRUSTWORTHY is enabled
  2. give you the SID of the database owner (used in the second query)
  3. indicate if there is a mismatch of owner SID values between the server-level and database-level

The second query should return at least the following row:

UNSAFE ASSEMBLY GRANT

Based on the results:

  1. If TRUSTWORTHY is actually set to OFF, you can set it to ON
  2. If the two SID values are not the same, then fix one of them via ALTER AUTHORIZATION
  3. If the login doesn't have the UNSAFE ASSEMBLY permission, grant it

So, what happened? Someone either:

  1. set TRUSTWORTHY to OFF
  2. changed the database owner to one that doesn't have the UNSAFE ASSEMBLY permission
  3. revoked the UNSAFE ASSEMBLY permission from the login
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.