Limit DB access outside application

  • We have applications connected to SQL using windows authentication. While having connection with Application user can also access to Database instance on the same time as well. We need to limit the access of user outside application. Can any one give any way around to this issue?

    Thanks in Advance

    Regards,

    SM

  • Go into SQL Server Management Studio and change the security settings for that login so that it can't access the other database. Here's an introduction to security in SQL Server.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • salman.sadruddin (12/3/2013)


    We have applications connected to SQL using windows authentication. While having connection with Application user can also access to Database instance on the same time as well. We need to limit the access of user outside application. Can any one give any way around to this issue?

    Thanks in Advance

    Regards,

    SM

    Please be more specific - if possible, give examples. If you have:

    User A

    User B

    Application 1

    Application 2

    Do you mean, for instance:

    User A is allowed to use Application 1

    User A is not allowed to use Application 2

    User A is not allowed to use SSMS, sqlcmd, or any other tool to get to SQL Server for any reason

    User B is not allowed to use Application 1

    Application 1 uses Windows trusted authentication to SQL Server

    ...

    etc. etc.

    ...

    Ideally, include all the cases that are allowed, and as many cases that are not allowed as possible.

  • Have you looked at logon trigger feature?

  • One option would be to change the applications so that they connect to the database using an application specific service account which the users will not know the credentials for. Then remove access to the database for the users who don't require it.

  • Is it web based application? If yest then create SQL login with appropriate permissions and let the application access database via this login only. Let application user get authenticated on application and pass those credemtials to newly created logins.

    Let me know if you need more details.

    Chandu

    [font="Comic Sans MS"]If you dont have question, you wont get answer[/font]

  • Is the users get authenticated at the application or database ? if at application changing the the access on only application account that connects database will be fine else chnage the security for all the users (Windows AD groups will be easier to manage if you have lot of users)

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply