{"id":925,"date":"2005-08-03T16:51:00","date_gmt":"2005-08-03T16:51:00","guid":{"rendered":"\/blogs\/bobb\/post\/Why-my-cat-cant-use-a-SQLCLR-proc-to-read-files.aspx"},"modified":"2005-08-03T16:51:00","modified_gmt":"2005-08-03T16:51:00","slug":"why-my-cat-cant-use-a-sqlclr-proc-to-read-files","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/why-my-cat-cant-use-a-sqlclr-proc-to-read-files\/","title":{"rendered":"Why my cat can&#8217;t use a SQLCLR proc to read files"},"content":{"rendered":"<p>\nI&#39;m known for my vivid imagination when making up test\/exposition examples. I have a cat named Sam. So, once upon a time, I wrote:\n<\/p>\n<p>\nCREATE CREDENTIAL myuser <br \/>\n&nbsp;WITH IDENTITY = &#39;mydomain\\myuser&#39;, SECRET = &#39;some56*Z&#39;<br \/>\nGO\n<\/p>\n<p>\nCREATE LOGIN sam WITH PASSWORD = &#39;meowPw!a3&#39;<br \/>\nGO\n<\/p>\n<p>\nALTER LOGIN sam WITH CREDENTIAL = myuser<br \/>\nGO\n<\/p>\n<p>\nThe DDL works. Now, I&#39;d hoped to use this alternate credential so that Sam (a SQL Server login) could use the credential to use an external_access SQLCLR procedure that reads a file on the file system. This would require (since we have a nice NTFS file system with ACLs), that the SQLCLR procedure use the WindowsIdentity property on SqlPipe and do the impersonation. Works with Windows users, now Sam could do it too. I thought.\n<\/p>\n<p>\nJust lately I found out that the alternate credential will not be useable with SQLCLR. WindowsIdentity will return null for Sam, regardless. This credential is useable with SQL Agent, something folks have always wanted for SQL Agent.\n<\/p>\n<p>\nSo no file system access for Sam, at least through SQLCLR and CREDENTIAL object. Unless the SQL Server service account has access to it and I don&#39;t do impersonation. He&#39;ll have to walk on the keyboard until he opens the file. As usual.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#39;m known for my vivid imagination when making up test\/exposition examples. I have a cat named Sam. So, once upon a time, I wrote: CREATE CREDENTIAL myuser &nbsp;WITH IDENTITY = &#39;mydomain\\myuser&#39;, SECRET = &#39;some56*Z&#39; GO CREATE LOGIN sam WITH PASSWORD = &#39;meowPw!a3&#39; GO ALTER LOGIN sam WITH CREDENTIAL = myuser GO The DDL works. Now, [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,28,38],"tags":[],"class_list":["post-925","post","type-post","status-publish","format-standard","hentry","category-security","category-sql-server-2005","category-sqlclr"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Why my cat can&#039;t use a SQLCLR proc to read files - 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\/why-my-cat-cant-use-a-sqlclr-proc-to-read-files\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Why my cat can&#039;t use a SQLCLR proc to read files - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"I&#039;m known for my vivid imagination when making up test\/exposition examples. I have a cat named Sam. So, once upon a time, I wrote: CREATE CREDENTIAL myuser &nbsp;WITH IDENTITY = &#039;mydomainmyuser&#039;, SECRET = &#039;some56*Z&#039; GO CREATE LOGIN sam WITH PASSWORD = &#039;meowPw!a3&#039; GO ALTER LOGIN sam WITH CREDENTIAL = myuser GO The DDL works. Now, [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/why-my-cat-cant-use-a-sqlclr-proc-to-read-files\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2005-08-03T16:51:00+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=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/why-my-cat-cant-use-a-sqlclr-proc-to-read-files\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/why-my-cat-cant-use-a-sqlclr-proc-to-read-files\/\",\"name\":\"Why my cat can't use a SQLCLR proc to read files - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2005-08-03T16:51:00+00:00\",\"dateModified\":\"2005-08-03T16:51:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/why-my-cat-cant-use-a-sqlclr-proc-to-read-files\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/why-my-cat-cant-use-a-sqlclr-proc-to-read-files\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/why-my-cat-cant-use-a-sqlclr-proc-to-read-files\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Security\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/security\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Why my cat can&#8217;t use a SQLCLR proc to read files\"}]},{\"@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":"Why my cat can't use a SQLCLR proc to read files - 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\/why-my-cat-cant-use-a-sqlclr-proc-to-read-files\/","og_locale":"en_US","og_type":"article","og_title":"Why my cat can't use a SQLCLR proc to read files - Bob Beauchemin","og_description":"I&#39;m known for my vivid imagination when making up test\/exposition examples. I have a cat named Sam. So, once upon a time, I wrote: CREATE CREDENTIAL myuser &nbsp;WITH IDENTITY = &#39;mydomainmyuser&#39;, SECRET = &#39;some56*Z&#39; GO CREATE LOGIN sam WITH PASSWORD = &#39;meowPw!a3&#39; GO ALTER LOGIN sam WITH CREDENTIAL = myuser GO The DDL works. Now, [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/why-my-cat-cant-use-a-sqlclr-proc-to-read-files\/","og_site_name":"Bob Beauchemin","article_published_time":"2005-08-03T16:51:00+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/why-my-cat-cant-use-a-sqlclr-proc-to-read-files\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/why-my-cat-cant-use-a-sqlclr-proc-to-read-files\/","name":"Why my cat can't use a SQLCLR proc to read files - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2005-08-03T16:51:00+00:00","dateModified":"2005-08-03T16:51:00+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/why-my-cat-cant-use-a-sqlclr-proc-to-read-files\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/why-my-cat-cant-use-a-sqlclr-proc-to-read-files\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/why-my-cat-cant-use-a-sqlclr-proc-to-read-files\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Security","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/security\/"},{"@type":"ListItem","position":3,"name":"Why my cat can&#8217;t use a SQLCLR proc to read files"}]},{"@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\/925","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=925"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/925\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=925"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=925"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=925"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}