Note: we updated the code on August 23, 2013. The new code includes an “abort” feature, discussed in the article Instrumenting Microsoft SQL Server to Abort Dangerous Queries.

Our Deviare hooking engine can be used to hook into Microsoft SQL Server 2012 RTM (11.00.2100.60) and 2014 CTP1 queries at the application level. Tools like WireShark use a different approach since they intercept SQL Server traffic at the network level. The key benefits of intercepting queries at the application level are:

Since the query is a string you do not need to understand a protocol to retrieve it

As the query is received on a single point, you do not have to handle a variety of communication channels between the client and the server

It is possible to change the flow of the application and modify or cancel a query

The sample code below dynamically loads SQL Server public debugging symbols and hooks the CSQLSource::Execute function.

In 2013, the most critical security risk is injection. Our code can be used to develop your own monitoring and mitigation tool. With a lot of zero day bugs around it is not possible to depend only on vendors and their hotfixes. The problem is even worse for closed source applications. For example, Microsoft is ending support for Windows XP on April 8, 2014. How can your organization deal with that? The three main options are:

Migrating to a newer operating system Paying for extra support Using tools like Deviare to quickly implement custom security sandboxes and security mitigation tools.

Code

The code is available on github.

Prerequisites

Download Deviare and register it for 32-bit or 64-bit To enable the Deviare engine to use debugging symbols, the following library files are required: symsrv.dll which can be obtained in the Debugging Tools for Windows package and msdiaXX.dll which is part of the DIA SDK, available in Microsoft Visual Studio installation folder, under DIA SDK/bin directory. The Recommended Debugging Tools for Windows version is 6.12.2633 or higher. Tested DIA SDK versions are 9.0 (msdia90.dll), 10.0 (msdia100.dll) and 11.0 (msdia110.dll). You must copy those 32 and/or 64-bit DLLs to the dllx86 and dllx64 folders in the root directory of the project, depending on which platforms you want to target. SQL Server SQLSERVR.EXE service platform should match the project architecture for the interception to succeed.

Use

Compile the solution Make sure sqlservr.exe service is running Copy all database files from the project’s DB folder into the binary output folder. See below for information about user-defined databases. Run SQLSvrIntercept.exe from the commandline with Administrator rights. It will load and cache the symbols under c:\symbols and then displays “Ready.”.

Development Notes

If the application will be deployed to a Microsoft Windows environment which does not have Visual Studio 2012, make sure that the runtime C++ dependencies are available. If they are missing:

To deploy the Debug compilation you can copy the library from %ProgramFiles%\Microsoft Visual Studio 11.0\VC\redist\Debug_NonRedist\x64\Microsoft.VC110.DebugCRT to the System32 directory in the destination computer. To deploy the Release compilation you can copy the %ProgramFiles%\Microsoft Visual Studio 11.0\VC\redist\x64\Microsoft.VC110.CRT to the System32 directory in the destination computer, or install the Visual Studio 2012 Redistributables from http://www.microsoft.com/en-us/download/details.aspx?id=30679

Generating User Defined Databases

Deviare offers many advanced features such as counting, inspecting and dereferencing function parameters, getting parameter types, and intercepted function skip. A database containing the function signature is required for those features to work with a specific function.

The default Deviare databases are enough for most standard operating system libraries.

However, in case you need to intercept a function that is not present in the default databases, Deviare provides a tool to generate user defined databases. This tool is located in the Deviare-DbGenerator distribution. It should be fed with a C header file containing the function and type definitions to add.

A simple example (along with our own CSQLSource::Execute signature definition) is included in the DB folder for this project. The database files were generated from the sample BASE.H file.

Note that databases are not required for basic interception: you can intercept an anonymous function by specifying its address. However databases are required for query blocking, which uses the Deviare’s call-skipping feature.

Acknowledgments

Nektra’s Hernan Di Pietro wrote the SQL Server interception tool. After researching a bit without finding the correct function to hook he asked on Reverse Engineering Q&A where Brendan Dolan-Gavitt kindly pointed him to the correct function, allowing him to complete the project.

He was helped by Windows driver development, Data Loss Prevention Solution Development and Windows Software Development teams.

Notes

Some of Microsoft’s product versions do not have public debugging symbols, which are required in order to use the SQL Server interception tool Not tested on SQL Server 2005 or SQL Server 2008

See Also

Further Reading