Showing posts with label encrypt. Show all posts
Showing posts with label encrypt. Show all posts

Thursday, March 22, 2012

Binary Data on Multiple Servers

I'm using EncryptByKey to encrypt data in my SS2005 database. Since our server is really slow to access from home to work on, I used the Database Publishing Wizard and installed the db to work on at home. Then I created the certificate and symmetric key in my home db.

When I pull info using the DecryptByKey on our database at work on Windows 2003 Server, no problem, the data is decrypted. However, the same data does not decrypt at home on my Windows XP computer. I'm using TripleDes on both machines for the symmetric key (AES won't work on XP).

--To create my cert and key:

USE My_DB;
CREATE CERTIFICATE MyCert
ENCRYPTION BY PASSWORD = 'some password'
WITH SUBJECT = My Data',
START_DATE = '01/01/2007',
EXPIRY_DATE = '01/01/2099';
GO

CREATE SYMMETRIC KEY MyKey WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE MyCert;
GO

To encrypt:

OPEN SYMMETRIC KEY MyKey

DECRYPTION BY CERTIFICATE MyCert

WITH PASSWORD = 'same password as above';

Insert my record, use scope_identity to return primary key into @.CustomerID.

INSERT INTO [Customers] (EncryptByKey(Key_GUID('MyKey'), @.DataToEncrypt, 1, CONVERT( varbinary, @.CustomerID)))

CLOSE SYMMETRIC KEY MyKey

To decrypt:

SELECT CONVERT(varchar(3925), DecryptByKey(EncryptedField, 1, CONVERT( varbinary, @.CustomerID))) as PlainTextData

FROM Customers

WHERE (CustomerID= @.CustomerID)

Everything works fine when I run the decrypt query on the database on our work server. But I'm not getting decrypted data at home. Is the symmetric key or certificate machine specific? If so, that will cause a huge problem when we deploy to a production server.

Thanks in advance for your help!

Did you up the database master key on the server and restore it to your home machine? If not, then the data will not decrypt on your home machine. The database master key is used to encrypt the symmetric key which would then be used to decrypt your data. If you create the "same" symmetric key, but use a different database master key, then the identity will be different which will prevent decryption. This is by design, because it ensures that someone can fake security credentials and gain access to your data.|||

That's one thing I didn't do! Thanks Michael. That's got to be it.

Thank you,

Richard

|||

You generated different keys. Have a look at http://blogs.msdn.com/lcris/archive/2006/07/06/658364.aspx for how you can regenerate the same key on different machines.

Thanks
Laurentiu

|||

Laurentiu, great article! I didn't realize the Key_Source and Identity_Value were essential to make the key work in multiple databases. I thought as long as the algorithm and name were identical everything would decrypt properly. Once I created a new key like you mentioned in your article, the decryption worked great across both databases. Thanks again!

Richard

sql

Tuesday, February 14, 2012

Best way to HIDE passwords that are in a table

Hello All,
If I am storing passwords in a table within SQL Server 2000. Is there a way
to ENCRYPT or HIDE the passwords, so that when I run a SELECT statement, I
can not see them?
If this is not possible, what is the recommended way to HIDE passwords from
the SQL Server Administrator?
thans...SQL 2000 does not have built in tools for encryption but it support 3rd part
y
solutions.
check out the link below
http://www.sqlservercentral.com/col...oolkitpart1.asp
and also this one is pretty popular not free but not expensive
http://www.xpcrypt.com/
Good day,
Bulent
"MSUTech" wrote:

> Hello All,
> If I am storing passwords in a table within SQL Server 2000. Is there a w
ay
> to ENCRYPT or HIDE the passwords, so that when I run a SELECT statement, I
> can not see them?
> If this is not possible, what is the recommended way to HIDE passwords fro
m
> the SQL Server Administrator?
> thans...|||Hello,
Please use the password encrytion from your application side and stored the
encrypted data into database.
Thanks
Hari
"MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
news:5D6620EA-05C0-4044-9D1B-497261028F16@.microsoft.com...
> Hello All,
> If I am storing passwords in a table within SQL Server 2000. Is there a
> way
> to ENCRYPT or HIDE the passwords, so that when I run a SELECT statement, I
> can not see them?
> If this is not possible, what is the recommended way to HIDE passwords
> from
> the SQL Server Administrator?
> thans...|||One common method of storing passwords securely in a database is to use a
one-way hash function on the password and store the hashed value in the
database instead of encrypting it. You'll probably want to use an
SHA-family (or other secure) hash function (MD5-family hashes and many other
hash functions are not considered secure). Using this method you'll do
something like this:
-Initially you hash the password and store the hashed value in the database
in a VARBINARY or BINARY column.
-When you need to validate, accept an input string from the user and hash
that string using the same algorithm. Then compare the hashed user input
string to the hash of the password stored in the database.
The down-side is that once you hash a password you can't retrieve it's
original value as you can with encryption/decryption (hence the term
"one-way hash"). This is generally not a problem, however. You can perform
the hashes client-side with .NET or the Windows CryptoAPI or server-side
with extended procedures like these
http://www.sqlservercentral.com/col...oolkitpart1.asp
Other options include using .NET or CryptoAPI to encrypt/decrypt your
password client side, or using server-side extended procedures to do the
job.
"MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
news:5D6620EA-05C0-4044-9D1B-497261028F16@.microsoft.com...
> Hello All,
> If I am storing passwords in a table within SQL Server 2000. Is there a
> way
> to ENCRYPT or HIDE the passwords, so that when I run a SELECT statement, I
> can not see them?
> If this is not possible, what is the recommended way to HIDE passwords
> from
> the SQL Server Administrator?
> thans...|||Not that this is immediately helpful, but encryption is built into SQL 2005
so that you can do this without having to move it to the business layer. In
2000, as other have pointed out, you would build this into the CRUD object o
r
the business layer accessing the field.
--
Ryan Hanisco
MCSE, MCTS: SQL 2005, Project+
Chicago, IL
Remember: Marking helpful answers helps everyone find the info they need
quickly.
"MSUTech" wrote:

> Hello All,
> If I am storing passwords in a table within SQL Server 2000. Is there a w
ay
> to ENCRYPT or HIDE the passwords, so that when I run a SELECT statement, I
> can not see them?
> If this is not possible, what is the recommended way to HIDE passwords fro
m
> the SQL Server Administrator?
> thans...