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
--
Showing posts with label algorithm. Show all posts
Showing posts with label algorithm. Show all posts
Monday, March 26, 2012
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
--
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
--
Thursday, March 22, 2012
Encryption - importing keys
Is it possible to load data into MSSQL 2005 that has been encrypted externally with a symmetric key algorithm, such as AES, and then import the key to SQL Server? After browsing through Books Online, I don't see any way to import a symmetric key from an external file, but maybe I'm missing something.
No, at this time, it is not possible to do that. You would have to decrypt the data and then encrypt it again using SQL Server's encryption functions.
Thanks
Laurentiu
If your primary concern is protecting data in transit, you should be able to use SSL to accomplish this:
http://msdn2.microsoft.com/en-us/library/ms189067.aspx
Sung
Sunday, March 11, 2012
Encrypted data size by original size, algorithm ?
I want to know encrypted data's size for designing database field size.
For example, cardnumber varchar(20) Encrypted by Triple_DES and PassPhrase, How match size does need to encrypted data store field.
I think the size does not depend to PassPhrase char length.
Regards,
Yoshihiro Kawabata
The simplest way is to just encrypt the largest piece of data that you will store using the encryption algorithm of your choice and take note of the size of the resulting blob. That will be the size of the field. There is also a formula that allows you to compute this, but it also includes the size of a header which might expand in future versions of SQL Server. I suggest to always leave several bytes more to account for changes in the format of encrypted data.
Thanks
Laurentiu
Here's a post that describes how to determine the length of the encrypted data for SQL Server 2005:
http://blogs.msdn.com/yukondoit/archive/2005/11/24/496521.aspx
Thanks
Laurentiu
Encrypted data size by original size, algorithm ?
I want to know encrypted data's size for designing database field size.
For example, cardnumber varchar(20) Encrypted by Triple_DES and PassPhrase, How match size does need to encrypted data store field.
I think the size does not depend to PassPhrase char length.
Regards,
Yoshihiro Kawabata
The simplest way is to just encrypt the largest piece of data that you will store using the encryption algorithm of your choice and take note of the size of the resulting blob. That will be the size of the field. There is also a formula that allows you to compute this, but it also includes the size of a header which might expand in future versions of SQL Server. I suggest to always leave several bytes more to account for changes in the format of encrypted data.
Thanks
Laurentiu
Here's a post that describes how to determine the length of the encrypted data for SQL Server 2005:
http://blogs.msdn.com/yukondoit/archive/2005/11/24/496521.aspx
Thanks
Laurentiu
Friday, March 9, 2012
Encrption Level
Does anyone know what encryption level (algorithm, API, strength, etc.) is used when SSAS 2005 encrypts data between a client and the server?
Thanks,
Dave Fackler1. For native connections (when you connect directly to AS2005, no http/IIS) SSPI is used for authentication, signatures and encryption and these 2 connection string properties can be used:
Protection Level =
NONE (no authentication, no signatures, no encryption)
CONNECT (authentication, clear text without signatures)
PKT INTEGRITY (authentication, clear text with signatures - for replay and data tempering protection)
PKT PRIVACY (authentication, encryption, signatures included)
SSPI = the name of the SSPI package to be used, for example 'Kerberos' or 'NTLM' (default is 'Negociate')
2. For http connections, you can use https
3. For local cubes connections, the problem won't apply
Adrian Dumitrascu.|||Do you know how can we change the protection level to NONE on a deployed OLAP project?
Encrption Level
Does anyone know what encryption level (algorithm, API, strength, etc.) is used when SSAS 2005 encrypts data between a client and the server?
Thanks,
Dave Fackler1. For native connections (when you connect directly to AS2005, no http/IIS) SSPI is used for authentication, signatures and encryption and these 2 connection string properties can be used:
Protection Level =
NONE (no authentication, no signatures, no encryption)
CONNECT (authentication, clear text without signatures)
PKT INTEGRITY (authentication, clear text with signatures - for replay and data tempering protection)
PKT PRIVACY (authentication, encryption, signatures included)
SSPI = the name of the SSPI package to be used, for example 'Kerberos' or 'NTLM' (default is 'Negociate')
2. For http connections, you can use https
3. For local cubes connections, the problem won't apply
Adrian Dumitrascu.|||Do you know how can we change the protection level to NONE on a deployed OLAP project?
Encrption Level
Does anyone know what encryption level (algorithm, API, strength, etc.) is used when SSAS 2005 encrypts data between a client and the server?
Thanks,
Dave Fackler1. For native connections (when you connect directly to AS2005, no http/IIS) SSPI is used for authentication, signatures and encryption and these 2 connection string properties can be used:
Protection Level =
NONE (no authentication, no signatures, no encryption)
CONNECT (authentication, clear text without signatures)
PKT INTEGRITY (authentication, clear text with signatures - for replay and data tempering protection)
PKT PRIVACY (authentication, encryption, signatures included)
SSPI = the name of the SSPI package to be used, for example 'Kerberos' or 'NTLM' (default is 'Negociate')
2. For http connections, you can use https
3. For local cubes connections, the problem won't apply
Adrian Dumitrascu.|||Do you know how can we change the protection level to NONE on a deployed OLAP project?
Subscribe to:
Posts (Atom)