0

Microsoft recommends using Windows authentication when connecting a Windows application to an SQL server database.

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

I understand this to mean that the database must have a user with enough permissions to manipulate data and that user links to the currently logged in Windows user. If this is true, how do I prevent the user from bypassing the application and simply modifying data directly in the database?

It seems like I am stuck between using Windows Authentication and potentially allowing users to modify data directly in the database, or attempting to hide the connection string password somewhere so only the app can modify this data.

Improve this question
1
  • If your windows app has a middle application later, you can connect to that, then use the middle application layer to connect as a single windows service account (this is basically how most web apps work) Commented Dec 11, 2014 at 22:50

3 Answers 3

Reset to default
1

If you're that concerned about it, you can implement a logon trigger on the server that for certain people (e.g. members of a certain Windows group), they can't log in unless the application name has a certain value. Note that this is weak security since it's pretty easy to set the application name (even in SSMS). It can/will slow down the logon process, though. So keep that in mind if that's a concern for you.

Alternatively, you can have your application authenticate to and interact with an application server, after which the application server connects to and interacts with the database. The application server can run as a service account, to which you'd grant the permissions you need. This way, the end users' accounts aren't in the database to do raw DML against the db.

But I agree with the other answer here: stored procedures are the classic answer to this question.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

It is possible to create stored procedures / views etc and only allow the user permission on those. This prevents the user from accessing the database structure directly, and you maintain control over what the user can do (via creating the functionality in the stored procedures / views). If using windows credentials, I think that this would be the best solution.

This site explains how to grant there permission on stored procedures. http://msdn.microsoft.com/en-us/library/ms345484.aspx

Improve this answer

1 Comment

Thank you, but this looks like it pushes quite a bit of logic into the database, which is less than ideal. Versioning sprocs has been far more expensive to maintain than branching code in my experience.
0

Here is the list of options, for posterity:

Windows authentication only

  • Pros: simple, No secret to hide.
  • Cons: user can easily modify data bypassing your app

Password in connection string

  • Pros: simple, prevents user meddling in your db's.
  • Cons: have to hide a password yourself, which is always the worst option.

Sprocs access

Create sprocs to access your data, grant access only to those sprocs. No one but the dbo can alter tables.

  • Pros: Tightest control over what both the user and the application can do to the data
  • Cons: Higher coupling of database and application; more expensive than the first 2 options.

Proxy

Create a second executable, whether a web or a windows service, with which your GUI application communicates. The 2nd executable can run with different, securely hidden credentials (IIS, Windows Services).

  • Pros: Decoupled database and executable, securely hidden secrets.
  • Cons: By far the most expensive solution.
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.