Encrypting with a function

I am using a certificate and symmetric keys for  the encryption  and  function to call the encrypt / decrypt passwords.
I am using ASP for my code and MS SQL 2008 for my database.

I am trying to run a SP (Asp/javascript) as follows:

<%

var SPUpdatePassword__UserLoginId = "0";
if(String(Session("reqloginid")) != "undefined"){ SPUpdatePassword__UserLoginId = String(Session("reqloginid"));}

var SPUpdatePassword__Password = "0";
if(Encryptbdot(String(Session("svPassword"))) != "undefined"){ SPUpdatePassword__Password = Encryptbdot(String(Session("svPassword")));}

%>

<%

var SPUpdatePassword = Server.CreateObject("ADODB.Command");
SPUpdatePassword.ActiveConnection = MM_bluedotjs_STRING;
SPUpdatePassword.CommandText = "dbo.SPUpdatePasswd";
SPUpdatePassword.CommandType = 4;
SPUpdatePassword.CommandTimeout = 0;
SPUpdatePassword.Prepared = true;
SPUpdatePassword.Parameters.Append(SPUpdatePassword.CreateParameter("@RETURN_VALUE", 3, 4));
SPUpdatePassword.Parameters.Append(SPUpdatePassword.CreateParameter("@UserLoginId", 3, 1,4,SPUpdatePassword__UserLoginId));
SPUpdatePassword.Parameters.Append(SPUpdatePassword.CreateParameter("@Password", 200, 1,255,SPUpdatePassword__Password));
SPUpdatePassword.Execute();

%

Open in new window



And I get the following error:

Microsoft JScript runtime  error '800a138f' 

Object expected 

/bluedot/forgotpasswordemailing.asp, line 74 

Open in new window


I had read that for me to be able to use the functions I would need to open the key first. I just don't know how to add the code to do that.
they key name is: bdotpassword

I can call the function in SQL with the code below. So I guess I am missing the OpenKeys in my ASP code .. how would I do that ?

EXEC OpenKeys
SELECT dbo.Encryptbdot(LoginId) as encryptfunc
FROM UserLogin

Open in new window


This is where I got my code:  http://benjii.me/2010/05/how-to-use-sql-server-encryption-with-symmetric-keys/

So I can call it in SQL I just don't know how to do the exce openkeys in the ASP page to then be able to use the encrypt function.  Hence the 'expected object' error.
LVL 1
AleksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AleksAuthor Commented:
I tried calling the SP Open Keys like this

<%

var OpenKeys = Server.CreateObject("ADODB.Command");
OpenKeys.ActiveConnection = MM_bluedotjs_STRING;
OpenKeys.CommandText = "dbo.OpenKeys";
OpenKeys.CommandType = 4;
OpenKeys.CommandTimeout = 0;
OpenKeys.Prepared = true;
OpenKeys.Parameters.Append(OpenKeys.CreateParameter("@RETURN_VALUE", 3, 4));
OpenKeys.Execute();

%>

Open in new window


Right before the SP that encrypts the password but that didn't work. I still get :

Microsoft JScript runtime  error '800a138f' 

Object expected 

/bluedot/forgotpasswordemailing.asp, line 89 

Open in new window

0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
when you run

<%

var OpenKeys = Server.CreateObject("ADODB.Command");
OpenKeys.ActiveConnection = MM_bluedotjs_STRING;
OpenKeys.CommandText = "dbo.OpenKeys";
OpenKeys.CommandType = 4;
OpenKeys.CommandTimeout = 0;
OpenKeys.Prepared = true;
OpenKeys.Parameters.Append(OpenKeys.CreateParameter("@RETURN_VALUE", 3, 4));
OpenKeys.Execute();

%>

Open in new window


what is line 89, where the error is occurring?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AleksAuthor Commented:
The error is on:

var SPUpdatePassword__Password = "0";
if(Encryptbdot(String(Session("svPassword"))) != "undefined"){ SPUpdatePassword__Password = Encryptbdot(String(Session("svPassword")));}

%>
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
can we do a test? let's try encrypting a static string and see if it works...comment out that code above and do:

SPUpdatePassword__Password = Encryptbdot("abc123");

this'll tell us if the encrypt function works or not
0
AleksAuthor Commented:
Ok. I'll be by my computer in 30 min.
I think the problem is that before I can use the function I need to somehow open the key.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
you should be doing that in your stored procedure, not in the code.

what does your SPUpdatePasswd SP look like?
0
AleksAuthor Commented:
I'll post it in 5 min
0
AleksAuthor Commented:
Here is the Stored Procedure to create passwords.

I assume I need to add:   EXEC OpenKeys    somewhere in this SP ?

USE [BlueDot]
GO

/****** Object:  StoredProcedure [dbo].[SPCreatePasswd]    Script Date: 11/17/2015 10:34:39 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO



CREATE PROCEDURE [dbo].[SPCreatePasswd]
    @loginid NVARCHAR(100) ,
    @Password NVARCHAR (255) ,
    @UserId INT,
    @FirmId INT,
    @Enable INT,
    @FirstTimeLogin INT,
    @PermissionGroup NVARCHAR(50),
    @AccessRights NVARCHAR(50),
    @Qnrlinkenable INT,
    @IsPswdEncrypted INT,
    @Uploadocs INT,
    @Usefulinks INT
    
AS 
    INSERT  INTO dbo.UserLogin
            ( LoginId ,
              Password ,
              UserId ,
              FirmId ,
              Enable ,
              FirstTimeLogin ,
              PermissionGroup ,
              AccessRights ,
              Qnrlinkenable ,
              IsPswdEncrypted,
              Uploadocs,
              Usefulinks
            )
    VALUES  ( @loginid, @Password, @UserId, @FirmId, @Enable, @FirstTimeLogin,
    @PermissionGroup, @AccessRights, @Qnrlinkenable, @IsPswdEncrypted,@Uploadocs,@Usefulinks
            )
            


GO

Open in new window


and here is the one at hand to update passwords. I assume Ill need to make changes to both.

USE [BlueDot]
GO

/****** Object:  StoredProcedure [dbo].[SPUpdatePasswd]    Script Date: 11/17/2015 10:34:35 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO




CREATE PROCEDURE [dbo].[SPUpdatePasswd]
    @UserLoginId INT ,
    @Password NVARCHAR(255)
AS
    UPDATE  dbo.UserLogin
    SET     Password = @Password
    WHERE   UserLoginId = @UserLoginId
    
    UPDATE dbo.UserLogin
    SET FailedTries = 0
    WHERE   UserLoginId = @UserLoginId
    
    UPDATE dbo.UserLogin
    SET IsLocked = 'False'
    WHERE   UserLoginId = @UserLoginId
            



GO

Open in new window

0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
can you post the code for your Encryptbdot function? i'm trying to find where you are making the call to the database functions for the encryption
0
AleksAuthor Commented:
I am just calling the function from the ASP. But this is in order what I have:

1. Create master key encryption

use BlueDot
create master key encryption by
password ='mystrongpassword';
go

Open in new window


2. Create my certificate

create certificate bluedot
with subject = 'bluedot';
go

Open in new window


3. Create symmetric key

create symmetric key bdotpassword
with algorithm=triple_des
encryption by certificate bluedot;
go

Open in new window


4.  Create SP "OpenKeys"

CREATE PROCEDURE OpenKeys
AS
BEGIN
    

    BEGIN TRY
        OPEN SYMMETRIC KEY bdotpassword
        DECRYPTION BY CERTIFICATE bluedot
    END TRY
    BEGIN CATCH
        -- Handle non-existant key here
    END CATCH
END

Open in new window


5.  Create encrypt function

CREATE FUNCTION Encryptbdot
(
    @LoginId varchar(max)
)
RETURNS varbinary(256)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result varbinary(256)

    SET @Result = EncryptByKey(Key_GUID('bdotpassword'), @LoginId)

    -- Return the result of the function
    RETURN @Result
END

Open in new window


6. Create decrypt function

CREATE FUNCTION Decryptbdot
(
    @LoginIdenctypted varbinary(128)
)
RETURNS varchar(max)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result varchar(max)

    SET @Result = DecryptByKey(@LoginIdenctypted)

    -- Return the result of the function
    RETURN @Result
END

Open in new window


Then in the ASP I need to set this up so that all I have to do is call the function to make it work as:

Encryptbdot(String(Session("svPassword")

Open in new window


I will also use the above with DB values like:

Decryptbdot(recordset("field") 

Open in new window

0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
i created 2 more SP's for you, one for encryption and the other for decryption. They're pretty self explanatory, they do all of the steps needed for encrypting/decrypting. The only part I'm unsure about are the CREATE statements, I assume you only run them once on your database, then you're done with them, correct? I commented them out for now, if you need them for each time you're going to run the SP then simply uncomment them and it'll be good to go.

CREATE PROCEDURE getEncryptedString 
	@LoginId varchar(max)
as begin

/*	create master key encryption by password ='mystrongpassword';

	create certificate bluedot with subject = 'bluedot';

	create symmetric key bdotpassword with algorithm=triple_des encryption by certificate bluedot;
*/
	exec OpenKeys;
	
	declare @result varbinary( 256 );
	set @result = dbo.Encryptbdot( @LoginId );
	
	select @result;
	
end

CREATE PROCEDURE getDecryptedString 
	@LoginIdenctypted varbinary(128)
as begin

/*	create master key encryption by password ='mystrongpassword';

	create certificate bluedot with subject = 'bluedot';

	create symmetric key bdotpassword with algorithm=triple_des encryption by certificate bluedot;
*/
	exec OpenKeys;
	
	declare @result varchar(max);
	set @result = dbo.Decryptbdot( @LoginIdenctypted );
	
	select @result;
	
end

Open in new window

0
AleksAuthor Commented:
I can delete the other procedures and use this instead. Would that work ?
And yes. That's the code for creating them run just once. As this ones too I assume.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
no need to delete anything. you'll still need the others to create/update a password, all this does is encrypt / decrypt it
0
AleksAuthor Commented:
Ok so I call the new function you added from the asp?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
yes, you call it to generate an encrypted value
0
AleksAuthor Commented:
Ok I'll test it in about an hr thx
0
AleksAuthor Commented:
How does the asp knows the function is called from the DB ?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
because the SP getEncryptedString contains the code for calling the necessary functions
0
AleksAuthor Commented:
Sorry for the wait am checking in at the airport. I'll try it once I am inside in the waiting room
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
yes, replace whatever database fields with what you have
0
AleksAuthor Commented:
Well .. doesn't matter because its just the name of the parameter, the value I pass will be that of the password so it should be ok .. testing now ...
0
AleksAuthor Commented:
I still get the error. The error is displayed below:

Microsoft JScript runtime  error '800a138f' 

Object expected 

/bluedot/forgotpasswordemailing.asp, line 76

Open in new window


line 76 is:

if(getEncryptedString(String(Session("svPassword"))) != "undefined"){ SPUpdatePassword__Password = getEncryptedString(String(Session("svPassword")));}

What is causing the error is the :getEncryptedString

If I remove it the password is change but not encrypted.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
whats the code for getEncryptedString()?
0
AleksAuthor Commented:
well .. there isn't. I thought that was the DB function  :#  that's why I asked if I enter that how will it know to run the DB function ... this is all kind of new to me so ... sorry  :$
I can add whatever code is needed in my include file for a function for getEncryptedString() and also for decrypt .... so I guess that's what I am missing ?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
no worries, i misunderstood your question...

what you need to do is create an ASP function that will call the SP from the database, just like every other time you call a SP. so we begin with:

if(getEncryptedStringFromDB(String(Session("svPassword"))) != "undefined"){ SPUpdatePassword__Password = getEncryptedString(String(Session("svPassword")));}

Open in new window


elsewhere in your code, create an ASP function called getEncryptedStringFromDB():

function getEncryptedStringFromDB( str ) {

}

Open in new window


within that function, make a call using the command object (or straight up sql, your choice) to the stored procedure getEncryptedString, which will return the encrypted string back to you.

I would actually, before doing any of this, run the SP directly in your database, with a hard-coded password parameter, and make sure you're getting back the encrypted value. if not, it'll be easier to debug without the application layer involved. if it does work, then move on to coding this function
0
AleksAuthor Commented:
I get the idea .. thing is .. I don't know how to create a function and I will be more than happy to sit down and learn ...  I will when I get back from my trip but unfortunately I am supposed to put this in place today .. any change you can help with the function ?  I am happy to open a separate ticket for that since you have been helping so much in just this one post.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
ok first verify it actually works in the database. are you able to run the SP directly?

keep everything in this ticket, easier to keep track of
0
AleksAuthor Commented:
I tested the following with the function I created and it works:

EXEC OpenKeys
SELECT dbo.Encryptbdot(password) as encrypted
FROM UserLogin

Open in new window


Is this what you want me to test ?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
nope, I want you to run the SP getEncryptedString we created earlier:

exec getEncryptedString 'somePasswordString';

and see if you get the encrypted value
0
AleksAuthor Commented:
Correct, that would include the EXEC OpenKeys correct ?

I did run it and got this:

0x00C4C84EF9132F4DB004EE7414EA69E301000000FE28371D33EC346D1BE94C7D290CF057903D36CE12E91DE9C384CC701ED880600BBC3EFB9B766972
0
AleksAuthor Commented:
I ran the descrypt with the long string created by the encrypt but got this error:


exec dbo.getDecryptedString '0x00C4C84EF9132F4DB004EE7414EA69E301000000FE28371D33EC346D1BE94C7D290CF057903D36CE12E91DE9C384CC701ED880600BBC3EFB9B766972';


Error:

Msg 257, Level 16, State 3, Procedure getDecryptedString, Line 0
Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query
0
AleksAuthor Commented:
On the other hand I tried to run it for the entire table:

SELECT getEncryptedString(password) as encrypted
FROM UserLogin


But I got this error:

Msg 195, Level 15, State 10, Line 2
'getEncryptedString' is not a recognized built-in function name.

Same for this:

SELECT getDecryptedString(password) as encrypted
FROM UserLogin
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
that looks good, progress! :)

now let's create the ASP function:

function getEncryptedStringFromDB( str ) {
var cmd = Server.CreateObject("ADODB.Command");
var rs = Server.CreateObject("ADODB.RecordSet");

cmd.ActiveConnection = MM_bluedotjs_STRING;
cmd.CommandText = "dbo.getEncryptedString";
cmd.CommandType = 4;
cmd.CommandTimeout = 0;
cmd.Prepared = true;
cmd.Parameters.Append(cmd.CreateParameter("@LoginId", 3, 1, 4, str));

rs.Open( cmd );

return rs( 0 );

}

Open in new window


be sure to make sure all of the parameters and names are correct
0
AleksAuthor Commented:
I'll do that once am on the plane am about to board. Meantime I sent you couple errors on the decrypt function
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
SELECT getEncryptedString(password) as encrypted
FROM UserLogin

you can't execute a stored procedure that way, remember, getEncryptedString is a SP, not a function

regarding the errors, double check the data types for the inputs and outputs, they may be mixed up. double check the function Decryptbdot as well and make sure the return value matches the variable it's going into.

I'm sorry to say, but I'm out of time for today. I need to finish up my work and head out for the night.

Good luck!
0
AleksAuthor Commented:
Ok. I'll test and reply later
0
AleksAuthor Commented:
I get an error
0
AleksAuthor Commented:
I am back, I tried this and got this error:

Microsoft JScript runtime  error '800a138f'

Object expected

/bluedot/forgotpasswordemailing.asp, line 77


What would the next step be ?

I also created a very simple VBScript page with the function included and this code to display the result:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html>

<!--#BlueDot Include files-->
<!--#include file="Connections/bluedot.asp" -->

 <script type="text/javascript">
 function Encryptvb( str ) {
var cmd = Server.CreateObject("ADODB.Command");
var rs = Server.CreateObject("ADODB.RecordSet");

cmd.ActiveConnection = MM_bluedot_STRING;
cmd.CommandText = "dbo.getEncryptedString";
cmd.CommandType = 4;
cmd.CommandTimeout = 0;
cmd.Prepared = true;
cmd.Parameters.Append(cmd.CreateParameter("@LoginId", 3, 1, 4, str));

rs.Open( cmd );

return rs( 0 );

}
 </script>
 

<%
Dim rs_decryptpassword
Dim rs_decryptpassword_cmd
Dim rs_decryptpassword_numRows

Set rs_decryptpassword_cmd = Server.CreateObject ("ADODB.Command")
rs_decryptpassword_cmd.ActiveConnection = MM_bluedot_STRING
rs_decryptpassword_cmd.CommandText = "select UserLoginId, LoginId, password from UserLogin where UserId = 1713" 
rs_decryptpassword_cmd.Prepared = true

Set rs_decryptpassword = rs_decryptpassword_cmd.Execute
rs_decryptpassword_numRows = 0
%>
<!--#BeginBlock-->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<p>username:<%=(rs_decryptpassword.Fields.Item("LoginId").Value)%></p>
<p>password: <%=Encryptvb(rs_decryptpassword.Fields.Item("LoginId").Value)%></p>
<p>password decrypted : <%=(rs_decryptpassword.Fields.Item("LoginId").Value)%></p>
</body>
</html>
<%
rs_decryptpassword.Close()
Set rs_decryptpassword = Nothing
%>

Open in new window


I got this error:

Microsoft VBScript runtime  error '800a000d' 

Type mismatch: 'Encryptvb' 

/bluedot/--tempdecrypt--.asp, line 48 

Open in new window

0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
what's line 77?
0
AleksAuthor Commented:
if(getEncryptedStringFromDB(String(Session("svPassword"))) != "undefined"){ SPUpdatePassword__Password = getEncryptedStringFromDB(String(Session("svPassword")));}
0
AleksAuthor Commented:
I can post all the SP and functions again if you like so we can take a look at all the code in one post.
The field I am trying to encrypt and then later on descrypt is a varchar(255). I want to do this right so if you think I should also change the data type I can do that.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
just post the function getEncryptedStringFromDB() if you would please
0
AleksAuthor Commented:
I just changed the name so it is easier for me to recognize and also apply to the rest of the system

   <script>
 function Encrypt( str ) {
var cmd = Server.CreateObject("ADODB.Command");
var rs = Server.CreateObject("ADODB.RecordSet");

cmd.ActiveConnection = MM_bluedotjs_STRING;
cmd.CommandText = "dbo.getDecryptedString";
cmd.CommandType = 4;
cmd.CommandTimeout = 0;
cmd.Prepared = true;
cmd.Parameters.Append(cmd.CreateParameter("@LoginId", 3, 1, 4, str));

rs.Open( cmd );

return rs( 0 );

}
 </script>

Open in new window


I changed the name of the function to:

var SPUpdatePassword__Password = "0";
if(Encrypt(String(Session("svPassword"))) != "undefined"){ SPUpdatePassword__Password = Encrypt(String(Session("svPassword")));}


I didn't get an error this time.
Let me check if that worked.

I also need to implement in an ASP VBScript page.  I use the code below I setup a simple page for it with the code below:

<p>password: <%=Encryptvb(rs_decryptpassword.Fields.Item("LoginId").Value)%></p>

Open in new window


I have the function above in an include file.

And I get this error when trying to decrypt:

Microsoft VBScript runtime  error '800a000d'

Type mismatch: 'Encryptvb'

/bluedot/--tempdecrypt--.asp, line 32
0
AleksAuthor Commented:
It seems like it encrypted the password, but When trying to decrypt in an ASP VB script page I got this error:

Microsoft VBScript runtime  error '800a000d'

Type mismatch: 'Decrypt'

/bluedot/--tempdecrypt--.asp, line 32


This is the page:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html>

<!--#BlueDot Include files-->
<!--#include file="Connections/bluedot.asp" -->
<!--#include file="includes/bdot/scripts.asp"-->


 

<%
Dim rs_decryptpassword
Dim rs_decryptpassword_cmd
Dim rs_decryptpassword_numRows

Set rs_decryptpassword_cmd = Server.CreateObject ("ADODB.Command")
rs_decryptpassword_cmd.ActiveConnection = MM_bluedot_STRING
rs_decryptpassword_cmd.CommandText = "select UserLoginId, LoginId, password from UserLogin where UserId = 1713" 
rs_decryptpassword_cmd.Prepared = true

Set rs_decryptpassword = rs_decryptpassword_cmd.Execute
rs_decryptpassword_numRows = 0
%>
<!--#BeginBlock-->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<p>username:<%=(rs_decryptpassword.Fields.Item("LoginId").Value)%></p>
<p>Decrypt password: <%=Decrypt(rs_decryptpassword.Fields.Item("Password").Value)%></p>


</body>
</html>
<%
rs_decryptpassword.Close()
Set rs_decryptpassword = Nothing
%>

Open in new window


I take it the functions only work on ASP/JavaScript pages ?  ASP pages have a different connetion string ... let me change It and have a separate function with the connection for VB script pages see if that works. ..
0
AleksAuthor Commented:
Didn't work :(  ...

I redid the functions with the correct connection string for ASP VBScript

 <script>
 function Encryptvb( str ) {
var cmd = Server.CreateObject("ADODB.Command");
var rs = Server.CreateObject("ADODB.RecordSet");

cmd.ActiveConnection = MM_bluedot_STRING;
cmd.CommandText = "dbo.getDecryptedString";
cmd.CommandType = 4;
cmd.CommandTimeout = 0;
cmd.Prepared = true;
cmd.Parameters.Append(cmd.CreateParameter("@LoginId", 3, 1, 4, str));

rs.Open( cmd );

return rs( 0 );

}
 </script>
 
    <script>
 function Decryptvb( str ) {
var cmd = Server.CreateObject("ADODB.Command");
var rs = Server.CreateObject("ADODB.RecordSet");

cmd.ActiveConnection = MM_bluedot_STRING;
cmd.CommandText = "dbo.getDecryptedString";
cmd.CommandType = 4;
cmd.CommandTimeout = 0;
cmd.Prepared = true;
cmd.Parameters.Append(cmd.CreateParameter("@LoginId", 3, 1, 4, str));

rs.Open( cmd );

return rs( 0 );

}
 </script>

Open in new window


I tried displaying it with :

<p>password decrypted : <%=Decryptvb(rs_decryptpassword.Fields.Item("Password").Value)%></p>

Open in new window


This is the error:

Microsoft VBScript runtime  error '800a000d'

Type mismatch: 'Decryptvb'

/bluedot/--tempdecrypt--.asp, line 32

I am testing the decrypt function in an ASP/JS page meantime.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
unfortunately i'm tied up for the next few hours with work, didn't want to leave you hanging though. try getting the decrypt function working first directly in the database, i remember you saying it wasn't.

i'll have a look tonight if i get the chance
0
AleksAuthor Commented:
I tried and I got close but I had to add code and also got 'null' in return

This are the two stored procedures:

USE [BlueDot]
GO
/****** Object:  StoredProcedure [dbo].[getEncryptedString]    Script Date: 11/18/2015 16:33:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Create procedure that calls function to encrypt

ALTER PROCEDURE [dbo].[getEncryptedString] 
	@LoginId varchar(max)
as begin

/*	create master key encryption by password ='mystrongpassword';

	create certificate bluedot with subject = 'bluedot';

	create symmetric key bdotpassword with algorithm=triple_des encryption by certificate bluedot;
*/
	exec OpenKeys;
	
	declare @result varbinary ( 256 );
	set @result = dbo.Encryptbdot( @LoginId );
	
	select @result;
	
end

Open in new window


USE [BlueDot]
GO

/****** Object:  StoredProcedure [dbo].[getDecryptedString]    Script Date: 11/18/2015 16:36:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- Create procedure that calls function to decrypt

CREATE PROCEDURE [dbo].[getDecryptedString] 
	@LoginIdenctypted varbinary(128)
as begin

/*	create master key encryption by password ='mystrongpassword';

	create certificate bluedot with subject = 'bluedot';

	create symmetric key bdotpassword with algorithm=triple_des encryption by certificate bluedot;
*/
	exec OpenKeys;
	
	declare @result varchar(max);
	set @result = dbo.Decryptbdot( @LoginIdenctypted );
	
	select @result;
	
end
GO

Open in new window


I run the following:

exec getEncryptedString 'demo';

Open in new window


I get this in return:

0x00C4C84EF9132F4DB004EE7414EA69E3010000003D391827035BEBE6E570460CAA9720677AB46B05DA6AAD4D

Then I try to decrypt the above by running the decrypt code:

exec dbo.getDecryptedString '0x00C4C84EF9132F4DB004EE7414EA69E3010000000DE45E6E14A6BB7236F3E8A4A64BE02C88A8325335E2B164';

Open in new window


I get error:

Msg 257, Level 16, State 3, Procedure getDecryptedString, Line 0
Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.

But if I run:

declare @param varbinary;
set @param = cast('0x00C4C84EF9132F4DB004EE7414EA69E3010000000DE45E6E14A6BB7236F3E8A4A64BE02C88A8325335E2B164' as varbinary);
exec dbo.getDecryptedString @param 

Open in new window


I get null.

So something is off with the decrypt SP or function. I tried to fix it but only made it work. As you mentioned we first need to make it work here.

I am heading to bed .. that 17 hr flight was tough ... Ill check tomorrow morning, hopefully you can catch whats wrong. Also, if it makes life easier I can change the data type of the password field to 'varbinary(256)

Let me know.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
try doing:

declare @param varbinary;
set @param = convert(varbinary(max)'0x00C4C84EF9132F4DB004EE7414EA69E3010000000DE45E6E14A6BB7236F3E8A4A64BE02C88A8325335E2B164');
exec dbo.getDecryptedString @param 

Open in new window


IF THAT DOESNT WORK, I like your idea of making your password field varbinary, let's try that. we would need to then tweka the 2 SP's:

CREATE PROCEDURE getEncryptedString
      @LoginId varbinary(128)
as begin

      exec OpenKeys;
      
      declare @result varbinary( 256 );
      set @result = dbo.Encryptbdot( @LoginId );
      
      select @result;
      
end

CREATE PROCEDURE getDecryptedString
      @LoginIdenctypted varbinary(128)
as begin

      exec OpenKeys;
      
      declare @result varbinary(128);
      set @result = dbo.Decryptbdot( @LoginIdenctypted );
      
      select @result;
      
end
0
AleksAuthor Commented:
Ok. This is where we stand at the moment.

1. I changed the field 'password' to  varbinary(128)
2. We already have the objects up to the encryption key, then I removed the functions we had created to have varbinay(128) on those as well below is the code

CREATE FUNCTION Encryptbdot
(
    @LoginId varbinary(128)
)
RETURNS varbinary(128)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result varbinary(128)

    SET @Result = EncryptByKey(Key_GUID('bdotpassword'), @LoginId)

    -- Return the result of the function
    RETURN @Result
END

Open in new window


and

CREATE FUNCTION Decryptbdot
(
    @LoginIdenctypted varbinary(128)
)
RETURNS varbinary(128)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result varbinary(128)

    SET @Result = DecryptByKey(@LoginIdenctypted)

    -- Return the result of the function
    RETURN @Result
END

Open in new window


3. I removed the SP and ran the code you sent me. I think there was a typo because one part of the code said varbinary(256) I changed it to 128 to match everything else.

CREATE PROCEDURE getEncryptedString 
       @LoginId varbinary(128)
 as begin

       exec OpenKeys;
       
       declare @result varbinary(128);
       set @result = dbo.Encryptbdot( @LoginId );
       
       select @result;
       
 end

Open in new window


and

CREATE PROCEDURE getDecryptedString 
       @LoginIdenctypted varbinary(128)
 as begin

       exec OpenKeys;
       
       declare @result varbinary(128);
       set @result = dbo.Decryptbdot( @LoginIdenctypted );
       
       select @result;
       
 end 

Open in new window


Then I run both lines below:

exec dbo.getEncryptedString 'demo';

exec dbo.getDecryptedString '0x00C4C84EF9132F4DB004EE7414EA69E3010000000DE45E6E14A6BB7236F3E8A4A64BE02C88A8325335E2B164';

Open in new window


But I get the errors below on both:

Msg 257, Level 16, State 3, Procedure getEncryptedString, Line 0
Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.

Open in new window


I'm attaching all the above code plus the code we run before this in a text file, should be easier for you to check. I am stuck at this point now.
10.-Encryption-create-cert-keys-and-.sql
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
do you have the sql for the EncryptByKey() / DecryptByKey() functions?
0
AleksAuthor Commented:
I don't. Where can I find those functions ? They must be in the DB already.

I found this for the decryptbykey

https://msdn.microsoft.com/en-us/library/ms181860.aspx?f=255&MSPPError=-2147217396
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
it looks like they're system functions, so i'm all set with that.

i'll have a look tomorrow morning on this (it's pretty late here) and let you know what I figure out.

I'll also respond to your message in the AM, I'm signing off for the night
0
AleksAuthor Commented:
OK
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
ok, I have a working example that I've run on my own server and it works great.

delete all of the SP / functions we created up to this point, the only thing that should be left is the master key and certificate. after that, run the following stored procedure:

create PROCEDURE [dbo].[USP_test_password]
	@Username nvarchar(50), 
	@Password nvarchar(50),
	@flag char(1)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	Declare @error as varchar(100),
	@EncryptionKey nvarchar(32)

	if(@flag='E')
	begin

		Insert into Testtable (username,encryptpassword) values (@Username, convert(varbinary(max),@Password))
		OPEN SYMMETRIC KEY bdotpassword
		DECRYPTION BY CERTIFICATE bluedot;

		UPDATE Testtable SET encryptpassword = EncryptByKey(Key_GUID('bdotpassword'), encryptpassword) where username=@username;
		CLOSE SYMMETRIC KEY bdotpassword

	end
	else if(@flag='D')
	begin

		OPEN SYMMETRIC KEY bdotpassword
		DECRYPTION BY CERTIFICATE bluedot;

		SELECT username,CONVERT(nvarchar, DecryptByKey(encryptpassword)) AS 'Password'
		FROM Testtable 
		where username=@username;

		CLOSE SYMMETRIC KEY bdotpassword
		
	end

end

Open in new window


as a test, run the following table creation script:

create table Testtable ( Username nvarchar(50), encryptpassword varbinary(max));

Open in new window


this'll create a table in your database that the SP will work of AS IS, to do so, run each line below individually:

exec USP_test_password 'userName', 'password', 'E'
exec USP_test_password 'userName', 'password', 'D'

Open in new window


Encryption/Decryption is based off of the final parameter, setting it to E will encrypt, setting it to D will decrypt.

Assuming that all works, we'll need to tweak it to match up your database structure. but one step at a time. we'll also need to change it so that it only updates your users table, so I imagine it'll look much like it does, except for the INSERT statement into the test table.
0
AleksAuthor Commented:
It seems to work ... we can move forward. I am still not very clear how we will use it so that I can use it to run when I either want to decrypt a value or decrypt it with a function like we were trying before but ... let me know what's the next step.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
Your ultimate goal is to store the encrypted passwords in your database right?
0
AleksAuthor Commented:
Yes. Encrypt a value I get from a form and decrypt a stored password from the database by calling a function. Like.

<%=encrypt("password"%>

Same for decrypt
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
using your function in this comment, we'll create just want one function:

 <script>
 function EncryptDecrypt( userName, pw, mode ) {
var cmd = Server.CreateObject("ADODB.Command");
var rs = Server.CreateObject("ADODB.RecordSet");

cmd.ActiveConnection = MM_bluedot_STRING;
cmd.CommandText = "dbo.spEncryptDecrypt";             '-- change to whatever your SP name is
cmd.CommandType = 4;
cmd.CommandTimeout = 0;
cmd.Prepared = true;
cmd.Parameters.Append(cmd.CreateParameter("@Username", 200, 1, 50, userName));
cmd.Parameters.Append(cmd.CreateParameter("@Password", 200, 1, 50, pw));
cmd.Parameters.Append(cmd.CreateParameter("@flag", 129, 1, 1, mode));

rs.Open( cmd );

return rs( 0 );

}
 </script>
 

Open in new window


if you have a test page already set up, copy this function into it, and to test it out, do the following:

<%=EncryptDecrypt(user, "password", "E" ) %>    <-- encryption method

<%=EncryptDecrypt(user, "password", "D" ) %>   <-- decryption method

user can be a user name or userID, any way to identify which user it is. remember to follow my instructions at the end of my post where I described the new SP
0
AleksAuthor Commented:
Ok ... I am going to do this now, will get back with results in 20 min.
0
AleksAuthor Commented:
I modified the SP to what fits the table:

USE [BlueDot]
GO
/****** Object:  StoredProcedure [dbo].[spEncryptDecrypt]    Script Date: 11/21/2015 20:10:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spEncryptDecrypt]
	@Username nvarchar(100), 
	@Password nvarchar(255),
	@flag char(1)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	Declare @error as varchar(100),
	@EncryptionKey nvarchar(32)

	if(@flag='E')
	begin

		Insert into Userlogin (LoginId,Password) values (@Username, convert(varbinary(max),@Password))
		OPEN SYMMETRIC KEY bdotpassword
		DECRYPTION BY CERTIFICATE bluedot;

		UPDATE UserLogin SET Password = EncryptByKey(Key_GUID('bdotpassword'), Password) where LoginId=@username;
		CLOSE SYMMETRIC KEY bdotpassword

	end
	else if(@flag='D')
	begin

		OPEN SYMMETRIC KEY bdotpassword
		DECRYPTION BY CERTIFICATE bluedot;

		SELECT Loginid,CONVERT(nvarchar, DecryptByKey(Password)) AS 'Password'
		FROM  Userlogin
		where Loginid=@username;

		CLOSE SYMMETRIC KEY bdotpassword
		
	end

end

Open in new window


I made this simple page to try and display the Encrypted value of a password using the "Loginid" value as if it was the password entered, just for testing purposes.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html>

<!--#BlueDot Include files-->
<!--#include file="Connections/bluedot.asp" -->

 <script>
 function EncryptDecrypt( userName, pw, mode ) {
var cmd = Server.CreateObject("ADODB.Command");
var rs = Server.CreateObject("ADODB.RecordSet");

cmd.ActiveConnection = MM_bluedot_STRING;
cmd.CommandText = "dbo.spEncryptDecrypt";
cmd.CommandType = 4;
cmd.CommandTimeout = 0;
cmd.Prepared = true;
cmd.Parameters.Append(cmd.CreateParameter("@Username", 200, 1, 50, userName));
cmd.Parameters.Append(cmd.CreateParameter("@Password", 200, 1, 50, pw));
cmd.Parameters.Append(cmd.CreateParameter("@flag", 129, 1, 1, mode));

rs.Open( cmd );

return rs( 0 );

}
 </script>


 

<%
Dim rs_decryptpassword
Dim rs_decryptpassword_cmd
Dim rs_decryptpassword_numRows

Set rs_decryptpassword_cmd = Server.CreateObject ("ADODB.Command")
rs_decryptpassword_cmd.ActiveConnection = MM_bluedot_STRING
rs_decryptpassword_cmd.CommandText = "select UserLoginId, LoginId, password from UserLogin where UserId = 1713" 
rs_decryptpassword_cmd.Prepared = true

Set rs_decryptpassword = rs_decryptpassword_cmd.Execute
rs_decryptpassword_numRows = 0
%>
<!--#BeginBlock-->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<p>encrypt username: <%=EncryptDecrypt(LoginId, "LoginId", "E" ) %></p>


</body>
</html>
<%
rs_decryptpassword.Close()
Set rs_decryptpassword = Nothing
%>

Open in new window


But I get this error:

encrypt username: 

Microsoft VBScript runtime  error '800a000d' 

Type mismatch: 'EncryptDecrypt' 

/bluedot/--tempdecrypt--.asp, line 51 

Open in new window


Line 51 is:  <p>encrypt username: <%=EncryptDecrypt(LoginId, "LoginId", "E" ) %></p>

Attached is a screenshot of the table so you can see the names and types.
table.PNG
0
AleksAuthor Commented:
A couple things to consider:

1. I don't need to update the DB with the SP, only encrypt or decrypt the value of the field I am applying the function to.
2. I don't really need to pass on the value of login. I already have the field filtered out by using my recordset (See sample ASP), so all I need is for the SP and function to encrypt/decrypt the value to which I apply the function, so something like:

<%=EncryptDecrypt("LoginId", "E" ) %>     to encrypt the value of "Loginid"

<%=EncryptDecrypt("LoginId", "D" ) %>    to decrypt the value of "Loginid"

I tried changing it to the code below but it says the encrypt function requires 2 to 4 arguments

USE [BlueDot]
GO
/****** Object:  StoredProcedure [dbo].[spEncryptDecrypt]    Script Date: 11/21/2015 20:10:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spEncryptDecrypt]
	@Username nvarchar(100), 
	@Password nvarchar(255),
	@flag char(1)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	Declare @error as varchar(100),
	@EncryptionKey nvarchar(32)

	if(@flag='E')
	begin

		OPEN SYMMETRIC KEY bdotpassword
		ENCRYPTION BY CERTIFICATE bluedot;

		SELECT Loginid,CONVERT(nvarchar, EncryptByKey(Password)) AS 'Password'
		FROM  Userlogin
		where Loginid=@username;
		
		
		CLOSE SYMMETRIC KEY bdotpassword

	end
	else if(@flag='D')
	begin

		OPEN SYMMETRIC KEY bdotpassword
		DECRYPTION BY CERTIFICATE bluedot;

		SELECT Loginid,CONVERT(nvarchar, DecryptByKey(Password)) AS 'Password'
		FROM  Userlogin
		where Loginid=@username;

		CLOSE SYMMETRIC KEY bdotpassword
		
	end

end

Open in new window

0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
Heading home from lunch, will have a look
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
sorry to be brief, but i'm heading out for the rest of the day. I'll keep an eye on your progress on my phone and answer when I can since you're on a deadline.

change your SP to the following:

ALTER PROCEDURE [dbo].[spEncryptDecrypt]
	@Password nvarchar(255),
	@flag char(1)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	Declare @error as varchar(100),
	@EncryptionKey nvarchar(32)

	if(@flag='E')
	begin

		OPEN SYMMETRIC KEY bdotpassword
		DECRYPTION BY CERTIFICATE bluedot;

		select EncryptByKey(Key_GUID('bdotpassword'), Password);
		CLOSE SYMMETRIC KEY bdotpassword

	end
	else if(@flag='D')
	begin

		OPEN SYMMETRIC KEY bdotpassword
		DECRYPTION BY CERTIFICATE bluedot;

		SELECT DecryptByKey(Password));

		CLOSE SYMMETRIC KEY bdotpassword
		
	end

end

Open in new window


this'll ONLY encrypt/decrypt the password value you pass to it, it will not write to any tables

for your test page, try this:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html>

<!--#BlueDot Include files-->
<!--#include file="Connections/bluedot.asp" -->

 <script>
 function EncryptDecrypt( pw, mode ) {
var cmd = Server.CreateObject("ADODB.Command");
var rs = Server.CreateObject("ADODB.RecordSet");

cmd.ActiveConnection = MM_bluedot_STRING;
cmd.CommandText = "dbo.spEncryptDecrypt";
cmd.CommandType = 4;
cmd.CommandTimeout = 0;
cmd.Prepared = true;
cmd.Parameters.Append(cmd.CreateParameter("@Password", 200, 1, 50, pw));
cmd.Parameters.Append(cmd.CreateParameter("@flag", 129, 1, 1, mode));

rs.Open( cmd );

return rs( 0 );

}
 </script>


 

<%
Dim rs_decryptpassword
Dim rs_decryptpassword_cmd
Dim rs_decryptpassword_numRows

Set rs_decryptpassword_cmd = Server.CreateObject ("ADODB.Command")
rs_decryptpassword_cmd.ActiveConnection = MM_bluedot_STRING
rs_decryptpassword_cmd.CommandText = "select UserLoginId, LoginId, password from UserLogin where UserId = 1713" 
rs_decryptpassword_cmd.Prepared = true

Set rs_decryptpassword = rs_decryptpassword_cmd.Execute
rs_decryptpassword_numRows = 0
%>
<!--#BeginBlock-->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<p>encrypt username: <%=EncryptDecrypt( rs("LoginId"), "E" ) %></p>
<p>decrypt username: <%=EncryptDecrypt( rs("LoginId"), "D" ) %></p>


</body>
</html>
<%
rs_decryptpassword.Close()
Set rs_decryptpassword = Nothing
%>

Open in new window


obviously I can't test it out on my end as i don't have your DB structure, but it'll encrypt/decrypt the LOGINID value from your recordset. if you want to change it to your password, just change the recordset mappings to point to that column instead.

hopefully this gets you to where you need to be!
0
AleksAuthor Commented:
I tried but got this error:

Msg 102, Level 15, State 1, Procedure spEncryptDecrypt, Line 29
Incorrect syntax near ')'.


Line 29 Is:

                        SELECT DecryptByKey(Password));

---- without running the alter on the SP I tried the ASP and got:

Microsoft VBScript runtime  error '800a000d'

Type mismatch: 'EncryptDecrypt'

/bluedot/--tempdecrypt--.asp, line 50


line 50 is:  <p>encrypt username: <%=EncryptDecrypt( rs_decryptpassword("LoginId"), "E" ) %></p>
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
Remove the end )
0
AleksAuthor Commented:
Msg 207, Level 16, State 1, Procedure spEncryptDecrypt, Line 19
Invalid column name 'Password'.
Msg 207, Level 16, State 1, Procedure spEncryptDecrypt, Line 29
Invalid column name 'Password'.

Perhaps we are missing another parameter to tell it which table is the field from ?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
No the problem is that it's looking at a column name and not the variable name, so change

select EncryptByKey(Key_GUID('bdotpassword'), Password);

To

select EncryptByKey(Key_GUID('bdotpassword'), @Password);

Do the same with the decryption portion of the SP
0
AleksAuthor Commented:
Ok. That worked, this is what I ended up running:

ALTER PROCEDURE [dbo].[spEncryptDecrypt]
	@Password nvarchar(255),
	@flag char(1)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	Declare @error as varchar(100),
	@EncryptionKey nvarchar(32)

	if(@flag='E')
	begin

		OPEN SYMMETRIC KEY bdotpassword
		DECRYPTION BY CERTIFICATE bluedot;

		select EncryptByKey(Key_GUID('bdotpassword'), @Password);
		CLOSE SYMMETRIC KEY bdotpassword

	end
	else if(@flag='D')
	begin

		OPEN SYMMETRIC KEY bdotpassword
		DECRYPTION BY CERTIFICATE bluedot;

		SELECT (@Password);
		CLOSE SYMMETRIC KEY bdotpassword
		
	end

end

Open in new window


That ran fine, but still get this error in the ASP file:

Microsoft VBScript runtime  error '800a000d' 

Type mismatch: 'EncryptDecrypt' 

/bluedot/--tempdecrypt--.asp, line 50 

Open in new window


<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html>

<!--#BlueDot Include files-->
<!--#include file="Connections/bluedot.asp" -->

 <script>
 function EncryptDecrypt( pw, mode ) {
var cmd = Server.CreateObject("ADODB.Command");
var rs = Server.CreateObject("ADODB.RecordSet");

cmd.ActiveConnection = MM_bluedot_STRING;
cmd.CommandText = "dbo.spEncryptDecrypt";
cmd.CommandType = 4;
cmd.CommandTimeout = 0;
cmd.Prepared = true;
cmd.Parameters.Append(cmd.CreateParameter("@Password", 200, 1, 50, pw));
cmd.Parameters.Append(cmd.CreateParameter("@flag", 129, 1, 1, mode));

rs.Open( cmd );

return rs( 0 );

}
 </script>


 

<%
Dim rs_decryptpassword
Dim rs_decryptpassword_cmd
Dim rs_decryptpassword_numRows

Set rs_decryptpassword_cmd = Server.CreateObject ("ADODB.Command")
rs_decryptpassword_cmd.ActiveConnection = MM_bluedot_STRING
rs_decryptpassword_cmd.CommandText = "select UserLoginId, LoginId, password from UserLogin where UserId = 1713" 
rs_decryptpassword_cmd.Prepared = true

Set rs_decryptpassword = rs_decryptpassword_cmd.Execute
rs_decryptpassword_numRows = 0
%>
<!--#BeginBlock-->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<p>encrypt username: <%=EncryptDecrypt( rs_decryptpassword("LoginId"), "E" ) %></p>
<p>decrypt username: <%=EncryptDecrypt( rs_decryptpassword("LoginId"), "D" ) %></p>


</body>
</html>
<%
rs_decryptpassword.Close()
Set rs_decryptpassword = Nothing
%>

Open in new window


Line 50:  <p>encrypt username: <%=EncryptDecrypt( rs_decryptpassword("LoginId"), "E" ) %></p>
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
Try hard coding the same string instead of using the rs value
0
AleksAuthor Commented:
<p>encrypt username: <%=EncryptDecrypt( demo , "E" ) %></p>

Still get this error:

encrypt username:

Microsoft VBScript runtime  error '800a000d'

Type mismatch: 'EncryptDecrypt'

/bluedot/--tempdecrypt--.asp, line 31
0
AleksAuthor Commented:
I created a test environment for you. It should be easy to install in your computer since you already have a web server and MS SQL.

Attached is a zip file with 3 files. Instructions below:

+ sqlfordb.txt:  This is the script to run in a database, it will create the "Userlogin" table and the relevant fields as well as the certificate, the keys, and the stored procedure you created.

1. Create an empty DB and run the script on that database
2. create a username and password and set the login as dbo.owner
3. Create a 32 bit ODBC that connects to the DB with the username/password used above

+ bluedot.asp: This is the include file with the connections to the database
1.  replace the first value with the name of your odbc, then the username, then the password

+ testencrypt.asp: This is the test page
1. test the encrypt first: Once you get this to work it should give you the encrypted password, open the database and past it into the password field

Then add:  <p>decrypt password: <%=DecryptDecrypt( Password, "E" ) %></p>  (or whatever it should be) to display the password

If all works well the first line of the test page should display the encrypted password and the second one should display the decrypted password

This pages require the website to allow parent paths and the pool to allow for 32 bit applications to run on it. I hope this gives you the full picture and all the elements needed to replicate, this way you can see the errors on your end and hopefully get this working on time.
testfiles.zip
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
ok, the problem, at least on your test page is you're trying to call a javascript function that's client side instead of call a javascript function that is from the server side (which you can do). originally, you had

 <script>
 function EncryptDecrypt( pw, mode ) {
var cmd = Server.CreateObject("ADODB.Command");
var rs = Server.CreateObject("ADODB.RecordSet");
......

that began your javascript function. this indicates its a client side function. to make it server side, you need to add:

 <script language="javascript" runat="server">
 function EncryptDecrypt( pw, mode ) {
var cmd = Server.CreateObject("ADODB.Command");
var rs = Server.CreateObject("ADODB.RecordSet");
....

try that and it should work
0
AleksAuthor Commented:
I changed it but now I get another error. Did you get a chance to test with the files I sent you ?

Error says:

Procedure or function 'spEnctyptDecrypt' expects parameter '@password' which was not supplied. Line 21

Line 21 is part of the function
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
I haven't had a chance to fully set it up on my db server, as it's being worked on today...

i noticed your line where you call the function:

<p>encrypt username: <%=EncryptDecrypt( Loginid, "E" ) %></p>


Loginid is never being set to any value. after you open up your rs, set the Loginid variable to whatever column you want to encrypt
0
AleksAuthor Commented:
For testing i want to encrypt loginid
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
ok, so after the line

Set rs_decryptpassword = rs_decryptpassword_cmd.Execute

put

Loginid = rs_decryptpassword("loginid")
0
AleksAuthor Commented:
Same error. I think the only way to move ahead is to setup the test site :$
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
ok let me get that set up, i think the server work is just about done

you going to be online for a bit longer?

also, can you post the latest version of your test page?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
ok I have a working example.  change your SP to:

alter PROCEDURE [dbo].[spEncryptDecrypt]
	@UserID int, 
	@Password nvarchar(50),
	@flag char(1)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	Declare @error as varchar(100),
	@EncryptionKey nvarchar(32)

	if(@flag='E')
	begin

		OPEN SYMMETRIC KEY bdotpassword
		DECRYPTION BY CERTIFICATE bluedot;

		select EncryptByKey(Key_GUID('bdotpassword'), @password);
		CLOSE SYMMETRIC KEY bdotpassword

	end
	else if(@flag='D')
	begin

		OPEN SYMMETRIC KEY bdotpassword
		DECRYPTION BY CERTIFICATE bluedot;

		SELECT CONVERT(nvarchar, DecryptByKey(password)) AS 'Password'
		FROM UserLogin 
		where UserLoginId = @UserID;

		CLOSE SYMMETRIC KEY bdotpassword
		
	end

end

Open in new window


and now the ASP code:

<%
Dim MM_bluedot_STRING
MM_bluedot_STRING = "dsn=bluedot;uid=test;pwd=test;"    
%>
<!DOCTYPE html>

 <script language="javascript" runat="server">
 function EncryptDecrypt( userID, pw, mode ) {
var cmd = Server.CreateObject("ADODB.Command");
var rs = Server.CreateObject("ADODB.RecordSet");

cmd.ActiveConnection = MM_bluedot_STRING;
cmd.CommandText = "dbo.spEncryptDecrypt";
cmd.CommandType = 4;
cmd.CommandTimeout = 0;
cmd.Prepared = true;
cmd.Parameters.Append(cmd.CreateParameter("@UserID", 3, 1, 4, userID));
cmd.Parameters.Append(cmd.CreateParameter("@Password", 200, 1, 50, pw));
cmd.Parameters.Append(cmd.CreateParameter("@flag", 129, 1, 1, mode));

rs.Open( cmd );

return rs( 0 );

}
 </script>

<%
dim encryptedValue : encryptedValue = EncryptDecrypt( 3, "password", "E" )
dim decryptedValue : decryptedValue = EncryptDecrypt( 3, "", "D" )
%>
<!--#BeginBlock-->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<p>encrypt userID 3: string "password" gets encrypted to: <%=encryptedValue %></p>
<p>decrypt UserID 3: encrypted value "<%=encryptedValue%>" decrypts to: <%=decryptedValue %></p>
</body>
</html>

Open in new window


NOTE: in this example, I have a user in my UserLogin table with a userLoginID of 3

the SP works in the following way. for encryption, you pass in the any integer value,, password to be encrypted, and the 'E' flag to mark it as encryption. it'll then encrypt that value and return the binary data back.

for decryption, you need to pass in the userLoginID, any string (or it can be blank), and the 'D' flag to mark it as decryption. it'll then look up the encrypted password belonging to the userLoginID parameter, decrypt it, and then return it back to a recordset.

i see the encryption functionality only being used when someone creates or updates their password. if that's the case, we could easily put that part directly in the SP, your call.

the decryption functionality would be used when a user logs on, and you want to check the password they entered in against the one encrypted in the database.
0
AleksAuthor Commented:
The mechanism seems to be in place, the problem is that when it encrypts the value it saves strange characters which it can't decrypt:

encrypt userloginid 1458: string "password" gets encrypted to: 쐀仈ᏹ䴯Ұ瓮

decrypt userloginid 1458: encrypted value "쐀仈ᏹ䴯Ұ瓮" decrypts to: 

Open in new window


Perhaps it is converting them into the wrong type ?

The password is varchar(255)
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
is the string "password" the actual password stored in the database?

and yes, let's change it over to varbinary(max)
0
AleksAuthor Commented:
password is just something I am hardcoding and testing with. the value would be more like

request.form("password")

Right now I am testing in an ASP/javascript page,  but I am getting this error:

Microsoft JScript runtime  error '800a1391' 

'encryptedValue' is undefined 

/bluedot/forgotpasswordemailing.asp, line 111 

Open in new window


This is line 111

if(encryptedValue != "undefined"){ SPUpdatePassword__Password = encryptedValue;}

Open in new window


This is my ASP

	<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
<!DOCTYPE html>
<!--#BeginBlock-->
<!--#include file="Connections/bluedotjs.asp" -->
<!--#include file="includes/BDot/Validates.js" -->
<!--#include file="includes/bdot/scripts.asp"-->
<!--#BeginBlock-->

<%
Response.Expires=-1500
Response.CacheControl="no-cache"
%>

<!--#BeginBlock-->

<%
var firstNumber, firstLower, firstUpper, firstOther, latterNumber,
latterLower, latterUpper, latterOther, passwordLength, extraChars

firstNumber = true
firstLower =  true
firstUpper =  true
firstOther =  false
latterNumber = true
latterLower = true
latterUpper = true
latterOther = false
passwordLength = 10
extraChars = "@!"

function getRandomNum(lbound, ubound) {
return (Math.floor(Math.random() * (ubound - lbound)) + lbound);
}
function getRandomChar(number, lower, upper, other, extra) {
var numberChars = "0123456789";
var lowerChars = "abcdefghijklmnopqrstuvwxyz";
var upperChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
var otherChars = "`~!@#$%^&*()-_=+[{]}\\|;:'\",<.>/? ";
var charSet = extra;
if (number == true)
charSet += numberChars;
if (lower == true)
charSet += lowerChars;
if (upper == true)
charSet += upperChars;
if (other == true)
charSet += otherChars;
return charSet.charAt(getRandomNum(0, charSet.length));
}
function getPassword(length, extraChars, firstNumber, firstLower, firstUpper, firstOther,
latterNumber, latterLower, latterUpper, latterOther) {
var rc = "";
if (length > 0)
rc = rc + getRandomChar(firstNumber, firstLower, firstUpper, firstOther, extraChars);
for (var idx = 1; idx < length; ++idx) {
rc = rc + getRandomChar(latterNumber, latterLower, latterUpper, latterOther, extraChars);
}
return rc;
}

strUltraPass = getPassword(passwordLength, extraChars,
firstNumber, firstLower, firstUpper, firstOther,
latterNumber, latterLower, latterUpper, latterOther)
%>
<%Session("svPassword") = strUltraPass%>



<script type="text/javascript" language="JavaScript">
    function js_EncryptDecrypt( password ) {
        var encryptedValue = vb_EncryptDecrypt( <%=Session ("reqloginid") %>, "password", "E" );
        var decryptedValue = EncryptDecrypt( <%= Session ("reqloginid") %>, "", "D" );

    }
</script>
 <script language="javascript" runat="server">
 function EncryptDecrypt( userID, pw, mode ) {
var cmd = Server.CreateObject("ADODB.Command");
var rs = Server.CreateObject("ADODB.RecordSet");

cmd.ActiveConnection = MM_bluedot_STRING;
cmd.CommandText = "dbo.spEncryptDecrypt";
cmd.CommandType = 4;
cmd.CommandTimeout = 0;
cmd.Prepared = true;
cmd.Parameters.Append(cmd.CreateParameter("@UserID", 3, 1, 4, userID));
cmd.Parameters.Append(cmd.CreateParameter("@Password", 200, 1, 50, pw));
cmd.Parameters.Append(cmd.CreateParameter("@flag", 129, 1, 1, mode));

rs.Open( cmd );

return rs( 0 );

}
 </script>
 


<%

var SPUpdatePassword__UserLoginId = "0";
if(String(Session("reqloginid")) != "undefined"){ SPUpdatePassword__UserLoginId = String(Session("reqloginid"));}

var SPUpdatePassword__Password = "0";
if(encryptedValue != "undefined"){ SPUpdatePassword__Password = encryptedValue;}

%>

<%

var SPUpdatePassword = Server.CreateObject("ADODB.Command");
SPUpdatePassword.ActiveConnection = MM_bluedotjs_STRING;
SPUpdatePassword.CommandText = "dbo.SPUpdatePasswd";
SPUpdatePassword.CommandType = 4;
SPUpdatePassword.CommandTimeout = 0;
SPUpdatePassword.Prepared = true;
SPUpdatePassword.Parameters.Append(SPUpdatePassword.CreateParameter("@RETURN_VALUE", 3, 4));
SPUpdatePassword.Parameters.Append(SPUpdatePassword.CreateParameter("@UserLoginId", 3, 1,4,SPUpdatePassword__UserLoginId));
SPUpdatePassword.Parameters.Append(SPUpdatePassword.CreateParameter("@Password", 200, 1,255,SPUpdatePassword__Password));
SPUpdatePassword.Execute();

%>

<!--#BeginBlock-->



<%
var LawFirmLogo__MMColParam = "0";
if (String(Session("FirmIdlogin")) != "undefined" && 
    String(Session("FirmIdlogin")) != "") { 
  LawFirmLogo__MMColParam = String(Session("FirmIdlogin"));
}
%>

<%
var LawFirmLogo = Server.CreateObject("ADODB.Recordset");
LawFirmLogo.ActiveConnection = MM_bluedotjs_STRING;
LawFirmLogo.Source = "SELECT * FROM ParametersLawFirm WHERE ParamFirmId="+ LawFirmLogo__MMColParam.replace(/'/g, "''") + " and ParamName='CustomLoginlogo'";
LawFirmLogo.CursorType = 0;
LawFirmLogo.CursorLocation = 2;
LawFirmLogo.LockType = 1;
LawFirmLogo.Open();
var LawFirmLogo_numRows = 0;
%>
<%
var LawFirmHeader = Server.CreateObject("ADODB.Recordset");
LawFirmHeader.ActiveConnection = MM_bluedotjs_STRING;
LawFirmHeader.Source = "SELECT * FROM Parameters WHERE ParamNm='LawFirmHeader'";
LawFirmHeader.CursorType = 0;
LawFirmHeader.CursorLocation = 2;
LawFirmHeader.LockType = 1;
LawFirmHeader.Open();
var LawFirmHeader_numRows = 0;
%>
<%
var LoginId__MMColParam = "0";
if (String(Session("reqloginid")) != "undefined" && 
    String(Session("reqloginid")) != "") { 
  LoginId__MMColParam = String(Session("reqloginid"));
}
%>
<%
var LoginId = Server.CreateObject("ADODB.Recordset");
LoginId.ActiveConnection = MM_bluedotjs_STRING;
LoginId.Source = "SELECT userloginid, loginid, password, users.email, LawFirm.logofirmemail  FROM userlogin  inner join users on users.userid = userlogin.userid inner join lawfirm on lawfirm.firmid=userlogin.firmid  WHERE Userloginid="+ LoginId__MMColParam.replace(/'/g, "''") + "";
LoginId.CursorType = 0;
LoginId.CursorLocation = 2;
LoginId.LockType = 1;
LoginId.Open();
var LoginId_numRows = 0;
%>

<%
var Fromemail_cmd = Server.CreateObject ("ADODB.Command");
Fromemail_cmd.ActiveConnection = MM_bluedotjs_STRING;
Fromemail_cmd.CommandText = "Select * from parameters where paramnm='suppemail'";
Fromemail_cmd.Prepared = true;

var Fromemail = Fromemail_cmd.Execute();
var Fromemail_numRows = 0;
%>
<%
var SMTPServer_cmd = Server.CreateObject ("ADODB.Command");
SMTPServer_cmd.ActiveConnection = MM_bluedotjs_STRING;
SMTPServer_cmd.CommandText = "SELECT * FROM Parameters WHERE ParamNm='smtpserver'";
SMTPServer_cmd.Prepared = true;

var SMTPServer = SMTPServer_cmd.Execute();
var SMTPServer_numRows = 0;
%>
<!--#include file="WA_Universal_Email/CDOSYS_JS.asp" -->
<!--#include file="WA_Universal_Email/MailFormatting_JS.asp" -->
<%
function WA_Universal_Email_1_SendMail(RecipientEmail)      {
  var MailAttachments = "";
  var MailBCC         = "";
  var MailCC          = "";
  var MailTo          = "";
  var MailBodyFormat  = "";
  var MailBody        = "";
  var MailImportance  = "";
  var MailFrom        = "" + String((Fromemail.Fields.Item("ParamVal").Value)) + "";
  var MailSubject     = "Password Reset";

  //Global Variables
gAddBCC = "";
  gAddCC = "";
  gAddRecipient = "";
  gBodyFormat = "";
  gSetImportance = "";

  WA_MailObject = WAUE_Definition("" + String((SMTPServer.Fields.Item("ParamVal").Value)) + "","25","","","","");

  if (RecipientEmail)     {
    WA_MailObject = WAUE_AddRecipient(WA_MailObject,RecipientEmail);
    MailTo = gAddRecipient;
    gAddRecipient = "";
  }
  else      {
    //To Entries
  }

  //Attachment Entries

  //BCC Entries
  MailBCC = gAddBCC;

  //CC Entries
  MailCC = gAddCC;

  //Body Format
  WA_MailObject = WAUE_BodyFormat(WA_MailObject,0);
  MailBodyFormat = gBodyFormat;

  //Set Importance
  WA_MailObject = WAUE_SetImportance(WA_MailObject,"1");
  MailImportance = gSetImportance;

  //Character Set
  WA_MailObject = WAUE_SetCharset(WA_MailObject,"ISO-8859-1");

  //Start Mail Body
MailBody = MailBody + "<html>\r\n";
MailBody = MailBody + "<head>\r\n";
MailBody = MailBody + "<title>Password reminder request</title>\r\n";
MailBody = MailBody + "</head>\r\n";
MailBody = MailBody + "<body bgcolor=\"#FFFFFF\" text=\"#000000\" link=\"#003366\" alink=\"#FF0000\" leftmargin=\"5\" topmargin=\"5\" marginwidth=\"0\" marginheight=\"0\">\r\n";
MailBody = MailBody + "<table width=\"700\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\r\n";
MailBody = MailBody + "  \r\n";
MailBody = MailBody + "  <tr> \r\n";
MailBody = MailBody + "    <td height=\"18\"> \r\n";
MailBody = MailBody + "      <div align=\"left\"></div></td>\r\n";
MailBody = MailBody + "  </tr>\r\n";
MailBody = MailBody + "</table>\r\n";
MailBody = MailBody + "<table width=\"700\" height=\"340\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\">\r\n";
MailBody = MailBody + "  <tr> \r\n";
MailBody = MailBody + "    <td align=\"left\" valign=\"top\" height=\"340\" width=\"15\"><br /> </td>\r\n";
MailBody = MailBody + "    <td height=\"340\" align=\"left\" valign=\"top\"><p><font size=\"-1\" face=\"Tahoma\"><br />\r\n";
MailBody = MailBody + "      <strong>Password reminder request </strong></font></p>\r\n";
MailBody = MailBody + "      <p><font size=\"-1\" face=\"Tahoma\">You have received this mail because you (or someone else) have asked to be reminded of your Password for <span class=\"tableheader2\">";
MailBody = MailBody + (LawFirmHeader.Fields.Item("ParamVal").Value);
MailBody = MailBody + "</span></font></p>\r\n";
MailBody = MailBody + "<br>\r\n";
MailBody = MailBody + "        Your Password: </font>";
MailBody = MailBody + decryptedValue;
MailBody = MailBody + "</p>\r\n";
MailBody = MailBody + "      <p><font size=\"-1\" face=\"Tahoma\">Thank you for using </font><font size=\"-1\" face=\"Tahoma\"><span class=\"tableheader2\">";
MailBody = MailBody + (LawFirmHeader.Fields.Item("ParamVal").Value);
MailBody = MailBody + "</span></font></p>\r\n";
MailBody = MailBody + "      <p><br>\r\n";
MailBody = MailBody + "      </p></td>\r\n";
MailBody = MailBody + "  </tr>\r\n";
MailBody = MailBody + "</table>\r\n";
MailBody = MailBody + "</body>\r\n";
MailBody = MailBody + "</html>\r\n";
MailBody = MailBody + "\r\n";
  //End Mail Body

  WA_MailObject = WAUE_SendMail(WA_MailObject,MailAttachments,MailBCC,MailCC,MailTo,MailImportance,MailFrom,MailSubject,MailBody);

  WA_MailObject = null;
}
%>
<%
if (("" == ""))     {
  //WA Universal Email object="CDOSYS"
  //Send Loop Once Per Entry
  WA_Universal_Email_1_SendMail("" + String((LoginId.Fields.Item("email").Value)) + "");

  //Send Mail All Entries
  if ("forgotpasswordemailed.asp"!="")     {
    Response.Redirect("forgotpasswordsent.asp");
  }
}
%>


<!--#BeginBlock-->

<HEAD>
<TITLE>Password sent</TITLE>
<link href="css/CMS.css" rel="stylesheet" type="text/css">
</HEAD>

<!--#BeginBlock-->

<BODY>
</BODY>
</HTML>

<!--#BeginBlock-->

<%
LawFirmLogo.Close();
%>
<%
LawFirmHeader.Close();
%>
<%
LoginId.Close();
%>
<%
Fromemail.Close();
%>
<%
SMTPServer.Close();
%>

Open in new window

0
AleksAuthor Commented:
It is still generating odd passwords, makes the next page crash.

http://localhost/bluedot/Intranet/Contacts/webaccess_SPadd.asp?NewUserid=31487&username=amucino2ytgjhguj2@mac.com&password=쐀仈ᏹ䴯Ұ瓮&expireson=

As you can see those are the characters that the encryption is generating, it should be ASCII characters, something like:

IAAAAAFoAACAAAAAawMAAAwAAAAMAAAAAQAAAJABAAAA1GT2N/SA8ozYOJE=

Perhaps something in the function or the SP needs to be tweaked.

Aleks
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
can you explain exactly what you're ultimate goal here is? is this just for verifying someone's login, correct?
0
AleksAuthor Commented:
That is one. Another is to create a password and email it. And given access rights display passwords for the admin to see.

Because of this I need to be able to encrypt and decrypt and see the actual password.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
let's work on login validation first. can you post the code for your login form and any other code that handles processing of the login? if it's sensitive, you can email it to me, otherwise post it here. You shouldn't have to display the encrypted value (and you shouldn't be passing the data from page to page), you would only need to display the unencrypted version, which you should be able to do easily.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
I came up with another test page for you

<%
Dim MM_bluedot_STRING
MM_bluedot_STRING = "dsn=bluedot;uid=test;pwd=test;"    
%>
<!DOCTYPE html>

 <script language="javascript" runat="server">
 function EncryptDecrypt( userID, pw, mode ) {
var cmd = Server.CreateObject("ADODB.Command");
var rs = Server.CreateObject("ADODB.RecordSet");

cmd.ActiveConnection = MM_bluedot_STRING;
cmd.CommandText = "dbo.spEncryptDecrypt";
cmd.CommandType = 4;
cmd.CommandTimeout = 0;
cmd.Prepared = true;
cmd.Parameters.Append(cmd.CreateParameter("@UserID", 3, 1, 4, userID));
cmd.Parameters.Append(cmd.CreateParameter("@Password", 200, 1, 50, pw));
cmd.Parameters.Append(cmd.CreateParameter("@flag", 129, 1, 1, mode));

rs.Open(cmd);

var returnValue = -1;

if (!rs.BOF && !rs.EOF)
    returnValue = rs(0);

return returnValue;

}
 </script>


<!--#BeginBlock-->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
    <%
    if Request("btnSave") = "Validate Password" then 
        dim passwordFromDB
        passwordFromDB = EncryptDecrypt( Request("txtLoginID"), "", "D" )

        if Request("txtPW") = passwordFromDB then
            Response.Write "User Passes Authentication<br/><br/>"
        else
            Response.Write "User Does Not Pass Authentication<br/><br/>"
        end if
    end if
    %>

    <form name="frm" method="post" action="test.asp">
        Enter in your LoginID:&nbsp;
        <input type="text" name="txtLoginID" value="<%=Request("txtLoginID")%>" />
        <br /><br />
        Enter in your Password:&nbsp;
        <input type="text" name="txtPW" value="<%=Request("txtPW")%>" />
        <br /><br />
        <button name="btnSave" type="submit" value="Validate Password">Validate Password</button>
    </form>

</body>
</html>

Open in new window


simply call the file test.asp, change your connection string at the top of the page, and then run it. The key part to this example is this line:

if Request("txtPW") = passwordFromDB then

it takes the entered password and compares it to decrypted value from the database and writes the appropriate message. Enter in any users LoginID value along with a valid password that's already stored in the database and it'll work. Try it with invalid combos as well, to prove it'll fail when it should.

Let's get this piece working before we do the admin side of things.
0
AleksAuthor Commented:
I'll test tomorrow. I still need to decrypt the password in the DB and show the decrypted password in the email
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
i updated the page you have that updates a users password and sends it to them. I changed the password parameter on SPUpdatePasswd to adBinary, make sure to update the SP definition in the database as well. You'll see I changed the parameter type on line 117 from 200 to 128

SPUpdatePassword.Parameters.Append(SPUpdatePassword.CreateParameter("@Password", 128, 1,255,encryptedValue));


have a look:

	<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
<!DOCTYPE html>
<!--#BeginBlock-->
<!--#include file="Connections/bluedotjs.asp" -->
<!--#include file="includes/BDot/Validates.js" -->
<!--#include file="includes/bdot/scripts.asp"-->
<!--#BeginBlock-->

<%
Response.Expires=-1500
Response.CacheControl="no-cache"
%>

<!--#BeginBlock-->

<%
var firstNumber, firstLower, firstUpper, firstOther, latterNumber,
latterLower, latterUpper, latterOther, passwordLength, extraChars

firstNumber = true
firstLower =  true
firstUpper =  true
firstOther =  false
latterNumber = true
latterLower = true
latterUpper = true
latterOther = false
passwordLength = 10
extraChars = "@!"

function getRandomNum(lbound, ubound) {
return (Math.floor(Math.random() * (ubound - lbound)) + lbound);
}
function getRandomChar(number, lower, upper, other, extra) {
var numberChars = "0123456789";
var lowerChars = "abcdefghijklmnopqrstuvwxyz";
var upperChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
var otherChars = "`~!@#$%^&*()-_=+[{]}\\|;:'\",<.>/? ";
var charSet = extra;
if (number == true)
charSet += numberChars;
if (lower == true)
charSet += lowerChars;
if (upper == true)
charSet += upperChars;
if (other == true)
charSet += otherChars;
return charSet.charAt(getRandomNum(0, charSet.length));
}
function getPassword(length, extraChars, firstNumber, firstLower, firstUpper, firstOther,
latterNumber, latterLower, latterUpper, latterOther) {
var rc = "";
if (length > 0)
rc = rc + getRandomChar(firstNumber, firstLower, firstUpper, firstOther, extraChars);
for (var idx = 1; idx < length; ++idx) {
rc = rc + getRandomChar(latterNumber, latterLower, latterUpper, latterOther, extraChars);
}
return rc;
}

strUltraPass = getPassword(passwordLength, extraChars,
firstNumber, firstLower, firstUpper, firstOther,
latterNumber, latterLower, latterUpper, latterOther)
%>
<%Session("svPassword") = strUltraPass%>

 <script language="javascript" runat="server">
 function EncryptDecrypt( userID, pw, mode ) {
var cmd = Server.CreateObject("ADODB.Command");
var rs = Server.CreateObject("ADODB.RecordSet");

cmd.ActiveConnection = MM_bluedot_STRING;
cmd.CommandText = "dbo.spEncryptDecrypt";
cmd.CommandType = 4;
cmd.CommandTimeout = 0;
cmd.Prepared = true;
cmd.Parameters.Append(cmd.CreateParameter("@UserID", 3, 1, 4, userID));
cmd.Parameters.Append(cmd.CreateParameter("@Password", 200, 1, 50, pw));
cmd.Parameters.Append(cmd.CreateParameter("@flag", 129, 1, 1, mode));

rs.Open( cmd );

var returnValue = -1;

if (!rs.BOF && !rs.EOF)
    returnValue = rs(0);

return returnValue;

}
 </script>
 


<%

var encryptedValue = EncryptDecrypt( String(Session("reqloginid")), strUltraPass, "E" )

var SPUpdatePassword__UserLoginId = "0";
if(String(Session("reqloginid")) != "undefined"){ SPUpdatePassword__UserLoginId = String(Session("reqloginid"));}

var SPUpdatePassword__Password = "0";
if(encryptedValue != "undefined"){ SPUpdatePassword__Password = encryptedValue;}

%>

<%

var SPUpdatePassword = Server.CreateObject("ADODB.Command");
SPUpdatePassword.ActiveConnection = MM_bluedotjs_STRING;
SPUpdatePassword.CommandText = "dbo.SPUpdatePasswd";
SPUpdatePassword.CommandType = 4;
SPUpdatePassword.CommandTimeout = 0;
SPUpdatePassword.Prepared = true;
SPUpdatePassword.Parameters.Append(SPUpdatePassword.CreateParameter("@RETURN_VALUE", 3, 4));
SPUpdatePassword.Parameters.Append(SPUpdatePassword.CreateParameter("@UserLoginId", 3, 1,4,SPUpdatePassword__UserLoginId));
SPUpdatePassword.Parameters.Append(SPUpdatePassword.CreateParameter("@Password", 128, 1,255,encryptedValue));
SPUpdatePassword.Execute();

%>

<!--#BeginBlock-->



<%
var LawFirmLogo__MMColParam = "0";
if (String(Session("FirmIdlogin")) != "undefined" && 
    String(Session("FirmIdlogin")) != "") { 
  LawFirmLogo__MMColParam = String(Session("FirmIdlogin"));
}
%>

<%
var LawFirmLogo = Server.CreateObject("ADODB.Recordset");
LawFirmLogo.ActiveConnection = MM_bluedotjs_STRING;
LawFirmLogo.Source = "SELECT * FROM ParametersLawFirm WHERE ParamFirmId="+ LawFirmLogo__MMColParam.replace(/'/g, "''") + " and ParamName='CustomLoginlogo'";
LawFirmLogo.CursorType = 0;
LawFirmLogo.CursorLocation = 2;
LawFirmLogo.LockType = 1;
LawFirmLogo.Open();
var LawFirmLogo_numRows = 0;
%>
<%
var LawFirmHeader = Server.CreateObject("ADODB.Recordset");
LawFirmHeader.ActiveConnection = MM_bluedotjs_STRING;
LawFirmHeader.Source = "SELECT * FROM Parameters WHERE ParamNm='LawFirmHeader'";
LawFirmHeader.CursorType = 0;
LawFirmHeader.CursorLocation = 2;
LawFirmHeader.LockType = 1;
LawFirmHeader.Open();
var LawFirmHeader_numRows = 0;
%>
<%
var LoginId__MMColParam = "0";
if (String(Session("reqloginid")) != "undefined" && 
    String(Session("reqloginid")) != "") { 
  LoginId__MMColParam = String(Session("reqloginid"));
}
%>
<%
var LoginId = Server.CreateObject("ADODB.Recordset");
LoginId.ActiveConnection = MM_bluedotjs_STRING;
LoginId.Source = "SELECT userloginid, loginid, password, users.email, LawFirm.logofirmemail  FROM userlogin  inner join users on users.userid = userlogin.userid inner join lawfirm on lawfirm.firmid=userlogin.firmid  WHERE Userloginid="+ LoginId__MMColParam.replace(/'/g, "''") + "";
LoginId.CursorType = 0;
LoginId.CursorLocation = 2;
LoginId.LockType = 1;
LoginId.Open();
var LoginId_numRows = 0;
%>

<%
var Fromemail_cmd = Server.CreateObject ("ADODB.Command");
Fromemail_cmd.ActiveConnection = MM_bluedotjs_STRING;
Fromemail_cmd.CommandText = "Select * from parameters where paramnm='suppemail'";
Fromemail_cmd.Prepared = true;

var Fromemail = Fromemail_cmd.Execute();
var Fromemail_numRows = 0;
%>
<%
var SMTPServer_cmd = Server.CreateObject ("ADODB.Command");
SMTPServer_cmd.ActiveConnection = MM_bluedotjs_STRING;
SMTPServer_cmd.CommandText = "SELECT * FROM Parameters WHERE ParamNm='smtpserver'";
SMTPServer_cmd.Prepared = true;

var SMTPServer = SMTPServer_cmd.Execute();
var SMTPServer_numRows = 0;
%>
<!--#include file="WA_Universal_Email/CDOSYS_JS.asp" -->
<!--#include file="WA_Universal_Email/MailFormatting_JS.asp" -->
<%
function WA_Universal_Email_1_SendMail(RecipientEmail)      {
  var MailAttachments = "";
  var MailBCC         = "";
  var MailCC          = "";
  var MailTo          = "";
  var MailBodyFormat  = "";
  var MailBody        = "";
  var MailImportance  = "";
  var MailFrom        = "" + String((Fromemail.Fields.Item("ParamVal").Value)) + "";
  var MailSubject     = "Password Reset";

  //Global Variables
gAddBCC = "";
  gAddCC = "";
  gAddRecipient = "";
  gBodyFormat = "";
  gSetImportance = "";

  WA_MailObject = WAUE_Definition("" + String((SMTPServer.Fields.Item("ParamVal").Value)) + "","25","","","","");

  if (RecipientEmail)     {
    WA_MailObject = WAUE_AddRecipient(WA_MailObject,RecipientEmail);
    MailTo = gAddRecipient;
    gAddRecipient = "";
  }
  else      {
    //To Entries
  }

  //Attachment Entries

  //BCC Entries
  MailBCC = gAddBCC;

  //CC Entries
  MailCC = gAddCC;

  //Body Format
  WA_MailObject = WAUE_BodyFormat(WA_MailObject,0);
  MailBodyFormat = gBodyFormat;

  //Set Importance
  WA_MailObject = WAUE_SetImportance(WA_MailObject,"1");
  MailImportance = gSetImportance;

  //Character Set
  WA_MailObject = WAUE_SetCharset(WA_MailObject,"ISO-8859-1");

  //Start Mail Body
MailBody = MailBody + "<html>\r\n";
MailBody = MailBody + "<head>\r\n";
MailBody = MailBody + "<title>Password reminder request</title>\r\n";
MailBody = MailBody + "</head>\r\n";
MailBody = MailBody + "<body bgcolor=\"#FFFFFF\" text=\"#000000\" link=\"#003366\" alink=\"#FF0000\" leftmargin=\"5\" topmargin=\"5\" marginwidth=\"0\" marginheight=\"0\">\r\n";
MailBody = MailBody + "<table width=\"700\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\r\n";
MailBody = MailBody + "  \r\n";
MailBody = MailBody + "  <tr> \r\n";
MailBody = MailBody + "    <td height=\"18\"> \r\n";
MailBody = MailBody + "      <div align=\"left\"></div></td>\r\n";
MailBody = MailBody + "  </tr>\r\n";
MailBody = MailBody + "</table>\r\n";
MailBody = MailBody + "<table width=\"700\" height=\"340\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\">\r\n";
MailBody = MailBody + "  <tr> \r\n";
MailBody = MailBody + "    <td align=\"left\" valign=\"top\" height=\"340\" width=\"15\"><br /> </td>\r\n";
MailBody = MailBody + "    <td height=\"340\" align=\"left\" valign=\"top\"><p><font size=\"-1\" face=\"Tahoma\"><br />\r\n";
MailBody = MailBody + "      <strong>Password reminder request </strong></font></p>\r\n";
MailBody = MailBody + "      <p><font size=\"-1\" face=\"Tahoma\">You have received this mail because you (or someone else) have asked to be reminded of your Password for <span class=\"tableheader2\">";
MailBody = MailBody + (LawFirmHeader.Fields.Item("ParamVal").Value);
MailBody = MailBody + "</span></font></p>\r\n";
MailBody = MailBody + "<br>\r\n";
MailBody = MailBody + "        Your Password: </font>";
MailBody = MailBody + SPUpdatePassword__Password;
MailBody = MailBody + "</p>\r\n";
MailBody = MailBody + "      <p><font size=\"-1\" face=\"Tahoma\">Thank you for using </font><font size=\"-1\" face=\"Tahoma\"><span class=\"tableheader2\">";
MailBody = MailBody + (LawFirmHeader.Fields.Item("ParamVal").Value);
MailBody = MailBody + "</span></font></p>\r\n";
MailBody = MailBody + "      <p><br>\r\n";
MailBody = MailBody + "      </p></td>\r\n";
MailBody = MailBody + "  </tr>\r\n";
MailBody = MailBody + "</table>\r\n";
MailBody = MailBody + "</body>\r\n";
MailBody = MailBody + "</html>\r\n";
MailBody = MailBody + "\r\n";
  //End Mail Body

  WA_MailObject = WAUE_SendMail(WA_MailObject,MailAttachments,MailBCC,MailCC,MailTo,MailImportance,MailFrom,MailSubject,MailBody);

  WA_MailObject = null;
}
%>
<%
if (("" == ""))     {
  //WA Universal Email object="CDOSYS"
  //Send Loop Once Per Entry
  WA_Universal_Email_1_SendMail("" + String((LoginId.Fields.Item("email").Value)) + "");

  //Send Mail All Entries
  if ("forgotpasswordemailed.asp"!="")     {
    Response.Redirect("forgotpasswordsent.asp");
  }
}
%>


<!--#BeginBlock-->

<HEAD>
<TITLE>Password sent</TITLE>
<link href="css/CMS.css" rel="stylesheet" type="text/css">
</HEAD>

<!--#BeginBlock-->

<BODY>
</BODY>
</HTML>

<!--#BeginBlock-->

<%
LawFirmLogo.Close();
%>
<%
LawFirmHeader.Close();
%>
<%
LoginId.Close();
%>
<%
Fromemail.Close();
%>
<%
SMTPServer.Close();
%>

Open in new window

0
AleksAuthor Commented:
Thanks for getting to this point. We can wrap up on this later.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.