SQL Server FILESTREAM with SQL Server Authentication

If you have an application that connects to SQL Server using SQL Server Authentication
And you want to use the FILESTREAM feature in your .NET application utilizing the SqlFileStream Class like below:

SqlFileStream sqlFileStream1 = new SqlFileStream(filePath1, transactionContext1, FileAccess.Write);

You will probably get an error saying
“Access is denied“

In all documentation it is mentioned that
“FILESTREAM requires integrated security. SQL Server authentication is not supported.”

But there is a workaround that will allow you still to use the SQL Authentication in your connection string.

To use the FILESTREAM in your application you need to do 2 steps:
First, you need to get from the DB the Logical Path Name and Transaction Context.

(Like a token or file handler)

FileStreamColumn.PathName() As [Path]
GET_FILESTREAM_TRANSACTION_CONTEXT() As TransactionContext

The second step, using the path and transaction context initialize the SqlFileStream object.

These both steps should be done in a transaction scope and under the same user.
Meaning, if the application runs under Windows Account “User1”
then the first step of getting the logical path and transaction context should

be also done with the same “User1”.

What we can do is to use impersonation in SQL Server.
If the application runs with Windows Account “User1”, 

we should create this Windows Account “User1” in SQL Server as Login and DB user.

Impersonate it as below and grant to it relevant permission to the table 

with FileStream columns and execute the relevant

Stored Procedures/SQL Queries with EXECUTE AS [User1]

Use Master
Go
GRANT IMPERSONATE ON LOGIN::[User1] TO SQL_Login_of_App;
Go

According to the POC we did, this workaround allows you to stay with SQL authentication in your connection string to a database. 

Note, the impersonation cannot be granted to a windows group, only to a single account.

Source reference:

https://www.sqlskills.com/blogs/bobb/using-filestream-streaming-io-and-sql-logins-with-impersonation/