SQL Server Security Configuration Options for Windows Desktop Client/Server application

Question

I am developing a windows desktop client/server application in .NET where the client application connects to SQL Server Express 2005 via the SQL native client and a connection string. The client then executes SQL over the connection on the database directly (no stored procedures).

How can I configure SQL Server (or windows) security in such a way that only my signed application binaries can connect to the database and nothing else from the client machines? (ie. not SQL Server Management Studio Express, hacked versions of my binaries or other malicious code) Would I need to embed cryptographic keys in my application? If so, how can these be protected from disassembly attacks?

The client or server machines can be placed on a corporate domain. Using SQL Server full edition instead of SQL server express is also an option. I would also like to still be able to run SSMSE locally on the server machine for upgrades and support work (the server will have physical doors-and-locks security to prevent access to it).

My application needs to read data from tables to calculate summary information. The summary information does not need to be secured but the detailed individual rows do. I think this requirement excludes any form of table based permissions combined with windows accounts or sql server user names and passwords.

Answer 1

You want to look in to using Application Roles in SQL Server. I'm not sure how available these are on express.

Here's a bit of info on Application Roles

Answer 2

I would use some sort of windows account or username and password combination :-)

Sarcasm aside, what you are talking about is just permissions. Connect your app to the SQL server using a user account you control, and don't provide the account credentials to the user. Problem solved. Well, almost.

The only other way "in" is with an account like Administrator or SA that has a server role granting access to the whole SQL server, so you'd have to make certain the user does not have access to the SQL server through that path. That's pretty simple to do -- restrict the Builtin\Administrators group in SQL Server, for example by giving it only Public and not sysadmin, disable the SA account (if the server has mixed mode authentication), and substitute another account in your control to act as sys admin. Be careful not to lock yourself out :-).