Secure SQL Server accessed by fat client

Question

Is there a way to secure a sql server database which is accessed by a fat client? Meaning: The application communicates directly with the database as it places sql statements itself. That means, the connection string has to be somewhere on the client. Using this connection string (either with winauth or sql server authentication) any user can access the db using some management studio or command line and place different statements to the db than the GUI would let him.
What to do about that? I cannot place another layer between the client and the database as this architecture is fix.

Answer 1

That is what SQL Server permissions are for.

You can do things like give permissions to a user on a View or Stored Procedure without giving the user permissions to the underlying tables.

You might want to look into Application Roles

http://msdn.microsoft.com/en-us/library/ms190998.aspx

http://www.sqlservercentral.com/articles/Security/sqlserversecurityprosandconsofapplicationroles/1116/

Answer 2

First and foremost the whole point of a SQL Injection vulnerability is that the attacker is able to manipulate queries. This purposed protocol is an even worse vulnerability. But not only that this is also a clear violation of CWE-602: Client-Side Enforcement of Server-Side Security and CWE-603: Use of Client-Side Authentication.

In order to make this secure you must do the following:

Each user must also have their own locked down database. As in they only have select/update/delete/insert and no other privileges (especially not xp_cmdshell()!!!!). You cannot allow users share a database, or an attacker will be able to view other users information. An attacker will always be able to obtain the username/password for the sql server and be able to connect directly with his own client. Its hard to think of this relationship as being secure, in almost all cases this is massive vulnerability.

In all reality this is a very serious architectural flaw and you must build a server side component that builds quires for the client. This is usually done with SOAP (wcf for ms platforms).

Answer 3

In all security models, including Windows and SQL Authentication, access rights are granted to an user (an identity), not to an application. therefore, any access right needed by the application must be granted to the user running the application. When Windows authentication is used this means that the same user can leverage all the privileges needed by the application himself, from an SSMS query. This is a fundamental rule any administrator must understand. From a security point of view (meaning things like CC compliance and such) this is a fact and any attempt to circumvent it is doomed.

But from a practical point of view, there are certain measures that can be deployed. The most commonly used one is to use a logon trigger that validates the APP_NAME() and allows access for SSMS only from a well defined set of client workstations, and for a well defined set of users.

CREATE TRIGGER reject_SSMS
ON ALL SERVER WITH EXECUTE AS '...'
FOR LOGON
AS
BEGIN
IF (APP_NAME() = 'Microsoft SQL Server Management Studio' 
   OR APP_NAME() = 'Microsoft SQL Server Management Studio - Query')
   AND (ORIGINAL_LOGIN() NOT IN (...)
   OR HOST_NAME() NOT IN (...))
    ROLLBACK;
END;

What is important to understand that such mechanisms are NOT security features, as they can be easily circumvented by a malevolent user. They are more like door locks: they don't keep thieves out, they keep honest users honest.