Password stored as binary

I have read somewhere on this forum that we could manage users by accessing directly the database.

I am using the SQL database and I was able to accomplish this except for passwords. I was able to add several users by using an Access front-end. The passwords generated by the FileVista interface are shown as Japanese characters on the Access interface. I know this is due to the fact that the data is stored as binary on the SQL table. 

I have tried to convert the password entered on the Access with the following code, with no avail:

    Dim arrbData() As Byte
    Dim strPassword As String
    str = Me.Password
    arrbData = StrConv(strPassword, vbFromUnicode)

It seems the conversion from text to binay is done differently since the characters.

So, the question is, how do I convert the password text entered on the Access password field to binary data in order to be saved on the SQL table?

Also, what is the difference of  Password and PasswordSalt?

Thank you,

Mauro

Hello Cem,

Can you please let me know how to code the password string via VB in order to have it stored as bynary data in the SQL table?

Thank you, Mauro

Hi Mauro,
You can use OleDB classes to import any field from Access to Sql Server. This way, you will not have to think about the underlying data types. See the code below:

Dim connectionToAccess As New OleDbConnection(accessConnectionString) 
Dim connectionToSql As New OleDbConnection(sqlConnectionString) 

connectionToAccess.Open() 
connectionToSql.Open() 

Dim accessCommand As OleDbCommand = connectionToAccess.CreateCommand() 
accessCommand.CommandText = "SELECT [Password], [PasswordSalt] FROM [User] WHERE [UserID] = 1" 
Dim reader As OleDbDataReader = accessCommand.ExecuteReader() 
If reader.Read() Then 
    
    Dim sqlCommand As OleDbCommand = connectionToSql.CreateCommand() 
    sqlCommand.CommandText = "UPDATE [User] SET [Password] = ?, [PasswordSalt] = ? WHERE [UserID] = 1" 
    
    Dim parameter1 As OleDbParameter = sqlCommand.Parameters.Add("@Password", OleDbType.Binary, 16) 
    parameter1.Value = reader("Password").Value 
    
    Dim parameter2 As OleDbParameter = sqlCommand.Parameters.Add("@PasswordSalt", OleDbType.Binary, 16) 
    parameter2.Value = reader("PasswordSalt").Value 
    
    sqlCommand.ExecuteNonQuery() 
    
End If 

connectionToAccess.Close() 
connectionToSql.Close() 


Password salt is a security field for the password field and it is responsible for preventing possible dictionary password attacks to your database.