locked
Restriced SQL Server DB connection not being restricted RRS feed

  • Question

  • User1383457719 posted

    We have an ASP website that connects to a SQL Server 2005 DB.  We use a dedicated user login in the connection string that has restricted rights on the DB (db_datareader and db_denydatawriter rights; furthermore, I've denied SELECT access to all of the system tables (sysobjects) for this user).  Yet, on Friday, we fell victim to a SQL injection where the malicious querystring included "SELECT a.name,b.name FROM sysobjects a,syscolumns b WHERE...".  How could this happen using the restricted DB connection for the page?

    Sunday, June 22, 2008 9:27 AM

Answers

All replies

  • User1073881637 posted

    The sql injections are being a pain. When you manually run the statement on the sql server connected to that user, does it execute or are you denied?

    I'd look at some tools to block sql injection attacks along with updating the code, if possible.

    http://www.port80software.com/products/serverdefender/

    http://www.aqtronix.com/?PageID=99

    I've not used either product personally, but they claim to help block sql injections.  I'd test them on a non-prod machine before deploying to a production environment.

    Sunday, June 22, 2008 11:59 PM
  • User1383457719 posted

    When I manually run a simple SELECT statement like "SELECT * from sysobjects" directly within SQL Server, it denies the request when I'm logged on as that user.  Any thoughs?

    Monday, June 23, 2008 8:38 AM
  • User-2064283741 posted

    Do the sql server logs confirm that when you access the database via the page that the user is teh same one?

    Monday, June 23, 2008 9:22 AM
  • User1383457719 posted

    good question.  My only interface to manage the DB is via Management Studio.  I'll ask the support team to look into this.

    Monday, June 23, 2008 9:37 AM
  • User1073881637 posted

    if you run a sql profiler trace, it would confirm which user account machine name and database that is executing the code.

    Here is a quick how-to on sql profiler.

    http://www.developer.com/db/article.php/3482216

    http://www.developer.com/db/article.php/3490086

    • Marked as answer by Anonymous Tuesday, September 28, 2021 12:00 AM
    Monday, June 23, 2008 9:44 AM