Encryption question, sql 2000

Situation: We are required to encrypt procs in part of our production
environment. We have the
unencrypted procs in test environment. As a sanity check (when a problem
arises), is there a way
to verify that an encrypted proc (ctext) in production matches a specific
unencrypted version?
If the procs were first encrypted with Alter Proc ... With Encryption, then
the same script can be
run again, and before/after syscomments.ctext values compared. But this is
a destructive test
that potentially changes prod environment.
Is there some benign alternative?
Thanks,
Craig Hessel
WPS, Madison, WICraig,
Two possibilities:
1) Restore db to development machine. Unencrypt stored procs there and check them
( unencryption algo is straightforward but destructive )
2) Check the length of the procedures in syscomments - most edits will change this
Regards
AJ
"Craig Hessel" <craig_hessel@.hotmail.com> wrote in message news:10181dttf4ta204@.corp.supernews.com...
> Situation: We are required to encrypt procs in part of our production
> environment. We have the
> unencrypted procs in test environment. As a sanity check (when a problem
> arises), is there a way
> to verify that an encrypted proc (ctext) in production matches a specific
> unencrypted version?
> If the procs were first encrypted with Alter Proc ... With Encryption, then
> the same script can be
> run again, and before/after syscomments.ctext values compared. But this is
> a destructive test
> that potentially changes prod environment.
> Is there some benign alternative?
> Thanks,
> Craig Hessel
> WPS, Madison, WI
>sql

Encryption question, sql 2000

Situation: We are required to encrypt procs in part of our production
environment. We have the
unencrypted procs in test environment. As a sanity check (when a problem
arises), is there a way
to verify that an encrypted proc (ctext) in production matches a specific
unencrypted version?
If the procs were first encrypted with Alter Proc ... With Encryption, then
the same script can be
run again, and before/after syscomments.ctext values compared. But this is
a destructive test
that potentially changes prod environment.
Is there some benign alternative?
Thanks,
Craig Hessel
WPS, Madison, WICraig,
Two possibilities:
1) Restore db to development machine. Unencrypt stored procs there and chec
k them
( unencryption algo is straightforward but destructive )
2) Check the length of the procedures in syscomments - most edits will chang
e this
Regards
AJ
"Craig Hessel" <craig_hessel@.hotmail.com> wrote in message news:10181dttf4ta204@.corp.supernews.com
..

quote:

---

> Situation: We are required to encrypt procs in part of our production
> environment. We have the
> unencrypted procs in test environment. As a sanity check (when a problem
> arises), is there a way
> to verify that an encrypted proc (ctext) in production matches a specific
> unencrypted version?
> If the procs were first encrypted with Alter Proc ... With Encryption, the
n
> the same script can be
> run again, and before/after syscomments.ctext values compared. But this i
s
> a destructive test
> that potentially changes prod environment.
> Is there some benign alternative?
> Thanks,
> Craig Hessel
> WPS, Madison, WI
>

---

Encryption Choices / Best Practices for hosted environment (shared server)

I'm building a hosted website and I am using SQL 2005.
The DBA for the host has told me that i can not encrypt a symmetric key with a certificate, when using that symmetric key for encryption. As i read that this method provided optimum performance/ security for encrypting columns of data.

The DBA told me i can use a cert or a symmetric key for encryption.
I have searched for comparisons and found a blog entry by Laurentiu Cristofor comparing certs with asymmetric keys. Which leads me to believe that certs and asymm are very different than symmetric keys.

My question is which is the best choice in a hosted environment for column encryption, a cert or symmetric key.
Which is more secure? Does one offer a significant performance (dis)advantage?

TIA

I'd encrypt the column data with a symmetric key and protect the symmetric key with an asymmetric key or a cert.

The encryption / decryption operations with a symmetric key are much faster then the same operations with an asymmetric key or cert.

I'd use AES_128 | AES_192 | AES_256 for the algorithm if the hosted OS supported it.

HTH,

-Steven Gott

SDE/T

SQL Server

|||

Steven Gott - MS wrote:

I'd encrypt the column data with a symmetric key and protect the symmetric key with an asymmetric key or a cert.

Steve, thanks for helping.

I think i need to clarify my question. On my development machine at home I am currently doing what you reccomend. Ecrypting the symmetric key with a certificate and using the symmetric key to encrypt column data.

BUT, to deploy my database on a shared server (hosted machine) the DBA at the host has told me I am not allowed to do this. I am only allowed to ecnrypt columns directly with a certificate or a symmetric key. (I need to retrieve the column data so I can't do asymmetric)

Is there a security benefit to using a cert over a symmetric key? (I am assuming i can directly encrypt data with just a cert.)

Basically what are the pro's and cons of encrypting data directly with only a cert and only a symmetric key.

TIA,

josh
|||

The performance of encrypting by certificates will be very bad for large amounts of data. Certificates are better for signing things than encrypting them.

You can also look at Raul's blog for insight into cryptography in SQL Server here is an entry involving indexes and encrypted columns http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx

I'd encrypt with a symmetric key.

HTH,

-Steven

SDE/T

SQL Server

|||

Does your dba has any rationale for not letting you use a symmetric key encrypted with a certificate? That is a best practice for encryption, if there ever was one.

You can also encrypt the symmetric key with a password, instead of using a certificate, but then you'll have to pass that password around, whenever you'll need to open the key.

Certificates are much slower at encryption than symmetric keys and they have some additional limitations on how large a piece of data they can encrypt, hence it's not recommended to use them for encrypting data directly. I second Steven's suggestion to look at Raul's blog for additional details on this.

Thanks

Laurentiu

Encryption Choices / Best Practices for hosted environment (shared server)

I'm building a hosted website and I am using SQL 2005.
The DBA for the host has told me that i can not encrypt a symmetric key with a certificate, when using that symmetric key for encryption. As i read that this method provided optimum performance/ security for encrypting columns of data.

The DBA told me i can use a cert or a symmetric key for encryption.
I have searched for comparisons and found a blog entry by Laurentiu Cristofor comparing certs with asymmetric keys. Which leads me to believe that certs and asymm are very different than symmetric keys.

My question is which is the best choice in a hosted environment for column encryption, a cert or symmetric key.
Which is more secure? Does one offer a significant performance (dis)advantage?

TIA

I'd encrypt the column data with a symmetric key and protect the symmetric key with an asymmetric key or a cert.

The encryption / decryption operations with a symmetric key are much faster then the same operations with an asymmetric key or cert.

I'd use AES_128 | AES_192 | AES_256 for the algorithm if the hosted OS supported it.

HTH,

-Steven Gott

SDE/T

SQL Server

|||

Steven Gott - MS wrote:

I'd encrypt the column data with a symmetric key and protect the symmetric key with an asymmetric key or a cert.

Steve, thanks for helping.

I think i need to clarify my question. On my development machine at home I am currently doing what you reccomend. Ecrypting the symmetric key with a certificate and using the symmetric key to encrypt column data.

BUT, to deploy my database on a shared server (hosted machine) the DBA at the host has told me I am not allowed to do this. I am only allowed to ecnrypt columns directly with a certificate or a symmetric key. (I need to retrieve the column data so I can't do asymmetric)

Is there a security benefit to using a cert over a symmetric key? (I am assuming i can directly encrypt data with just a cert.)

Basically what are the pro's and cons of encrypting data directly with only a cert and only a symmetric key.

TIA,

josh
|||

The performance of encrypting by certificates will be very bad for large amounts of data. Certificates are better for signing things than encrypting them.

You can also look at Raul's blog for insight into cryptography in SQL Server here is an entry involving indexes and encrypted columns http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx

I'd encrypt with a symmetric key.

HTH,

-Steven

SDE/T

SQL Server

|||

Does your dba has any rationale for not letting you use a symmetric key encrypted with a certificate? That is a best practice for encryption, if there ever was one.

You can also encrypt the symmetric key with a password, instead of using a certificate, but then you'll have to pass that password around, whenever you'll need to open the key.

Certificates are much slower at encryption than symmetric keys and they have some additional limitations on how large a piece of data they can encrypt, hence it's not recommended to use them for encrypting data directly. I second Steven's suggestion to look at Raul's blog for additional details on this.

Thanks

Laurentiu

Encryption

We need to be able to encrypt very sensitive columns of data in a
quasi-warehouse type environment and, from what I've read, SS 2005 should
work quite well for this. But I need examples of how to implement this. I
know very little about encryption and so I need some easy-to-read examples of
how to encrypt/decrypt using stored procs, scripts and code in 2005. Where
can I find examples like this? Any sites, books, articles, links would be
much appreciated.CLM wrote:
> We need to be able to encrypt very sensitive columns of data in a
> quasi-warehouse type environment and, from what I've read, SS 2005 should
> work quite well for this. But I need examples of how to implement this. I
> know very little about encryption and so I need some easy-to-read examples of
> how to encrypt/decrypt using stored procs, scripts and code in 2005. Where
> can I find examples like this? Any sites, books, articles, links would be
> much appreciated.
There are some examples in Books Online...|||See Laurentiu's blog:
http://blogs.msdn.com/lcris/archive/category/10357.aspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e3lV0PUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> CLM wrote:
>> We need to be able to encrypt very sensitive columns of data in a
>> quasi-warehouse type environment and, from what I've read, SS 2005 should
>> work quite well for this. But I need examples of how to implement this.
>> I know very little about encryption and so I need some easy-to-read
>> examples of how to encrypt/decrypt using stored procs, scripts and code
>> in 2005. Where can I find examples like this? Any sites, books,
>> articles, links would be much appreciated.
> There are some examples in Books Online...

Encryption

CLM wrote:
> We need to be able to encrypt very sensitive columns of data in a
> quasi-warehouse type environment and, from what I've read, SS 2005 should
> work quite well for this. But I need examples of how to implement this.
I
> know very little about encryption and so I need some easy-to-read examples
of
> how to encrypt/decrypt using stored procs, scripts and code in 2005. Wher
e
> can I find examples like this? Any sites, books, articles, links would be
> much appreciated.
There are some examples in Books Online...See Laurentiu's blog:
http://blogs.msdn.com/lcris/archive/category/10357.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e3lV0PUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> CLM wrote:
> There are some examples in Books Online...|||We need to be able to encrypt very sensitive columns of data in a
quasi-warehouse type environment and, from what I've read, SS 2005 should
work quite well for this. But I need examples of how to implement this. I
know very little about encryption and so I need some easy-to-read examples o
f
how to encrypt/decrypt using stored procs, scripts and code in 2005. Where
can I find examples like this? Any sites, books, articles, links would be
much appreciated.|||CLM wrote:
> We need to be able to encrypt very sensitive columns of data in a
> quasi-warehouse type environment and, from what I've read, SS 2005 should
> work quite well for this. But I need examples of how to implement this.
I
> know very little about encryption and so I need some easy-to-read examples
of
> how to encrypt/decrypt using stored procs, scripts and code in 2005. Wher
e
> can I find examples like this? Any sites, books, articles, links would be
> much appreciated.
There are some examples in Books Online...|||See Laurentiu's blog:
http://blogs.msdn.com/lcris/archive/category/10357.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e3lV0PUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> CLM wrote:
> There are some examples in Books Online...

Emulate Multi-CPU ?

Are there any option to emulate multi-cpu ?

Because, I start to use SQL Server 2008 in VirtualPC environment.

In VirtualPC, CPU is one.

So, I cannot check new feature for multi-cpu, like bitmap filter.

Therefore, I wish the option for two or more SOS_CPU node, SOS_Scheduler in one CPU.

Regards,

I've not seen any virtualization software capable of emulating multiple CPUs, but if you use VMWare on a computer with more than one CPU you can assign more than one CPU to the VM; and they will appear as multiple CPUs in the VM.

Apart from that, what I really like is to get my hands on an old 2-way or 4-way computer, they're really great for this kind of testing.

|||As Ole says; VMWare allows (both on Win as well as on Mac) the guest VM to utilize multi CPU's. Both if you have a multi-core box, or a "proper" multi CPU box.

Niels
|||

I don't know if they removed it from SQL Server 2008, but if not then the -P startup switch should do the trick. See http://www.microsoft.com/technet/abouttn/flash/tips/tips_120705.mspx

|||

Excellent !.

-P startup switch works on SQL Server 2008 June CTP. like -P4.

4 scheduler found in 'select * from sys.dm_os_schedulers'.

and Some query use 'Parallel' in execute plan.

best regards,