Results 1 to 7 of 7
  1. #1
    Join Date
    May 2009
    Posts
    2

    Unanswered: Restrict MS-SQL database for specific application

    Hi All,

    We are having VB.Net application which uses MS-SQL 2005 database as backend. If we use windows authentication using MS-SQL, apart from our VB.Net application, excel and other application are also connected to that database. How do we restrict the database specific for that application alone and restricting other applications (like excel, other vb.net apps) not to connect with that DB.

    Can anybody share your ideas.

    TIA
    Rammurali Krishnan B

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    This looks a lot like http://www.dbforums.com/microsoft-sq...rver-time.html, but much more understandable.

    Do you have any control over the VB.Net application?

  3. #3
    Join Date
    May 2009
    Posts
    2
    Hi,

    Actually there is no control in the VB.Net application.

    We are having multiple VB.NET applications using the same MS-SQL database. Our problem is, if any one of the VB.Net application is connected to MS-SQL database and connection established, then it is possible to connect to that database using other applications too without authentication. Now, we want to set validation in the database to identify different set of application during login. Is there any options available.

    I have identified that "Application Roles" feature in the MS SQL will resolve this issue. But as I haven't familar using the "Application Roles", I am not sure whether it will resolve the issue.

    Thanks,
    Rammurali Krishnan B

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Application roles are not very secure. They go some way to hiding the database from novice users but they probably won't keep out a determined intruder.

    The way to protect your database is in the middle tier or data access code. Either with stored procedures (and deny end user access to table) or with a server-side tier of .NET code that performs all data access. In other words, don't grant the end user direct access to the database itself.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If the application comes in with a specific application name, you can probably create a login trigger to bounce connections that do not come from the accepted application.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Sample logon trigger. This will probably work on SQL 2005 with the right Service Pack (sp1 or better, i think), and has been tested on SQL 2008 SP1
    Code:
    create trigger Application_Only on all server
    for logon
    as 
    begin
       if (program_name () <> 'SQL Query Analyzer')
        begin
    	rollback;
        end
    end
    EDIT: Note, this should be used with EXTREME caution. You might be able to lock yourself out of your server with this. Although, I bet you can drop the trigger by bringing up the server in safe mode, I would not want to try it.

  7. #7
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    The application name is just a property in the connection string. So it's an easy matter for a user to discover what it is and even easier to "impersonate" one application from any other that allows you to edit a connection string. Enough to deter casual browsing perhaps but no real security there.

    Quote Originally Posted by MCrowley
    I bet you can drop the trigger by bringing up the server in safe mode, I would not want to try it.
    You can do it with DAC, which won't fire the logon trigger.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •