Encrypting passwords

A friend of my self asked me how he can save a password not as clear text. He wanted to encrypt the password and save the encrypted string in the database.

How can he do this? Maybe somebody can help me here.

Regards Markus

What does your friend need the password for? If it is used for authentication (to verify that another password submitted is matching the password stored in the database), then you can hash the password. If you need to store the password to use it somewhere else in clear form, then you need to encrypt it.

For SQL Server 2000 there are no builtin functions for hashing or encryption You may hear about pwdencrypt - an undocumented function - do not use it. For SQL Server 2000, you will have to write your own extended procedures for performing encryption or hashing.

In SQL Server 2005, you can use HashBytes to hash the password and EncryptByKey to encrypt it.

Thanks
Laurentiu

|||

but is this truly secure? you're still sending the password over a connection in clear text, unless you're in SSL, yes? isn't it best to simply hash the password on the clientside to begin with?

Laurentiu Cristofor wrote:

In SQL Server 2005, you can use HashBytes to hash the password and EncryptByKey to encrypt it.

Thanks
Laurentiu

|||

It is secure if the connection is secured using SSL - it should be secured that way if you're concerned about security.

Hashing on the client side does not address the insecure connection problem, because your authentication will then only depend on the hash (that's all the server will see from the client), and then the hash will effectively serve the same role as the password, so if a hash is intercepted on an insecure connection, a third party can pass it back to the server and connect this way.

Also, note that in my previous post I did not recommend implementing custom authentication schemes using those functions. Instead, you should leverage the mechanisms already provided by SQL Server.

Thanks
Laurentiu

sql

Encrypt By Key gives different results every call time

Every time I call Encrypt by key I get a different result, although I am encrypting the same clear text,using the same authenticator and using the same key.
I tried calling it twice inside one stored procedure and I got two different results.

Declare @.Temp varbinary(8000);
Declare @.Temp2 varbinary(8000);
set @.Temp = EncryptByKey(Key_GUID('MyKey'),@.cleartext,1,@.Authenticator);
set @.Temp2= EncryptByKey(Key_GUID('MyKey'),@.cleartext,1,@.Authenticator);
Select @.Temp,@.Temp2;

when executing this stored procedure, the value of @.Temp is differnent from the result of @.Temp2, and the same thing happens without using an authenticator.

Is there something going behind in the encryption mechanism of SQL server?

yes it's deliberate and important. You might have 2 rows with the same value, for example a salary. If these had the same resulting encrypted value it would make discovery of the non-encrypted value much easier. To address this SQL 2005's encryption methods use a salt with a random initialisation vector so you get a different encrypted value for the same input text.

thx,

Simon.

Emptying reportservertempdb

I have a reportservertempdb database that has suddenly grown to 16Gb and has
stayed that way for a few days. How do I clear what might be cached in there
and/or get this database to shrink back down to a reasonable size?John,
We are experiencing exactly the same problem as you described: the
ReportServerTempDB suddenly grow to over 10GB and stay that large.
We recently run large number of reports, more than 5,000 pages, with
images merged in using URL on every page. At first, the transaction
log grow rapidly to over 15GB. We then turned off the caching,
controled the size of the transaction log, and scheduled backup of the
transaction log every hour. Now the size of the tranaction log is
under control, but there is a table named CHUNKDATA in
ReportServerTempDB which stayed to be large (over 11GB). I wonder if
it is save to truncate the CHUNKDATA table. And how to correctly
control the overall size of this database.
If you or anybody found or heard of any solutions to this problem,
please share them with me.
Thanks in advance!
Tommy|||I'm thinking since nothing in that database is important I'm going to drop
and recreate it from a script. I too turned off all caching but it didn't
help.
"Tommy" wrote:
> John,
> We are experiencing exactly the same problem as you described: the
> ReportServerTempDB suddenly grow to over 10GB and stay that large.
> We recently run large number of reports, more than 5,000 pages, with
> images merged in using URL on every page. At first, the transaction
> log grow rapidly to over 15GB. We then turned off the caching,
> controled the size of the transaction log, and scheduled backup of the
> transaction log every hour. Now the size of the tranaction log is
> under control, but there is a table named CHUNKDATA in
> ReportServerTempDB which stayed to be large (over 11GB). I wonder if
> it is save to truncate the CHUNKDATA table. And how to correctly
> control the overall size of this database.
> If you or anybody found or heard of any solutions to this problem,
> please share them with me.
> Thanks in advance!
> Tommy
>|||need to delete snapshots that are in history.
John wrote:
>I'm thinking since nothing in that database is important I'm going to drop
>and recreate it from a script. I too turned off all caching but it didn't
>help.
>"Tommy" wrote:
>> John,
>> We are experiencing exactly the same problem as you described: the
>[quoted text clipped - 16 lines]
>> Tommy
--
Gene Hunter
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1