{"id":4982,"date":"2019-04-02T13:02:16","date_gmt":"2019-04-02T20:02:16","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4982"},"modified":"2019-04-02T13:02:16","modified_gmt":"2019-04-02T20:02:16","slug":"the-curious-case-of-the-clr-assembly-failure-after-an-ag-failover","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-clr-assembly-failure-after-an-ag-failover\/","title":{"rendered":"The Curious Case of… the CLR assembly failure after an AG failover"},"content":{"rendered":"

(The Curious Case of\u2026<\/em>\u00a0used to be part of our bi-weekly\u00a0newsletter<\/a>\u00a0but we decided to make it a\u00a0regular 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.)<\/p>\n

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\u00a0testing their\u00a0AG disaster recovery\u00a0strategy 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. \u00a0When they failed over their AG to their DR server, the CLR assembly failed with the following error:<\/p>\n

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:\u00a0System.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<\/em><\/p>\n

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

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.<\/p>\n

Why would that be the case, especially since it originally worked on the primary replica before the DR failover?<\/p>\n

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

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

    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.<\/p>\n","protected":false},"excerpt":{"rendered":"

    (The Curious Case of\u2026\u00a0used to be part of our bi-weekly\u00a0newsletter\u00a0but we decided to make it a\u00a0regular 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 […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[110,79,115],"tags":[],"class_list":["post-4982","post","type-post","status-publish","format-standard","hentry","category-availability-groups","category-security","category-the-curious-case-of"],"yoast_head":"\nCLR assembly failure after an AG failover<\/title>\n<meta name=\"description\" content=\"CLR assembly failure after an AG failover\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-clr-assembly-failure-after-an-ag-failover\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"CLR assembly failure after an AG failover\" \/>\n<meta property=\"og:description\" content=\"CLR assembly failure after an AG failover\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-clr-assembly-failure-after-an-ag-failover\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2019-04-02T20:02:16+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-clr-assembly-failure-after-an-ag-failover\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-clr-assembly-failure-after-an-ag-failover\/\",\"name\":\"CLR assembly failure after an AG failover\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2019-04-02T20:02:16+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"description\":\"CLR assembly failure after an AG failover\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-clr-assembly-failure-after-an-ag-failover\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-clr-assembly-failure-after-an-ag-failover\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-clr-assembly-failure-after-an-ag-failover\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Curious Case of… the CLR assembly failure after an AG failover\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"CLR assembly failure after an AG failover","description":"CLR assembly failure after an AG failover","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-clr-assembly-failure-after-an-ag-failover\/","og_locale":"en_US","og_type":"article","og_title":"CLR assembly failure after an AG failover","og_description":"CLR assembly failure after an AG failover","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-clr-assembly-failure-after-an-ag-failover\/","og_site_name":"Paul S. Randal","article_published_time":"2019-04-02T20:02:16+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-clr-assembly-failure-after-an-ag-failover\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-clr-assembly-failure-after-an-ag-failover\/","name":"CLR assembly failure after an AG failover","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2019-04-02T20:02:16+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"description":"CLR assembly failure after an AG failover","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-clr-assembly-failure-after-an-ag-failover\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-clr-assembly-failure-after-an-ag-failover\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-clr-assembly-failure-after-an-ag-failover\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"The Curious Case of… the CLR assembly failure after an AG failover"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4982","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=4982"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4982\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4982"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4982"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4982"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}