Of course, with
xp_cmdshell being enabled, you can read and write files using OS commands. However, one is not always blessed with the CONTROL SERVER permission, which is generally only granted with the sysadmin role. But if you happen to have the ADMINISTER BULK OPERATIONS permission (implied by the bulkadmin role), then
OPENROWSET is a viable option for both reading and writing files.
OPENROWSET for reading and writing files is nothing new. However, all examples for writing files that I have seen so far require the access to remote OLE DB providers being enabled, which is controlled by the ad hoc distributed queries configuration option, which is disabled by default. And changing and reconfiguring any configuration option requires ALTER SETTINGS permissions (implied by the sysadmin and serveradmin roles), which are not always available. And, well, in case of the sysadmin role, you could just as well use
So the technique for writing files demonstrated here is a little different as it exploits a side-effect of
OPENROWSET, which just requires ADMINISTER BULK OPERATIONS permission. It also does not require any temporary table or whatsoever; everything can be performed in a sub-query.
For looking up whether the current user has ADMINISTER BULK OPERATIONS permissions, you can use the
The the result should be
Reading a file is pretty straight forward. When
OPENROWSET is referenced in the
FROM clause, it allows reading from the given file through a built-in BULK provider and returns it as a rowset. The following
SELECT reads the
C:\Windows\win.ini file and returns a rowset of one single character LOB:
This, of course, can also be used to leak hashes for pass-the-hash.
Writing to a file is a little more difficult. The common approach with
OPENROWSET only is calling an external OLE DB provider. But this generally requires ad hoc distributed queries being enabled and has certain caveats like restriction to certain output formats depending on the selected OLE DB provider (e. g., CSV), previous existence of the output file, registration of linked servers, etc.
However, there is a way of writing arbitrary data to an arbitrary file without any of these restrictions or prerequisites by exploiting a side-effect of
The feature that is being exploited is the error reporting feature. The reference states the purpose of the
ERRORFILE argument as follows:
- ERRORFILE = 'file_name'
- Specifies the file used to collect rows that have formatting errors and cannot be converted to an OLE DB rowset. These rows are copied into this error file from the data file "as is."
So any erroneous record from the input is directly written to the given error file as is.
SELECT reads from
\\192.168.13.37\share\input.txt with the format specified in
\\192.168.13.37\share\input.fmt and writes to
The input file
input.txt has the following contents:
The non-XML format file
input.fmt specifies one single column named BulkColumn of type CHAR with the length of 1 character (fourth column) and the last character in our input file (i. e.,
>) as terminator:
Since we specified the length with just one byte, a truncation error occurs and the whole record up to and including the final terminator (i. e.,
>) gets written to the error file:
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (BulkColumn).
For binary files, the XML format seems to be better as you can specify the length for both the field input and the column output and don't need a terminator. The following format file
input.xml allows writing binary data up to 512 kB:
The length seems to be irrelevant and it certainly raises a data conversion error due to the binary data, resulting in writing the entire data to the error file.