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 .


  1. It's a very good example, but
    1) it will work only for SQL Server 2008
    2) it won't compile.

    I found it to be a step in the right direction.

  2. If you change WMI namespace as follows, it will work for SQL 2005 - SQL 2012:
    SQL2005: root\Microsoft\SqlServer\ComputerManagement
    SQL2008: root\Microsoft\SqlServer\ComputerManagement10
    SQL2012: root\Microsoft\SqlServer\ComputerManagement11

    General info about SQL WMI also can be found at

  3. I tried this code and manually enabled Filestream, however

    ManagementObjectCollection moColl = searcher.Get();

    mColl count is always 0. Is there anything I'm missing here in the or the query?

    1. Donnel, if you build your app in x86 platform target and your SQL Server is in 64bit, you will get 0 since only 64bit application can access 64bit WMI provider. I've added more details in my [Additional Note] at the bottom of this post.

  4. Thank you Alex! That was exactly my issue.