SUMMARY
Utilities that use detours or similar logic to change the behavior of Microsoft SQL Server can result in the following issues:
- Performance problems
- Incorrect results
- Corruption on disk and in memory
- Loss of SQL Server response
- Unexpected process termination
- Inability to use standard diagnostics, such as the fn_get_sql function and the DBCC INPUTBUFFER command
- Increased chance of issues that are related to security
Note Features that use detours or similar techniques to change the behavior of SQL Server are outside the scope of Microsoft support services. If you are working with SQL Server support services on a SQL Server issue, the support professional will ask you to turn off or remove such detours or similar features. The detours or features must be removed so that the reported problem can be reproduced without the detours or features before additional troubleshooting can occur. If the problem occurs because of such detours of features, primary support may be denied by Microsoft.
MORE INFORMATION
Detours are powerful. Detours provide enhanced capabilities and a risk/reward tradeoff. Typically, when a detour is implemented in SQL Server, the applicable code is injected into the process space. SQL Server methods or functions are intercepted. This activity can change the basic performance behavior of SQL Server.
The following are some scenarios and possible side effects:
- Incoming network traffic (TDS) packets are scanned and changed. The detour is added at a critical location at the net_readdata network process thread. Even some 100 CPU cycles at this location can significantly reduce batch rate throughput.
Changing the actual TDS data can lead to memory scribblers. This problem has resulted in various SQL Server stability problems and in data corruption. Problems can cause a TDS packet to be partially changed and to replay garbage to SQL Server. Logging facilities at this level can expose passwords and other sensitive data that SQL Server tracing is designed to suppress and to help secure. - SQL Server parsing routines are detoured to change behavior. The following are possible side effects of this scenario:
- Execution plans may not match actual query text.
- A command is submitted only one time from the client. However, the command is executed multiple times.
- Trace output shows the original command instead of the altered query.
- The DBCC INPUTBUFFER command shows the original command instead of the altered query.
- The fn_get_sql function can show incorrect data. Additionally, the fn_get_sql function is susceptible to exceptions and incorrect results. The fn_get_sql function is used by many monitoring solutions and may cause problems on the monitoring solutions.
- Overall User Mode Scheduler (UMS) and SQL Server Operating System (SQLOS) scheduling can be interrupted, leading to loss of SQL Server response, performance changes, and outages.
- Win32 APIs that provide
enhanced security features are detoured. Depending on the implementation, logging
facilities at this level could expose passwords and other sensitive data. Overall UMS and SQLOS scheduling is interrupted, leading to loss of SQL Server response and outages.
The following example shows that the
kernel32!GetQueuedCompletionStatus function has been detoured.
MyDLL!MyGetQueuedCompletionStatus
ssnetlib!ConnectionReadAsyncWait
In the assembly for the
GetQueuedCompletionStatus function, the first instruction has been replaced with a jump instruction.
0:038> u kernel32!GetQueuedCompletionStatus
kernel32!GetQueuedCompletionStatus
77e660f1 e90a9f00aa jmp 21e70000 ß This points to an address that does not appear in the loaded module list (lm). It is injected code.
77e660f6 83ec10 sub esp,10h
The assembly for the injected code shows the detoured activity and a call to the MyDLL file.
0:038> u 21e70000
21e70000 55 push ebp
21e70001 8bec mov ebp,esp
21e70003 51 push ecx
21e70004 8b4518 mov eax,dword ptr [ebp+18h]
21e70007 50 push eax
21e70008 8b4d14 mov ecx,dword ptr [ebp+14h]
21e7000b 51 push ecx
21e7000c 8b5510 mov edx,dword ptr [ebp+10h]
21e7000f 52 push edx
21e70010 8b450c mov eax,dword ptr [ebp+0Ch]
21e70013 50 push eax
21e70014 8b4d08 mov ecx,dword ptr [ebp+8]
21e70017 51 push ecx
21e70018 e8234d19ee call MyDLL+0x4d40 (10004d40) <- Call to the MyDLL file.
21e7001d 8945fc mov dword ptr [ebp-4],eax
21e70020 8b55fc mov edx,dword ptr [ebp-4]
You can use Debugging Tools for Windows to determine whether detours are being used. To do this, follow these steps.
Note Always test this method before you try it in production. When you use Debugging Tools for Windows, the process can freeze when you run the commands. This behavior can adversely affect a production server.
- Attach Debugging Tools for Windows to SQL Server or load a full user dump file.
- Issue the following debugger command. This command inspects each image against the on-disk image to determine whether detours have been injected.
!for_each_module "!chkimg -v @#Base -d"
- Detach the debugger.
To obtain Debugging Tools for Windows, visit the following Microsoft Web site:
If the in-memory image has been altered, the output may resemble the following:
Comparison image path: c:\program files\microsoft sql server\mssql\binn\ssnetlib.dll\ssnetlib.dll
Scanning section: .text
Size: 56488
Range to scan: 0c261000-0c26eca8
0c263710-0c26371a 11 bytes - ssnetlib!ConnectionClose
[ 8b ff 55 8b ec 83 ec 10:68 00 00 00 00 e9 27 8a ]
0c2641e0-0c2641ea 11 bytes - ssnetlib!ConnectionReadAsync (+0xad0)
[ 8b ff 55 8b ec 83 ec 38:68 00 00 00 00 e9 00 7e ]
0c265160-0c26516a 11 bytes - ssnetlib!ConnectionWriteAsync (+0xf80)
[ 8b ff 55 8b ec 83 ec 28:68 00 00 00 00 e9 ba 70 ]
Total bytes compared: 56488(100%)
Number of errors: 33
33 errors : 0c260000 (0c263710-0c26516a)
You can review the assembly to look more closely at the issue.
0:038> u ssnetlib!ConnectionClose
ssnetlib!ConnectionClose]:
0c263710 6800000000 push 0
0c263715 e9278ada03 jmp MyDLL!MyGetQueuedCompletionStatus <- A detour has been installed.
We recommend that you contact the provider of the detours or similar techniques for information about how to use the detours or techniques in SQL Server. For more information about detours and similar techniques, visit the following Microsoft Research Center Web site: