Sunday, February 19, 2012
Column Encryption/Decryption in SQL 2000
I m using MS SQL 2000 server. I want to encrypt a password column of
my table and retrieve it later by decrypting the same. How can I do so in sq
l
2000 without using any third party tool?
Thanks,
RbanBphYou can't (unless you write your own - technically that would be a 1st
party tool wouldn't it? :P).
*mike hodgson*
http://sqlnerd.blogspot.com
RbanBph wrote:
>Hello,
> I m using MS SQL 2000 server. I want to encrypt a password column o
f
>my table and retrieve it later by decrypting the same. How can I do so in s
ql
>2000 without using any third party tool?
>Thanks,
>RbanBph
>
>|||You can encrypt password column using Extended procedures.
You can search BOL and below URL.
http://www.codeproject.com/database/xp_md5.asp
Hong.
"RbanBph"?? ??? ??:
> Hello,
> I m using MS SQL 2000 server. I want to encrypt a password column
of
> my table and retrieve it later by decrypting the same. How can I do so in
sql
> 2000 without using any third party tool?
> Thanks,
> RbanBph
>
Column Encryption via stored procedure
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?
Column encryption questions
With SQL Server 2005 encryption:
1. Is it possible to encrypt a column "in place" - or do we always need to create a new varbinary column to hold the encrypted column? Some products (NetLib Encryptionizer) seem to be able to encrypt an existing column in place.
2.If you encrypt an existing column to a new varbinary column, it it customary to then ALTER the table and delete the existing plain-text column?
Thanks...
Anyone? I mean, does everyone create new columns all the time?
|||HI John,
i think these articles will clear your doubts
http://www.tometasoftware.com/MySQL-5-vs-Microsoft-SQL-Server-2005.asp
http://www.netlib.com/sql-column-encryption-api.shtml
http://www.mssqltips.com/tip.asp?tip=928
Hemantgiri S. Goswami
|||1) No, you cannot encrypt in place, as the output of the encryption may not make sense as a value of the original data type; so, you need to create the varbinary column.
2) Yes, you would want to delete the old column, after encrypting it.
Thanks
Laurentiu
Column encryption in SQL server Compact Edition?
You can only encrypt the entire database file, by setting a password on the connection string used with the database:
Password-or-Pwd-or-database password-or-ssceatabase password
The database password, which can be up to 40 characters in length. If not specified, the default value is no password. This property is required if you enable encryption on the database. If you specify a password, encryption is automatically enabled on the database.
Encrypt-or-encrypt database -or-ssce: encrypt database
A Boolean value that determines whether or not the database is encrypted. Must be set to true to enable encryption or false for no encryption. If not specified, the default value is false. If you enable encryption, you must also specify a password with the password property. If you specify a password, encryption is enabled regardless of how you set this property.
See this topic for a code sample on how to create a encrypted database:
http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceengine.aspx
|||Thanks for the reply. So it is not possible to encrypt data on a column only basis. It must be the full database. Is this just the case for Compact Edition?|||This is just the case for CE...|||Thankyou for the help. Im going to have to look into another solution, perhaps Oracle Lite. Cheers!|||Oracle Lite only offers the ability to encrypt the entire database file as well.
There is nothing stopping you from doing the encryption in code.
Column encryption in SQL server Compact Edition?
You can only encrypt the entire database file, by setting a password on the connection string used with the database:
Password-or-Pwd-or-database password-or-ssceatabase password
The database password, which can be up to 40 characters in length. If not specified, the default value is no password. This property is required if you enable encryption on the database. If you specify a password, encryption is automatically enabled on the database.
Encrypt-or-encrypt database -or-ssce: encrypt database
A Boolean value that determines whether or not the database is encrypted. Must be set to true to enable encryption or false for no encryption. If not specified, the default value is false. If you enable encryption, you must also specify a password with the password property. If you specify a password, encryption is enabled regardless of how you set this property.
See this topic for a code sample on how to create a encrypted database:
http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceengine.aspx
|||Thanks for the reply. So it is not possible to encrypt data on a column only basis. It must be the full database. Is this just the case for Compact Edition?|||This is just the case for CE...|||Thankyou for the help. Im going to have to look into another solution, perhaps Oracle Lite. Cheers!|||Oracle Lite only offers the ability to encrypt the entire database file as well.
There is nothing stopping you from doing the encryption in code.
Column Encryption in Sql Server 2000
thanks,
DinakarHi ndinakar:
Nothing in SQL 2000.
Just as a side note, MS recently announced native encryption for SQL Server 2005.|||There is an undocumented function in SQL 2000, which allow to encrypt data, but it is one way hash, there is no way to decrypt it, so will not be suitable to store CC #, its better suited to store the passwords for compaison purpose.|||so how do people do it ? using 3rd party encryptioners ? anyone already doing it willing to share some of your xperience pls ?
TIA.
Column Encryption
Is there a way to encrypt a column on SQL Server 7 ? So
when doing a direct query on SQL Administrator, etc. the
column is not readable.
Thanks for your comments.
You might check out this article: http://www.dbazine.com/larsen2.shtml
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Jorge Sanchez" <anonymous@.discussions.microsoft.com> wrote in message
news:176b701c448f4$55da5a90$a501280a@.phx.gbl...
> Hi All,
> Is there a way to encrypt a column on SQL Server 7 ? So
> when doing a direct query on SQL Administrator, etc. the
> column is not readable.
> Thanks for your comments.
>
|||Thanks, I have tried and made it work.
>--Original Message--
>You might check out this article:
http://www.dbazine.com/larsen2.shtml
>
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Jorge Sanchez" <anonymous@.discussions.microsoft.com>
wrote in message
>news:176b701c448f4$55da5a90$a501280a@.phx.gbl...
>
>.
>
Column Encryption
Is there a way to encrypt a column on SQL Server 7 ? So
when doing a direct query on SQL Administrator, etc. the
column is not readable.
Thanks for your comments.You might check out this article: http://www.dbazine.com/larsen2.shtml
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Jorge Sanchez" <anonymous@.discussions.microsoft.com> wrote in message
news:176b701c448f4$55da5a90$a501280a@.phx
.gbl...
> Hi All,
> Is there a way to encrypt a column on SQL Server 7 ? So
> when doing a direct query on SQL Administrator, etc. the
> column is not readable.
> Thanks for your comments.
>|||Thanks, I have tried and made it work.
>--Original Message--
>You might check out this article:
http://www.dbazine.com/larsen2.shtml
>
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Jorge Sanchez" <anonymous@.discussions.microsoft.com>
wrote in message
> news:176b701c448f4$55da5a90$a501280a@.phx
.gbl...
>
>.
>
Column encryption
news:bk9mmf$spu$1@.sunce.iskon.hr...
> How to encrypt a column in a table (i.e. which might contain passwords)?
There's nothing in MSSQL itself, so you can implement your own solution or
look for a third-party solution. Handling it in your application layer is
probably the most obvious solution.
http://www.sqlsecurity.com/ (see the FAQ)
Simon
Column Encryption
Is there a way to encrypt a column on SQL Server 7 ? So
when doing a direct query on SQL Administrator, etc. the
column is not readable.
Thanks for your comments.You might check out this article: http://www.dbazine.com/larsen2.shtml
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Jorge Sanchez" <anonymous@.discussions.microsoft.com> wrote in message
news:176b701c448f4$55da5a90$a501280a@.phx.gbl...
> Hi All,
> Is there a way to encrypt a column on SQL Server 7 ? So
> when doing a direct query on SQL Administrator, etc. the
> column is not readable.
> Thanks for your comments.
>|||Thanks, I have tried and made it work.
>--Original Message--
>You might check out this article:
http://www.dbazine.com/larsen2.shtml
>
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Jorge Sanchez" <anonymous@.discussions.microsoft.com>
wrote in message
>news:176b701c448f4$55da5a90$a501280a@.phx.gbl...
>> Hi All,
>> Is there a way to encrypt a column on SQL Server 7 ? So
>> when doing a direct query on SQL Administrator, etc. the
>> column is not readable.
>> Thanks for your comments.
>
>.
>