{"id":2721,"date":"2020-01-30T12:46:00","date_gmt":"2020-01-30T09:46:00","guid":{"rendered":"https:\/\/www.ozgurguler.net\/blog\/?p=2721"},"modified":"2026-09-23T10:30:16","modified_gmt":"2026-09-23T07:30:16","slug":"sql-2017-clr-strict-security-hatasi","status":"publish","type":"post","link":"https:\/\/www.ozgurguler.net\/blog\/sql-2017-clr-strict-security-hatasi\/","title":{"rendered":"SQL 2017 Clr strict security hatas\u0131"},"content":{"rendered":"\r\n

SQL Server 2017 “CLR Strict Security” Hatas\u0131 \u2014 Kal\u0131c\u0131 \u00c7\u00f6z\u00fcm<\/span><\/h1>\r\n

K\u0131sa \u00f6zet:<\/span><\/strong> SQL Server 2017 ile birlikte Microsoft, CLR assembly’lerini varsay\u0131lan olarak <\/span>engelleyen<\/span><\/strong> yeni bir g\u00fcvenlik ayar\u0131 getirdi (<\/span>clr strict security = 1<\/code>). Bu y\u00fczden daha \u00f6nce sorunsuz \u00e7al\u0131\u015fan CLR tabanl\u0131 uygulamalar (Mikro, Logo, Netsis vb.) SQL 2017’ye ge\u00e7i\u015fte hata vermeye ba\u015flar. A\u015fa\u011f\u0131daki ad\u0131mlar sorunu kal\u0131c\u0131 olarak \u00e7\u00f6zer.<\/span><\/p>\r\n

\r\n

Belirtiler<\/span><\/h2>\r\n

Bu hatayla kar\u015f\u0131la\u015f\u0131yorsan\u0131z do\u011fru yerdesiniz:<\/span><\/p>\r\n

    \r\n
  • \r\n

    CREATE ASSEMBLY failed because assembly ... is not trusted<\/code><\/p>\r\n<\/li>\r\n

  • \r\n

    The assembly ... could not be loaded because it is not authorized (CLR strict security)<\/code><\/p>\r\n<\/li>\r\n

  • \r\n

    Mikro\/ERP uygulamas\u0131 a\u00e7\u0131l\u0131\u015fta CLR kaynakl\u0131 hata veriyor<\/span><\/p>\r\n<\/li>\r\n

  • \r\n

    SQL 2016’da \u00e7al\u0131\u015fan bir uygulama SQL 2017’ye ge\u00e7ince bozuldu<\/span><\/p>\r\n<\/li>\r\n<\/ul>\r\n

    \r\n

    Neden Oluyor?<\/span><\/h2>\r\n

    SQL Server 2017’den itibaren <\/span>clr strict security<\/code> ayar\u0131 <\/span>varsay\u0131lan olarak 1 (a\u00e7\u0131k)<\/span><\/strong> gelir. Bu ayar a\u00e7\u0131kken:<\/span><\/p>\r\n

      \r\n
    • \r\n

      \u0130mzas\u0131z CLR assembly’leri \u00e7al\u0131\u015ft\u0131r\u0131lamaz<\/span><\/p>\r\n<\/li>\r\n

    • \r\n

      SAFE<\/code> olarak i\u015faretlenmi\u015f olsa bile assembly g\u00fcvenilir say\u0131lmaz<\/span><\/p>\r\n<\/li>\r\n

    • \r\n

      Eski uygulamalar\u0131n CLR bile\u015fenleri devre d\u0131\u015f\u0131 kal\u0131r<\/span><\/p>\r\n<\/li>\r\n<\/ul>\r\n

      \u00c7\u00f6z\u00fcm iki katmanl\u0131d\u0131r: <\/span>sunucu seviyesi ayar<\/span><\/strong> + <\/span>veritaban\u0131 seviyesi g\u00fcven<\/span><\/strong>.<\/span><\/p>\r\n

      \r\n

      \u00c7\u00f6z\u00fcm<\/span><\/h2>\r\n

      Ad\u0131m 1 \u2014 Sunucu Seviyesi Ayarlar<\/span><\/h3>\r\n

      SSMS’te <\/span>master<\/code> veritaban\u0131na ba\u011fl\u0131yken a\u015fa\u011f\u0131daki blo\u011fu <\/span>tek seferde<\/span><\/strong> \u00e7al\u0131\u015ft\u0131r\u0131n:<\/span><\/p>\r\n

      \r\n
      \r\n
      \r\n
      \r\n
      sql<\/span><\/div>\r\n<\/div>\r\n<\/div>\r\n<\/div>\r\n
      -- Geli\u015fmi\u015f se\u00e7enekleri a\u00e7 (clr strict security'yi g\u00f6rebilmek i\u00e7in gerekli)<\/span>\r\nEXEC<\/span> sp_configure 'show advanced options'<\/span>,<\/span> 1<\/span>;<\/span>\r\nRECONFIGURE<\/span>;<\/span>\r\nGO\r\n\r\n-- CLR'yi etkinle\u015ftir<\/span>\r\nEXEC<\/span> sp_configure 'clr enabled'<\/span>,<\/span> 1<\/span>;<\/span>\r\nRECONFIGURE<\/span>;<\/span>\r\nGO\r\n\r\n-- SQL 2017'nin kat\u0131 g\u00fcvenlik k\u0131s\u0131tlamas\u0131n\u0131 kapat<\/span>\r\nEXEC<\/span> sp_configure 'clr strict security'<\/span>,<\/span> 0<\/span>;<\/span>\r\nRECONFIGURE<\/span>;<\/span>\r\nGO\r\n\r\n-- Geli\u015fmi\u015f se\u00e7enekleri tekrar kapat (temizlik, opsiyonel)<\/span>\r\nEXEC<\/span> sp_configure 'show advanced options'<\/span>,<\/span> 0<\/span>;<\/span>\r\nRECONFIGURE<\/span>;<\/span>\r\nGO<\/pre>\r\n<\/div>\r\n
      \r\n
      \u26a0\ufe0f <\/span>Bu ad\u0131mdan sonra SQL Server servisini mutlaka yeniden ba\u015flat\u0131n.<\/span><\/strong> Aksi halde <\/span>clr strict security<\/code> de\u011fi\u015fikli\u011fi tam olarak devreye girmez.<\/span><\/p>\r\n<\/blockquote>\r\n
      Servisi yeniden ba\u015flatmak i\u00e7in:<\/span><\/strong><\/p>\r\n
      \r\n
      \r\n
      \r\n
      \r\n
      powershell<\/span><\/div>\r\n<\/div>\r\n<\/div>\r\n<\/div>\r\n
      Restart-Service<\/span> -<\/span>Name MSSQLSERVER<\/pre>\r\n<\/div>\r\n
      (Named instance kullan\u0131yorsan\u0131z servis ad\u0131 <\/span>MSSQL$InstanceAd\u0131<\/code> \u015feklindedir.)<\/span><\/p>\r\n
      \r\n
      Ad\u0131m 2 \u2014 Veritaban\u0131 Seviyesi G\u00fcven<\/span><\/h3>\r\n
      Sunucu ayarlar\u0131 tek ba\u015f\u0131na yetmezse, ilgili veritaban\u0131n\u0131 “g\u00fcvenilir” olarak i\u015faretlemek gerekir.<\/span><\/p>\r\n
      \r\n
      \ud83d\udd34 <\/span>MikroDB_V15_XXX<\/code> yazan yeri <\/span>kendi veritaban\u0131 ad\u0131n\u0131zla<\/span><\/strong> de\u011fi\u015ftirin.<\/span>
      \ud83d\udd34 <\/span>SRV\\Administrator<\/code> yazan yeri <\/span>kendi SQL login’inizle<\/span><\/strong> de\u011fi\u015ftirin.<\/span><\/p>\r\n<\/blockquote>\r\n
      \r\n
      \r\n
      \r\n
      \r\n
      sql<\/span><\/div>\r\n<\/div>\r\n<\/div>\r\n<\/div>\r\n
      -- Veritaban\u0131n\u0131 g\u00fcvenilir olarak i\u015faretle<\/span>\r\nALTER<\/span> DATABASE<\/span> MikroDB_V15_XXX SET<\/span> TRUSTWORTHY ON<\/span>;<\/span>\r\nGO\r\n\r\n-- Veritaban\u0131 sahibini yetkili bir login yap<\/span>\r\n-- (sp_changedbowner SQL 2012+ s\u00fcr\u00fcmlerde kullan\u0131mdan kald\u0131r\u0131ld\u0131, a\u015fa\u011f\u0131daki daha do\u011fru)<\/span>\r\nALTER<\/span> AUTHORIZATION<\/span> ON<\/span> DATABASE<\/span>::MikroDB_V15_XXX TO<\/span> [<\/span>SRV\\Administrator]<\/span>;<\/span>\r\nGO<\/pre>\r\n<\/div>\r\n
      \r\n
      Do\u011frulama<\/span><\/h2>\r\n
      Ayarlar\u0131n ger\u00e7ekten uyguland\u0131\u011f\u0131n\u0131 kontrol edin:<\/span><\/p>\r\n
      \r\n
      \r\n
      \r\n
      \r\n
      sql<\/span><\/div>\r\n<\/div>\r\n<\/div>\r\n<\/div>\r\n
      EXEC<\/span> sp_configure 'clr enabled'<\/span>;<\/span>           -- value_in_use = 1 olmal\u0131<\/span>\r\nEXEC<\/span> sp_configure 'clr strict security'<\/span>;<\/span>   -- value_in_use = 0 olmal\u0131<\/span>\r\nGO\r\n\r\nSELECT<\/span> name,<\/span> is_trustworthy_on\r\nFROM<\/span> sys.<\/span>databases<\/span>\r\nWHERE<\/span> name =<\/span> 'MikroDB_V15_XXX'<\/span>;<\/span>            -- is_trustworthy_on = 1 olmal\u0131<\/span>\r\nGO<\/pre>\r\n<\/div>\r\n
      \u00dc\u00e7 de\u011fer de bekledi\u011finiz gibi \u00e7\u0131k\u0131yorsa sorun \u00e7\u00f6z\u00fclm\u00fc\u015ft\u00fcr.<\/span><\/p>\r\n
      \r\n
      S\u0131k Yap\u0131lan Hatalar<\/span><\/h2>\r\n
      \r\n
      \r\n
      \u00a0<\/div>\r\n
      \u00a0<\/div>\r\n<\/div>\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n
      Hata<\/span><\/th>\r\nSonu\u00e7<\/span><\/th>\r\n<\/tr>\r\n<\/thead>\r\n
      SQL servisini yeniden ba\u015flatmamak<\/span><\/td>\r\nclr strict security = 0<\/code> g\u00f6r\u00fcnse de etkili olmaz<\/span><\/td>\r\n<\/tr>\r\n
      Veritaban\u0131 ad\u0131n\u0131 de\u011fi\u015ftirmeyi unutmak<\/span><\/td>\r\nALTER DATABASE<\/code> komutu hata verir<\/span><\/td>\r\n<\/tr>\r\n
      TRUSTWORTHY ON<\/code> yapmay\u0131 atlamak<\/span><\/td>\r\nAssembly yine “not trusted” hatas\u0131 verir<\/span><\/td>\r\n<\/tr>\r\n
      Yanl\u0131\u015f login’e sahiplik vermek<\/span><\/td>\r\nCLR \u00e7a\u011fr\u0131lar\u0131 yetki hatas\u0131 verir<\/span><\/td>\r\n<\/tr>\r\n
      Sadece <\/span>clr enabled<\/code> a\u00e7\u0131p <\/span>clr strict security<\/code>‘yi unutmak<\/span><\/td>\r\nEn s\u0131k yap\u0131lan hata \u2014 as\u0131l sorun bu ayardad\u0131r<\/span><\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n<\/div>\r\n
      \r\n
      G\u00fcvenlik Notu<\/span><\/h2>\r\n
      clr strict security = 0<\/code> ve <\/span>TRUSTWORTHY ON<\/code> ayarlar\u0131, SQL Server’\u0131n CLR i\u00e7in getirdi\u011fi ek g\u00fcvenlik katman\u0131n\u0131 <\/span>devre d\u0131\u015f\u0131 b\u0131rak\u0131r<\/span><\/strong>. Bu ayarlar:<\/span><\/p>\r\n
        \r\n
      • \r\n
        \u2705 K\u0131sa vadede uygulaman\u0131z\u0131 \u00e7al\u0131\u015ft\u0131r\u0131r<\/span><\/p>\r\n<\/li>\r\n
      • \r\n
        \u26a0\ufe0f Uzun vadede daha az g\u00fcvenli bir yap\u0131land\u0131rma demektir<\/span><\/p>\r\n<\/li>\r\n<\/ul>\r\n
        Daha g\u00fcvenli alternatif:<\/span><\/strong> Assembly’leri bir sertifika ile imzalamak ve sertifikay\u0131 <\/span>UNSAFE ASSEMBLY<\/code> yetkisiyle bir login’e ba\u011flamak. Ancak bu, uygulama \u00fcreticisinin (Mikro vb.) imzal\u0131 assembly da\u011f\u0131tmas\u0131n\u0131 gerektirir. Uygulama \u00fcreticiniz imzal\u0131 s\u00fcr\u00fcm sunmuyorsa yukar\u0131daki \u00e7\u00f6z\u00fcm pratikte tek se\u00e7enektir.<\/span><\/p>\r\n
        \u00d6neri:<\/span><\/strong> Bu ayarlar\u0131 yaln\u0131zca uygulamay\u0131 \u00e7al\u0131\u015ft\u0131ran sunucuda uygulay\u0131n; m\u00fcmk\u00fcnse bu sunucuyu di\u011fer i\u015f y\u00fcklerinden izole edin.<\/span><\/p>\r\n
        \r\n
        \u00d6zet<\/span><\/h2>\r\n
          \r\n
        1. \r\n
          show advanced options<\/code> \u2192 <\/span>1<\/code><\/p>\r\n<\/li>\r\n
        2. \r\n
          clr enabled<\/code> \u2192 <\/span>1<\/code><\/p>\r\n<\/li>\r\n
        3. \r\n
          clr strict security<\/code> \u2192 <\/span>0<\/code><\/p>\r\n<\/li>\r\n
        4. \r\n
          SQL Server servisini yeniden ba\u015flat<\/span><\/strong><\/p>\r\n<\/li>\r\n
        5. \r\n
          H\u00e2l\u00e2 \u00e7al\u0131\u015fm\u0131yorsa: <\/span>TRUSTWORTHY ON<\/code> + veritaban\u0131 sahibini ayarla<\/span><\/p>\r\n<\/li>\r\n<\/ol>\r\n
          \u00c7o\u011fu durumda 1-4 aras\u0131 ad\u0131mlar sorunu \u00e7\u00f6zer. 5. ad\u0131m yaln\u0131zca eski\/yetki sorunlu veritabanlar\u0131nda gerekir.<\/span><\/p>\r\n
          Bu not, SQL Server 2017’ye ge\u00e7i\u015fte CLR tabanl\u0131 ERP uygulamalar\u0131nda ya\u015fanan “strict security” hatas\u0131 i\u00e7in haz\u0131rlanm\u0131\u015ft\u0131r.\u00a0<\/span><\/em><\/p>\r\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":4,"featured_media":2722,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,243,10],"tags":[],"class_list":["post-2721","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mikro","category-one-cikan","category-sql-server-tsql"],"_links":{"self":[{"href":"https:\/\/www.ozgurguler.net\/blog\/wp-json\/wp\/v2\/posts\/2721","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ozgurguler.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ozgurguler.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ozgurguler.net\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ozgurguler.net\/blog\/wp-json\/wp\/v2\/comments?post=2721"}],"version-history":[{"count":15,"href":"https:\/\/www.ozgurguler.net\/blog\/wp-json\/wp\/v2\/posts\/2721\/revisions"}],"predecessor-version":[{"id":4672,"href":"https:\/\/www.ozgurguler.net\/blog\/wp-json\/wp\/v2\/posts\/2721\/revisions\/4672"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.ozgurguler.net\/blog\/wp-json\/wp\/v2\/media\/2722"}],"wp:attachment":[{"href":"https:\/\/www.ozgurguler.net\/blog\/wp-json\/wp\/v2\/media?parent=2721"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ozgurguler.net\/blog\/wp-json\/wp\/v2\/categories?post=2721"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ozgurguler.net\/blog\/wp-json\/wp\/v2\/tags?post=2721"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}