How to Enable SQL Server FILESTREAM?

If you are using SQL Server 2008 and above, and want to utilize SQL Server FILESTREAM feature, then you'll have to activate it for BOTH SQL Server Window Service as well as for the Instance.

With these configurations done you should be good enough to start making use of this feature.


Enabling FILESTREAM for the Windows Service

This option can be enabled during SQL Server Installation OR after installation from SQL Server Configuration Manager.

During Installation:

From SQL Server Configuration Manager

1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In the list of services, right-click SQL Server Services, and then click Open.
3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
4. Right-click the instance and then click Properties.
5. In the SQL Server Properties dialog box, click the FILESTREAM tab.
6. Select the Enable FILESTREAM for Transact-SQL access check box.
7. Click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
8. Click Apply.
9. Once the above configurations are done RESTART the server instance.

Enabling FILESTREAM for SQL Server Instance

In SQL Server Management Studio (SSMS) perform this step from the “Advanced” page in the Server Properties dialog

As an alternate one can also use the below script

EXEC sp_configure filestream_access_level, n
RECONFIGURE

(Specify n as 0, 1, or 2 for disabled, T-SQL access enabled, full access enabled, respectively)