The Crux of Windows Authentication? RRS feed

  • Question

  • Hi,

    I'm relatively new to SQL Server, so please bear with me. I am currently redesigning an application that uses windows authentication to authenticate to SQL Server 2005. I would like to continue using windows authentication in the new version of the software, given its security advantages and that it will save me having to implement my own account/user/authentication mechanisms in the application. However, there seems to be a gaping hole with windows authentication in that once a user or group is setup with windows authentication on SQL Server, there's no way to prevent them direct/manual access to the database. It's necessary for the users to have permissions to edit the tables, but they must be edited in accordance with the rules of my application. I've searched the forum a bit and have found similar threads to this one, but a lot of them centered around DRM or were focused on a specific application. My questions are these:

    - Are my assertions correct?
    - What are some work arounds for this problem? The only one I can think of is to lock down all the tables and only allow editing via stored procedures, which I would really like to avoid.
    - Is anyone aware of plans for Microsoft to address what I view as a shortcoming? For example, some sort of application password feature?

    Thanks for any insight.
    Thursday, May 14, 2009 8:16 PM

All replies

  • Yes, SQL Server can't differentiate between what you consider an "OK" app (your app) and a non-desirable app (SSMS, Acces or whatever). However, what you want to look into is "application roles" (CREATE APPLICATION ROLE). They were introduced in 7.0, so pretty mature functionality. There are some programming apspectes (like this is per connection), but it isn't unlikely that it can be useful.
    Tibor Karaszi
    Thursday, May 14, 2009 9:05 PM
  • With SQL Server 2005 SP2 and above, you may have the option to use a logon trigger. So if the user gains access to the database through the application, but should not gain access through any other means, you have the potential for restricting access using such. There are several provisos, however.

    • The application must correctly pass some identifying name (query against sys.dm_exec_sessions to see) that's not something generic (for instance, ".Net SqlClient Data Provider" is generic).
    • The user doesn't need access to any other databases on the server. With the logon trigger you can effectively log them out using a ROLLBACK TRANSACTION if they aren't coming from the correct application.
    • You can identify the logon in some way, such as through membership in a particular group or the like.
    • A smart and knowledgable user can still use a DSN to connect and spoof the application name. This isn't terribly hard, but the majority of folks won't know how to do it and they'll have to get the app name exactly right.


    K. Brian Kelley, http://www.truthsolutions.com/
    Friday, May 15, 2009 1:57 AM
  • Thanks guys, those are interesting points. I wasn't aware of Application Roles before and that's an interesting concept, although it still doesn't really meet my needs. My problem is that the users talking to the database through my application have different permissions. So 1 set of permissions per application won't work very well because I would have to grant that application role the "least common denominator" of permissions that everyone needs.

    I guess it would be possible to create multiple Application Roles for use by my application that essentially correspond to the users/user roles I already have set up and have my application call sp_setapprole with different role/password parameters accordingly. So basically I would lock down all of my tables so that they're only writable by the various application roles and have my application look up a user's corresponding application role out of a readable table, then use passwords stored in the source code to switch to that application role. But honestly, that seems like an abuse of the system and a lot of work ;)
    Friday, May 15, 2009 4:05 PM