Encryption by Password

Hi,
1) What kind of encryption algorithm is used when we use EncryptByPassPhrase
function?
2) What's the difference between using "EncryptByPassPhrase" and "Symmetric
Key" (when used with password) except that you can use other algorythms in
Symmetric Keys?
Thanks in advance,
Leila"Leila" <Leilas@.hotpop.com> wrote in
news:#b$l6BPIGHA.1188@.TK2MSFTNGP14.phx.gbl:

> Hi,
> 1) What kind of encryption algorithm is used when we use
> EncryptByPassPhrase function?
>
I believe it is Triple DES
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Leila wrote:
> Hi,
> 1) What kind of encryption algorithm is used when we use EncryptByPassPhra
se
> function?
> 2) What's the difference between using "EncryptByPassPhrase" and "Symmetri
c
> Key" (when used with password) except that you can use other algorythms in
> Symmetric Keys?
> Thanks in advance,
> Leila
1) I can't find this documented. I guess it is Triple DES. That's the
default for key encryption by passphrase and Triple DES is commonly
assumed to be the most secure of the 64-bit algorithms I believe.
2) The important difference is that EncryptByKey makes password
management easier because the key acts as a level of indirection - the
plaintext is encrypted with the key not the password. That means you
can have more than one password and you can add and remove passwords
without having to re-encrypt all your data. For those reasons
EncryptByKey is a much more powerful tool than EncryptByPassPhrase.
It's likely to be more secure too because you can expire old passwords
more promptly and frequently.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks indeed David,
What are 'Password' and 'KEY_SOURCE' in CREATE SYMMETRIC KEY? I can't really
understand that what they do.
I read this in BOL:
"When a symmetric key is encrypted with a password instead of the public key
of the database master key, the TRIPLE_DES encryption algorithm is used"
Does this mean that other algorithms (listed in syntax of CREATE SYMMETRIC
KEY) are available only when we use a certificate, asymmetric key or other
symmetric keys in creation of our symmetric key?
Leila
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1138117788.519975.189610@.z14g2000cwz.googlegroups.com...
> Leila wrote:
> 1) I can't find this documented. I guess it is Triple DES. That's the
> default for key encryption by passphrase and Triple DES is commonly
> assumed to be the most secure of the 64-bit algorithms I believe.
> 2) The important difference is that EncryptByKey makes password
> management easier because the key acts as a level of indirection - the
> plaintext is encrypted with the key not the password. That means you
> can have more than one password and you can add and remove passwords
> without having to re-encrypt all your data. For those reasons
> EncryptByKey is a much more powerful tool than EncryptByPassPhrase.
> It's likely to be more secure too because you can expire old passwords
> more promptly and frequently.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Leila wrote:
> Thanks indeed David,
> What are 'Password' and 'KEY_SOURCE' in CREATE SYMMETRIC KEY? I can't real
ly
> understand that what they do.
> I read this in BOL:
> "When a symmetric key is encrypted with a password instead of the public k
ey
> of the database master key, the TRIPLE_DES encryption algorithm is used"
> Does this mean that other algorithms (listed in syntax of CREATE SYMMETRIC
> KEY) are available only when we use a certificate, asymmetric key or other
> symmetric keys in creation of our symmetric key?
> Leila
>
>
Triple DES is used only to encrypt the *key*. The algorithm used by
EncryptByKey to encrypt your *data* will be whatever is specified in
the CREATE SYMMETRIC KEY statement - be that RC4, DESX, AES, etc.
Where does the key come from? Either it is generated randomly for you
OR if you specify some value for KEY_SOURCE it will be generated
directly from that value (using a hashing function). The point of
KEY_SOURCE is that it means you can reproduce the same key again and
again - on a different server for example. Similarly, if the
IDENTITY_VALUE is specified it is used to reproduce the same key GUID
for that key.
The key itself will be Triple DES encrypted by each password you
specify. So you finish up with one encrypted copy of the key for each
password. Any of those passwords can therefore be used to open
(decrypt) the key and allow encryption and decryption to take place.
Laurentiu Cristofor's blog is a good source on SQL Server's encryption.
It explained a lot for me anyway:
http://blogs.msdn.com/lcris/archive/category/10357.aspx
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks David,
I tried symmetric key in several ways. I encrypted some string with that key
and I wasn't able to decrypt it on the new system unless I provided the same
KEY_SOURCE and IDENTITY_VALUE. Is it right or I am missing something?
If I ignore KEY_SOURCE and IDENTITY_VALUE when creating symmetric key
(supplying only a password) and encrypting some strings, how can I decrypt
them on the new system?
Leila
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1138135007.689698.260300@.g44g2000cwa.googlegroups.com...
> Leila wrote:
> Triple DES is used only to encrypt the *key*. The algorithm used by
> EncryptByKey to encrypt your *data* will be whatever is specified in
> the CREATE SYMMETRIC KEY statement - be that RC4, DESX, AES, etc.
> Where does the key come from? Either it is generated randomly for you
> OR if you specify some value for KEY_SOURCE it will be generated
> directly from that value (using a hashing function). The point of
> KEY_SOURCE is that it means you can reproduce the same key again and
> again - on a different server for example. Similarly, if the
> IDENTITY_VALUE is specified it is used to reproduce the same key GUID
> for that key.
> The key itself will be Triple DES encrypted by each password you
> specify. So you finish up with one encrypted copy of the key for each
> password. Any of those passwords can therefore be used to open
> (decrypt) the key and allow encryption and decryption to take place.
> Laurentiu Cristofor's blog is a good source on SQL Server's encryption.
> It explained a lot for me anyway:
> http://blogs.msdn.com/lcris/archive/category/10357.aspx
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Leila wrote:
> Thanks David,
> I tried symmetric key in several ways. I encrypted some string with that k
ey
> and I wasn't able to decrypt it on the new system unless I provided the sa
me
> KEY_SOURCE and IDENTITY_VALUE. Is it right or I am missing something?
That's correct.

> If I ignore KEY_SOURCE and IDENTITY_VALUE when creating symmetric key
> (supplying only a password) and encrypting some strings, how can I decrypt
> them on the new system?
> Leila
To do that you have to have some mechanism for exchanging keys with the
other server. That's exactly where KEY_SOURCE is useful. If you
regularly need to exchange symmetric keys or passwords with another
system then you would typically want to create an asymmetric key and
exchange public keys first. The target system's public key or
certificate can then be used in conjunction with EncryptByAsmKey or
EncryptByCert to exchange keys or passwords between the two servers.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--