Extended Stored Procedure

Securing the Instance

Denny Cherry , in Securing SQL Server (Third Edition), 2015

Extended Stored Procedures

Extended stored procedures are DLL files which are referenced by the SQL Server by having the extended stored procedure created which then reference functions or procedures within the DLL. The DLLs which are behind the extended stored procedures are typically created in a lower level language like C or C++. Sample code to create an extended stored procedure can be found in Example 6.23. Even though Extended Stored Procedures are scheduled for removal from Microsoft SQL Server, they can still be a useful tool. When you create an extended stored procedure, the dll must physically exist on the database server, which makes it a little more complex for attackers to create an extended stored procedure on the fly as they can with an SQL Server Assembly.

In order to create an extended stored procedure, the login that the user uses to log into the database must be a member of the sysadmin fixed server role. Extended stored procedures are always created within the master database, but can be referenced from any database. Typically, an extended stored procedure would be created with a name starting with xp_ or sp_ so that the database engine would automatically look in the master database for the object if there was no object with that name in the user database.

Note

Getting a New dll Onto the Server Is Not All That Hard

Once attackers have figured out that they can use SQL Injection attacks to gain access to the system, it is not hard to determine what sort of access they have to the database server. If the attacker is able to gain access to xp_cmdshell, even if it is without admin rights, the attacker can still do a decent amount of damage to the system.

As an example, an attacker could create text file that has FTP (File Transfer Protocol) commands and then run ftp.exe using xp_cmdshell passing ftp.exe the name of the text file, which has the commands to run. The text file with the commands could be set up to log into the attacker's FTP server and download various files. If attackers have sysadmin rights to the SQL Server instance, then they can simply attack an extended stored procedure to the instance. If they do not have sysadmin rights to the instance, the attacker could install software on the machine and configure it to startup automatically either via a service (probably hidden by a rootkit), or in the run registry key for either one or all users. At this point this software could be configured to do anything the attacker wants to and will probably be hidden from the view of the database administrator and system administrators.

Like .NET assemblies, extended stored procedures can be written to access anything on the local system or the Internet, limited only by the coding knowledge of the developer who writes the dll that is attached as an extended stored procedure. Extended stored procedures run within the SQL Server, meaning that the code is executed within the SQL Server memory space. As the extended stored procedures run within the SQL Server process, bugs within the extended stored procedure can surface within the SQL Server and cause the SQL Server to crash or core dump, even causing the SQL Server engine processes to stop completely.

Extended stored procedures can be added to an SQL Server instance by using the sp_addextendedproc system stored procedure within the master database. The system stored procedure accepts two parameters, @functname and @dllname, as shown in Example 6.23. @functname is the name of the function to call within the DLL and is a nvarchar(517) parameter. @dllname is the full path to the DLL and is varchar(255). There can be multiple functions within a single DLL, each one with a different name and exposed as a separate extended stored procedure.

Example 6.23

Adding an extended stored procedure to an SQL Server Instance.

Read full chapter

URL:

https://www.sciencedirect.com/science/article/pii/B9780128012758000063

Additional Security for an Internet Facing SQL Server and Application

Denny Cherry , Thomas Larock , in Securing SQL Server, 2011

Extended Stored Procedures

Even though Extended Stored Procedures are scheduled for removal from Microsoft SQL Server in a future version (after Microsoft SQL Server 2008 R2), they can still be a useful tool. When you create an extended stored procedure, the dll must physically exist on the database server, which makes it a little more complex for attackers to create an extended stored procedure on the fly as they can with an SQL Server Assembly.

In order to create an extended stored procedure, the login that the user uses to log into the database must be a member of the sysadmin fixed server role. Extended stored procedures are always created within the master database, but can be referenced from any database. Typically, an extended stored procedure would be created with a name starting with xp_ or sp_ so that the database engine would automatically look in the master database for the object if there was no object with that name in the user database.

Note

Getting a New dll Onto the Server Isn't All That Hard

Once attackers have figured out that they can use SQL Injection attacks to gain access to the system, it isn't hard to determine what sort of access they have to the database server. If the attacker is able to gain access to xp_cmdshell, even if it is without admin rights, the attacker can still do a decent amount of damage to the system.

As an example, an attacker could create text file that has FTP (File Transfer Protocol) commands and then run ftp.exe using xp_cmdshell passing ftp.exe the name of the text file, which has the commands to run. The text file with the commands could be set up to log into the attacker's FTP server and download various files. If attackers have sysadmin rights to the SQL Server instance, then they can simply attack an extended stored procedure to the instance. If they don't have sysadmin rights to the instance, the attacker could install software on the machine and configure it to start up automatically either via a service (probably hidden by a rootkit), or in the run registry key for either one or all users. At this point this software could be configured to do anything the attacker wants to and will probably be hidden from the view of the database administrator and system administrators.

Like .NET assemblies, extended stored procedures can be written to access anything on the local system or the Internet, limited only by the coding knowledge of the developer who writes the dll that is attached as an extended stored procedure. Extended stored procedures run within the SQL Server, meaning that the code is executed within the SQL Server memory space. As the extended stored procedures run within the SQL Server process, bugs within the extended stored procedure can surface within the SQL Server as it crashes or core dumps, even causing the SQL Server engine processes to stop completely.

Extended stored procedures can be added to an SQL Server instance by using the sp_addextendedproc system stored procedure within the master database. The system stored procedure accepts two parameters, @functname and @dllname, as shown in Example 5.3. @functname is the name of the function to call within the DLL and is a nvarchar(517) parameter. @dllname is the full path to the DLL and is varchar(255). There can be multiple functions within a single DLL, each one with a different name and exposed as a separate extended stored procedure.

Example 5.3

Adding an extended stored procedure to an SQL Server Instance.

EXEC sp_addextendedproc @functname='MyFunction', @dllname='c:\Program Files\Microsoft SQL Server\MSSQL\binn\myfile.dll'

Read full chapter

URL:

https://www.sciencedirect.com/science/article/pii/B9781597496254100058

Additional Security for an Internet Facing SQL Server and Application

Denny Cherry , in Securing SQL Server (Second Edition), 2013

Extended stored procedures

Extended stored procedures are DLL files which are referenced by the SQL Server by having the extended stored procedure created which then reference functions or procedures within the DLL. The DLLs which are behind the extended stored procedures are typically created in a lower level language like C or C++. Sample code to create an extended stored procedure can be found in Example 5.3. Even though extended stored procedures are scheduled for removal from Microsoft SQL Server, extended stored procedures were marked as deprecated in SQL Server 2008 R2, in a future version (extended stored procedures will be removed one or two versions after the release of SQL Server 2012), they can still be a useful tool. When you create an extended stored procedure, the dll must physically exist on the database server, which makes it a little more complex for attackers to create an extended stored procedure on the fly as they can with an SQL Server Assembly.

In order to create an extended stored procedure, the login that the user uses to log into the database must be a member of the sysadmin fixed-server role. Extended stored procedures are always created within the master database, but can be referenced from any database. Typically, an extended stored procedure would be created with a name starting with xp_ or sp_ so that the database engine would automatically look in the master database for the object if there was no object with that name in the user database.

Note

Getting a New dll onto the Server isn't all that Hard

Once attackers have figured out that they can use SQL Injection attacks to gain access to the system, it isn't hard to determine what sort of access they have to the database server. If the attacker is able to gain access to xp_cmdshell, even if it is without admin rights, the attacker can still do a decent amount of damage to the system.

As an example, an attacker could create text file that has FTP (File Transfer Protocol) commands and then run ftp.exe using xp_cmdshell passing ftp.exe the name of the text file, which has the commands to run. The text file with the commands could be set up to log into the attacker's FTP server and download various files. If attackers have sysadmin rights to the SQL Server instance, then they can simply attack an extended stored procedure to the instance. If they don't have sysadmin rights to the instance, the attacker could install software on the machine and configure it to start up automatically either via a service (probably hidden by a rootkit), or in the run registry key for either one or all users. At this point this software could be configured to do anything the attacker wants to and will probably be hidden from the view of the database administrator and system administrators (SAs).

Like .NET assemblies, extended stored procedures can be written to access anything on the local system or the Internet, limited only by the coding knowledge of the developer who writes the dll that is attached as an extended stored procedure. Extended stored procedures run within the SQL Server, meaning that the code is executed within the SQL Server memory space. As the extended stored procedures run within the SQL Server process, bugs within the extended stored procedure can surface within the SQL Server as it crashes or core dumps, even causing the SQL Server engine processes to stop completely.

Extended stored procedures can be added to an SQL Server instance by using the sp_addextendedproc system stored procedure within the master database. The system stored procedure accepts two parameters, @functname and @dllname, as shown in Example 5.3. @functname is the name of the function to call within the DLL and is a nvarchar(517) parameter. @dllname is the full path to the DLL and is varchar(255). There can be multiple functions within a single DLL, each one with a different name and exposed as a separate extended stored procedure.

Example 5.3

Adding an extended stored procedure to an SQL Server instance:

EXEC sp_addextendedproc @functname='MyFunction', @dllname='c:⧹Program Files⧹Microsoft SQL Server⧹MSSQL⧹binn⧹myfile.dll'

Read full chapter

URL:

https://www.sciencedirect.com/science/article/pii/B9781597499477000058

References

Justin Clarke , in SQL Injection Attacks and Defense, 2009

System Command Execution via xp_cmdshell

Microsoft SQL Server 7, 2000, and 2005 include an extended stored procedure named xp_cmdshell that can be called to execute operating system commands. When attacking SQL server versions 2000 and earlier, the following SQL statement can be executed by the DBO of the master database (e.g., the sa user):

EXEC master.dbo.xp_cmdshell 'os command'

For SQL Server Version 2005, the xp_cmdshell stored procedure is disabled by default and must first be reenabled using the following SQL:

EXEC sp_configure 'show advanced options', 1

EXEC sp_configure reconfigure

EXEC sp_configure 'xp_cmdshell', 1

EXEC sp_configure reconfigure

If the xp_cmdshell stored procedure has been dropped but the .dll has not been deleted, the following will reenable it:

EXEC sp_addextendedproc 'xp_cmdshell', 'xpsql70.dll'

EXEC sp_addextendedproc 'xp_cmdshell', 'xplog70.dll'

Read full chapter

URL:

https://www.sciencedirect.com/science/article/pii/B9781597494243000104

Hacking database services

Jeremy Faircloth , in Penetration Tester's Open Source Toolkit (Fourth Edition), 2017

SQL Server Stored Procedures

One important difference between SQL Server and Oracle is the use of precoded stored procedures and extended stored procedures in SQL Server. Stored procedures are pieces of code written in Transact-SQL (T-SQL) that are compiled upon use. An example of a useful stored procedure is sp_addlogin, which is the stored procedure used to create a new user. Some others are listed in Table 6.2.

Table 6.2. Useful SQL Server Stored Procedures

Stored Procedure Name Purpose
sp_addlogin Creates a new SQL Server account
sp_defaultdb Changes the default database for an account
sp_denylogin Disables an account from connecting to the database
sp_droplogin Deletes an account
sp_grantdbaccess Associates an account to a database
sp_grantlogin Allows an account to log in
sp_helplogins Provides information on accounts
sp_helpuser Provides information about accounts and roles
sp_password Changes the password for an account
sp_revokedbaccess Drops an account from the database
sp_revokelogin Drops an account from the server

Extended stored procedures are similar to stored procedures except they contain dynamic link libraries (DLLs). Extended stored procedures run in the SQL Server process space and are meant to extend the functionality of the database to the host server. One extended stored procedure useful to the penetration tester is xp_cmdshell, which allows the user to execute commands in a shell on the Windows operating system. Stored procedures in SQL Server can greatly improve database capabilities, however, they can also create significant vulnerabilities. We'll discuss exploitation of stored procedures in the next section.

Read full chapter

URL:

https://www.sciencedirect.com/science/article/pii/B9780128021491000063

Hacking database services

Jeremy Faircloth , in Penetration Tester's Open Source Toolkit (Third Edition), 2011

5.3.3 SQL Server stored procedures

One important difference between SQL Server and Oracle is the use of pre-coded stored procedures and extended stored procedures in SQL Server. Stored procedures are pieces of code written in Transact-SQL (T-SQL) that are compiled upon use. An example of a useful stored procedure is sp_addlogin, which is the stored procedure used to create a new user. Some others are listed in Table 5.2.

Table 5.2. Useful SQL Server Stored Procedures

Stored Procedure Name Purpose
sp_addlogin Creates a new SQL Server account
sp_defaultdb Changes the default database for an account
sp_denylogin Disables an account from connecting to the database
sp_droplogin Deletes an account
sp_grantdbaccess Associates an account to a database
sp_grantlogin Allows an account to log in
sp_helplogins Provides information on accounts
sp_helpuser Provides information about accounts and roles
sp_password Changes the password for an account
sp_revokedbaccess Drops an account from the database
sp_revokelogin Drops an account from the server

Extended stored procedures are similar to stored procedures except they contain dynamic link libraries (DLLs). Extended stored procedures run in the SQL Server process space and are meant to extend the functionality of the database to the server. One extended stored procedure useful to the penetration tester is xp_cmdshell, which allows the user to execute commands in a shell on the Windows operating system. As you can see, stored procedures in SQL Server can greatly improve database capabilities. However, they can also create significant vulnerabilities. We'll discuss exploitation of stored procedures in the Open source tools section for SQL Server later in this chapter.

Read full chapter

URL:

https://www.sciencedirect.com/science/article/pii/B9781597496278100054

Database Backup Security

Denny Cherry , in Securing SQL Server (Third Edition), 2015

Deleting Old Backups

Once each of the backups is being done to a separate file, the older files will need to be deleted at some point. If the backups are not deleted, eventually the backups will fill the hard drive they are being written to, causing new backups to fail to function. If a database maintenance plan is being used to backup the databases, it can be configured to remove the database backups after a specified number of days. In the SQL Server 2000 maintenance plan (Figure 10.1), this can be done by checking the checkbox and setting the value to the required number of days as shown in Example 10.1. In the SQL Server 2005 and above maintenance plan, this can be done by adding a "Maintenance Cleanup Task" to the maintenance plan and setting the path for the backup files as well as the file extension to be deleted as shown in Figure 10.2.

Figure 10.1. Showing the page of the SQL Server 2000 maintenance plan which controls the deletion of database backups.

Figure 10.2. Showing the page of the SQL Server 2005 and up maintenance plan which controls the deletion of database backups.

As shown in Figure 10.2, which presents the SQL Server 2005 and higher, the "Maintenance Cleanup Task" only supports cleaning up one file extension at a time. If transaction log backups are backed up with a different file extension, then a second "Maintenance Cleanup Task" will need to be added to the maintenance plan for the second file extension.

The "Maintenance Cleanup Task" in SQL Server 2005 and above is not all that complex as a function, as can be seen by clicking the View T-SQL button at the bottom of the "Maintenance Cleanup Task" dialog. This operator simply calls the xp_delete_file extended stored procedure, which can be found in the master database. This procedure, when the correct parameters are passed to it, will automatically delete any backup files in the specified path, as well as subfolders. The extended stored procedure xp_delete_file accepts five parameters:

1.

Reports or Backups = Uses a 1 for reports and 0 for backups;

2.

Path = The path to the backup files;

3.

Extension = The extension of the backups to process;

4.

Date to delete to = The newest backup to keep; and

5.

Search subfolders = 0 only searches the specified folder, 1 searches the specified folder and the first level of subfolders.

This procedure can be easily called passing a variable into the procedure for the value of the date to stop deleting files, as shown in Example 10.3.

Example 10.3

The use of the xp_delete_file extended stored procedure to delete older backup files.

There are a couple of catches when using the xp_delete_file extended stored procedure. The first is that this is technically an undocumented stored procedure and its use may change at any time at Microsoft's whim. The second is that the procedure can only be used to delete valid database backups that are created using the native CREATE BACKUP and CREATE LOG commands. This is because the xp_delete_file extended stored procedure opens each file before deleting it to ensure that the file is a valid Microsoft Tape Format (MTF) file. If the file is not a valid MTF file, then the xp_delete_file will not delete the file. This can become a problem when using one of the third-party backup utilities such as Dell's LiteSpeed for SQL Server, or Red Gate SQL Backup, as these backup utilities do not use the native MTF file format, and as such these backups are not recognized by xp_delete_file extended stored procedure. When using one of these third-party tools, a different technique needs to be used to manage the deleting of these database backups. One technique to do this is to use PowerShell to delete the backups that are older than the required amount of time to keep. A simple one-line PowerShell script will identify all the files that are more than four weeks old, sending that list to the remote-item applet that will remove each of the files as shown in Example 10.4. Example 10.4 shows how to process two different file extensions (or more) in a single command. To change the file extensions, simply change the values in the comma separated list within the -include parameter. If a single value needs to be passed into the -include parameter in the PowerShell script, the double quotes can be removed from around the -include parameter.

Example 10.4

PowerShell script that removes the database backups that do not need to be kept on hand.

Beyond the need to delete old backups to keep disk space free, there is a security implication to keeping database backups on disk for longer than is needed. The more files that are on disk, the more data that a data thief could walk away with. Any data breach, including the loss of encrypted backup files, should be taken seriously. The fewer files to maintain, the smaller the risk in keeping those files around. For those worried about having to restore the database from older files, the files should never be removed from the local disk until they have been archived to a tape backup system that would allow the backup files to be restored to disk and then restored to the SQL Server engine.

Note

SQL Server Analysis Services Does Not Use MTF

Only two components of the SQL Server product suite require that backups be made of the data they keep: the SQL Server engine and the SQL Server Analysis Services engine. However, the SQL Server Analysis Services engine does not create its database backups using the MTF format. This means that, like the third-party SQL Server engine database backups, the SQL Server Analysis Services backups cannot be deleted by using the xp_delete_file extended stored procedure. A process such as the PowerShell script shown in Example 10.4 must be used to remove these older backups.

Read full chapter

URL:

https://www.sciencedirect.com/science/article/pii/B9780128012758000105

Database Backup Security

Denny Cherry , in Securing SQL Server (Second Edition), 2013

Deleting Old Backups

Once each of the backups is being done to a separate file, the older files will need to be deleted at some point. If the backups are not deleted, eventually the backups will fill the hard drive they are being written to, causing new backups to fail to function. If a database maintenance plan is being used to back up the databases, it can be configured to remove the database backups after a specified number of days. In the SQL Server 2000 maintenance plan, this can be done by checking the checkbox and setting the value to the required number of days as shown in Figure 9.1. In the SQL Server 2005 and above maintenance plan, this can be done by adding a "Maintenance Cleanup Task" to the maintenance plan and setting the path for the backup files as well as the file extension to be deleted as shown in Figure 9.2.

Figure 9.1. Showing the Page of the SQL Server 2000 Maintenance Plan Which Controls the Deletion of Database Backups

Figure 9.2. Showing the Page of the SQL Server 2005 and Up Maintenance Plan Which Controls the Deletion of Database Backups

As shown in Figure 9.2, which presents the SQL Server 2005 and higher, the "Maintenance Cleanup Task" only supports cleaning up one file extension at a time. If transaction log backups are backed up with a different file extension, then a second "Maintenance Cleanup Task" will need to be added to the maintenance plan for the second file extension.

The "Maintenance Cleanup Task" in SQL Server 2005 and above isn't all that complex as a function, as can be seen by clicking the View T-SQL button at the bottom of the "Maintenance Cleanup Task" dialog. This operator simply calls the xp_delete_file extended stored procedure, which can be found in the master database. This procedure, when the correct parameters are passed to it, will automatically delete any backup files in the specified path, as well as subfolders. The extended stored procedure xp_delete_file accepts five parameters:

1.

Reports or Backups   =   Uses 1 for reports and 0 for backups;

2.

Path   =   The path to the backup files;

3.

Extension   =   The extension of the backups to process;

4.

Date to delete to   =   The newest backup to keep;

5.

Search subfolders   =   0 only searches the specified folder, 1 searches the specified folder and the first level of subfolders.

This procedure can be easily called passing a variable into the procedure for the value of the date to stop deleting files, as shown in Example 9.3.

Example 9.3

The use of the xp_delete_file extended stored procedure to delete older backup files:

DECLARE @date DATETIME

SET @date = DATEADD(dd, -28, getdate())

EXEC master.dbo.xp_delete_file 0,N'd:⧹backups',N'bak',@date,1

EXEC master.dbo.xp_delete_file 0,N'd:⧹backups',N'trn',@date,1

There are a couple of catches when using the xp_delete_file extended stored procedure. The first is that this is technically an undocumented stored procedure and its use may change at any time at Microsoft's whim. The second is that the procedure can only be used to delete valid database backups that are created using the native CREATE BACKUP and CREATE LOG commands. This is because the xp_delete_file extended stored procedure opens each file before deleting it to ensure that the file is a valid Microsoft Tape Format (MTF) file. If the file is not a valid MTF file, then the xp_delete_file will not delete the file. This can become a problem when using one of the third-party backup utilities such as Quest's LiteSpeed for SQL Server, Red Gate SQL HyperBac, or Red Gate SQL Backup, as these backup utilities do not use the native MTF file format, and as such these backups are not recognized by xp_delete_file extended stored procedure. When using one of these third-party tools, a different technique needs to be used to manage the deleting of these database backups. One technique to do this is to use PowerShell to delete the backups that are older than the required amount of time to keep. A simple one-line PowerShell script will identify all the files that are more than 4   weeks old, sending that list to the remote-item applet that will remove each of the files as shown in Example 9.4. Example 9.3 shows how to process two different file extensions (or more) in a single command. To change the file extensions, simply change the values in the comma separated list within the -include parameter. If a single value needs to be passed into the -include parameter in the PowerShell script, the double quotes can be removed from around the -include parameter.

Example 9.4

PowerShell script that removes the database backups that do not need to be kept on hand:

Get-ChildItem "d:⧹backups⧹" "-include ∗.BAK,∗.trn" -recurse | where {((get-date)-$_.creationTime).weeks -ge 4} | remove-item -force –recurse

Beyond the need to delete old backups to keep disk space free, there is a security implication to keeping database backups on disk for longer than is needed. The more files that are on disk, the more data that a data thief could walk away with. Any data breach, including the loss of encrypted backup files, should be taken seriously. The fewer files to maintain, the smaller the risk in keeping those files around. For those worried about having to restore the database from older files, the files should never be removed from the local disk until they have been archived to a tape backup system that would allow the backup files to be restored to disk and then restored to the SQL Server engine.

Note

SQL Server Analysis Services Does Not Use MTF

Only two components of the SQL Server product suite require that backups be made of the data they keep: the SQL Server engine and the SQL Server Analysis Services engine. However, the SQL Server Analysis Services engine doesn't create its database backups using the MTF format. This means that, like the third-party SQL Server engine database backups, the SQL Server Analysis Services backups cannot be deleted by using the xp_delete_file extended stored procedure. A process such as the PowerShell script shown in Example 9.4 must be used to remove these older backups.

Read full chapter

URL:

https://www.sciencedirect.com/science/article/pii/B9781597499477000095

Platform level defenses

Justin Clarke , in SQL Injection Attacks and Defense (Second Edition), 2012

Ensure That the Database Server Software is Patched

Keeping software up to date with the current patch level is a fundamental security principle, but it's easy to overlook given that database servers are not usually Internet-facing systems. An attacker can often exploit server vulnerabilities via an application-level SQL injection vulnerability just as easily as though he were on the same network as the database server. The exploit payload could be a sequence of SQL commands that exploit a SQL injection vulnerability in a PL/SQL package, or even shell code to exploit a buffer overflow in an extended stored procedure. Automated update mechanisms are ideal for keeping up to date. You can keep SQL Server up to date with Microsoft Update ( http://update.microsoft.com). Oracle database administrators can check for current updates by signing up with the Oracle MetaLink service (https://metalink.oracle.com/CSP/ui/index.html). MySQL and PostgreSQL will often be packaged by the operating system vendor (for example, Red Hat), and can therefore be patched via the same method used for updating the operating system—if installed or compiled manually, updates will need to be installed manually, and therefore it is not recommended to custom install unless this is required. Third-party patch management systems are another way to keep patch levels current. Table 9.1 shows commands that can help you determine the version of the database server software for SQL Server and Oracle. Also included in the table are links for checking the version information to tell whether your database server is completely patched for these platforms.

Table 9.1. Determining SQL Server/Oracle Database Server Versions

Database Command Version Reference
SQL Server select @@version http://support.microsoft.com/kb/321185
Oracle -- show database version http://www.oracle.com/technetwork/topics/security/alerts-086861.html
select ∗ from v$version;
-- show version of installed components
select ∗ from dba_registry;
-- show patchlevel
select ∗ from dba_registry_history;

Read full chapter

URL:

https://www.sciencedirect.com/science/article/pii/B9781597499637000098

SQL Server – Stored Procedure Attacks

Rob Kraus , ... Naomi J. Alpern , in Seven Deadliest Microsoft Attacks, 2010

Accessing Stored Procedures

Once an attacker has administrative control over the SQL Server instance, attacks can be leveraged against the stored procedures implemented on the server. Stored procedures come in different flavors and provide different functionalities. For Microsoft's SQL Server, three main categories of stored procedures exist:

•

User-defined stored procedures are implemented to maximize code reuse and user-defined operations via Transact-SQL (T-SQL) statements or using the .NET framework Common Language Runtime (CLR).

•

Extended stored procedures allow database developers to create reusable code in languages such as C. This is a legacy method and will be removed at some point in the future. B

•

System-stored procedures provide administrative interfaces for some of the administrative management of the SQL Server instance.

Accessibility of stored procedures will depend on the version of SQL Server installed and the configuration of the server. In the last several versions of SQL Server, Microsoft has slowly implemented controls and configuration changes to the default implementation of SQL Server in an attempt to reduce the exploitation of some of the more well-known vulnerabilities associated with SQL Server.

Tip

The sqlcmd utility is new as of SQL Server 2005 and provides additional features and options as compared to the osql utility. In some cases, the osql utility may not be compatible with all of the features found in SQL Server 2005 and 2008. Microsoft recommends using the sqlcmd utility to ensure compatibility with the new features found in these versions. In this chapter, we will be using the sqlcmd C utility for our examples, as many of the commands are identical in comparison to the legacy osql utility. D

Executing stored procedures interactively using the sqlcmd utility is a fairly straightforward task. Once a valid account is obtained, an administrator may use the sqlcmd utility to connect to the SQL Server and execute command to access data or perform functions. Successful connection to the SQL Server with the sqlcmd utility will enable you to execute commands in a command-line environment.

Depending on the SQL Server version and the implemented configuration, stored procedures may or may not be enabled. Figure 3.1 provides an example of an administrator connecting to the SQL Server and attempting to leverage the functionality of the xp_cmdshell extended stored procedure. The initial error message indicates that the requested stored procedure is disabled and the administrator is not able to successfully complete the command as requested; however, if the stored procedure has not been fully removed, the administrator can reenable the stored procedure with a few simple commands, assuming that the administrator has appropriate permissions to do so.

FIGURE 3.1. Enabling xp_cmdshell Stored Procedure

The database engine stored procedure "sp_configure" allows configuration of many options globally on the SQL Server instance. Using sp_configure to reenable the stored procedure will allow the administrator to continue on with the task at hand.

1>EXEC sp_configure 'show advanced options',1

2>GO

1>RECONFIGURE

2>GO

1>EXEC sp_configure 'xp_cmdshell',1

2>GO

1>RECONFIGURE

2>GO

Read full chapter

URL:

https://www.sciencedirect.com/science/article/pii/B9781597495516000030