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.
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 theCREATE ASSEMBLY
code on each node.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.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 checkedsys.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.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