SQL Server Authentication or Integrated Security?

Question

We have some corporate intranet users using a WinForms app to work on a system with SQL server behind. Integrated Security is setup, allowing all users update and delete permissions, where application security limits how and where table updates take place.

However, some users are power users with SQL query tools at their disposal, and access the DB directly for building reports. However, with integrated security, they have default update rights on tables where they should not have, as the application apply rules to the updates.

Is this an example of where it's more appropriate providing the app with a central SQL authenticated login, whilst users get read only rights for integrated security?

Answer 1

sql authentication is one option. Stored procedures are another. However, building more granular roles for assigning just the appropriate permissions to just the appropriate user types is where you should really be looking.

Additionally, I would really avoid giving these users direct access to the DB at all. Security reasons aside, it doesn't take much for a user who isn't proficient in SQL to accidentally execute a query that will swamp your database server and create an effective denial of service. Even pros can do this accidentally from time to time.

Instead, give them access to a reporting services or analysis services type solution, or use replication to give them access to a clone of the data. This way your production system is protected.

Answer 2

Personally I would do all application data access through stored procedures. I would set Integrated security to only allow users to run the SP's and not manipulate the data directly.

Advanced access can be given to DB admins to manipulate the data directly when needed.

Group based permissions will provide you with much more flexibility for access rights, and less administrative burden when controlling these with integrated security.

Answer 3

As Jon mentioned stored procedures would give you the protection over direct table modifications. There are other options too. You can use SQL Server's "Application Role" (via sp_setapprole proc). This enables you to continue to use a separate ID for everyone but only at application connection time (through the front-end) are the user's rights elevated.

A major downside to using a shared ID is you lose track of who is submitting SQL to the server though if they're all internal you can get to the machine name.

Something else is concerning though. It sounds as if your users can connect to the database and run queries at will. You run a major risk of downtime in the application due to user behavior in the directly connected SQL sessions. If you can pull it off you may want to try to have a reporting database created that is updated at intervals that your business can tolerate, i.e., daily. HTH

Answer 4

I presume from the way that you've worded your question that your app executes sql statements directly. If you could refactor it so that it executes stored procedures, you could grant exec rights on the procedures and deny direct updating of the tables. This might not be possible though, depending on what your app does.