Encrypt passwords on Sql Server 2008 using SHA1

Question

I have designed a Log In System using C# where the username and password is checked in SQL server 2008 before loading the main page. I wish to encrypt the stored password on the database. Is it possible to do it using C# and SHA1 algorithm?

Following is my stored procedure:

ALTER procedure [dbo].[proc_UserLogin]
 @userid varchar(20),
  @password nvarchar(50)
  As 

  declare
  @ReturnVal              varchar(500)


SET NOCOUNT ON      

  if exists(select userid,password from LoginManager where userid=@userid and password=@password)
  set @ReturnVal='0|Logged in Successfully'
  else
  set @ReturnVal='1|Login Failed/Username does not exist'

  select @ReturnVal

C# Code

public void button1_Click(object sender, EventArgs e)
        {
            mainform = new Form1();
            string[] v;

            OleDbConnection conn = new OleDbConnection("File Name=E:\\Vivek\\License Manager\\License Manager\\login.udl");

            try
            {

                conn.Open();
                string query = "EXEC dbo.proc_UserLogin'" + username.Text+ "', '" + password.Text+"'";
                OleDbCommand cmd = new OleDbCommand(query, conn);
                string s = Convert.ToString(cmd.ExecuteScalar());
                v= s.Split('|');
                if (v[0]=="0")
                {
                    mainform.Show();
                    this.Hide();
                }
                else
                {
                    MessageBox.Show("Please enter correct user credentials and try again");
                }


             }

             catch (Exception ex)
             {
                 MessageBox.Show(ex.Message);
             }


              conn.Close();

         }

I have gone through similar questions asked by other users here, but they were not working for me. Can anyone suggest changes to the code, so that password encryption can be accomplished?

Thanks

there are already tried and true pre-build methods of user stores and password hashing, why attempt to roll your own? What you've posted isn't anywhere near a solution, so what are you hoping for as an answer? — Kritner, Oct 08 '14 at 12:14
You seriously need to consider sql injection. NEVER execute user input directly. Parameterize those queries, or even better move it to stored procedures. http://xkcd.com/327/ — Sean Lange, Oct 08 '14 at 14:27
good ol little bobby tables. Technically he's already using stored procedures though :P — Kritner, Oct 08 '14 at 14:39
Sure he is calling stored procedures but calling them as pass through sql is basically the same thing as executing dynamic sql. The way that is written took the security and threw it away. — Sean Lange, Oct 09 '14 at 13:48

score 3 · Accepted Answer · edited May 23 '17 at 12:11

Hash and salt passwords in C#

https://crackstation.net/hashing-security.htm

https://www.bentasker.co.uk/blog/security/201-why-you-should-be-asking-how-your-passwords-are-stored

As I stated in my comments, hashing passwords is something that you probably shouldn't be doing yourself.

A few things to note:

SHA1 is not recommended for passwords
Passwords should be salted
You should use a verified userstore framework rather than attempting to create your own, as you will likely "do it wrong"
I'm sure there are many more

That being said, to accomplish your specific question, you would want something like this:

Users
----
userId
passwordHashed

passwordHashed stores a hashed version of the user's password (the plain text password is never stored anywhere in persistence.)

for checking for valid password something like this is done:

ALTER procedure [dbo].[proc_UserLogin]
 @userid varchar(20),
  @password nvarchar(50)
  As 

  declare
  @ReturnVal              varchar(500)


SET NOCOUNT ON      

  if exists(select userid,password from LoginManager where userid=@userid and password=HASHBYTES('SHA1', @password))
  set @ReturnVal='0|Logged in Successfully'
  else
  set @ReturnVal='1|Login Failed/Username does not exist'

  select @ReturnVal

For inserting/updating user passwords, you need to make sure to store the hashed password not the plain text password, as such;

INSERT INTO users(userId, passwordHashed) 
VALUES (@userId, HASHBYTES('SHA1', @rawPassword)

or

UPDATE users 
SET passwordHased = HASHBYTES('SHA1', @rawPassword) 
WHERE userId = @userId

EDIT:

just realized you're asking how to accomplish the hash in C#, not SQL. You could perform the following (taken from Hashing with SHA1 Algorithm in C#):

public string Hash(byte [] temp)
{
    using (SHA1Managed sha1 = new SHA1Managed())
    {
        var hash = sha1.ComputeHash(temp);
        return Convert.ToBase64String(hash);
    }
}

Your code snip could be:

            conn.Open();
            string query = "EXEC dbo.proc_UserLogin'" + username.Text+ "', '" + this.Hash(System.Text.Encoding.UTF8.GetBytes(password.Text))+"'";
            OleDbCommand cmd = new OleDbCommand(query, conn);

You should also note that you should parameterize your parameters to your stored procedure rather than passing them in the manner you are - which it looks like you already have a separate question in regarding that.

Thanks a lot @Kritner.I am new to c#.All I wanted is that whenever the user enters the password ,it should be stored in the sql server Database in encrypted format.Our customers are not that much sophisticated to crack the password :) So no worries — user2614235, Oct 09 '14 at 11:56
Hey Kitner I am able to encrypt the password ,Now how will comparison take place ie(Converting encrypted to decrypted to compare) , — user2614235, Oct 10 '14 at 05:25
this is why i pointed out encryption IS NOT the same thing as hashing. An SHA1 hash is *one way* once you hash it, you cannot reverse it to its original form. the same characters will get hashed to the same hash each time. This is why hashes are used and not encryption, administrators of systems cannot "recover" passwords ever, the only option is to reset. — Kritner, Oct 10 '14 at 10:26
for "checking for login" success, you already have that in my answer. You take the user's raw password, hash it, and then compare that hash to the hash on record for that user. Is there something I'm missing that you need? — Kritner, Oct 10 '14 at 10:27
I modified the stored procedure as per your answer,but I am getting login failed message.I dont know where I am going wrong Do you know bout this Exec master.dbo.xp_sha1 (@0Password, @EncPassword output how to add this to my stored procedure and encrypt it — user2614235, Oct 10 '14 at 11:04
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/62819/discussion-between-kritner-and-user2614235). — Kritner, Oct 10 '14 at 11:45

Encrypt passwords on Sql Server 2008 using SHA1

1 Answers1

Linked