Why my cat can’t use a SQLCLR proc to read files

I'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
 WITH IDENTITY = 'mydomain\myuser', SECRET = 'some56*Z'
GO

CREATE LOGIN sam WITH PASSWORD = 'meowPw!a3'
GO

ALTER LOGIN sam WITH CREDENTIAL = myuser
GO

The DDL works. Now, I'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.

Just 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.

So 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't do impersonation. He'll have to walk on the keyboard until he opens the file. As usual.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.