May 1, 2018 at 1:06 am
Hello, Community!
For a little project I want to design a login concept which should allow users to register and login securely through an winforms application written in C#. The application is used on several devices which aren't in an active directory. Here are some steps that I have already thought of:
Thanks in advance!
May 1, 2018 at 12:09 pm
julian97 - Tuesday, May 1, 2018 1:06 AMHello, Community!For a little project I want to design a login concept which should allow users to register and login securely through an winforms application written in C#. The application is used on several devices which aren't in an active directory. Here are some steps that I have already thought of:
- I think that I need a database user account which credentials are saved in the application so it can connect to the database. On the database I will need a User table where the username and password of each user is saved.
- After the user has entered his username and password in the login form, the application user account connects to the database and runs a stored procedure which returns a value indicating if the username and password combination exists in the User table.
- Now I could continue with my application, but aren't there security vulnerabilities? If someone would reverse engineer my application, he could get the credentials of the applications database user and pretend to be any user in the ongoing application part. So do I need to always run stored procedures and pass the credentials that the user entered to login every time? Or is there any better solution?
Thanks in advance!
If you aren't using windows accounts then the password would have to be used by the client computer to connect to the host computer to get database access to then even get enough access to read the user and password table in the database, so if your goal is to prevent database access entirely, you will be powerless if someone can reverse engineer the password as you said and I don't see how stored procedures help because you have to make a database connection to run the stored procedure.
I guess you could do stored procedures that would limit what the "connection only" account could do and then try to do a user / salted password hash table but then you'd have to remember either that combo or pass a session variable back and forth.
So you could alternatively have to have the user have an sql login and a password to even get to the database and have one of those for each login and just skip the password table. Have the app ask once and then use those credentials to run any subsequent connections using sql authentication. In this case, I'm guessing its best to do your security in the database and like you said, do the work in stored procedures. If you need business level security beyond what you can do in SQL then I'm guessing like you said user tables that the stored procedures could consult with could help, but I don't see why you would bother storing a password in the user table as the user has already used the password to gain the SQL connection to begin with.
If you want to use a generic SQL login then you'd have to hope it doesn't get reverse engineered like you said.
With bigger apps, there is an app server between the client and the database and that app server manages user accounts, this way the client never has direct access to the SQL server and this is essentially how web apps do it, with the web server hosting database enabled programming acting as the application server in the middle, and the client software being a web browser. In this way, app managed user tables with salted password hashes would work much more conventionally.
Heh complicated enough that it took me a few times to even do this post 😉
another edit: make sure you don't store plain text passwords even if its just a play app unless you don't care to learn programming on any sort of professional level. google and learn how to use "salted password hashes".
May 2, 2018 at 2:29 am
patrickmcginnis59 10839 - Tuesday, May 1, 2018 12:09 PMjulian97 - Tuesday, May 1, 2018 1:06 AMHello, Community!For a little project I want to design a login concept which should allow users to register and login securely through an winforms application written in C#. The application is used on several devices which aren't in an active directory. Here are some steps that I have already thought of:
- I think that I need a database user account which credentials are saved in the application so it can connect to the database. On the database I will need a User table where the username and password of each user is saved.
- After the user has entered his username and password in the login form, the application user account connects to the database and runs a stored procedure which returns a value indicating if the username and password combination exists in the User table.
- Now I could continue with my application, but aren't there security vulnerabilities? If someone would reverse engineer my application, he could get the credentials of the applications database user and pretend to be any user in the ongoing application part. So do I need to always run stored procedures and pass the credentials that the user entered to login every time? Or is there any better solution?
Thanks in advance!
If you aren't using windows accounts then the password would have to be used by the client computer to connect to the host computer to get database access to then even get enough access to read the user and password table in the database, so if your goal is to prevent database access entirely, you will be powerless if someone can reverse engineer the password as you said and I don't see how stored procedures help because you have to make a database connection to run the stored procedure.
I guess you could do stored procedures that would limit what the "connection only" account could do and then try to do a user / salted password hash table but then you'd have to remember either that combo or pass a session variable back and forth.
So you could alternatively have to have the user have an sql login and a password to even get to the database and have one of those for each login and just skip the password table. Have the app ask once and then use those credentials to run any subsequent connections using sql authentication. In this case, I'm guessing its best to do your security in the database and like you said, do the work in stored procedures. If you need business level security beyond what you can do in SQL then I'm guessing like you said user tables that the stored procedures could consult with could help, but I don't see why you would bother storing a password in the user table as the user has already used the password to gain the SQL connection to begin with.
If you want to use a generic SQL login then you'd have to hope it doesn't get reverse engineered like you said.
With bigger apps, there is an app server between the client and the database and that app server manages user accounts, this way the client never has direct access to the SQL server and this is essentially how web apps do it, with the web server hosting database enabled programming acting as the application server in the middle, and the client software being a web browser. In this way, app managed user tables with salted password hashes would work much more conventionally.
Heh complicated enough that it took me a few times to even do this post 😉
another edit: make sure you don't store plain text passwords even if its just a play app unless you don't care to learn programming on any sort of professional level. google and learn how to use "salted password hashes".
Thanks for your detailed answer!
I'm still a bit unsure what way I should choose now. I think that I don't really need a SQL server login account since I could use a database user account as well. But I read some discussions in which it was said that even creating a database user for each user registration is bad practice. So I would prefer to create a user table. However, in this way it would be difficult to handle the permissions. Especially because I need a regular user and administrator group. I have two ways I think of right now:
1)
a) For the application there is a database user which credentials are saved in the application code (could be reverse engineered). This application database user connects to the database when opening the application. This database user has the permission to run several stored procedures (all procedures a user or a administrator could run).
b) A user registers through the application with a username and a password. In the user tables the username, the hashed salt+password, the decrypted salt value, the current session key and a flag if the user is an administrator is stored. When a user creates an account the username and password entered in the application will be sent to the database. There a salt value is generated with the NEWID() function. With HASHBYTES('SHA2_256', salt + 'password') the database generates the hashed value which is saved in salt+password column in the user table.
c) When a user logins the username and password is sent to the database and there the saved salt value is added to the password he entered. Then it generates a hash value with HASHBYTES('SHA2_256', salt + 'password') and compares it with the saved hash value in the salt+password column. If the two values equal the login is successfull, if they are different it's not. When the login is successfull a random session key will be generated with the NEWID() function. This session key will be generated new after each login. The session key is saved in the application as well in the users table in the corresponding column.
d) Now in every stored procedure the user runs the session key will be sent to the database to check if the right user accesses the database and for administrator stored procedures it will be also checked if the user is an administrator (with the admin flag in the users table). Only if the session key is valid (and the user is an administrator if he runs an administrator stored procedure) the stored procedure will continue with its actual task.
The thing where I'm not sure in way 1 is if it is secure to give the database application user the right to run all these stored procedures. Is the session key (and administrator group check) in each stored procedure enough or are there still security risks?
2)
The second way would be to create a database user for each account and add a user and administrator role. That would make a lot easier, but I'm not sure if that's the better approach.
Thanks in advance and I hope you take the time to read my post. 🙂
May 2, 2018 at 8:41 am
I don't think the hash process your using sounds correct, but to be safe on that just go ahead and check for some good routines out there, I'd probably do the same, so I'll not comment further on that. I will assume that if you elect to store and verify passwords yourself instead of using sql server's authentication to do that, then you will use routines that will accomplish that safely so lets just leave that alone for the rest of the conversation, in fact, I recommend that not only do you chat about this here, you also find other sources for what folks do. Its best not to just act just because some rando like me said it sounds ok. Trust but verify!
So for the rest of this, it comes down to a few more issues for me anyways.
If this is a wide area network or internet application, I'd simply not allow internet connections to the sql server so I'd bail from that entirely. I would put an app server between the client and database server and my choice there would be a web server.
If this is a local area network application then with further reflection I feel like I should mention that its not recommended to use Microsoft networking without Active Directory. With Active Directory you are assured you are only allowing legit devices on your network, not only are your users authenticated, your windows computers and client software get some level of authentication also and you get a bit of help with security there. And if you then use Active Directory I would then just recommend you use it for authentication also and put your users in there.
Otherwise, without Active Directory it comes down to two choices, do you do password authentication in your app, or do you let SQL Server do it. If you do password authentication and store your hashes in SQL Server then you will essentially be allowing guest connections to SQL and then allowing further access via stored procedures that check for and maintain the identity of the user holding that connection. This is going to result in a bigger programming workload for you and to do this correctly you'll have to get everything right, your "adversary" will only have to find one mistake. If you let SQL Server do authentication then that's simply that much less programming you have to do and additionally you can then avail yourself of the relatively rich set of security primitives T-SQL gives you as a part of the product itself.
I know you've probably read that giving separate user accounts is a bad idea, and if this were a web app that had a use case of potentially hundreds of thousands or even millions of users then yep, web app would maintain the users but in that case I would also not present an open SQL Server connection to the internet at large, there would be an application server doing the work of accepting connections and verifying users.
So I guess as usual, "it depends" lol
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply