2

I have a SQL 2012 Availability group. On that I also have a database with a .Net assembly that is using PERMISSION_SET = UNSAFE. 

CREATE ASSEMBLY drawing 
  FROM C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Drawing.dll' 
  WITH PERMISSION_SET = UNSAFE;
GO

When the system fails over to the Secondary Node the Assemblies don't work with an error like:

Could not load file or assembly System.Drawing....

I can't seem to find a good reference on how CLR assemblies function in an Availability Group. Any assistance is greatly appreciated.

Improve this question
6
  • 2
    When do "the Assemblies don't work"? When you run the CREATE ASSEMBLY command on the new node, or just from referencing it in other code? I'm fairly certain that (like logins, jobs, linked servers etc.) you'll need to run the CREATE ASSEMBLY code on each node.
    – Aaron Bertrand
    Jun 9, 2015 at 0:29
  • Aaron - Thanks for the reply. While acting as the Secondary I can't run the CREATE ASSEMBLY because the database is read-only. I thought about failing it over and then trying to create it but I think that it would create a different key, and then might not work when failed back to the primary because of the same issue.
    – Justin Valdez
    Jun 9, 2015 at 1:08
  • Is the secondary node set up exactly the same? 1) CLR Integration needs to be enabled, 2) the DB needs to be set to TRUSTWORTHY (since it is a Microsoft Framework DLL that is being loaded), 3) the version of .NET that you got the assembly from for the primary node needs to have been installed on the secondary server, AND 4) the assembly needs to be loaded. Have you checked sys.assemblies to see if it is there? Not being able to load does not necessarily mean that it is not present in the DB. It could mean any of those other items I mentioned are not set correctly.
    – Solomon Rutzky
    Jun 9, 2015 at 1:11
  • You should create LOGIN with SAME SID on both server also you should be aware of You may receive an error message when you try to run an existing CLR object or create an assembly that has the external_access or unsafe permission set on a database that is attached or restored from a different server
    – Kin Shah
    Jun 9, 2015 at 1:17
  • I wasn't saying to run create assembly while the other node is still a secondary. You'll have to do it when it becomes primary.
    – Aaron Bertrand
    Jun 9, 2015 at 1:18

1 Answer 1

Reset to default
2

When you create a mirror of the database the "trustworthy" flag is set to false by default. And you need to sync the SID of the dbo on each side (I don't understand how this works for the SA user. Need to study more.

To resolve the issue I failed the database over to the secondary node. Set Trustworthy to True. After that I attempted to run the Procedure and got a failure related to the SID not being registered (sorry forgot to capture the message). Since the database was owned by SA i decided to remove and readd the assemblies.

After removing and readding the assemblies i was able to then run the assembly on the node. At that point I failed everything back to the primary node where I was able to run the assembly. So I failed back and forth a few times and the assembly works on both sides. While this worked for us I'm still not entirely certain how SIDs for the SA user work on both sides.

To summarize: - You have to failover to the secondary to set Trustworthy to True - While the secondary was in read-write Drop and ReADD the assemblies on the second node (while acting as primary) - That seemed to enable the assembly to run on both sides.

Thanks to Aaron and srutzky who guided me to a working solution. If anyone can better explain why what I did worked I'd be glad to know.

Improve this answer
2
  • Did you check both [master].[sys].[databases].[owner_sid] and [db-in-question].[sys].[database_principals].[sid] WHERE [name] = N'dbo' ? If both are 0x01 then it might have to do with the System.Drawing assembly being a Microsoft assembly that you don't have the certificate for? Are you using a strong-named assembly for your assembly that references System.Drawing, and an asymmetric key (or certificate)-based Login, or just using the TRUSTWORTHY setting being ON to get your assembly to be UNSAFE?
    – Solomon Rutzky
    Jun 9, 2015 at 21:16
  • I'm just using "TRUSTWORTHY ON". I believe it comes down to not having the certificate for System.Drawing.
    – Justin Valdez
    Jun 10, 2015 at 7:07

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.