FILESTREAM feature is disabled

Question

I have a database set up with FILESTREAM enabled on it (if you go to Properties\Advanced on the instance, it shows that FILESTREAM is enabled). I also ran the sp_configure script on it after doing this to enable the access (to make sure it was enabled). But...as soon as I try to alter the database and add the filegroup to it, it says that the feature is disabled.

I have it enabled on other instances on the same computer, and those work fine. It is only this one that is not working. I have not been able to find any good suggestions as to why it is not working.

It is a SQL 2008 R2 instance.

Giblets

Please mark the most useful answer as the correct answer – Cockshy

Answer 1

I was unable to script this, (using the link provided by user1977061), as the MSSQLService requires a restart afterwards to apply the change. Even after doing this it didn't work, and instead I had to manually configure FILESTREAM usage via:

1. SqlServer Configuration Manager
2. Sql SQL Server Services
3. SQL Server(MSSQLSERVER) [Properties]
4. FILESTREAM tab - enable the first 2, and optionally 3rd check box.

Look at the SQLServer properties > Advanced > Running Values value for "Filestream Access Level" to ensure that the FILESTREAM is actually enabled if in doubt.

Cockshy

For me this also required restarting the SQL service, but at least doing this + SchmitzIT's answer made it work. – Hanghangar

Here is a link on Microsoft learn.microsoft.com/en-us/sql/relational-databases/blob/… – Corneliuscornell

Helped me a lot, restored a backup from Standard edition to express and didn't knew that this setting was turned on at source but not at destination.. Not sure why though – Stepfather

Answer 2

Was FILESTREAM enabled on the instance level, though?

In Microsoft Sql Server Management Studio, right-click the server and select Properties. Then on the Advanced page, ensure that FILESTREAM Access Level is not set to Disabled (Which I believe is default):

Fernyak

The OP said that they looked in Properties\Advanced on the instance, in a parenthetical, which I can only imagine is what you're showing here since there's no Advanced page when looking at database Properties. – Sibell

@Sibell - My mistake I read it as being enabled on DB level. I just checked, and there's FILESTREAM options at database level under Options though. Can delete if the OP confirms the above is enabled. – Fernyak

Yes, it is set to enabled. – Giblets

@Ztech - Could you please post the error message you receive in the OP? – Fernyak

Answer 3

For me this worked out:

0. forget about Microsoft SQL Server Management Studio to solve this bug
1. find SQLServerManager**.msc in folder C:\Windows\SysWOW64 and open it
2. enable filestream as in the picture, by clicking on properties

4. Restart SQL Server Service
5. Database import should work then!

Topical

It still says that FILESTREAM feature is disabled for me. – Lidless

Don't forget to restart the SQL server after following these steps. – Windjammer

Answer 4

Changing the FILESTREAM settings through SQL Server Management Studio -> Properties -> Advanced had no effect for me, and SQL Server Configuration Manager kept giving me an error when I enabled FILESTREAM there:

There was an unknown error applying the FILESTREAM settings.

Check the parameters are valid. (0x80041008)

Digging around on the internet led me here as well as to this page: https://social.msdn.microsoft.com/forums/sqlserver/en-US/d8060fdd-4583-4bf7-bdcf-4fd053d5d2c0/unknown-error-applying-the-filestream-settings

Fortunately one of the suggestions there was the solution. Connect to the server with SQL Server Management Studio and run this script:

sp_configure 'filestream access level', 2
reconfigure with override

Note: This was for 64-bit SQL Server 2012

Angelinaangeline

Answer 5

I've done this a thousand times and always follow the steps as Mojo outlines above. However I found that on one computer I couldn't get this to work unless I changed SQL Server Service from running as NT Service to running under a local account. I even reinstalled SQL Server, no luck.

I did as follows:

1. Click start
2. Type 'compmgmt.msc'
3. Expand 'Services and Applications'
4. Sql Server Configuration
5. Right Click on 'SQL Server (MSSQLSERVER)' and choose 'Properties'
6. On the Log On tab, change the user to a local account

Maybe one day this will save someone from banging their head on their desk until their eyeballs pop-out.

Superposition

Answer 6

If you still are having problems with this i suggest you checkout this link!

I had the same problem in which only one instance didn't activate filestream properly. The solution seems to be to use the SQL Server Configuration Manager and enable it from there.

Akihito

Link was altered to a functioning page (of Microsoft) – Shook

Answer 7

In addition to the other answers: the account that is used by the sql server service must be a member of the Administrators group.

Symptom: the "Configured value" in the server properties does show "enabled", but the "Running value" stays at "disabled".

Stimulate

Answer 8

I got this working by enabling filestream not in SQL Management Studio but in SQL Configuration Manager. See https://learn.microsoft.com/en-us/sql/relational-databases/blob/enable-and-configure-filestream?view=sql-server-2017.

Comatose

Answer 9

On the Start menu, point to All Programs, point to SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.

In the list of services, right-click SQL Server Services, and then click Open.

In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.

Right-click the instance, and then click Properties.

In the SQL Server Properties dialog box, click the FILESTREAM tab.

Select the Enable FILESTREAM for Transact-SQL access check box.

If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.

If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.

Click Apply.

In SQL Server Management Studio, click New Query to display the Query Editor.

In Query Editor, enter the following Transact-SQL code:

SQL

Copy EXEC sp_configure filestream_access_level, 2
RECONFIGURE
Click Execute.

Restart the SQL Server service.

Ytterbite

Answer 10

For me only the combination of SchmitzIT and Mojo suggestions made it work

Furthermost

Answer 11

I also stumbled into this problem and by trial and error i found out that the solution was to change the account under which Sql Server runs so from SS Configuration manager i located the sql server instante, right clicked "Properties" and on the "LOg On" tab i changed Logon as "This account" to "Bulil-in account", where i choose "Local system".

After a service restart all went fine.

Lantana

Answer 12

Sometimes this problem is not solved by the SQL Server Configuration Manager ! The best solution is to right-click on the desired instance and enter the properties. Enter the advanced section and Set the FILESTREAM Access Level to Full access enabled

Yogurt

Answer 13

I wanted to leave this for folks getting "There was an unknown error applying the filestream settings."

I have had this happen on two different fresh installations of SQL 2016 standard edition. We ended up going to MS support, and they advised us to perform a repair on the instance (under the SQL Server installation center), which we did, and the problem was solved. We were able to enable Filestream on the instance with no error. It seems ridiculous to have to "repair" a fresh install, but I've had it fix the Filestream issue twice now.

Judges