Blog Post

sys.xp_delete_files and ‘allow filesystem enumeration’: two new undocumented items in SQL Server 2019

,

(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:

  1. a system stored procedure, sys.xp_delete_files(), and
  2. 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:

  1. Create a Certificate
  2. Wrap the call to sys.xp_delete_files in a Stored Procedure
  3. GRANT EXECUTE on that Stored Procedure to whomever should be able to delete files
  4. Sign the stored procedure with that Certificate
  5. Copy the Certificate (public key only!) to the [master] database
  6. Create a Login from that Certificate
  7. 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

  1. SYNTAX: EXEC sys.xp_delete_files 'fileSpec.01' [, 'fileSpec.02' [, ...] ] ;
  2. This system stored procedure is undocumented (meaning: unsupported)
  3. File type / extension does not matter (this is not restricted like with xp_delete_file (no "s" at the end))
  4. Return Value is always "0"
  5. Works with standard DOS wildcard characters:
    • "*" == zero or more of any character (same as "%" in T-SQL LIKE and PATINDEX)
    • "?" ==
      • When there are characters (in a potential match) to the right of the "?": exactly one of any character (same as "_" in T-SQL LIKE and PATINDEX)
      • When there are no characters (in a potential match) to the right of the "?": zero / ignored
  6. Does NOT recurse through subdirectories (for deleting files)
  7. Can specify multiple fully-qualified path specifications, each being able to handle wildcards
  8. 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)
  9. 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 )
  10. 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:

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*';
*/

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (1)

You rated this post out of 5. Change rating