Working with SQL Server File Tables 

Did you ever think that your SQL server can work as a file server? The odds are that you didn’t – neither did I until I tried a FileTable feature first introduced in SQL Server 2012. The FileTable feature is the next step in the FILESTREAM technology that allows you to store files and folders in the special tables but access them using traditional Windows SMB protocol – users can create/read/modify/delete files from a generic file share without even being aware of the underlying SQL commands. In other words, FileTables let us use SQL tables for non-transactional access.

In my opinion, one of the greatest benefits of FileTables feature is the storing of file attributes (such as Last-WriteTime, Last_AccessTime, etc.) that allows administrators to audit file access operations using the corresponding columns in the FileTables. In this article I’d like to show you how SQL Server 2012-2016 can be used for the file storage and what auditing mechanisms may be leveraged for files and folders residing in the SQL file shares.  Some prerequisites must be met before File Tables can be created in a database and we’ll go over all the steps required to prepare for File Tables, then create several tables and see how we can work with them.

Let’s start from enabling the FILESTREAM for the SQL 2012 instance and creating a new FILESTREAM database:

https://msdn.microsoft.com/en-us/library/gg509097%28v=sql.120%29.aspx

I Enable FILESTREAM at the Windows level

1. Open SQL Configuration manager:
   
2. Make sure the following checkboxes are selected:
   

II Enable FILESTREAM at the SQL Instance level:

1. In SQL Server Management Studio, click New Query to display the Query Editor.
2. In Query Editor, enter the following Transact-SQL code:
   EXEC sp_configure filestream_access_level, 2
   RECONFIGURE

III Provide a FILESTREAM Filegroup at the Database Level

Before we can create FileTables in a database, the database must have a FILESTREAM filegroup. For more information about this prerequisite, please see Create a FILESTREAM-Enabled Database.
Since I’m going to create a new database I will first create a folder to contain FileTables:

and then define a Filestream filegroup in this code:

CREATE DATABASE FILESERVER
ON
PRIMARY (NAME = FS,
FILENAME = ‘c:\FILESERVER\FSdat.mdf’),
FILEGROUP FileStreamFS CONTAINS FILESTREAM(NAME = FSgr,
   FILENAME = ‘c:\FILESERVER\fs1’)
LOG ON (NAME = FSlog,   FILENAME = ‘c:\ FILESERVER \FSlog.ldf’)
GO

Attention! As MS says: “For a FILESTREAM filegroup, FILENAME refers to a path. The path up to the last folder must exist, and the last folder must not exist.” In the preceeding code, c:\FILESERVER must exist, but fs1 must NOT at the time you execute the CREATE DATABASE statement.

The Filegroups database property page now displays the FileStreamFS filestream for the FILESERVER database:

IV Enable Non-Transactional Access at the Database Level

Once we’ve got the database created the last step in preparing it for FileTables is to enbable non-transactional access at the database level.

FileTables let Windows applications obtain a Windows file handle to FILESTREAM data without requiring a transaction. To allow this non-transactional access to files stored in SQL Server, you have to specify the desired level of non-transactional access at the database level for each database that will contain FileTables. If we execute the following code –

SELECT DB_NAME(database_id), non_transacted_access, non_transacted_access_desc 
   FROM sys.database_filestream_options;
GO

…we’ll see that no databases have non-transactional access enabled:

To enable non-transactional access to files at the database level (for the FILESERVER database in this case) one more option must be specified – a directory for FileTables: it is the directory I’ve created in the begining of the article – C:\FILESERVER, but the directory name should be typed without the drive letter and the symbol “\”:

ALTER DATABASE FILESERVER
    SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N’FILESERVER’ )
GO

“In the FileTable folder hierarchy, this database-level directory becomes the child of the share name specified for FILESTREAM at the instance level, and the parent of the FileTab les created in the database” – it means FILESERVER will be the subfolder of MSSQLSERVER share as I havn’t changed it in step I-2. For more information, please see Work with Directories and Paths in FileTables

Executing this code again will show the non-transactional access has been enabled successfully:

SELECT DB_NAME(database_id), non_transacted_access, non_transacted_access_desc
   FROM sys.database_filestream_options;
GO

The following code displays the directory name for the FILESERVER databse:

Select DB_NAME ( database_id), direcory_name
  FROM sys.database_filestream_options;
GO

Here’s the “SQL” file share that users can now use for storing their files and folders:

If I had renamed the default MSSQLSERVER share into something like SHARE1 in step I-2 there would have been no indication of any SQL Server involved – from the user’s perspective it would be just an ordinary file share.

All prerequisites for creating FileTables are fulfilled now and we can proceed to creating FileTables.

I will create the four FileTables:

• USERS
• Documents
• Confidential
• HR

use FILESERVER
GO
CREATE TABLE USERS AS FileTable;
GO

use FILESERVER
GO
CREATE TABLE Documents AS FileTable;
GO

use FILESERVER
GO
CREATE TABLE Confidential AS FileTable;
GO

use FILESERVER
GO
CREATE TABLE HR AS FileTable;
GO

The corresponding folders are created in the MSSQLSERVER\FILESERVER share:

Then we can create several personal user folders in the USERS folder:

Here’s the corresponding new rows in the USERS file table:

As you can see each row corresponds  to a file (is_directory = 0) or a folder (is_directory = 1) in the file share.

What permissions should users have to be able to create  files/folders in the “SQL” file share? First of all, we must create a server login (at least with public role) and a database user login (public + other permissions or roles) for each user  – otherwise he/she won’t even be able to access the share – for example I tried to access \\sql1\mssqlserver share as User2 which has neither sql server login no db login:

For the purpose of this test I will create two sql server logins that will map to the corresponding database users – User1 and User2:

and make them members of the db_datareader role:

Now User2 can browse the share:

..but not to create new file/folders (i.e.  insert new rows) or modify them (i.e. update the table):

Consider the following situation: User1 should be able to insert and update information in Confidential file table, User2 should have only read access. All insert/update operations on the Confidential file table (or creating/modifying file/folders from the file share’s perspective) must be audited.

Since User2 already has read access I will grant User1 the nesessary permissions:

Next I should create two new tables (the ordinary tables, not File Tables!) that will contain the auditing information, for example:

1) AuditCONFIDENTIALinsert table

use FILESERVER
CREATE TABLE AuditCONFIDENTIALinsert
(
ID int IDENTITY NOT NULL,
InsertTime    DateTime,
ServerLogin nvarchar(50),
DBuser nvarchar(50),
FileName nvarchar(50),
CONSTRAINT PKAuditConfidentialInsert
PRIMARY KEY (ID)
)
GO

2) AuditCONFIDENTIALupdate table

use FILESERVER
CREATE TABLE AuditCONFIDENTIALupdate
(
ID int IDENTITY NOT NULL,
UpdateTime    DateTime,
ServerLogin nvarchar(50),
DBuser nvarchar(50),
FileName nvarchar(50),
CONSTRAINT PKAuditConfidentialUpdate
PRIMARY KEY (ID)
)
GO

The tables are created:

To fulfill the auditing requirements I will add the two triggers to Confidential table:

1) Trigger for the insert operations

use FILESERVER
go
IF OBJECT_ID (‘CONFIDENTIALinsertTR’,’tr’) IS NOT NULL
DROP TRIGGER ‘CONFIDENTIALinsertTR’
go
CREATE TRIGGER ‘CONFIDENTIALinsertTR’
ON dbo.CONFIDENTIAL
AFTER INSERT
NOT FOR REPLICATION
AS
BEGIN
   INSERT INTO dbo.AuditCONFIDENTIALinsert (InsertTime, ServerLogin, DBuser, FileName)
VALUES ((SELECT Top 1 Creation_Time FROM dbo.Confidential), SUSER_SNAME(), 
USER_NAME(), (SELECT Top 1 name FROM dbo.Confidential))
END
GO

2)  Trigger for the update operations

use FILESERVER
go
IF OBJECT_ID (‘CONFIDENTIALupdateTR’,’tr’) IS NOT NULL
DROP TRIGGER CONFIDENTIALupdateTR
go
CREATE TRIGGER CONFIDENTIALupdateTR
ON dbo.CONFIDENTIAL
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
INSERT INTO dbo.AuditCONFIDENTIALupdate (UpdateTime, ServerLogin, DBuser, FileName)
VALUES ((SELECT Top 1 Creation_Time FROM dbo.Confidential), SUSER_SNAME(),
USER_NAME(), (SELECT Top 1 name FROM dbo.Confidential))
END
GO

These triggers add a new row containing the time of the insert/update operation, sql server login and database user login under which this operation has been performed and the file which has been inserted/updated.

Now it’s time to see how it works:

1) I will copy the file SecretReport.xlsx to the \\FILESERVER\Confidential share – in other words we’ll see how the auditing of the insert operation works:

This code will show the content of the Confidential file table:

use FILESERVER
go
select name, file_type, cached_file_size, is_directory, creation_time, last_write_time, 
last_access_time from Confidential
order by creation_time desc
go

This one will show the content of the AuditConfidentialInsert  table:

use FILESERVER
go
SELECT *  FROM AuditCONFIDENTIALinsert
order by InsertTime desc
go

This table shows what has been created (“inserted”), when and by whom.

2)  This time we’ll see how the auditing of the update operation works – I will open SecretReport.xlsx, make some changes and save the file:

Here’s the content of the AuditConfidentialUpdate table:

use FILESERVER
go
SELECT * FROM AuditCONFIDENTIALupdate
order by UpdateTime desc
go

I’d like to draw your attention to the following facts:

1) working with the document (“updating”) produces more the one row in the audit table even if you click “Save” button only once,

2) the updating takes place for the temporary file ~$___, not for the original file name

3) for the reason unknown to me only the first update event is registered under the name of the current user – Testcompany\Administrator – all subsequent ones are registered under the sa account and the most interesting fact:

4) some time after I took the previous screenshot  (minutes or hours later) the same code produces another output:

…and this really can become an issue when auditing file modifications because 1) it was only User1 who was changing the SecretReport.xlsx file at 2016-01-12 05:14:34.880 and 2) if Administrator had really changed this document at the exactly same time as User1 why whould SQL Server have not  added the corresponding row to the table at 2016-01-12 05:14:34.880 and have done it only some time later?

I hope this article provided some insight into what the FileTables feature is, how it can be used and what issues you may encounter working with them.