Using filestream, streaming I/O and SQL logins with impersonation

A number of months ago, I wrote an article using the SQL Server filestream feature programmatically. I've gotten a number of inquiries since from folks who *need* to login to SQL Server using SQL logins (often the same SQL login for everyone), but want to access the stream using streaming I/O (e.g. SqlFileStream data type). This doesn't ordinarily work, but how about using impersonation in SQL Server? 

I have a SQL login named "bob". And a Windows login named "ZMV03\Mary". Mary is not a Windows Administrator. Neither one is sysadmin in SQL Server. Added users for each one to the filestream-enabled database with appropriate permissions. In the master database, I granted bob impersonate privilege.

GRANT IMPERSONATE on LOGIN::[zmv03\Mary] to bob;

In the ADO.NET program, I impersonate the login before accessing the getting the transaction context and PathName. The rest of the program looks the same.

SqlConnection string contains Bob's credentials. Then:

command.CommandText = @"execute as login ='zmv03\Mary';select Top(1) Photo.PathName(),GET_FILESTREAM_TRANSACTION_CONTEXT () from employees2;revert";

And the streaming I/O using SqlFileStream and the transaction token works. Some cavaets:
 You need to impersonate the login, not the user correspoinding to the login.
 You can't use "execute as login=" in a CREATE PROCEDURE statement, but you can use it inside a stored procedure.
 You need to use the same Windows login with the SQL impersonation as with the client code. With ASP.NET for example, that would be the user running the ASP.NET worker process/app pool. You can't do the SQL impersonation using any old Windows login.
 Note that you can even revert the impersonation before accesing the stream. And that's probably a good idea.

And about the setup, I used:
 Local console app, running as zmv03\Mary, which is an individual, not a Windows group, login. Local means console app is running on same machine as SQL Server.
 SQL Server 2012. Database is defined with non_transacted_mode = off.  Machine was not a part of a domain.

Just listed these for sanity reasons. I'm not talking about filetable, but filestream. You can use transacted access with filetable, but normally you'd use non-transacted and open the file directly, using the network share. If this breaks using different conditions, I'd like to hear about it. I read email more often that I look for blog comments. This may be spec'd to work somewhere but I couldn't find it.

Also, I've been unsuccessful in using SQLCLR, filestream, and streaming I/O with the context connection. If you really, really want to do this, maybe using a real connection and external access might work. I didn't have time to try this, and am not sure if it would be particularly useful. But you would have to be running the SQLCLR proc as the exact (Windows login) that does the impersonation, and craft your SqlCommand text accordingly.

Also in case you confused this discussion with the error message "Either a required impersonation level was not provided, or the provided impersonation level is invalid." with older OSes, I'm not talking about that, but there is a fix for it. See this KB entry. The error that you get trying to access the stream after using SQL login and GET_FILESTREAM_TRANSACTION_CONTEXT is "Access denied".

@bobbeauch

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.