The Curious Case of… the CLR assembly failure after an AG failover

(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)

Jonathan was working with a client recently who experienced a CLR assembly failure after an AG failover and needed to figure out why. They’d been testing their AG disaster recovery strategy and ran into an unexpected problem with their application which relies heavily on SQLCLR and an UNSAFE assembly that calls a web service from inside SQL Server.  When they failed over their AG to their DR server, the CLR assembly failed with the following error:

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65546. 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 ‘sqlclr_assemblyname, Version=1.0.0.0, Culture=neutral, PublicKeyToken=fa39443c11b12591’ or one of its dependencies. Exception from HRESULT: 0x80FC80F1

To try and bypass this error, they executed the command ALTER DATABASE <DBNAME> SET TRUSTWORTHY ON to enable the trustworthy bit on the DR server.  They then tried the steps in KB Article 918040 and changed the database owner for the database on the DR server and then their CLR assembly began to work.

Well, at least it worked until they tried to failover to their original primary replica, and they again began to have problems with their CLR assembly.

Why would that be the case, especially since it originally worked on the primary replica before the DR failover?

It has to do with login SIDs in SQL Server and server-scoped permissions.  The database owner is mapped inside the database by the SID of the login on the server.  If the SID of the owner internally in the database doesn’t match a SID of a server principal on the server then the owner can’t be established.  The dbo SID internally in the database is replicated as a part of the AG, but the server login is not.  Also server scoped objects, like the asymmetric key used to sign the CLR assembly, are maintained in master, as is the login associated with that key and the EXTERNAL_ACCESS or UNSAFE ASSEMBLY permission associated with it.  So to fix this issue and get rid of the TRUSTWORTHY ON bit setting for the database they had to do the following steps:

  1. Create the asymmetric key from the assembly DLL on the DR server.
  2. Change the database owner to match the SID on both servers in sys.server_principals (script the dbo login using sp_help_revlogin to transfer with SID intact to both servers)
  3. Create login from asymmetric key on DR server and grant UNSAFE ASSEMBLY to match primary replica
  4. ALTER DATABASE <DBNAME> SET TRUSTWORTHY OFF
  5. Fail over to test between both sites

Bottom line: it’s *always* a good idea to regularly test your failover strategy as you never know what’s going to fail when you do! Kudos to this client for doing that.

Physical security

TSQL2sDay150x150_388014A5

This month’s T-SQL Tuesday (hosted by Kenneth Fisher – @sqlstudent144) is about security This hasn’t been my area of expertise for a long time, although I did write a long TechNet Magazine article about common security issues and solutions back in 2009.

There’s a huge amount that’s been written about implementing security in SQL Server and Windows – working towards the security of the data while it’s in the database, being sent to the client application, and within the client application. This can be incredibly important for your business and your clients and so the focus there is justifiable.

However, I think there’s an aspect to data security that’s often completely overlooked: physical security.

Consider the infrastructure in your environment, and ask yourself the following questions:

  • Are the servers running SQL Server physically secured so only authorized people have access to them? I’m not just talking about whether someone can walk out with a server under their arm (and then get the hard drives with the data on – the actual server hardware isn’t a physical security risk if there is no data storage in it), although this is something you should consider. I also want you to consider whether an unauthorized person can walk up to such a server and insert a USB drive that could have an auto-run program on it that installs some kind of security hole.
  • And what about if the server has server-local storage? An unauthorized person could grab a hard drive from a server and clone it really quickly, maybe overnight so no-one’s available onsite to see why the server went down. Here‘s a link on Amazon to a machine we use for quickly cloning laptop hard drives when we upgrade them. Really useful, but also useful in the hands of someone with nefarious aims.
  • Are the storage arrays where the data resides physically secured so only authorized people have access to them? And what about the routers? Here is a thread from the Dell support forums about making an MD3000i password reset cable from scratch. You don’t want someone to be able to physically reset the password on some storage array, and then make a connection to it from an unauthorized server on the network and gain access to the data on the drives. And then there’s the question of someone just popping out some of the drives and walking away with them…
  • Are there cameras monitoring all of the above?
  • For the questions above, now ask them about your failover data center. And what if you data center is hosted? Does the hoster guarantee physical security of your data?
  • Now let’s think about your admin users. What kind of physical security protects the desktops of the people with secure access to the data? Is it possible for them to walk away and leave their screen unlocked? Is it possible for someone to walk up to their computer and plug in a USB drive with some auto-run software on it?
  • Now let’s think about your admin users’ laptops. Same questions as above. What about if they take their laptops home? Or they use their own systems at home? Are they physically secured so someone can’t access your data from these people’s systems?

Still think your data is secure?

A SQL Server DBA myth a day: (4/30) DDL triggers are INSTEAD OF triggers

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)

Myth #4: DDL triggers (introduced in SQL Server 2005) are INSTEAD OF triggers.

FALSE

DDL triggers are implemented as AFTER triggers, which means the operation occurs and is then caught in the trigger (and optionally rolled-back, if you put a ROLLBACK statement in the trigger body).

This means they’re not quite as lightweight as you might think. Imagine doing the following:

ALTER TABLE MyBigTable ADD MyNewNonNullColumn VARCHAR (20) DEFAULT 'Paul';

If there’s a DDL trigger defined for ALTER_TABLE events, or maybe even something more restrictive like DDL_TABLE_EVENTS, every row in the table will be expanded to include the new column (as it has a non-null default), and then the trigger will fire and the operation is rolled back by your trigger body. Not ideal at all. (Try it yourself and look in the log with fn_dblog – you’ll see the operation rollback.)

What would be better in this case is to specifically GRANT or DENY the ALTER permission, or do something like only permitting DDL operations through stored-procedures that you create.

However, DDL triggers do allow you to effectively stop it happening, but in a relatively expensive way. And they do allow you to perform auditing of who did what, so I’m not saying they’re without use – just be careful.

Kimberly has a great post on DDL triggers at “EXECUTE AS” and an important update your DDL Triggers (for auditing or prevention).