How to save PASSWORD in SQL Server with bit or binary type data

[Niyazi]

Hi,

I developed an application and I am using SQL Server 2000 developer edition.
I create my database and I have also created tbl_USER table.

I have an ID, RealName, UserName, and UserPassword fields.

I want to save UserName and UserPassword using bit or binary data type with
VB.NET. Then ofcourse I have to retrive them to compare it later and if I
find match than user can enter the MAIN forum.

But I don't know how to save the UserName and UserPassword with bit or
binary type data. Is this best way to store the UserName and UserPassword
using bit or binary data type or should I find better way?

My major problem that 4 admin people can enter the sqlServer and read the
userName and UserPassword if I use nchar data type.

The encrypt method seems bit difficult at this moment. I can store images
and retrive them with SQL Server but I don't know how to achive saving the
password with binary data type.

Does anyone knows how to do it?

Thank you.

Rgds,
GC

[David Farr]

You might want to try creating an MD5 hash and then storing it in ascii/hex
format.

You then create a hash of the user input data for comparison.

Dave

"Niyazi" <Ni****@discussions.microsoft.com> wrote in message
news:9D**********************************@microsof t.com...

Hi,

I developed an application and I am using SQL Server 2000 developer
edition.
I create my database and I have also created tbl_USER table.

I have an ID, RealName, UserName, and UserPassword fields.

I want to save UserName and UserPassword using bit or binary data type
with
VB.NET. Then ofcourse I have to retrive them to compare it later and if I
find match than user can enter the MAIN forum.

But I don't know how to save the UserName and UserPassword with bit or
binary type data. Is this best way to store the UserName and UserPassword
using bit or binary data type or should I find better way?

My major problem that 4 admin people can enter the sqlServer and read the
userName and UserPassword if I use nchar data type.

The encrypt method seems bit difficult at this moment. I can store images
and retrive them with SQL Server but I don't know how to achive saving the
password with binary data type.

Does anyone knows how to do it?

Thank you.

Rgds,
GC

[David Farr]

Sorry that should have been Digest, not Hash.

Dave

[Niyazi]

Hi David,

Thank you for your reply. I know it wasn't good idea to tell you how can I
retrive information from SQL Server using MD5 and how to store it new user
account information as UserName and UserPassword using MD5.

Do you know where I can find a good example for MD5.

I created the UserName and UserPassword in SQL Server table call tblUSER and
their data was nchar. And I cahnge the data type to binary.

So now I can log in using MD5 but I don't know how to do it. Do you know how
to get a link for showing good example of the MD5 storing and retriving data
into SQL Server?

Thank you.

Rgds,
GC

"David Farr" <news".at." wrote:

Sorry that should have been Digest, not Hash.

Dave

[David Farr]

Hi,

SQL's not my usual line of country but this points in the right direction:-
http://aspnet.4guysfromrolla.com/articles/112002-1.aspx Does this help?

Which part are you having a problem with?

Dave

"Niyazi" <Ni****@discussions.microsoft.com> wrote in message
news:AD**********************************@microsof t.com...

Hi David,

Thank you for your reply. I know it wasn't good idea to tell you how can I
retrive information from SQL Server using MD5 and how to store it new user
account information as UserName and UserPassword using MD5.

Do you know where I can find a good example for MD5.

I created the UserName and UserPassword in SQL Server table call tblUSER
and
their data was nchar. And I cahnge the data type to binary.

So now I can log in using MD5 but I don't know how to do it. Do you know
how
to get a link for showing good example of the MD5 storing and retriving
data
into SQL Server?

Thank you.

Rgds,
GC

"David Farr" <news".at." wrote:

Sorry that should have been Digest, not Hash.

Dave

[Niyazi]

Hi David,

I have to thank you. It was the right direction that you show it to me.

I also find very good example from "Dev Articles":
"http://www.devarticles.com/c/a/VB.Net/String-Encryption-With-Visual-Basic-.NET/"

It was very good. I created seperate Class Library and now I can Encrypt the
"DATA" before sending into SQL Server (I use varbinary data type) and I can
also Decrypt the data to compare it with user input.

My problem was I am doing the applicatuion for small Turkish comapny which
is Collation name of the SQL Server is Turkish_BIN.

The user password and username have only 10 character. So I have to adjust
the Length of this two field to 30.

First I couldn't understand why I cannot enter 10 turkish character at once.
It always gave me an error. But later I found that the DES uses 2 bytes for
each character that can support none western langueges as well.

If I enter as 'öçşiğüğiçş' (I am not sure if you going to see the these
Turkish character) It fails if the field length is 10 or even 20. So I use
set the field length to be 30. And now everythings works perfectly.

Thank you very much for your kind contribution to my project.

Rgds,
GC

[David Farr]

Hi GC,

thanks for the feedback.
I still have a lot to learn. If we can all help each other, so much the
better.
All the best with your project,

David

"Niyazi" <Ni****@discussions.microsoft.com> wrote in message
news:10**********************************@microsof t.com...

Hi David,

I have to thank you. It was the right direction that you show it to me.

I also find very good example from "Dev Articles":
"http://www.devarticles.com/c/a/VB.Net/String-Encryption-With-Visual-Basic-.NET/"

It was very good. I created seperate Class Library and now I can Encrypt
the
"DATA" before sending into SQL Server (I use varbinary data type) and I
can
also Decrypt the data to compare it with user input.

My problem was I am doing the applicatuion for small Turkish comapny which
is Collation name of the SQL Server is Turkish_BIN.

The user password and username have only 10 character. So I have to adjust
the Length of this two field to 30.

First I couldn't understand why I cannot enter 10 turkish character at
once.
It always gave me an error. But later I found that the DES uses 2 bytes
for
each character that can support none western langueges as well.

If I enter as 'öçsigügiçs' (I am not sure if you going to see the these
Turkish character) It fails if the field length is 10 or even 20. So I use
set the field length to be 30. And now everythings works perfectly.

Thank you very much for your kind contribution to my project.

Rgds,
GC

[Niyazi]

Hi David,

I am the person who have to say the word "THANK YOU."

You were realy open my eyes, yes I agree with you I also have more to learn
and things in this area will changable everyday new things comes or we
realize how to use it.

I wish I can help others as well. But this is going to my one of big
project. And yestarday I learn that company wants to move the application in
WAN network area and not to be only use inside the company. So I am desiging
eveythings from zero.

Sometimes I find difficulty to work alone but inthe good side I am and will
going to learn alot.

It was small application in 1 pc as SDI project. Untill yesterday I move it
to Remote SQL server as 3 tire MDI application and now I have to move as 3
tire to WAN network.

I need to overcome this so I can start to learn and practise again the SQL
Server Stored Procedures and move it to n-tire MDI application.

I am more than ready to help if you need it. If you post a new question
please make sure use this post to send me your question and the link of the
your new post.

I am realy fed-up using my hotmail account due to spam mails.

I thank you one more.
Here is the my Class Library Project for Encryption and Decryption:

First I created a VB.NET class Library to created a dll. So I can use it in
all other my programs to. You can change it anyway you like it. It is not my
code I get it from the article that I mentioned it.

So I hope the Authors will not mind much.
-----------------------------------------------------------------------------------------------
Just post into class library and compile.
Imports System
Imports System.IO
Imports System.Text
Imports System.Security.Cryptography

Public Class clsTripleDES

Private key() As Byte = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
15, 16, 17, 18, 19, 20, 21, 22, 23, 24}
Private iv() As Byte = {65, 110, 68, 26, 69, 178, 200, 219}
'Encrypt the user data as byte before saving into SQL Server 2000
Public Function Encrypt(ByVal plainText As String) As Byte()

'Decalre UTF8Encoding object so we may use the GetByte method to
transform
'the plainText into Byte array
Dim utf8encoder As UTF8Encoding = New UTF8Encoding
Dim inputInBytes() As Byte = utf8encoder.GetBytes(plainText)

'Create a new TripleDES service provider
Dim tdesProvider As TripleDESCryptoServiceProvider = New
TripleDESCryptoServiceProvider

'The ICryptTransform interface uses the TripleDes crypt provider along
with
'encryption key and init vector information
Dim cryptoTransform As ICryptoTransform =
tdesProvider.CreateEncryptor(Me.key, Me.iv)

'All cryptographic functions need a stream to output the encrypted
information.
'Here we declare a memory stream for this purpose.
Dim encryptedStream As MemoryStream = New MemoryStream
Dim cryptStream As CryptoStream = New CryptoStream(encryptedStream,
cryptoTransform, CryptoStreamMode.Write)

'Write the encrypted information to the stream. Flush the information
'when done to ensure everything is out of the buffer.
cryptStream.Write(inputInBytes, 0, inputInBytes.Length)
cryptStream.FlushFinalBlock()
encryptedStream.Position = 0

'Read the stream back into a Byte array and return it to the calling
method.
Dim result(encryptedStream.Length - 1) As Byte
encryptedStream.Read(result, 0, encryptedStream.Length)
cryptStream.Close()
Return result

End Function

'Decrypt the data from SQL Server 2000 before using it as string
Public Function Decrypt(ByVal inputInBytes() As Byte) As String
'UFTEncoding is used to transform the decrypted Byte Array information
back into a string
Dim utf8encoder As UTF8Encoding = New UTF8Encoding
Dim tdesProvider As TripleDESCryptoServiceProvider = New
TripleDESCryptoServiceProvider

'As before we must provide the encryption/decryption key along with
the init vector
Dim cryptoTransform As ICryptoTransform =
tdesProvider.CreateDecryptor(Me.key, Me.iv)

'Provider a memory stream to decrypt information into
Dim decryptedStream As MemoryStream = New MemoryStream
Dim cryptStream As CryptoStream = New CryptoStream(decryptedStream,
cryptoTransform, CryptoStreamMode.Write)
cryptStream.Write(inputInBytes, 0, inputInBytes.Length)
cryptStream.FlushFinalBlock()
decryptedStream.Position = 0

'Read the memory stream and convert it back into a string
Dim result(decryptedStream.Length - 1) As Byte
decryptedStream.Read(result, 0, decryptedStream.Length)
cryptStream.Close()

Dim myutf As UTF8Encoding = New UTF8Encoding
Return myutf.GetString(result)

End Function

End Class
----------------------------------------------------------------------------------------------

I hope this helps other as well.

Rgds,
GC