Post reply

Limit access to SQL Servers

  • October 20, 2003 at 12:27 pm

    #83335

    Hi,

    We are contemplating moving from SQL sec. to NT Authorization. One question I have is, How to only allow my users to access the SQL Servers from certain applications (our in-house apps), and not be able to connect with others (MS-Access via ODBC, for instance)?

    Thanks,

    Paul

  • October 20, 2003 at 1:09 pm

    #478855

    Use an Application Role. Refer to Books OnLine (BOL), use the INDEX tab and enter:

    roles-SQL Server, application

    Then choose the option for title: Establishing Application Security and Application Roles.

    -SQLBill

  • October 21, 2003 at 9:07 am

    #478962

    The other option is something I believe Andy has mentioned... use SQL Server logins for these apps and give no permissions specifically to the user. App Roles have some issues that may be a problem.

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/

    K. Brian Kelley
    @kbriankelley

  • October 21, 2003 at 9:42 am

    #478970

    If you use standard sql logins for the app, is there a way to keep the password from the developers so they can't use other sources outside the app to manipulate the data, like QA?

    Thanks! - Chris

  • October 21, 2003 at 9:54 am

    #478971

    Actually, yes, but it's more of a change control procedure than anything else. This is similar to what we did on a project recently.

    For instance:

    Developer documents where username/password is stored (encrypted in the registry, for instance). Application is able to read encrypted string and decipher. Development team writes small program to take a connection string and encrypt using a symmetric function.

    DBA assigns username/password in production database.

    DBA, System Administrator, or member of change control runs program on production server to generate encrypted string and places it into the registry. Alternately, this could be bundled in the install package.

    The issue comes in if the developers are going to use the application as well. Then, unfortunately, there's not a whole lot you can do except audit. After all, they are in possession of the decrypt program. Profiler, however, will tell you the program.

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/

    K. Brian Kelley
    @kbriankelley

  • October 22, 2003 at 1:49 pm

    #479183

    Brian,

    You are right.:)

    mom

  • October 23, 2003 at 6:29 am

    #479261

    Hi all,

    Thanks for the input.

    Application roles: Don't want to do this, we are aiming for *really* thin clients, all business rules (including security) on back-end. Also, from what I have read in BOL and usenet, and as Brian notes, app roles have issues.

    Don't want any more SQL Server logins than necessary to implement NT Sec. This is because of the problems associated with moving logins around between servers.

    Developers will be members of NT groups that do not have access to prod boxes, so I think that potential problem is mitigated. They will be app users, but in a DB role having no write permissions.

    No, my problem is my users, some of whom are computer-savvy, trying to manipulate their own data without going through the application layer we've so thoughtfully written for them. (The "Relational" part of RDBMS sometimes eludes them, or makes life seem more difficult than it should be...).

    P

  • October 24, 2003 at 12:34 pm

    #479544

    I have run into the 'computer-savvy' associates at my job also. To limit their damage to the databases, we implemented the following:

    The developers of the application have full access on the development database which is located on a development server. The developers have access to the data on the production machine but are unable to add, change, or delete any of the database objects (tables and SP's). The application interacts with the database only through SP's. We do not allow in-line SQL statements. Also, the application has its own id that it connects to the database which has permission to execute the SP's but is unable to do anything at the table level.

    Dave N

  • October 24, 2003 at 12:55 pm

    #479548

    Hi Dave,

    OK, that's all good.

    Question 1) Can you tell who (which user) is connected to the server when they get there via the app?

    Question 2) If the app can connect to the server, and the developers can use the app, how can they not use it to write to prod.

    Quesion 3) In your implementation, what logins exist on prod? Just the app?

    Thanks,

    P

Viewing 9 posts - 1 through 8 (of 8 total)

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