(last updated: 2020-01-26 @ 18:20 EST / 2020-01-26 @ 23:20 UTC )
SQL Server 2019 introduced, among other things, two new filesystem-related items:
- a system stored procedure,
sys.xp_delete_files()
, and - an instance-level configuration option,
'allow filesystem enumeration'
Both are undocumented, so let’s see if we can figure out what they do.
sys.xp_delete_files()
This new stored procedure might be a replacement for the fairly restricted system stored procedure, sys.xp_delete_file
.
Setup
Before we can run any tests, we first need to execute the following in a Command Prompt window:
MKDIR C:tempSQLDeleteTestLevel1aLevel2a-aLevel3a-a-a MKDIR C:tempSQLDeleteTestLevel1aLevel2a-aLevel3a-a-b MKDIR C:tempSQLDeleteTestLevel1aLevel2a-bLevel3a-b-a MKDIR C:tempSQLDeleteTestLevel1aLevel2a-bLevel3a-b-b MKDIR C:tempSQLDeleteTestLevel1bLevel2b-aLevel3b-a-a MKDIR C:tempSQLDeleteTestLevel1bLevel2b-aLevel3b-a-b MKDIR C:tempSQLDeleteTestLevel1bLevel2b-bLevel3b-b-a MKDIR C:tempSQLDeleteTestLevel1bLevel2b-bLevel3b-b-b COPY /Y NUL: C:tempSQLDeleteTestLevel1bLevel2b-bLevel3b-b-bNotEmpty COPY /Y NUL: C:tempSQLDeleteTestTest01x.txt COPY /Y NUL: C:tempSQLDeleteTestTest02y.txt COPY /Y NUL: C:tempSQLDeleteTestTest03x.txt COPY /Y NUL: C:tempSQLDeleteTestTest14y.txt COPY /Y NUL: C:tempSQLDeleteTestTest15x.txt COPY /Y NUL: C:tempSQLDeleteTestTest16y.txt COPY /Y NUL: C:tempSQLDeleteTestTest999x.txt COPY /Y NUL: C:tempSQLDeleteTestLevel1aRecursive.1 COPY /Y NUL: C:tempSQLDeleteTestLevel1aLevel2a-bRecursive.2 COPY /Y NUL: C:tempSQLDeleteTestLevel1aLevel2a-bRecursive.24 COPY /Y NUL: C:tempSQLDeleteTestLevel1aLevel2a-bRecursive.4 COPY /Y NUL: C:tempSQLDeleteTestLevel1aLevel2a-bRecursive.46 COPY /Y NUL: C:tempSQLDeleteTestLevel1aLevel2a-bLevel3a-b-aRecursive.3 COPY /Y NUL: C:tempSQLDeleteTestDELETE.ME ATTRIB +R C:tempSQLDeleteTestDELETE.ME
Verify
The following two statements return the same number of rows, indicating that they both see all of the files and folders/directories that we just created:
SELECT * FROM sys.dm_os_enumerate_filesystem(N'C:tempSQLDeleteTest', N'*'); -- 29 rows EXEC sys.xp_dirtree N'C:tempSQLDeleteTest', 0, 1; -- 29 rows
Functionality Tests
There is no parameter info help available for system stored procedures, so we will need to pass in various values to see how it behaves:
EXEC sys.xp_delete_files 0; /* Msg 22049, Level 16, State 1, Line XXXXX Error executing extended stored procedure: Invalid Parameter */EXEC sys.xp_delete_files N'DELETE.ME'; /* Msg 22049, Level 16, State 1, Line XXXXX xp_delete_files: Error 0 from SvlPathIsRelative on line 874 */
The results shown above tell us that the first parameter must be an absolute file path.
Now we can test to see if the stored procedure accepts, or requires, additional parameters. Also, since stored procedures can pass back an INT
value (which is supposed to be a status / error code, but people often use it to pass back the number of items affected, etc), we should occassionally check to see if anything useful is being returned.
GO DECLARE @RetVal INT; EXEC @RetVal = sys.xp_delete_files N'DELETE.ME', N'DELETE.ME'; SELECT @RetVal; -- 0 /* Msg 22049, Level 16, State 1, Line XXXXX xp_delete_files: Error 0 from SvlPathIsRelative on line 874 Msg 22049, Level 16, State 1, Line XXXXX xp_delete_files: Error 0 from SvlPathIsRelative on line 874 */EXEC sys.xp_delete_files 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; -- 44 errors for 44 "0" parameters
The results shown above tell us that multiple parameters are accepted, and that none of them are numbers.
Knowing that the first parameter needs to be an absolute path, what happens if we pass in an aboluste path that either does not exist, or exists but cannot be deleted (two common error scenarios)?
EXEC sys.xp_delete_files N'C:tempSQLDeleteTestDELETE.ME2'; -- no error, yet file does not exist! GO DECLARE @RetVal INT; EXEC @RetVal = sys.xp_delete_files N'C:tempSQLDeleteTestDELETE.ME'; SELECT @RetVal; -- 0 /* Msg 22049, Level 16, State 1, Line XXXXX xp_delete_files() returned error 5, 'Access is denied.' */
The results shown above tell us that non-existent files fail silently, but undeletable files do throw an error. In both cases the return value is "0
".
In the same Command Prompt window, execute the following (so that we can delete the file):
ATTRIB -R C:tempSQLDeleteTestDELETE.ME
Now execute the following in SSMS:
EXEC sys.xp_delete_files N'C:tempSQLDeleteTestDELETE.ME'; -- Success!! SELECT * FROM sys.dm_os_enumerate_filesystem(N'C:tempSQLDeleteTest', N'*'); -- 28 rows
The results shown above tell us that the file extension does not matter (not restricted like with sys.xp_delete_file
(no "s" at the end)).
Next we should probably check to see if we can use the standard path wildcard characters:
GO SELECT * FROM sys.dm_os_enumerate_filesystem(N'C:tempSQLDeleteTest', N'Test??x.txt'); -- 3 rows DECLARE @RetVal INT = -999; EXEC @RetVal = sys.xp_delete_files N'C:tempSQLDeleteTestTest??x.txt'; SELECT @RetVal AS [@RetVal]; -- 0 SELECT * FROM sys.dm_os_enumerate_filesystem(N'C:tempSQLDeleteTest', N'*'); -- 25 rows
The results shown above tell us that ?
works just like it does in a command prompt / CMD script. And if that works, then *
will also work as expected. We also see that the return value is still "0
".
Next, we test to see if the delete works recursively down through subdirectories:
SELECT * FROM sys.dm_os_enumerate_filesystem(N'C:tempSQLDeleteTest', N'Rec*.?'); -- 4 rows EXEC sys.xp_delete_files N'C:tempSQLDeleteTestRec*.?'; SELECT * FROM sys.dm_os_enumerate_filesystem(N'C:tempSQLDeleteTest', N'Rec*.?'); -- 4 rows EXEC sys.xp_delete_files N'C:tempSQLDeleteTestLevel1aRec*.?'; SELECT * FROM sys.dm_os_enumerate_filesystem(N'C:tempSQLDeleteTest', N'Rec*.?'); -- 3 rows
The results shown above tell us that the delete operation is not recursive.
Next, we need to test what happens when we pass in multiple parameters since we saw earlier (when we passed in 44 zeros) that this stored procedure does accept multiple parameters of absolute paths:
SELECT * FROM sys.dm_os_enumerate_filesystem(N'C:tempSQLDeleteTest', N'*'); -- 24 rows EXEC sys.xp_delete_files N'C:tempSQLDeleteTestTest14y.txt', N'C:tempSQLDeleteTestLevel1aLevel2a-bRec*.?', N'C:tempSQLDeleteTestLevel1aLevel2a-bLevel3a-b-a*'; SELECT * FROM sys.dm_os_enumerate_filesystem(N'C:tempSQLDeleteTest', N'*'); -- 20 rows
The results shown above tell us that we can, indeed, pass in multiple absolute paths, and that each one can use standard path wildcard characters.
Now, just out of curiosity, even though the name of the stored procedure indicates that it deletes files, what happens if we give it a folder / directory instead? And, if it does allow for deleting directories, does it allow for deleting non-empty directories and/or deleting recursively through subdirectories? Only one way to find out, right?
SELECT * FROM sys.dm_os_enumerate_filesystem(N'C:tempSQLDeleteTest', N'*'); -- 20 rows -- trailing "" when deleting directories / folders is optional EXEC sys.xp_delete_files N'C:tempSQLDeleteTestLevel1b'; SELECT * FROM sys.dm_os_enumerate_filesystem(N'C:tempSQLDeleteTest', N'*'); -- 12 rows (if more than 12, just re-execute "sys.xp_delete_files" -- as sometimes it can't delete everything)
The results shown above tell us that we can, indeed, delete non-empty directories, even if there are files and/or subdirectories present. We also (sometimes) see that there might be some filesystem caching that can retain locks that prevent deletion of subdirectories or the directory itself for a short period of time.
Permissions
Let’s assume that deleting files is not available to [public]
.
Create and Impersonate (i.e. Become) a Restricted Account
USE [tempdb]; CREATE LOGIN [Restricted] WITH PASSWORD = 'TestingOnly', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF; CREATE USER [Restricted] FOR LOGIN [Restricted]; EXECUTE AS LOGIN = N'Restricted'; SELECT SYSTEM_USER AS [Login], CURRENT_USER AS [User]; -- Restricted Restricted
Run Test
SELECT * FROM sys.dm_os_enumerate_filesystem(N'C:tempSQLDeleteTest', N'*'); -- 9 rows EXEC sys.xp_delete_files N'C:tempSQLDeleteTestLevel1aLevel2a-bRecursive.24'; /* Msg 229, Level 14, State 5, Procedure sys.xp_delete_files, Line XXXXX [Batch Start Line YYYYY] The EXECUTE permission was denied on the object 'xp_delete_files', database 'mssqlsystemresource', schema 'sys'. */
Add Permissions Similar to Requirement for xp_cmdshell
REVERT; EXEC (N' USE [master]; CREATE USER [Restricted] FOR LOGIN [Restricted]; GRANT EXECUTE ON sys.xp_delete_files TO [Restricted]; ');
Re-run Test
EXECUTE AS LOGIN = N'Restricted'; SELECT SYSTEM_USER AS [Login], CURRENT_USER AS [User]; -- Restricted Restricted EXEC sys.xp_delete_files N'C:tempSQLDeleteTestLevel1aLevel2a-bRecursive.24'; /* Msg 22049, Level 16, State 1, Line XXXXX Error executing 'xp_delete_files': Permission denied. User must be a member of 'sysadmin' server role. */
Stop Impersonating
REVERT; SELECT SYSTEM_USER AS [Login], CURRENT_USER AS [User];
Solution / Remedy
Do NOT make Login a "sysadmin". Instead:
- Create a Certificate
- Wrap the call to
sys.xp_delete_files
in a Stored Procedure GRANT EXECUTE
on that Stored Procedure to whomever should be able to delete files- Sign the stored procedure with that Certificate
- Copy the Certificate (public key only!) to the
[master]
database - Create a Login from that Certificate
- Add that Certificate-based Login to the
[sysadmin]
fixed Server Role
For detailed explanation and instructions (and demo script), please see:
Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level
Backporting
It appears that sys.xp_delete_files
was backported to SQL Server 2017, possibly in CU18 (based on the dates being the same):
SELECT * FROM [master].sys.[all_objects] WHERE [name] = N'xp_delete_files'; -- 2019-11-16 03:18:18.920 SELECT @@VERSION; --Microsoft SQL Server 2017 (RTM-CU18) (KB4527377) - 14.0.3257.3 (X64) -- Nov 16 2019 01:14:50
There is a minor bug in sys.xp_delete_files
, only on SQL Server 2017, where if you execute it without specifying any input parameters, it will enter an infinite loop of reporting “invalid parameter”. You will need to cancel the query (which will take a few seconds to do), and that will abort the connection.
Summary
- SYNTAX:
EXEC sys.xp_delete_files 'fileSpec.01' [, 'fileSpec.02' [, ...] ] ;
- This system stored procedure is undocumented (meaning: unsupported)
- File type / extension does not matter (this is not restricted like with
xp_delete_file
(no "s" at the end)) - Return Value is always "
0
" - Works with standard DOS wildcard characters:
- "
*
" == zero or more of any character (same as "%
" in T-SQLLIKE
andPATINDEX
) - "
?
" ==- When there are characters (in a potential match) to the right of the "
?
": exactly one of any character (same as "_
" in T-SQLLIKE
andPATINDEX
) - When there are no characters (in a potential match) to the right of the "
?
": zero / ignored
- When there are characters (in a potential match) to the right of the "
- "
- Does NOT recurse through subdirectories (for deleting files)
- Can specify multiple fully-qualified path specifications, each being able to handle wildcards
- Can remove (i.e. prune) entire non-empty subfolder structure!! (though sometimes one or more directories remain, most likely due to filesystem caching; just need to re-try until all gone)
- Must be a member of the
[sysadmin]
fixed Server Role in order to execute (use Module Signing instead of adding application Login to[sysadmin]
fixed Server Role; please see: Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level ) - Backported to SQL Server 2017 (possibly in CU18)
‘allow filesystem enumeration’
First, let’s look at the option and its properties:
SELECT [configuration_id], [name], [description], [is_dynamic],[is_advanced] FROM sys.configurations WHERE [name] LIKE N'%filesys%' COLLATE Latin1_General_100_CI_AS_SC; /* config_id name description is_dynamic is_advanced 16398 allow filesystem Allow enumeration 1 1 enumeration of filesystem */
The results shown above indicate that this option is:
- advanced: you might need to execute the following in order to see or change the value
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
- dynamic: it will take effect upon executing
RECONFIGURE
(i.e. no need to restart the service)
Ok. But, what does this option do? The option is currently enabled, and we have been able to list files with both xp_dirtree
and dm_os_enumerate_filesystem
(see “Verify” section towards the top). It seems reasonable to imply, based on the name of the option, its current state (i.e. enabled), and the behavior of the filesystem functions (i.e. we can currently list the files on the filesystem), that disabling this option will prevent those functions from returning the list of files.
Only one way to find out.
Setup
EXEC sp_configure 'allow filesystem enumeration', 0; RECONFIGURE; EXEC sp_configure 'allow filesystem enumeration'; -- run_value = 0
Test
We will impersonate the restricted account to avoid the possiblity of implied permission for sysadmins.
USE [tempdb]; EXECUTE AS LOGIN = N'Restricted'; SELECT SYSTEM_USER AS [Login], CURRENT_USER AS [User]; -- Restricted Restricted SELECT * FROM sys.dm_os_enumerate_fixed_drives; -- 2 rows SELECT * FROM sys.dm_os_enumerate_filesystem(N'C:tempSQLDeleteTest', N'*'); -- 9 rows EXEC xp_dirtree N'C:tempSQLDeleteTest', 0, 1; -- 9 rows REVERT; SELECT SYSTEM_USER AS [Login], CURRENT_USER AS [User];
It looks to be unfinished / incomplete as it does not appear to have any effect at all.
Purpose?
Since this option doesn’t seem to do anything, we can only speculate as to its intended purpose. To that end, there might be some clues in various places.
Personnaly, I think the goal here is to fullfill the desired behavior requested in the following Q & A (on DBA.StackExchange):
Prevent SSMS from seeing the server’s file system
in a more granular / elegant / appropriate way than the current advice found here:
Add the ability to disable or enable a few new DMVs and DMFs introduced in SQL Server 2017 (posted on 2017-11-07)
which is to disable the instance-wide configuration option of "'SMO and DMO XPs'
" (an advanced option which is enabled by default). Following this advice will disable (starting in CU1 for SQL Server 2017) the following:
sys.dm_os_enumerate_fixed_drives
sys.dm_os_enumerate_filesystem
sys.dm_os_file_exists
But what else does it disable? It has a link to the SMO and DMO XPs Server Configuration Option page, which tells us nothing useful, but does have a link to the SQL Server Management Objects (SMO) Overview page. That page lists quite a few things, and it’s still not clear what the dependencies are to any other XPs, DMVs, or DMFs. So, without knowing more about what else might be affected by disabling this option, I’m not very comfortable turning it off.
ALSO, does disabling "'SMO and DMO XPs'
" also disable xp_dirtree
?
Can’t hurt to try, right?
Test Disabling
EXEC sp_configure 'SMO and DMO XPs', 0; RECONFIGURE; SELECT * FROM sys.dm_os_enumerate_fixed_drives; SELECT * FROM sys.dm_os_enumerate_filesystem(N'C:tempSQLDeleteTest', N'*'); EXEC xp_dirtree N'C:tempSQLDeleteTest', 0, 1; /* Msg 15281, Level 16, State 2, Line XXXXX SQL Server blocked access to ... of component 'SMO and DMO XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'SMO and DMO XPs' by using sp_configure. For more information about enabling 'SMO and DMO XPs', search for 'SMO and DMO XPs' in SQL Server Books Online. */EXEC sp_configure 'SMO and DMO XPs', 1; RECONFIGURE;
All 3 get the same error.
Backporting
This option has not been back-ported to SQL Server 2017 (which makes sense if it’s incomplete).
Summary
I suppose this option, once completed, will be better than disabling "'SMO and DMO XPs'
", but what if some accounts / Logins need to be able to use it?
PPLLLLEEEEAASEEE let this not be yet another ill-conceived, bolted on, one-off security mechanism hack that works differently than everything else in the main security and permissions architecture, such as:
- trusted assemblies, introduced in SQL Server 2017
- feature restrictions, almost introduced in SQL Server 2019
While this instance-level configuration option would be more granular / less impacting than disabling "'SMO and DMO XPs'
", it’s still an all-or-none approach that doesn’t allow for some Logins to have the permissions while others do not. Disabling access to those three DMFs should instead be handled by something along the lines of:
DENY FILESYSTEM_ENUMERATION TO [SomeLoginOrServerRole];
CLEAN UP
/* EXEC sys.xp_delete_files N'C:tempSQLDeleteTest'; -- OR: EXEC sys.xp_delete_files N'C:tempSQLDeleteTestLevel1a'; EXEC sys.xp_delete_files N'C:tempSQLDeleteTestLevel1b'; EXEC sys.xp_delete_files N'C:tempSQLDeleteTest*'; */