How do you secure your sql servers accessed by C# applications

Question

Simple and contrived example:

C# desktop application talks to SQL Server database. All orders exist in Orders table.

Application views, creates and amends orders. In this example a user can only amend their own orders.

Concerns:

Storage of connection string if using dedicated sql credentials. Even if user credentials are used, application security could be bypassed by connecting directly through Excel or Access.

Solutions:

Provide access to SQL through web service/middleware only. Good, but not necessarily viable in this case.

Encrypt connection string in application somewhere. Not hugely secure, security through obscurity.

Secure database by granting access to specific stored procedures, views, etc and no access to actual tables. SP's and views take into account the user's rights/credentials. Pretty awful. Ok for simple examples (Select where user , becomes complicated once you introduce users in different groups, manager relationships, etc.

Alternatives:

How would you approach this?

Thanks

Answer 1

Even if user credentials are used, application security could be bypassed by connecting directly through Excel or Access

what do you mean? you should not allow users to connect to SQL Server directly or with Excel or Access. They should NOT know the sa or other password.

After this, surely you could encrypt some sections of your app,config so that nobody can see its content.

I would really have the logic that a user can only modify his/her own Orders at the application level. Could be done also in the stored procedures I guess but it depends and more details should be known about this to suggest the best or most appropriate approach.