Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2007
    Posts
    4

    Unanswered: Security to prevent external tools access to SQL Server

    We are setting up a security plan for accessing SQL Server, using Windows domain groups and Windows authentication. Data access would be closed down to using stored procedures and views. Our in-house written applications and reports would typically be the direct consumers of these stored procedures and views. That would also be the way our users would primarily access SQL Server data. However, it is my understanding (please correct me if I'm wrong), that anyone with a recognized Windows login, whose permissions grant him access to SQL Server stored procedures/views via using our in-house applications, could just as easily use some external tool such as Excel, Access, or Crystal Reports - if they knew what they were doing - to access the very same stored procedures and views.

    A. Is this correct?
    B. Is there a way to limit or control access from external tools, within the framework I've described above? Or, is there a preferred way of handling this?

    Thanks,
    Randy

  2. #2
    Join Date
    Sep 2003
    Location
    Vietnam
    Posts
    10

    Re:

    Hello Randy,

    Never setup accounts to access MSSQL by local permission.
    Should be private account only for MSSQL, via Store procedures and Views can be using.

    With each table, field... in database, can be limit to access with levels other

    So that, you can prevent that issues not wanting.

    Jockey

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    You are correct if your application architecture is a two-tier architecture where your database server is accessible from the user's desktop. However, it is very common these days for applications to use a tiered architecture, either because they are browser-based or because they use a server-side service layer to do all database access. If you use a tiered architecture then you can stop users accessing the database directly just by not opening up the DBMS's ports to end users' desktops.

    If you do need users to access the database directly then you can still limit their access to the activities permitted by procs and object-level permissions. That same level of access applies equally to your application and to those external tools. So if you design procs and permissions accordingly those other apps don't necessarily have to be seen as a "threat".

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If you have "problem users", you can set up a login trigger to query the program name, and kill the connection, if it is not one of the standard ones you want. I played around with a sample of one. If I can remember where I stashed it, I will post it, but I believe there are samples in BOL, as well. Look for Server Triggers.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Application Roles sound like what you want
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by MCrowley
    If you have "problem users", you can set up a login trigger to query the program name, and kill the connection, if it is not one of the standard ones you want. I played around with a sample of one. If I can remember where I stashed it, I will post it, but I believe there are samples in BOL, as well. Look for Server Triggers.
    That might thwart some casual users but since the application name is specified in the connection string it's a fairly trivial matter for an intelligent and determined user to set the application name to the same one used for the "real" application.

    As Pootle suggests, application roles may offer a marginally better alternative but they still rely on a "hidden" password and if your application is on the desktop then that password could be accessible in various ways (decompiling the source code, reading the binaries or wherever else you cache the password, watching memory or the protocol stack, etc). Just be aware that app roles are a "security by obscurity" method.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If you have people crafting connection strings in Access, and Excel with custom Application Names, then you don't just have "problem users", in my opinion. You have openly hostile users. Get the network guys to shut off their network line. Should only cost you a bottle of scotch.

Posting Permissions

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