{"id":767,"date":"2007-09-19T18:22:00","date_gmt":"2007-09-19T18:22:00","guid":{"rendered":"\/blogs\/bobb\/post\/SQLCLR-and-system-functionality-in-SQL-Server-2008-part-2.aspx"},"modified":"2014-01-20T12:14:17","modified_gmt":"2014-01-20T20:14:17","slug":"sqlclr-and-system-functionality-in-sql-server-2008-part-2","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-and-system-functionality-in-sql-server-2008-part-2\/","title":{"rendered":"SQLCLR and system functionality in SQL Server 2008 – part 2"},"content":{"rendered":"

\nI'm running with SQLCLR on, because I'd like to see the interaction between system SQLCLR code and appdomains. See the previous blog posting for an explanation of why this doesn't affect my system functions. First, I bring up SQL Server 2008 "fresh", open SQL log in SSMS, and start a profiler trace to catch Assembly Loading events. SQLCLR is nowhere to be seen.\n<\/p>\n

\nUSE TEMPDB
\nGO\n<\/p>\n

\nSELECT * FROM sys.assemblies;
\nGO\n<\/p>\n

\nThe assembly list contains an entry for Microsoft.SqlServer.Types, that's the assembly that contains the system UDTs. It actually lives in the resource database<\/a>, but shows up in system metadata lists for every database. In the SQL Server log, just listing the assemblies in a database produces:\n<\/p>\n

\nCommon language runtime (CLR) functionality initialized using…
\nAppDomain 2 (32767.sys[runtime].1) created\n<\/p>\n

\nDatabase 32767 is the resource database, although its not for the most part directly visible in SQL Server 2005 metadata or in SSMS.\n<\/p>\n

\n.NET appdomains are created on a per-database basis, currently one appdomain per assembly owner. So this functionality runs under an appdomain created for the owner "sys". The assembly is actually owned by principal_id 4, which is INFORMATION_SCHEMA according to sys.database_principals. But sys.schemas indicates that the sys schema is owned by principal_id 4 as well, INFORMATION_SCHEMA schema is owned by principal 3. They're likely referring to principal_id in the resource database, not the current database. Sys.assemblies also has this assembly marked as is_user_defined = 0 (false). And, the assembly has a safety level of UNSAFE. Hmmm…\n<\/p>\n

\nKnowing how SQLCLR exception escalation works, I was concerned by the system assembly being UNSAFE. It is  running in its own appdomain. However, when I looked at the SQL Server log for the SQLCLR message, another seemingly unrelated message attracted my attention:\n<\/p>\n

\nUsing xpstar.dll version 2007.100.1049 to execute extended stored procedure xp_instance_regread…\n<\/p>\n

\nSQL Server internals have always included extended stored procedures to perform system functions. User-written extended stored procedures can cause problems if poorly written, but this one (xp_instance_regread) was written by the SQL Server team, its part of SQL Server itself. Hmmm…so is Microsoft.SqlServer.Types part of SQL Server. And .NET code has more built in safeguards than unmanaged code. And BOL has indicated since SQL Server 2005 betas:\n<\/p>\n

\n"This feature (i.e. extended stored procedures) will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CLR Integration instead."\n<\/p>\n

\nSo I guess its OK, they're taking their own advise. And this assembly has been tested with SQL Server for hardening, it doesn't produce the error message that cataloging, say, System.Runtime.Remoting, does.\n<\/p>\n

\nOne final item. SQL Profiler does not load Microsoft.SqlServer.Types.dll just because I execute "select * from sys.assemblies", but profiler reports:\n<\/p>\n

\nAssembly Load Failed for requested assembly system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86 with Error code: 0x80070002\n<\/p>\n

\nPerhaps there's a stray assembly dependency somewhere in CTP4. System.EnterpriseServices is not on the list of tested assemblies either, so it shouldn't load automatically.<\/p>\n","protected":false},"excerpt":{"rendered":"

I'm running with SQLCLR on, because I'd like to see the interaction between system SQLCLR code and appdomains. See the previous blog posting for an explanation of why this doesn't affect my system functions. First, I bring up SQL Server 2008 "fresh", open SQL log in SSMS, and start a profiler trace to catch Assembly […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29,38],"tags":[],"class_list":["post-767","post","type-post","status-publish","format-standard","hentry","category-sql-server-2008","category-sqlclr"],"yoast_head":"\nSQLCLR and system functionality in SQL Server 2008 - part 2 - 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\/sqlclr-and-system-functionality-in-sql-server-2008-part-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLCLR and system functionality in SQL Server 2008 - part 2 - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"I'm running with SQLCLR on, because I'd like to see the interaction between system SQLCLR code and appdomains. See the previous blog posting for an explanation of why this doesn't affect my system functions. First, I bring up SQL Server 2008 "fresh", open SQL log in SSMS, and start a profiler trace to catch Assembly […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-and-system-functionality-in-sql-server-2008-part-2\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2007-09-19T18:22:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-01-20T20:14:17+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\/sqlclr-and-system-functionality-in-sql-server-2008-part-2\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-and-system-functionality-in-sql-server-2008-part-2\/\",\"name\":\"SQLCLR and system functionality in SQL Server 2008 - part 2 - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2007-09-19T18:22:00+00:00\",\"dateModified\":\"2014-01-20T20:14:17+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-and-system-functionality-in-sql-server-2008-part-2\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-and-system-functionality-in-sql-server-2008-part-2\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-and-system-functionality-in-sql-server-2008-part-2\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2008\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2008\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"SQLCLR and system functionality in SQL Server 2008 – part 2\"}]},{\"@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":"SQLCLR and system functionality in SQL Server 2008 - part 2 - 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\/sqlclr-and-system-functionality-in-sql-server-2008-part-2\/","og_locale":"en_US","og_type":"article","og_title":"SQLCLR and system functionality in SQL Server 2008 - part 2 - Bob Beauchemin","og_description":"I'm running with SQLCLR on, because I'd like to see the interaction between system SQLCLR code and appdomains. See the previous blog posting for an explanation of why this doesn't affect my system functions. First, I bring up SQL Server 2008 "fresh", open SQL log in SSMS, and start a profiler trace to catch Assembly […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-and-system-functionality-in-sql-server-2008-part-2\/","og_site_name":"Bob Beauchemin","article_published_time":"2007-09-19T18:22:00+00:00","article_modified_time":"2014-01-20T20:14:17+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\/sqlclr-and-system-functionality-in-sql-server-2008-part-2\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-and-system-functionality-in-sql-server-2008-part-2\/","name":"SQLCLR and system functionality in SQL Server 2008 - part 2 - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2007-09-19T18:22:00+00:00","dateModified":"2014-01-20T20:14:17+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-and-system-functionality-in-sql-server-2008-part-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-and-system-functionality-in-sql-server-2008-part-2\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-and-system-functionality-in-sql-server-2008-part-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2008","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2008\/"},{"@type":"ListItem","position":3,"name":"SQLCLR and system functionality in SQL Server 2008 – part 2"}]},{"@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\/767","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=767"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/767\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=767"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=767"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=767"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}