1

Background

I'm building a single tier application Winforms application using C#. A SQL Server localdb database is attached to the application that runs when the application does. The plan was to use Windows Authentication to verify that the user is part of the MyApplication role/group and can use the application. But, in order to prevent users from accessing the database via other means, I was thinking of using an Application Role so that only the one SQL application user can edit the db tables.

Question

My understanding is that in order to use an Application Role, you need to provide a username and password in the connection string. I can encrypt this information, but obviously it will need decoded before being sent to the database. Is this a concern in a single tier application? What are other alternatives?

Andrew
  • 11
  • 4

2 Answers2

0

To use an application Role, you'll use the sp_setapprole stored procedure where you will provide the name of the application role and the password, rather than sending it in the connection string. When you use application roles, you still connect using an ordinary login, but once you successfully sp_setapprole, your connection loses its user permissions and instead gains the permissions of the application role. Having the decoded password in memory is a concern if you have reason to believe that users may decide to use a debugger to attach to your process to extract the password. Any administrator would also be able to decrypt the encrypted password on disk as well if you choose to use windows machine-level key containers. Using only a single tier for an application that uses a database is a security risk, and you have to decide based on the circumstances surrounding the application whether it is an acceptable risk to gain the reward of skipping a few weeks of design and development.

Source: https://technet.microsoft.com/en-us/library/ms190998(v=sql.110).aspx

I highly recommend implementing a web api to manage your application's interactions with the database as well as security. This web api could use a windows "service" account to authenticate with the database, and users would authenticate with the api using their individual windows accounts. This has the added benefit of you never having to think about passwords. As far as managing API permissions, that is an issue that is up to you to design and implement as you see fit. The main issue you need to understand and deal with is uniquely identifying AD users. Take a look at this SO post for more info on that: What Active Directory field do I use to uniquely identify a user?

Your service account would have all necessary permissions on the database to do what the application needs to do, but not all api users would necessarily have permission to use all api functions. You would manage a store of uniquely identified AD users that have permission to use the application and what permissions they have. The rest is design and implementation details that are up to you.

Community
  • 1
  • 1
Jakotheshadows
  • 1,485
  • 2
  • 13
  • 24
  • 1
    Thanks @Jakotheshadows . So, if security was a bigger concern to me, then the approach would be to separate this into two tiers. Are you saying I should, for example, create a Web API service to hide my business logic and database behind, and then have the Winforms app on the other tier? Then, the server running the Web API service would be the only machine/user with access to the database? – Andrew Mar 10 '16 at 20:12
  • Yes, and your service could use a windows authentication "service" account. Users could authenticate to the service using their AD accounts, which you could manage API permissions for, and when they execute api actions in the database it would use the application service account. Therefore, no USER windows accounts would have any sort of direct access to the database, and you have the added benefit of not ever having to think about passwords. – Jakotheshadows Mar 10 '16 at 20:37
  • Right, that makes a lot of sense. I appreciate your responses greatly. I imagine this has the added benefit of making it easier to add a MVC web application down the road that has access to the same database. Looking back at the Winforms app, how do you manage permissions for users using their AD accounts within the Web service? And, how does the cross over to using the web service account to access the database occur? – Andrew Mar 10 '16 at 21:19
  • The comment I was going to leave was too long, so I updated the answer. – Jakotheshadows Mar 10 '16 at 21:32
  • You have opened up my eyes here, but everything is very logical. I'll take some time now to digest. Thank you very much for taking the time to respond! – Andrew Mar 11 '16 at 01:03
0

Define user with privilege only to execute stored procedures. By this way if someone use SQL Management Studio, s/he cannot browse/edit tables and even cannot see the table names.

i486
  • 6,491
  • 4
  • 24
  • 41
  • Thanks for the response. I had actually come across this approach in my research, however I'm trying to keep as much of my application in C#/.net as possible, including business logic and CRUD operations (via entity framework). Using procedures for CRUD would take me in a direction I'm not looking to take. – Andrew Mar 11 '16 at 01:07
  • You can use entity framework with stored procedures, just FYI. – Jakotheshadows Mar 11 '16 at 16:11
  • Maybe I need some perspective, but aside from the security related advantages, what benefits would using procedures provide? My experience using procedures in the past has been frustrating from a debugging standpoint. I feel that keeping all application logic in C# is comparatively beneficial, including initial development speed and long term maintenance. – Andrew Mar 12 '16 at 04:06
  • @Andrew If you move business logic to stored procedures, you implement real client-server system. The main advantage is data protection in multiuser (simultaneous) work. For example, if you process stock quantity at C# side on 5 different PC in the same time for the same item - you will need complex logic to get correct values. If the same is implemented with stored procedure, all current quantity values are kept only in database and cannot be overwritten with old/incorrect values. Also, the clients do not need powerful hardware - all heavy calculations are made on the server. – i486 Mar 12 '16 at 14:19
  • That sounds like a real benefit. However, building off the conversation on @Jakotheshadows answer, if I end up building a 2 tier system where a single service (Web API)/database supplies data to multiple clients, I would be reaping the benefits you mentioned while keeping business logic in C#, no? – Andrew Mar 12 '16 at 17:54
  • @Andrew People don't understand that business logic in stored procedures is equivalent of 2 tier (for those who want multi tier app at any price). – i486 Mar 12 '16 at 23:10
  • The only problem with this approach is that users could still conceivably use these stored procedures to circumvent application logic in a way that you may or may not foresee. If you do this, it is up to you to make sure that if they do decide to just connect with SSMS that they can't do undesirable things with these stored procedures that they would be unable to do with the application itself. I also disagree with the statement that you'd need "complex logic" to get correct values in a multi-client scenario. – Jakotheshadows Mar 13 '16 at 07:31
  • Stored procedures can be at least partially protected from execution outside their official application. I have implemented such scenario I hope it is enough secure. There is initial `login` SP expecting username and password (stored in DB - the pwd from GUI is extended with shared secret). It returns session GUID which is bound to current `@@ssid`. All other SP-s receive the GUID as input parameter and verify that `@@ssid` corresponds to this GUID. About "complex logic" - it is too long (for SO comment - it is for email) to give you real example with simple SQL implementation. – i486 Mar 13 '16 at 10:24