locked
User Access RRS feed

  • Question

  • Is it possible in SQL Server 2000, that a user can access the database from an application build in VB but cannot login through Enterprise Manager or SSMS?

     


    Regards, Muhammad Bilal.
    Monday, October 17, 2011 7:32 PM

Answers

  • Hi Muhammad,


    I think it is not possible.
    Suppose you select Windows Authentication in your connection. No matter which authentication your SQL Server is set, you can log into the SQL Server via SSMS with your windows login.

    On the other hand, if you select SQL Authentication in your connection, you can use both windows login and SQL Server login in your connection. But if your SQL Server select windows authentication, you use SQL login in your connection, it will not be connected.

    That is, if you could connect your SQL Server from an application, you could login through SSMS too.

    Hope this helps.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by Iric Wen Wednesday, October 19, 2011 9:01 AM
    • Marked as answer by Stephanie Lv Wednesday, October 26, 2011 8:39 AM
    Wednesday, October 19, 2011 7:37 AM
  • Is it possible in SQL Server 2000, that a user can access the database from an application build in VB but cannot login through Enterprise Manager or SSMS?

     
    There are two options. One is to use application roles. The application issues sp_setapprole with a password. The user on its own can access the database, but has no permissions, whereas the application has all permissions needed. The problem is where to store the password. In a two-tier application, this is only security by obscurity, since a skilled user will be able to find the password eventually. On a three-tier application
    you can store it in a place where the user does not have access.

    The other option is to use Terminal Server. That is, the user needs to log in on Terminal Server to run the application. The network is setup so that he cannot reach SQL Server from his own machine.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Stephanie Lv Wednesday, October 26, 2011 8:39 AM
    Wednesday, October 19, 2011 9:26 PM

All replies

  • Muhammad, That would depend on the type of security access you are using for you application. If you are using for instance a SQL Server Login that the application uses to connect to the instance of SQL Server and has the appropriate permissions for the user to only perform tasks via the application, as long as the user never knows the application user account then the user does not have the ability via Management studio to gain access.

    Having said that if you want the application to use the users windows account, then you could look at having the application performing everything operation via stored procedures. This will then allow the user to have access via Management Studio but as long as you only give them execute permissions on the stored procedures that they need to use from within the application then this limits the access and work that the user is able to perform in the database.

    I hope this helps.


    Warwick Rudd
    MCT MCITP SQL Server 2008 Admin
    My Blog
    -------------------------------------------------------
    Please mark as Answered if I have answered your question
    Please vote if this was useful
    -------------------------------------------------------
    Monday, October 17, 2011 8:02 PM
  • Hi Muhammad,


    I think it is not possible.
    Suppose you select Windows Authentication in your connection. No matter which authentication your SQL Server is set, you can log into the SQL Server via SSMS with your windows login.

    On the other hand, if you select SQL Authentication in your connection, you can use both windows login and SQL Server login in your connection. But if your SQL Server select windows authentication, you use SQL login in your connection, it will not be connected.

    That is, if you could connect your SQL Server from an application, you could login through SSMS too.

    Hope this helps.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by Iric Wen Wednesday, October 19, 2011 9:01 AM
    • Marked as answer by Stephanie Lv Wednesday, October 26, 2011 8:39 AM
    Wednesday, October 19, 2011 7:37 AM
  • I believe this is impossible.

    The user who has the access to SQL Server via Application has the same privileges to access to SQL Server via SSMS


    velmurugan.s
    Wednesday, October 19, 2011 8:39 AM
  • Is it possible in SQL Server 2000, that a user can access the database from an application build in VB but cannot login through Enterprise Manager or SSMS?

     
    There are two options. One is to use application roles. The application issues sp_setapprole with a password. The user on its own can access the database, but has no permissions, whereas the application has all permissions needed. The problem is where to store the password. In a two-tier application, this is only security by obscurity, since a skilled user will be able to find the password eventually. On a three-tier application
    you can store it in a place where the user does not have access.

    The other option is to use Terminal Server. That is, the user needs to log in on Terminal Server to run the application. The network is setup so that he cannot reach SQL Server from his own machine.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Stephanie Lv Wednesday, October 26, 2011 8:39 AM
    Wednesday, October 19, 2011 9:26 PM