Troubleshooting Performance with Your SQL Server
Non-intrusive ZERO Impact SQL capture allows every production server SQL statement with it’s performance to be captured 24×7. Requires no intrusive server profiles or traces that degrade the server performance by 25% to 60% (SQL Server Magazine, August 2008). Since 80% of database server performance problems are due to SQL, knowing the current and historical performance of every production SQL statement is a requirement to providing consistent end-user response time to all end-users. Most database servers have their 4,000 unique application SQL statements run at least 10+ million during the business day. Sql Power Tools will track for each unique SQL statement it’s deviation in performance from the prior day, week, month, quarter or year. Get more information here.
Database Issue Recovery Tips and Experiences
I know the question has been largely about recovering from injection-based attacks, but there are some good tips and tricks here that apply to just about any type of surprise modification or issue with your system. Rex wrote in with their system of digging in to determine what happened and what needs to be fixed (and how to block it in the first place):
"We were just subject to some unsuccessful attacks. Method used was the "Binary encryption" method you have discussed in the past.
But they were persistent for some time so we took steps to monitor for success and prevent them from happening.
Our first step was a rough analysis of IIS logs determined that the attack was coming from multiple sources but all were trying to update back-end data with a Trojan "link" that was referenced to .js file.
So, we wrote a script that scanned our databases (1 at a time, but this could be expanded to all dbs with some work) using a similar technique to what the attackers were trying to do. So, traverse system objects looking for char (and variants) fields then search each field for a string ".js"
We then through the objects that had a hit on the string with what field into a table that was used for further analysis. Manual examination revealed in every case matches were usually things like myfile.jsp or bob.jswhatever@email.com
It was roughly put together but it got the job done.
At the same time as our searching began we notified the network engineering teams and they got to work on a suppression issue. We provided them with initial examination and then daily reports of the number of attacks to what sites. We had a junior dba comb the logs of about 30 sites on a daily basis searching for instances of "EXEC" which is almost always required unencrypted to provide this data. In the long run we will automate this. Search and have a monitoring package on each of our hosting environments (internal and external facing)
The division of labor on our side is that DBA and 3rd tier support manage IIS and application/db level. Network engineering manages Internet Appliances, Proxy, Etc. In our case we use Netscaler and Cisco products to do this depending on the geographic location(s). I believe we also use the Stonegate security device as well.
After about 3 weeks of tweaking the network guys got the problem suppressed and we are down to 0 attacks daily from a peak of 500-1000.
I’m not sure if they used it but I figured even if binary/hex encryption is being used there has to be an encoded version of the word "CURSOR" in the binary string.
In the "disguised" string of the attack, a cursor (loop) is used to traverse system tables to build a list of tables to attempt to compromise. Cursor is pretty unique word and certainly unique in a binary/hex pattern.
So if we look for it in all its permutations….
0x637572736F72 –cursor
0x437572736F72 –Cursor
0x435552534F52 –CURSOR
0x435572736F72 –CUrsor
0x435552736F72 –CURsor
0x435552536F72 –CURSor
0x435552534F72 –CURSOr
0x437572736F52 –CursoR
0x637572734F52 –cursOR
0x637572534F52 –curSOR
0x637552534F52 –cuRSOR
0x635552534F52 –cURSOR
0x635572736F72 –cUrsor
0x635552736F72 –cURsor
0x635552536F72 –cURSor
0x635552534F72 –cURSOr
0x437552534F72 –CuRSOr
0x437552534F52 –CuRSOR
etc.
The pattern to get any case of the word would be:
First Pair 63 or 43 if true then check second pair
Second Pair 75 or 55 if true then check third pair
Third Pair 72 or 52 if true then check fourth pair
Fourth Pair 73 or 53 if true then check fifth pair
Fifth Pair 6F or 4F if true then check 6th pair
Sixth Pair 72 or 52 if true then suppress
A regex expression could be written at the packet level to block requests that contain a minimal number checks. However that is still a lot of additional processing to be done. It might be possible to slip past this check if an additional layer of abstraction was done like building the word cursor from CHAR(x). Again, I have not received word back from the Network team on the actual method of suppression.
Knock on wood things are looking good."
Featured White Paper(s)
Windows Server 2008: What To Expect
When people hear that a new version of Windows Server is coming out, they often ask, "How different is it?" "Is this a quantu… (read more)
BitLocker: Is It Really Secure?
What is BitLocker? How does it work? Is it a truly safe way to protect your data and applications, hard drive, and operation … (read more)