THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Who's Got Access to your FileStream Directories?

One of the great features of SQL Server 2008 is the FileStream data type. Paul Randal (blog | twitter) wrote a great white paper on it called FILESTREAM Storage in SQL Server 2008.

When you define a column to use the FileStream datatype SQL Server actually writes the contents of the column to the filesystem of your server, in individual files identified by a GUID. This provides a significant performance increase over allocating database pages and SQL Server still manages them as though they were within the database.

The issue that can occur, then, is if a user gains access to the directory where the files are maintained, and makes changes. This is, in effect, causing corruption in the database. So, how do we prevent it?

The first thing you'll want to do is identify who's got access to these directories. I've written a short script that will do this. We'll use SMO to navigate the instance, so let's first load the SMO libraries.

# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null
  }

We're going to be changing our directory location, so let's store the current one so we can get back there. Then we'll connect to the instance using the SMO Server object, and set a flag to let us know whether or not this instance actually has any databases using the FileStream data type.

# Get the current location so we can return to it after we're done
$currloc = Get-Location

# Connect to the instance using SMO
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer\MyInstance'
$fsused = $False  # Initialize a flag to indicate whether or not FileStream is used

We can now iterate through the server's Databases collection, and for each database, test to see if the DefaultFileStreamFileGroup has been set. If so, the database has been configured for the FileStream data type.

# Iterate through the databases to find the audit specifications
foreach ($db in $s.Databases) {
	[string]$nm = $db.Name
	[string]$dfg = $db.DefaultFileStreamFileGroup
	if ($dfg.Length -gt 0) {
		
		# We now know there's at least a default filestream file group, let's check them all
		write-output "Database: $nm"

Once we're in the database we'll loop through the file groups, and see if the group is a FileStream filegroup. If so, set our flag to true.

		foreach ($fg in $db.FileGroups) {
			if ($fg.IsFileStream -eq $True) {
				# Found a FileStream filegroup
				$fsused = $True

Here's where things get interesting. For each file in the filegroup (there'll only be one, but let's be certain) the FileName property holds the directory for the FileStream files. Assign that name to the $dir variable and display it. Then we use the Get-Acl cmdlet to get the Access Control List from Windows and assign it to the $acl variable. That variable then has a property called Access which contains a collection of all the FileSystemAccessRules for every account that has access to the directory. We pipe that to a Select-Object cmdlet to just return the IdentityReference (containing the user or group having access) and the FileSystemRights (what access they have) properties.

				# Loop through the files (which are actually directories)
				foreach ($fl in $fg.Files) {
					# The FileName property contains the directory where the Filestream files live
					$dir = $fl.FileName
					write-output "Filestream Directory: $dir"
					
					# Get and display the users and their access to the directory
					$acl = get-acl $dir
					$acl.Access | select IdentityReference, FileSystemRights
					write-output ""
					}
				}
			}
		}
	}

Finally we test the flag and let the user if there weren't any databases using FileStream.

# If we don't have any FileStream databases, let the caller know that
if ($fsused -eq $False) {
	write-output 'No FileStream-enabled Databases exist on this instance'
	}

# Get back to our original location
Set-Location $currloc

This script won't fix your security issues, but it will let you know whether or not you have one, at least with respect to the FileStream data directories.

Allen

Published Thursday, March 24, 2011 5:52 PM by AllenMWhite

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Martin Bell UK SQL Server MVP said:

A question about how to find out how much space is being used on for FileStream data a the Manchester user group inspired me to produce this Powershell script.

June 25, 2011 6:26 PM

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement