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:
I Enable FILESTREAM at the Windows level
- Open SQL Configuration manager:
- Make sure the following checkboxes are selected:
II Enable FILESTREAM at the SQL Instance level:
- In SQL Server Management Studio, click New Query to display the Query Editor.
- 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:
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.
