2

I have an SQL Server running on a Windows 2008 R2 machine which needs to have basic queries run on it (SELECT / INSERT / UPDATE). These operations are executed directly by the client, an application written in C# which installed on one computer in a different location so the connection is over the internet.

Since the nature of the operations run on the DB is so simple, I would rather not write a back-end on top of the SQL Server. So the setup for security:

1) Username and Password written in client and submitted.

2) A (parameterised) query is run on the server and the password hash and salt are returned to the client.

3) The password and salt are appended, hashed using SHA_512 and is compared to the password hash.

4) If the two match you are given access to the toolset that creates and sends the queries.

After researching the topic somewhat I feel like this system has some security flaws, but I cannot pin-point exactly what these vulnerabilities might be.

Improve this question
3
  • For one you have a server on the Internet with port 1433 open. Two program can be hacked. Commented Aug 22, 2016 at 8:31
  • The traffic between your client and the sql server is encrypted, right? Commented Aug 22, 2016 at 8:37
  • Yes SSL. Well for now Im testing so its all Local using 'Integrated Security'. Commented Aug 22, 2016 at 8:39

3 Answers 3

Reset to default
2

Based on your scenario I would consider creating SQL accounts for each user of your application. When the user logs into your application use these credentials when constructing the SQL Server connection string and allow the server to perform the credential validation. This is often referred to as pass-thru authentication.

Even better, if your application will be executed by users on the same Active Directory domain as the SQL Server you can use the more secure Windows Integrated Security (what you are currently using for local testing) and the users will not need to login at all. The connection will simply use their current AD credentials. See this link for more information about setting up user accounts in SQL Server using Windows domain credentials: https://msdn.microsoft.com/en-us/library/dd787978.aspx

Also, with either option you will still want to use a TLS protected connection (Encrypt=true) to help prevent snooping of credentials over the wire.

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

4 Comments

So now a user can just download SSMS log on and have direct access to the database? Does not sound like a good plan to me.
@Paparazzi: you would want to ensure that each user account was granted specific database permissions to limit their actions within the database. With database level auditing you could also track changes made by each user. That being said, the user will always have the ability to directly connect to the database when using a simplistic model like this where the SQL Server is the only server side component. Anything more advanced from a security perspective will require an additional service/proxy on the server side that implements the authentication layer.
Thanks brice this is what I was looking for. it i a bit frustrating that I set up a log in system with SHA_512 and salts etc but this seems like the best way to do it for the 2-3 users that this system will have.
Permissions include SELECT / INSERT / UPDATE. They can do a lot of harm. Hey - what do I know - this got accepted.
0

If I understand this correctly, then the whole password-check is done on the client side. Is that correct? If so, it seems potentially insecure.

Do you trust the client itself entirely? If an unauthorized person can get access to the client, or create another client that mimics your client, then that significantly increases the chance they'll gain access to your server side.

It definitely sounds like you would be more secure creating a back end system, even if it is just a very simple proxy-service that simply checks the username and passwords, and then forwards the query itself as-is.

That way, the password-hash and salt will never have to leave the server.

Improve this answer

7 Comments

1) Yes if I did not make it clear the password check is done on the cleint side 2) Getting access to the client is a potential threat. 3) Would the proxy system not involve the password leaving the client in plaintext? (be it over SSL)
Perhaps, but are they not sent that way initially anyway?
No, the username is sent in plaintext, the password is temporarily stored on the client awaiting the hash and salt for the 'password check' process. Mind you this is a desktop application.
Ok; then you've avoided sending the password, but you're letting the client tell ther server: "Yes, I've got the right to access this stuff!". The problem is that the server has no way of knowing whether that is true; a rogue client could in theory be lying, and pretending a mismatched password was valid.
Thank you for your time. It seems like there should be .NET packages that handle this stuff so that I don't have to reinvent the wheel with my (possibly insecure) implementations.
|
0

A client connecting directly to a SQL Server is not secure. If the client is on the local hard drive then it can be hacked.

You are not finding tools for what you are trying to do because it is not a good design.

Don't get your logic of "Since the nature of the operations run on the DB is so simple, I would rather not write a back-end on top of the SQL Server." If it is easy then that is a reason to set up a back end.

.NET has a whole infrastructure for it. Windows Communication Foundation.

Improve this answer

2 Comments

The DB and the client will not be on the same local hard drive. Does this mitigate the risks you are talking about?
NO it does mitigate the risk. What is so hard about concept of a client can be hacked. Credentials in the client can be hacked. Just giving credential to their domain login is even worse. Now they don't need to hack a single thing.

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.