SQL Server Enable filestream without having SQL Server Configuration Manager(so via command line only)

Question

I have SQL server 2017 on a Windows Server 2019 Core installation, no GUI. One of our users wants to use Filestream. I tried to follow this setup and this however, since it requires SSCM I can't complete that part. I can enable in SSMS however, cannot set file location etc, so when executing the stored procedure command on the setup page I get this error:

FILESTREAM feature could not be initialized. The operating system Administrator must enable FILESTREAM on the instance using Configuration Manager.

When I connect to SSCM remotely through the computer management window and try to open the services none will display. I can access the networking options but not the services so maybe those won't work remotely? The firewall is off between the Core install and the GUI version I am trying to use.

Anybody have a way to remotely enable Filestream and set the needed parameters like you would through SSCM?

Thanks in advance.

Answer 1

Regedit

You could change the registry settings for the instance's filestream settings. Below is an example with the master..xp_regwrite procedure.

If filestream is disabled for the instance this shows:

The same is true when viewing with the configuration manager:

---

Via SSMS / SQLCMD / ...

As a result, you can change the EnableLevel value for the Filestream registry key. Remember to change MSSQL14.MSSQLSERVER in the example below to your sql server instance.

EXEC master..xp_regwrite
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\Filestream',
'EnableLevel',
'REG_DWORD',
3

This changes the value to full filestream access:

This can be validated with the configuration manager:

As always, change the registry at your own risk. Taking a backup of the registry is also a good idea.

After changing these settings, you would have to restart your sql server instance and configure the filestream_access_level .

---

Extra info

Level 2:

EXEC master..xp_regwrite
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\Filestream',
'EnableLevel',
'REG_DWORD',
2

And as expected, level 1:

EXEC master..xp_regwrite
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\Filestream',
'EnableLevel',
'REG_DWORD',
1

Answer 2

Not super relevant to this, but if you end up pivoting over to doing your sql installs via desired state configuration you can set the registry value like below (requires the computermanagementdsc) then use sqlserverdsc/sqlconfiguration to set the filestream access level like beneath (note: you have to remove the underscores typically passed to sp_configure for it to work)

        Registry RegistrySqlEnableFilestream
        {
            Ensure      = "Present"  # You can also set Ensure to "Absent"
            Key         = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\Filestream"
            ValueType   = "Dword"
            ValueName   = "EnableLevel"
            ValueData   = "3"
        }

        SqlConfiguration SQLFilestreamAccess
        {

            InstanceName   = 'MSSQLSERVER'
            OptionName     = 'filestream access level'
            OptionValue    = 2
            RestartService = $false
            DependsOn      = '[SqlSetup]DefaultInstance'
        }