SQl Server Filestream security access levels

Mark Cooter
Mark Cooter used Ask the Experts™
on
I have a native C++ application that uses SQL Server 2012 with filestream.  It uses win32 API calls for streaming access e.g. OpenFileStream() to Save/Load the data from the database(s)/filestream.  In the past we have granted sysadmin privileges to SQL logins as we control the SQL instance.  
I would now like to restrict the SQL privileges given to the SQL logins to the least possible.  I have tried SQL Authentication and also Application Roles but neither allow the use of the Win32 API calls to filestream.  Can anyone suggest how I can continue to use the API streaming calls and restrict the SQL access?  My users need to be able a read/write to the application databases and also create, delete, backup, restore user databases.  Many thanks for any help on this....
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
hmm, not sure that I understand your problem..

Why not using integrated authentication? Cause otherwise you will need to manage NTFS permissions and SQL Server permissions for your restore/backup scenarios, which is more complex than using integrated security.

What breaks in your code, if you restrict the permissions? What does "user databases" mean in this context?

Author

Commented:
Calling OpenFileStream() returns 'Access Denied' message when I use SQL Authentication or Windows Authentication (integrated Security) with an Application Role.  Unless I give my login sysadmin privileges then the filestream access call will not work.  How can I restrict the privileges and still use filestream via the win32 api calls?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial