Processing SQL Server FILESTREAM Data: Part 2 - The Setup

Bob McGowan

5.00/5 (3 votes)

Sep 22, 2014

CPOL

4 min read

12091

Utilizing SQL Server FILESTREAM capabilities from .NET

In Part 1 of this topic, I discussed the reasoning behind the decision to use Microsoft's FILESTREAM technology for a recent client project. In this installment, I discuss the setup portion of this on the SQL Server side. I'll spare you much of the swing-and-a-miss frustration while attempting to understand how the parts work, but I'll try to pinpoint the traps that I located the hard way.

Stream of Consciousness

The first step is to ensure that SQL Server's FILESTREAM technology is enabled for the instance in which you're working. This isn't too difficult to configure, but there is a portion of it that might be confusing.

In SQL Server Configuration Manager, you will be presented with a list of SQL Server services that have been installed. Double click the SQL Server (MSSQLSERVER) service to see its configuration. The third tab in that dialog is the FILESTREAM configuration (see Image 1). The selections on this page require some explanation:

The "Enable FILESTREAM for Transact-SQL Access" seems pretty simple. This option is necessary for any FILESTREAM access. But what's subtle here is what it omits, which is the next portion.
The "Enable FILESTREAM for file I/O streaming access" is the portion that will allow you as a developer to read and write FILESTREAM data as if it were any other .NET Stream. I recommend enabling this since it allows some nifty capabilities that will be seen in the code for a subsequent post.
The "Windows share name" was another option that seemed obvious but was more subtle. This essentially creates a pseudo-share, like any other network share, that contains files that can be read and written. But it won't show up in Windows Explorer. It's only accessible via the SqlFileStream .NET Framework class.
The final option, "Allow remote clients to have streaming access to FILESTREAM data" is still a bit of a mystery to me. Why would you enable the access without allowing remote clients to stream to it? Is it likely that only local clients would use it? It doesn't seem so to me but perhaps I'm mistaken.

Image 1 - FILESTREAM Configuration

Instance Kharma

Next, we need to ensure that our database instance is enabled to utilize FILESTREAM capabilities. This can be done from SQL Server Management Studio. Right click on the database instance and choose Properties from the resulting menu. The Advanced configuration selection in that dialog has a dropdown list for FILESTREAM support right at the very top (see Image 2). It's uncertain to me whether this step is necessary or not because I didn't necessarily do this in the prescribed order but it seemed to me that it needed to be done. I chose the "Full access enabled" option in order to employ the remote streaming access that will be shown in a subsequent post.

Image 2 - FILESTREAM Instance Configuration

Filegroup Therapy

Since FILESTREAM BLOB data is stored on the file system. It can't live inside the PRIMARY filegroup for a database. So we need to create a new filegroup and file to contain this data. This is done pretty simply with a few SQL statements, or so it would seem.

First the filegroup.

ALTER DATABASE FilestreamExample
    ADD FILEGROUP FilestreamExampleFilegroup
    CONTAINS FILESTREAM
GO

This is very simple and straightforward. It creates a logical filegroup that specifies that the files contained within will be where FILESTREAM BLOB data is stored.

Pernicious Permissions

Now that I had a filegroup, I needed to add files to it. This is where things went a little sideways.

The SQL code to add a file to a filegroup is not terribly complicated.

ALTER DATABASE FilestreamExample
    ADD FILE( NAME = N'FilestreamExampleFiles', 
      FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\FilestreamExampleFiles' ) 
    TO FILEGROUP FilestreamExampleFilegroup
GO

Upon execution of this piece of code, I was presented with the following noxious error:

Operating system error 0x80070005(Access is denied.) occurred while creating or opening file 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\FilestreamExampleFiles'. Diagnose and correct the operating system error, and retry the operation.

As I investigated this issue, I began to understand what was happening. SQL Server was attempting to create a folder on disk with the name I specified in the ALTER DATABASE command, which is where it would store the files that would comprise the BLOB data. But there was clearly a permissions issue creating the folder.

Well, I'm a developer not an IT technician but I know enough to solve this issue. But I was unable to do so in a satisfactory way. The SQL Server service was running under the NetworkService account, which seemed appropriate for the situation. That account had full control to the entire SQL Server folder tree and everything beneath it. But no matter what I did, the problem persisted. I finally changed the service account to LocalSystem and the problem disappeared but I'm uncomfortable with that answer. If I set the permissions for the NetworkService user, why was it unable to write to a local disk resource?

Up Next - Processing SQL Server FILESTREAM Data: Part 3 - Creating Tables