Encrypting with a function

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();

%>

Select allOpen 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 

Select allOpen in new window

The error is on:

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

%>

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

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.

you should be doing that in your stored procedure, not in the code.

what does your SPUpdatePasswd SP look like?

I'll post it in 5 min

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

Select allOpen 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

Select allOpen in new window

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

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

Select allOpen in new window

2. Create my certificate

create certificate bluedot
with subject = 'bluedot';
go

Select allOpen in new window

3. Create symmetric key

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

Select allOpen 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

Select allOpen 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

Select allOpen 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

Select allOpen 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")

Select allOpen in new window

I will also use the above with DB values like:

Decryptbdot(recordset("field") 

Select allOpen in new window

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

Select allOpen in new window

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.

no need to delete anything. you'll still need the others to create/update a password, all this does is encrypt / decrypt it

Ok so I call the new function you added from the asp?

yes, you call it to generate an encrypted value

Ok I'll test it in about an hr thx

How does the asp knows the function is called from the DB ?

because the SP getEncryptedString contains the code for calling the necessary functions

Sorry for the wait am checking in at the airport. I'll try it once I am inside in the waiting room

yes, replace whatever database fields with what you have

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 ...

I still get the error. The error is displayed below:

Microsoft JScript runtime  error '800a138f' 

Object expected 

/bluedot/forgotpasswordemailing.asp, line 76

Select allOpen 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.

whats the code for getEncryptedString()?

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 ?

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")));}

Select allOpen in new window

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

function getEncryptedStringFromDB( str ) {

}

Select allOpen 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

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.

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

I tested the following with the function I created and it works:

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

Select allOpen in new window

Is this what you want me to test ?

nope, I want you to run the SP getEncryptedString we created earlier:

exec getEncryptedString 'somePasswordString';

and see if you get the encrypted value

Correct, that would include the EXEC OpenKeys correct ?

I did run it and got this:

0x00C4C84EF9132F4DB004EE7414EA69E301000000FE28371D33EC346D1BE94C7D290CF057903D36CE12E91DE9C384CC701ED880600BBC3EFB9B766972

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

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

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 );

}

Select allOpen in new window

be sure to make sure all of the parameters and names are correct

I'll do that once am on the plane am about to board. Meantime I sent you couple errors on the decrypt function

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!

Ok. I'll test and reply later

I get an error

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
%>

Select allOpen in new window

I got this error:

Microsoft VBScript runtime  error '800a000d' 

Type mismatch: 'Encryptvb' 

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

Select allOpen in new window

what's line 77?

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

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.

just post the function getEncryptedStringFromDB() if you would please

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>

Select allOpen 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>

Select allOpen 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

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
%>

Select allOpen 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. ..

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>

Select allOpen in new window

I tried displaying it with :

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

Select allOpen 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.

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

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

Select allOpen 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

Select allOpen in new window

I run the following:

exec getEncryptedString 'demo';

Select allOpen 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';

Select allOpen 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 

Select allOpen 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.

try doing:

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

Select allOpen 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

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

Select allOpen 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

Select allOpen 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

Select allOpen 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 

Select allOpen in new window

Then I run both lines below:

exec dbo.getEncryptedString 'demo';

exec dbo.getDecryptedString '0x00C4C84EF9132F4DB004EE7414EA69E3010000000DE45E6E14A6BB7236F3E8A4A64BE02C88A8325335E2B164';

Select allOpen 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.

Select allOpen 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

do you have the sql for the EncryptByKey() / DecryptByKey() functions?

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

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

OK

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

Select allOpen in new window

as a test, run the following table creation script:

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

Select allOpen 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'

Select allOpen 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.

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.

Your ultimate goal is to store the encrypted passwords in your database right?

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

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>
 

Select allOpen 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

Ok ... I am going to do this now, will get back with results in 20 min.

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

Select allOpen 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
%>

Select allOpen in new window

But I get this error:

encrypt username: 

Microsoft VBScript runtime  error '800a000d' 

Type mismatch: 'EncryptDecrypt' 

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

Select allOpen 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

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

Select allOpen in new window

Heading home from lunch, will have a look

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

Select allOpen 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
%>

Select allOpen 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!

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>

Remove the end )

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 ?

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

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

Select allOpen 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 

Select allOpen 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
%>

Select allOpen in new window

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

Try hard coding the same string instead of using the rs value

<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

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

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

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

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

For testing i want to encrypt loginid

ok, so after the line

Set rs_decryptpassword = rs_decryptpassword_cmd.Execute

put

Loginid = rs_decryptpassword("loginid")

Same error. I think the only way to move ahead is to setup the test site :$

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?

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

Select allOpen 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>

Select allOpen 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.

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: 

Select allOpen in new window

Perhaps it is converting them into the wrong type ?

The password is varchar(255)

is the string "password" the actual password stored in the database?

and yes, let's change it over to varbinary(max)

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 

Select allOpen in new window

This is line 111

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

Select allOpen 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();
%>

Select allOpen in new window

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

can you explain exactly what you're ultimate goal here is? is this just for verifying someone's login, correct?

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.

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.

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>

Select allOpen 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.

I'll test tomorrow. I still need to decrypt the password in the DB and show the decrypted password in the email

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();
%>

Select allOpen in new window

Thanks for getting to this point. We can wrap up on this later.