4

I am currently involved in the implementation/design of an existing application for a large scale customer. The application has a similar model as e.g. vCenter Server whereby a number of components store information in and extract information and workflows form a central database. The environment is managed from a console application which uses a windows service to connect to the database.

Due to security considerations SQL Authentication is not an option, but the application does support Windows Authentication. However when using windows authentication it passes the credentials of the user who is using the management console application on to the database in stead of using the service account under which the service runs.

This results in the situation where the security roles as they are defined within the application (AD users/groups mapped to certain permissions) requires these AD objects to also have a SQL Server login and certain permissions on the database (db-writer for certain tables, depending on the specific permissions in the app).

Apart from the additional administration, this also creates a security risk IMO because the whole point of the application security roles in this specific application is to allow users access to only a part of a certain table (e.g. a subset of the objects). But since they now have a SQL login and db_writer on that entire table they could bypass the app and with a direct connection to the database they could read/alter records they would not be allowed to view/manipulate through the application.

This risk could then (partly) be mitigated by restricting access to the database on a network level, or possibly use triggers to check the calling hostname and program name, but as far as I know that is not completely safe.

The only benefit as is presented to me is there is now a full audit trail available in SQL server on exactly who did what. But this is already implemented in the application as well so it seems kind of moot to me.

I have been frantically searching for any info on this and what would be the best practice. I can only find ASP / Web based stuff but nothing regarding this type of application.

Is it really the best way to go with passing through authentication to SQL server or should the app handle authorization and send all database requests using the service account?

P.S.: This is my first question on stackexchange, if I posted on the wrong sub-site or you find my question inappropriate, please just let me know.

Bilesh Ganguly
  • 343
  • 1
  • 3
  • 13
mvdwrd
  • 41
  • 1
  • 2
  • I have to agree with you. Service accounts certainly would be best in this case. I am interested, since writing this two years ago, what has been your experience ? – Walter Aug 10 '15 at 05:33
  • I have been working quite extensively with this application for the past two years and feel confident in saying that there are many more very bad design decisions besides the authentication model. As the customer involved did not pursue a full blown installation (they abandoned the poc) it is no longer a real issue. I do however point out to other customers that the alleged option between the two security models offered (the other being SQL auth) is not really an option. They should go with SQL auth and if they have some issue with that, they should choose a different product all together. – mvdwrd Aug 11 '15 at 07:36

2 Answers2

3

In general, if you are using this model, you should not use any access method except stored procs and the rights shoud be on the procs themselves and never directly on a table or view. This means no dynamic SQL of any kind (including in procs). That way users who directly access the database can still only do the things they are allowed to do by the application. This is one place where using an ORM would be a disaster. There shoud be no SQL Server logins. Instead the users should be in windows groups that are given rights based on what they are allowed to do. These rights could consist of read rights for a table but no insert/updates or deletes that are not done through executing a stored proc. They would get exec rights only to the procs that their user group should be able to run in the application.

HLGEM
  • 28,709
  • 4
  • 67
  • 116
  • Thank you for your response. I was thinking along the same lines. Currently however all manipulation is done directly on the tables. There are no stored procedues in place at all. – mvdwrd Sep 17 '13 at 21:01
1

Managing database access through a service account is simpler. It segregates awareness of user rights from the database, and that has the usual benefits of such segregation, like reducing management and failure points. The less of those that one has, the easier it is to put together administration procedures and to diagnose problems.

Depending on your application, it may also enable better connection pooling and therefore better performance.

John Bickers
  • 773
  • 3
  • 7