Thursday, April 12, 2012

How to check if FileStream is enabled using SQL WMI

FILESTREAM can be enabled during SQL installation or by using SQL Configuration Manager. And there is no TSQL statement that enables FILESTREAM. The following code snippet shows how to check whether the SQL Server enabled FILESTREAM in full access mode.

private bool IsFileStreamFullAccess()
  string path= @"\\.\root\Microsoft\SqlServer\ComputerManagement10";
  string wql = "SELECT * FROM FileStreamSettings " + 
               "WHERE InstanceName = 'SQLEXPRESS'";

    ManagementScope scope = new ManagementScope(path);
    WqlObjectQuery wqlObj = new WqlObjectQuery(wql);
    ManagementObjectSearcher searcher = 
       new ManagementObjectSearcher(scope, wqlObj);
    ManagementObjectCollection moColl = searcher.Get();
    foreach (ManagementObject m in moColl)
       return m["AccessLevel"].ToString() == 
  return false;

The sample code inspects FileStreamSettings class and checks AccessLevel property. AccessLevel property can be 0, 1, 2, 3. If it is zero, FILESTREAM is disabled. If access level is 1, it enables FILESTREAM for TSQL access only. The below picture shows access level 1 in a SQL Configuration Manager dialog.

Access level 2 includes Access Level 1 and also file I/O streaming access. Lastly, access level 3 includes [Allow remote clients...] on top of access level 2.

Windows share name is typically the same as InstanceName. In SQL WMI, it can be retrieved by accessing ShareName property of FileStreamSettings class.

[Additional Note]
Donnel's comment below reminds me that I should have mentioned this in the post...
You might get 0 for moColl (ManagementObjectCollection ) which means there is no WMI information for FileStream. This can happen when you installed SQL Server on x64 machine and your C# application target platform is x86. 32 bit application will invoke 32 bit WMI, but 64bit SQL Server instance cannot be retrieved by 32bit WMI. An easy way of solving this problem is change your platform target to x64 in your C# project properties page. If you want to support both 32bit and 64bit or want to do this in programming way, you can read this post : How to call 32bit/64bit SQL WMI providers .