Column Encryption via stored procedure

Hi
http://blogs.msdn.com/lcris/--Enscrypt
"MartinaL" <MartinaL@.discussions.microsoft.com> wrote in message
news:E88D0208-1BB5-4834-91BA-F7A332A827EB@.microsoft.com...
>I have a registration process on a website which sends in the
> username/password etc via a stored procedure into SQL 2005.
> I want to encrypt the password on the way in in the stored procedure. This
> is the code that I am trying to do this with;
> ALTER PROCEDURE [dbo].[AdminsAdd]
> (
> @.AdminUsername varchar(100),
> @.AdminPassword varchar(100),
> @.AdminName varchar(100)
> )
> AS
> OPEN SYMMETRIC KEY SSN_Key_02
> DECRYPTION BY CERTIFICATE testEncryptionCertificate2;
> INSERT INTO Admins
> (
> AdminUsername,
> AdminPassword,
> AdminName
> )
> VALUES
> (
> @.AdminUsername,
> EncryptByKey(Key_GUID('SSN_Key_02'), @.AdminPassword),
> @.AdminName
> )
> I have used similar code to encrypt all of the current passwords that are
> in
> the table and it works fine. And I have another stored procedure that
> checks
> people's details when they are login in and the decrypt function works
> fine
> in that procedure.
> What am I doing wrong in this Stored Proc?I have a registration process on a website which sends in the
username/password etc via a stored procedure into SQL 2005.
I want to encrypt the password on the way in in the stored procedure. This
is the code that I am trying to do this with;
ALTER PROCEDURE [dbo].[AdminsAdd]
(
@.AdminUsername varchar(100),
@.AdminPassword varchar(100),
@.AdminName varchar(100)
)
AS
OPEN SYMMETRIC KEY SSN_Key_02
DECRYPTION BY CERTIFICATE testEncryptionCertificate2;
INSERT INTO Admins
(
AdminUsername,
AdminPassword,
AdminName
)
VALUES
(
@.AdminUsername,
EncryptByKey(Key_GUID('SSN_Key_02'), @.AdminPassword),
@.AdminName
)
I have used similar code to encrypt all of the current passwords that are in
the table and it works fine. And I have another stored procedure that checks
people's details when they are login in and the decrypt function works fine
in that procedure.
What am I doing wrong in this Stored Proc?|||Hi
http://blogs.msdn.com/lcris/--Enscrypt
"MartinaL" <MartinaL@.discussions.microsoft.com> wrote in message
news:E88D0208-1BB5-4834-91BA-F7A332A827EB@.microsoft.com...
>I have a registration process on a website which sends in the
> username/password etc via a stored procedure into SQL 2005.
> I want to encrypt the password on the way in in the stored procedure. This
> is the code that I am trying to do this with;
> ALTER PROCEDURE [dbo].[AdminsAdd]
> (
> @.AdminUsername varchar(100),
> @.AdminPassword varchar(100),
> @.AdminName varchar(100)
> )
> AS
> OPEN SYMMETRIC KEY SSN_Key_02
> DECRYPTION BY CERTIFICATE testEncryptionCertificate2;
> INSERT INTO Admins
> (
> AdminUsername,
> AdminPassword,
> AdminName
> )
> VALUES
> (
> @.AdminUsername,
> EncryptByKey(Key_GUID('SSN_Key_02'), @.AdminPassword),
> @.AdminName
> )
> I have used similar code to encrypt all of the current passwords that are
> in
> the table and it works fine. And I have another stored procedure that
> checks
> people's details when they are login in and the decrypt function works
> fine
> in that procedure.
> What am I doing wrong in this Stored Proc?|||What goes wrong with your code? Do you get an error message, does it not
encrypt the passwords properly?
One issue is that you don't close the key in the procedure. You should close
the key after inserting the data. Otherwise, it will remain open after the
procedure call completes, and it will stay open until the session will be
terminated.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"MartinaL" <MartinaL@.discussions.microsoft.com> wrote in message
news:E88D0208-1BB5-4834-91BA-F7A332A827EB@.microsoft.com...
>I have a registration process on a website which sends in the
> username/password etc via a stored procedure into SQL 2005.
> I want to encrypt the password on the way in in the stored procedure. This
> is the code that I am trying to do this with;
> ALTER PROCEDURE [dbo].[AdminsAdd]
> (
> @.AdminUsername varchar(100),
> @.AdminPassword varchar(100),
> @.AdminName varchar(100)
> )
> AS
> OPEN SYMMETRIC KEY SSN_Key_02
> DECRYPTION BY CERTIFICATE testEncryptionCertificate2;
> INSERT INTO Admins
> (
> AdminUsername,
> AdminPassword,
> AdminName
> )
> VALUES
> (
> @.AdminUsername,
> EncryptByKey(Key_GUID('SSN_Key_02'), @.AdminPassword),
> @.AdminName
> )
> I have used similar code to encrypt all of the current passwords that are
> in
> the table and it works fine. And I have another stored procedure that
> checks
> people's details when they are login in and the decrypt function works
> fine
> in that procedure.
> What am I doing wrong in this Stored Proc?|||What goes wrong with your code? Do you get an error message, does it not
encrypt the passwords properly?
One issue is that you don't close the key in the procedure. You should close
the key after inserting the data. Otherwise, it will remain open after the
procedure call completes, and it will stay open until the session will be
terminated.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"MartinaL" <MartinaL@.discussions.microsoft.com> wrote in message
news:E88D0208-1BB5-4834-91BA-F7A332A827EB@.microsoft.com...
>I have a registration process on a website which sends in the
> username/password etc via a stored procedure into SQL 2005.
> I want to encrypt the password on the way in in the stored procedure. This
> is the code that I am trying to do this with;
> ALTER PROCEDURE [dbo].[AdminsAdd]
> (
> @.AdminUsername varchar(100),
> @.AdminPassword varchar(100),
> @.AdminName varchar(100)
> )
> AS
> OPEN SYMMETRIC KEY SSN_Key_02
> DECRYPTION BY CERTIFICATE testEncryptionCertificate2;
> INSERT INTO Admins
> (
> AdminUsername,
> AdminPassword,
> AdminName
> )
> VALUES
> (
> @.AdminUsername,
> EncryptByKey(Key_GUID('SSN_Key_02'), @.AdminPassword),
> @.AdminName
> )
> I have used similar code to encrypt all of the current passwords that are
> in
> the table and it works fine. And I have another stored procedure that
> checks
> people's details when they are login in and the decrypt function works
> fine
> in that procedure.
> What am I doing wrong in this Stored Proc?|||Also if it's a password, you might consider a one-way hash instead of
encryption.
"MartinaL" <MartinaL@.discussions.microsoft.com> wrote in message
news:E88D0208-1BB5-4834-91BA-F7A332A827EB@.microsoft.com...
>I have a registration process on a website which sends in the
> username/password etc via a stored procedure into SQL 2005.
> I want to encrypt the password on the way in in the stored procedure. This
> is the code that I am trying to do this with;
> ALTER PROCEDURE [dbo].[AdminsAdd]
> (
> @.AdminUsername varchar(100),
> @.AdminPassword varchar(100),
> @.AdminName varchar(100)
> )
> AS
> OPEN SYMMETRIC KEY SSN_Key_02
> DECRYPTION BY CERTIFICATE testEncryptionCertificate2;
> INSERT INTO Admins
> (
> AdminUsername,
> AdminPassword,
> AdminName
> )
> VALUES
> (
> @.AdminUsername,
> EncryptByKey(Key_GUID('SSN_Key_02'), @.AdminPassword),
> @.AdminName
> )
> I have used similar code to encrypt all of the current passwords that are
> in
> the table and it works fine. And I have another stored procedure that
> checks
> people's details when they are login in and the decrypt function works
> fine
> in that procedure.
> What am I doing wrong in this Stored Proc?|||Also if it's a password, you might consider a one-way hash instead of
encryption.
"MartinaL" <MartinaL@.discussions.microsoft.com> wrote in message
news:E88D0208-1BB5-4834-91BA-F7A332A827EB@.microsoft.com...
>I have a registration process on a website which sends in the
> username/password etc via a stored procedure into SQL 2005.
> I want to encrypt the password on the way in in the stored procedure. This
> is the code that I am trying to do this with;
> ALTER PROCEDURE [dbo].[AdminsAdd]
> (
> @.AdminUsername varchar(100),
> @.AdminPassword varchar(100),
> @.AdminName varchar(100)
> )
> AS
> OPEN SYMMETRIC KEY SSN_Key_02
> DECRYPTION BY CERTIFICATE testEncryptionCertificate2;
> INSERT INTO Admins
> (
> AdminUsername,
> AdminPassword,
> AdminName
> )
> VALUES
> (
> @.AdminUsername,
> EncryptByKey(Key_GUID('SSN_Key_02'), @.AdminPassword),
> @.AdminName
> )
> I have used similar code to encrypt all of the current passwords that are
> in
> the table and it works fine. And I have another stored procedure that
> checks
> people's details when they are login in and the decrypt function works
> fine
> in that procedure.
> What am I doing wrong in this Stored Proc?