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.
Best Answer
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.