{"id":857,"date":"2006-05-28T07:13:00","date_gmt":"2006-05-28T07:13:00","guid":{"rendered":"\/blogs\/bobb\/post\/New-SQLCLR-approved-assembly-in-SP1.aspx"},"modified":"2013-01-04T00:00:13","modified_gmt":"2013-01-04T08:00:13","slug":"new-sqlclr-approved-assembly-in-sp1","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-sqlclr-approved-assembly-in-sp1\/","title":{"rendered":"New &#8220;SQLCLR-approved&#8221; assembly in SP1"},"content":{"rendered":"<p>\nPeople often ask about the set of base class library assemblies that can be safely used in SQLCLR. It usually takes the form &quot;can I use assembly System.XYZ.dll in SQLCLR procedural code&quot; or &quot;why do I get &quot;assembly System.XYZ.dll is not found&quot; when I try and catalog my own assembly that calls this one? The ones is hear mentioned most frequently is System.DirectoryServices.dll (Active Directory support) or System.Management.dll (WMI support) or System.Remoting.dll et al. The only way you use these is to run CREATE ASSEMBLY on them yourself, which involves using&nbsp;PERMISSION_SET = UNSAFE. And cataloging all the dependencies. Not for the faint of heart.\n<\/p>\n<p>\nI explain that there is a hardcoded list of assemblies that SQL Server will load from the file system and these assemblies have undergone an extensive code review to ensure they don&#39;t destablize SQL Server when they are running in process. Since some assemblies contain both crucial and &quot;unsafe&quot; (to SQL Server) classes (mscorlib.dll is an example that comes to mind) these libraries must also be decorated with instances of HostProtectionAttribute. This attribute gives the host (SQL Server 2005, in this case) the final say over whether to run a method (e.g. Thread.Start in mscorlib.dll). SQL Server is very picky about when it will run an HPA decorated method, its only if the calling assembly is CREATEd with PERMISSION_SET = UNSAFE. There&#39;s more about this in chapter 2 of <a href=\"http:\/\/www.informit.com\/store\/developers-guide-to-sql-server-2005-9780321382184?rll=1\">A Developer&#39;s Guide to SQL Server 2005<\/a>.\n<\/p>\n<p>\nI refer to this list of assemblies as &quot;the approved list&quot;. You can see most of the &quot;approved list&quot; assemblies by creating a Visual Studio 2005 Database\/SQL Server project and choosing &quot;Add Reference&quot;. This is the entire list of approved assemblies with one exception. Right before RTM &quot;System.Configuration&quot; was added to the list. I&#39;m not entirely sure why, because although you can successfully use a .NET config file with SQL Server 2005 currently, it neither supported or encouraged (see <a href=\"\/blogs\/bobb\/#af8086242-8dba-4a77-9610-580c47d1e81d\">my blog articles <\/a>about this).\n<\/p>\n<p>\nWhy the long story? Because, in SQL Server 2005 SP1 another assembly made the list: System.Deployment.dll. I&#39;m rather puzzled by this one too, because it has to do almost entirely with click-once deployment. Not sure why someone would want a sproc to do this, unless it was used to tie SQL Server to .NET project deployment in conjunction with Visual Studio Team System. But, its in there. Hmmm&#8230;\n<\/p>\n<p>\nThe libraries that I&#39;d hoped would make the list eventually, perhaps in a SQL Server service pack when Windows Vista and WinFX are released, are the Windows Communication Foundation (System.ServiceModel.dll) and Windows Workflow Foundation (System.Workflow.Runtime.dll et al). I&#39;ve heard a lot of folks ask the same question, but so far there&#39;s been silence on this. Since System.WebServices (but not WSE) is aleady &quot;in there&quot;, there might be some uses going forward. As a look toward &quot;intent&quot;, I ran ILDASM against the just-released May CTP version of these. Not a HostProtectionAttribute in sight. Oh well.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>People often ask about the set of base class library assemblies that can be safely used in SQLCLR. It usually takes the form &quot;can I use assembly System.XYZ.dll in SQLCLR procedural code&quot; or &quot;why do I get &quot;assembly System.XYZ.dll is not found&quot; when I try and catalog my own assembly that calls this one? The [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38],"tags":[],"class_list":["post-857","post","type-post","status-publish","format-standard","hentry","category-sqlclr"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>New &quot;SQLCLR-approved&quot; assembly in SP1 - Bob Beauchemin<\/title>\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\/bobb\/new-sqlclr-approved-assembly-in-sp1\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"New &quot;SQLCLR-approved&quot; assembly in SP1 - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"People often ask about the set of base class library assemblies that can be safely used in SQLCLR. It usually takes the form &quot;can I use assembly System.XYZ.dll in SQLCLR procedural code&quot; or &quot;why do I get &quot;assembly System.XYZ.dll is not found&quot; when I try and catalog my own assembly that calls this one? The [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-sqlclr-approved-assembly-in-sp1\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2006-05-28T07:13:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-04T08:00:13+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\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\/bobb\/new-sqlclr-approved-assembly-in-sp1\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-sqlclr-approved-assembly-in-sp1\/\",\"name\":\"New \\\"SQLCLR-approved\\\" assembly in SP1 - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2006-05-28T07:13:00+00:00\",\"dateModified\":\"2013-01-04T08:00:13+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-sqlclr-approved-assembly-in-sp1\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-sqlclr-approved-assembly-in-sp1\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-sqlclr-approved-assembly-in-sp1\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQLCLR\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sqlclr\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"New &#8220;SQLCLR-approved&#8221; assembly in SP1\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"New \"SQLCLR-approved\" assembly in SP1 - Bob Beauchemin","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\/bobb\/new-sqlclr-approved-assembly-in-sp1\/","og_locale":"en_US","og_type":"article","og_title":"New \"SQLCLR-approved\" assembly in SP1 - Bob Beauchemin","og_description":"People often ask about the set of base class library assemblies that can be safely used in SQLCLR. It usually takes the form &quot;can I use assembly System.XYZ.dll in SQLCLR procedural code&quot; or &quot;why do I get &quot;assembly System.XYZ.dll is not found&quot; when I try and catalog my own assembly that calls this one? The [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-sqlclr-approved-assembly-in-sp1\/","og_site_name":"Bob Beauchemin","article_published_time":"2006-05-28T07:13:00+00:00","article_modified_time":"2013-01-04T08:00:13+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-sqlclr-approved-assembly-in-sp1\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-sqlclr-approved-assembly-in-sp1\/","name":"New \"SQLCLR-approved\" assembly in SP1 - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2006-05-28T07:13:00+00:00","dateModified":"2013-01-04T08:00:13+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-sqlclr-approved-assembly-in-sp1\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/new-sqlclr-approved-assembly-in-sp1\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-sqlclr-approved-assembly-in-sp1\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"SQLCLR","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sqlclr\/"},{"@type":"ListItem","position":3,"name":"New &#8220;SQLCLR-approved&#8221; assembly in SP1"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/857","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=857"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/857\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=857"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=857"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=857"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}