FILESTREAM can be enabled typically either during SQL installation or using SQL Configuration Manager. This post shows how to enable FILESTREAM in C#. This example can apply to any SQL Server 2005 or above if you specify WMI namespace correctly. (For more details about SQL WMI namespace, please refer to
this POST (SQL WMI Provider)).
The C# code below is similar to the example of my previous post (
How to check if the FILESTREAM is enabled).
The difference is this time we call a method to change FILESTREAM setting instead of reading its properties.
public class SqlWmi
{
const int FILESTREAM_FULL_ACCESS = 3;
const uint RESTART_REQUIRED = 0x80070bc3;
string DEFAULT_SHARE = string.Empty;
string SqlWmiPath = @"\\.\root\Microsoft\SqlServer\ComputerManagement11";
string WqlSqlFs = "SELECT * FROM FileStreamSettings WHERE InstanceName='SQLEXPRESS'";
public bool EnableFileStream()
{
ManagementScope scope = new ManagementScope(SqlWmiPath);
int arch = Environment.Is64BitOperatingSystem ? 64 : 32;
scope.Options.Context.Add("__ProviderArchitecture", arch);
scope.Connect();
WqlObjectQuery wql = new WqlObjectQuery(WqlSqlFs);
ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, wql);
ManagementObjectCollection moColl = searcher.Get();
foreach (ManagementObject m in moColl)
{
ManagementBaseObject param = m.GetMethodParameters("EnableFileStream");
param["AccessLevel"] = FILESTREAM_FULL_ACCESS;
param["ShareName"] = DEFAULT_SHARE;
var output = m.InvokeMethod("EnableFileStream", param, null);
if (output != null)
{
uint retValue = (uint)output.GetPropertyValue("ReturnValue");
if (retValue == 0 || retValue == RESTART_REQUIRED)
{
return true;
}
}
}
return false;
}
}
FileStreamSettings SQL WMI class has only one method called EnableFileStream. When calling this method, we pass two parameters - one for AccessLevel, the other for Share Name. Valid values for Access Level are 0,1,2,3. If you want to give FULL access, you specify 3. For the second parameter Share Name, most likely we use default share name and if you want to specify default share name, you simply pass empty string (rather than full instance name, even though that will also work).
Calling a SQL WMI method is done by using InvokeMethod() of ManagementObject instance. There is an output of this method and it has return value from SQL WMI call. If everything is fine, it returns zero but you might get 0x80070bc3 which is a predefined value for [restart required] in the SQL WMI provider. For this case, you need to restart SQL Server instance.
Some bullet points to add:
- You have to run the program in administrator permission
- This sample code points to SQL Server 2012. If you want to use 2005-2008, you need to change SqlWmiPath to ComputerManagement (2005) or ComputerManagement10 (2008/R2)
- To make the code work in both 64bit and 32bit, __ProviderArchitecture was added. (for more details, please refer to this POST)
Lastly,
can we disable FILESTREAM using SQL WMI?
Yes, you simply set 0 to AccessLevel parameter before you call EnableFileStream() method...
