locked
Update database ONLY through the app and stop back end tampering RRS feed

  • Question

  •  

    We are using Sql Server Database as backend for our product. We want to protect our database from tampering. That means the database can only be updated through the app. However if DB has been  modified using backend or by any other means then app should raise alert next time used and stop working.

     

    Can anyone help me on this regard?

     

    Thanks,

    Chanan

     

    Monday, February 28, 2011 8:59 AM

All replies

  • Refer http://learn.iis.net/page.aspx/731/secure-your-sql-server-database/


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, February 28, 2011 9:04 AM
  • How did they get access? For example, do they have sysadmin permission on the SQL Server or administrator access to the computer? We need to know how they received access in order to know how to block their access.
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Monday, February 28, 2011 4:29 PM
  • Hi Chanan,

    Instead of allowing users to connect the database from back-end for first time and planning to restrict next time, it is better to avoid users while login to the server from back end.

    please go through the following possible solution.

    SET ANSI_NULLS ON

    GO

     

    SET QUOTED_IDENTIFIER OFF

    GO

     

    CREATE TRIGGER [Application_restriction_trigger] ON ALL SERVER WITH EXECUTE AS 'mention SQL Server service account'

    FOR LOGON

    AS

          BEGIN

                IF (original_login() = 'sa' OR IS_SRVROLEMEMBER('sysadmin') = 1 OR PROGRAM_NAME() = 'mention your program name')

    Begin

          Print 'This user is a Trusted User'

    end

    else

    Begin

    ROLLBACK;

    end

          END

    GO

     

    SET ANSI_NULLS OFF

    GO

     

    SET QUOTED_IDENTIFIER OFF

    GO

     

    ENABLE TRIGGER [Application_restriction_trigger] ON ALL SERVER

    GO

     

    ---------------------------

    Thanks,

    RajaSekhara Reddy . K

     


    Thanks, RajaSekhara Reddy . K
    Monday, February 28, 2011 5:58 PM

  • You can create a database DDL trigger which captures information for events like create,alter,drop, update, delete, insert etc in the database as xml and inserts it into aother table outside the database. Query that table on login name to see who changed what and decide whom to block and not.

    Alternatively you can also do a change tracking as explained by Adam Machanic in his blog here: http://sqlblog.com/blogs/adam_machanic/archive/2010/11/11/pass-2010-change-tracking-and-change-data-capture-demos.aspx
    Monday, February 28, 2011 8:20 PM
  • I would offer up that this is exactly what APPLICATION ROLES were created for, with restricting access form everyone else.

    -Sean

    Monday, February 28, 2011 9:29 PM
  • All these can be useful strategies, but remember that if the user that you are concerned about has Administrator control of the computer, then they can still access the database directly.
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Monday, February 28, 2011 9:42 PM