Empty Column: master

Showing posts with label master. Show all posts

Thursday, March 22, 2012

Encryption and recovery on standby machine

Hi there,

to follow on my question in the other forum, I have a database with triple des encrypted columns

Master key created, certificate and symmetric key using certificate. Values inserted into table (encrypted)

Can select etc, all works fine.

Back up and restore database on another server, decrypting the data results in NULLs being returned. Backed up and restore the master key, cerficate, etc but either it completes successfully or SQL states that the old and the new keys are the same and wont be changed. Master key opens (using password), cert opens (is viewable in sys.openkeys) but then closes automatically after trying to decrypt. Suggests there is something wrong with the certificate or the key, but I have no idea what it is.

Can someone please tell me exactly what is needed to view this on the 2ndary box? And what I might be missing?

Thanks

When you restore the database on the other server, you will need to add the service master key encryption to the database master key. You don't need to individually backup and restore anyhting else (don't worry about individually restoring the master key and the certificate). See this post for additional information on the master keys: http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx.

If you opened the master key using a password, things should also work with no change.

But the certificate cannot be "opened" and it never shows in sys.openkeys. What you need to open is the symmetric key and this should be done following the same steps that you did in the original database.

Please send me more details on the steps that you are following, and I'll be able to help you more specifically. Also, please copy-paste the text and number of any error that you encounter.

Thanks
Laurentiu

|||

Hi,

Thanks Laurentiu.

I added the SMK encrypted to the database masterkey, and I was able to succesfully view decrypted information on the secondary node.

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Tomorrow I will run through the procedure again using new databases, and post all the scripts I used for the encryption and restoration on the second server (it wont be pages full, merely one line per activity and someone else might get benefit from it as well)

|||

This ALTER MASTER KEY statement is the only thing you'll need to issue to complete the restoration of the database. Everything else should already be recovered with the database itself. Let us know if you hit any issues.

Thanks
Laurentiu

|||

Hi,

Ok source database -

create master key with password

create certificate

create symmetric key with encryption by certificate created above

open key

insert values into table (using encryptbykey)

create view that that uses decryptbykey, all works fine.

Backup database

Restore on 2nd machine

Open the master key

Alter master key add encryption by service master key

After this I open the symmetric key and try to decrypt but everything gets returned as NULLs.

Will run through this again and see if I missed anything.

|||

The steps you followed seem correct. Let us know if you've also been unable to decrypt the second time.

One simple suggestion: verify that the symmetric key is indeed opened (select * from sys.openkeys).

Thanks
Laurentiu

|||

Hi,

Tried it just now and the values decrypted fine. Havent touched the server since the post was made, and did not run any other scripts on it. Weird.,,

Repeated the restore again with another database, same steps, same result, NULLs when decrypting. The key is open and in sys.openkeys (cant open the key without opening the master key first), try to use the decryptbykey and the are no values returned (key remains open). Restarted SQL, key opens fine but still NULLs get returned but now the key gets closed automatically after the first decryptbykey attempt.

Will leave the server "as is" for now and check it again later to see if it's successfull then.

This made me think of some extra security questions, will post in a seperate thread :)

|||

You should be able to open the symmetric key without opening the master key if you added the service master key encryption to it.

The behavior that you describe is very strange indeed. Could you post the T-SQL that you used to open the key and to decrypt? I will try to repro this behavior, but because of holidays, I may not be able to do this very soon.

Thanks
Laurentiu

|||

This morning, check it out and the restored database is decrypting information... again no changes done, server just left overnight.

This is what I used to check the information/decrypt - you'll recognise it from your blog as I was using snippets of your code as part of this :)

If you need any other info, please let me know.

open symmetric key sk_player decryption by certificate cert_sk_admin;

select * from sys.openkeys

select id,name, CONVERT(varchar(10), decryptbykey(salary, 1, CONVERT(varchar(30), id))) AS salary from t_employees

select * from v_employees

select * from sys.openkeys

This was done before and only once, as it couldnt open the symmetric key without the master key being open first.

open master key decryption by password = 'xxxkickasspasswordxxxx';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||Both your steps and the SQL look right. Is this issue still occurring? What version are you using? (SELECT @.@.version).|||

Problem still occuring.

Version is (on the standby box) - SQL2005 Dev Edition on Windows Server 2000

On the main box it is SQL2005 Dev Edition on Windows 2003 Enterprise SP1

I got the Enterprise Editions of SQL2005 (which will go on the live systems) from MSDN and will try that as well, maybe it's something with the dev edition?

Thanks

|||

There shouldn't be any difference between Developer and Enterprise regarding encryption. Could you also specify how you created the certificate and the symmetric key?

Thanks
Laurentiu

|||

Hi there,

I create the cert and key using this (using Triple Des since the standby machine is Windows 2000 and doesnt do AES)

create certificate cert_sk_td3 with subject = 'Certificate'

create symmetric key sk_td3 with algorithm = triple_des encryption by certificate cert_sk_td3;

Let me know if you need any more info

|||

We have tried the scenario that you describe, moving a database between a Windows 2000 and a Windows 2003 machine, and we were successful in decrypting data after restores. We were not able to reproduce the problem that you described.

You mentioned that data becomes available after a while. How much time did it took before you could decrypt? Could you post the entire script that you are executing for encrypting the data and then decrypting it after restoring the database?

I'd also suggest to try to encrypt and decrypt some value after the restore to verify if the behavior that you are noticing is also happening for data encrypted after the restore. If the encryption would fail as well, then that would indicate an issue with the key.

In your scenario, which is the machine on which the decryption fails after restore? Is it the 2000 machine or the 2003 one? (I'm not sure what you mean by the standby machine)

Thanks
Laurentiu

|||

Hi Laurentiu,

In the interim I have upgradede both SQL2005s from Dev Edition to Enterprise Edition (from the MSDN Subscription downloads)

What usually happens with the data is that is becomes available the next morning when I am back at the office, after playing with it in the afternoon and not getting any results. I have tried things like restart SQL and reboot the server to see if that would speed up the recovery, but it didnt help anything. Nothing happens in the server during that time, I just get in, open the symm key and it decrypts.

I am going from a Server2003 (SP1 and hotfixes) to a Server2000(SP4 and all hotfixes). It is on the 2000 Server that I cant decrypt (I am using TripleDES to test since AES isnt supported on 2000).

I retried this after the upgrade from Dev to Enterprise, but I still had the problem occuring. I am able to open the key (appears in sys.openkeys) but inserting any values into the 2000 database just insert NULLs into the encrypted column. So the key doesnt seem happy to do encryption or decryption.

I then backed up the database on the 2000 server, and restored it again on the 2003 server (where it came from originally) under a new name. Again I opened the master key and did the service master key change, opened the symmetric key and was able to view the information. So yeah, it seems everything is fine on the database side, just something on the 2000 set up is causing some issues.

This is a bit of a mystery, I have left the machines and will check it tomorrow morning to see if the 2000 machine magically starts to decrypt again. There arent any jobs or policies on them and they are purely standalone.

Is there any trace or debugging that I can enable that might give some more info?

Encryption and recovery on standby machine

Hi there,

to follow on my question in the other forum, I have a database with triple des encrypted columns

Master key created, certificate and symmetric key using certificate. Values inserted into table (encrypted)

Can select etc, all works fine.

Can someone please tell me exactly what is needed to view this on the 2ndary box? And what I might be missing?

Thanks

If you opened the master key using a password, things should also work with no change.

Please send me more details on the steps that you are following, and I'll be able to help you more specifically. Also, please copy-paste the text and number of any error that you encounter.

Thanks
Laurentiu

|||

Hi,

Thanks Laurentiu.

I added the SMK encrypted to the database masterkey, and I was able to succesfully view decrypted information on the secondary node.

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||

Thanks
Laurentiu

|||

Hi,

Ok source database -

create master key with password

create certificate

create symmetric key with encryption by certificate created above

open key

insert values into table (using encryptbykey)

create view that that uses decryptbykey, all works fine.

Backup database

Restore on 2nd machine

Open the master key

Alter master key add encryption by service master key

After this I open the symmetric key and try to decrypt but everything gets returned as NULLs.

Will run through this again and see if I missed anything.

|||

The steps you followed seem correct. Let us know if you've also been unable to decrypt the second time.

One simple suggestion: verify that the symmetric key is indeed opened (select * from sys.openkeys).

Thanks
Laurentiu

|||

Hi,

Tried it just now and the values decrypted fine. Havent touched the server since the post was made, and did not run any other scripts on it. Weird.,,

Will leave the server "as is" for now and check it again later to see if it's successfull then.

This made me think of some extra security questions, will post in a seperate thread :)

|||

You should be able to open the symmetric key without opening the master key if you added the service master key encryption to it.

Thanks
Laurentiu

|||

This morning, check it out and the restored database is decrypting information... again no changes done, server just left overnight.

This is what I used to check the information/decrypt - you'll recognise it from your blog as I was using snippets of your code as part of this :)

If you need any other info, please let me know.

open symmetric key sk_player decryption by certificate cert_sk_admin;

select * from sys.openkeys

select id,name, CONVERT(varchar(10), decryptbykey(salary, 1, CONVERT(varchar(30), id))) AS salary from t_employees

select * from v_employees

select * from sys.openkeys

This was done before and only once, as it couldnt open the symmetric key without the master key being open first.

open master key decryption by password = 'xxxkickasspasswordxxxx';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||Both your steps and the SQL look right. Is this issue still occurring? What version are you using? (SELECT @.@.version).|||

Problem still occuring.

Version is (on the standby box) - SQL2005 Dev Edition on Windows Server 2000

On the main box it is SQL2005 Dev Edition on Windows 2003 Enterprise SP1

I got the Enterprise Editions of SQL2005 (which will go on the live systems) from MSDN and will try that as well, maybe it's something with the dev edition?

Thanks

|||

There shouldn't be any difference between Developer and Enterprise regarding encryption. Could you also specify how you created the certificate and the symmetric key?

Thanks
Laurentiu

|||

Hi there,

I create the cert and key using this (using Triple Des since the standby machine is Windows 2000 and doesnt do AES)

create certificate cert_sk_td3 with subject = 'Certificate'

create symmetric key sk_td3 with algorithm = triple_des encryption by certificate cert_sk_td3;

Let me know if you need any more info

|||

In your scenario, which is the machine on which the decryption fails after restore? Is it the 2000 machine or the 2003 one? (I'm not sure what you mean by the standby machine)

Thanks
Laurentiu

|||

Hi Laurentiu,

In the interim I have upgradede both SQL2005s from Dev Edition to Enterprise Edition (from the MSDN Subscription downloads)

I am going from a Server2003 (SP1 and hotfixes) to a Server2000(SP4 and all hotfixes). It is on the 2000 Server that I cant decrypt (I am using TripleDES to test since AES isnt supported on 2000).

Is there any trace or debugging that I can enable that might give some more info?

Wednesday, March 21, 2012

Encrypting Data using SQL Server 2005

If you encrypt some data using a symmetric key with a password. It appears
that the database master key is not used at all to encrypt the data. Is thi
s
true? Also it appears you can backup the database and move it to another
server, and retrain the password for the symmetric key from the old server.
Meaning that after you restore the database to a new server you can use the
symmetric key password from the old server to open the symmetric key in the
database on the new server and decrypt the data.
My basic question if you create a symmetric key with a password, and encrypt
data with that symmetric key, then is there any reason you would need to
create a master key for the database?
--
If you are looking for SQL Server examples check out my Website at
http://www.sqlserverexamples.comHello Greg,
GL> If you encrypt some data using a symmetric key with a password. It
GL> appears that the database master key is not used at all to encrypt
GL> the data. Is this true?
Strictly speaking, yes. But recall that the symmetric's key decryption devic
e
is stored encrypted by the Service Master (SMK) in its absence. So while
the SMK isn't part the encryption vector per se, you aren't using going to
be able to decrypt encrypted data without the correct SMK if you don't use
a Database Master Key (DBMK).
GL> Also it appears you can backup the
GL> database and move it to another server, and retrain the password for
GL> the symmetric key from the old server. Meaning that after you
GL> restore the database to a new server you can use the symmetric key
GL> password from the old server to open the symmetric key in the
GL> database on the new server and decrypt the data.
Yes, you wouldn't want to unrecoverable data, but you will still need to
regenerate off that instance's SMK.
GL> My basic question if you create a symmetric key with a password, and
GL> encrypt data with that symmetric key, then is there any reason you
GL> would need to create a master key for the database?
Consider the following example. Although both DBs have the same keys, they
really don't because the keys have different GUIDs. And if you look at the
encrypted data carefully enough, its pretty obvious that the key guid is
part of the encrypted data.
use master
go
create database enc1
create database enc2
go
use enc2
create table dbo.secrets(data varbinary(255))
go
use enc1
create symmetric key signingKey with algorithm = triple_des encryption by
password = 'theKey'
open symmetric key signingkey decryption by password = 'theKey'
create symmetric key enc_Key with algorithm = triple_des encryption by symme
tric
key signingKey
close symmetric key signingKey
go
open symmetric key signingkey decryption by password = 'theKey'
open symmetric key enc_key decryption by symmetric key signingKey
close symmetric key signingKey
select name,key_guid,algorithm_desc from sys.symmetric_keys
insert into enc2.dbo.secrets values (encryptByKey(key_guid('enc_key'),'beSur
eToDrinkYourOvaltine'))
select key_guid('enc_key'),data,cast(decryptByK
ey(data) as varchar(255))
from enc2.dbo.secrets
close symmetric key enc_key
go
use enc2
create symmetric key signingKey with algorithm = triple_des encryption by
password = 'theKey'
open symmetric key signingkey decryption by password = 'theKey'
create symmetric key enc_Key with algorithm = triple_des encryption by symme
tric
key signingKey
close symmetric key signingKey
go
open symmetric key signingkey decryption by password = 'theKey'
open symmetric key enc_key decryption by symmetric key signingKey
close symmetric key signingKey
select name,key_guid,algorithm_desc from sys.symmetric_keys
select key_guid('enc_key'),data,cast(decryptByK
ey(data) as varchar(255))
from enc2.dbo.secrets
close symmetric key enc_key
go|||So if I understand you correctly the encrypted data can not be decrypted
without the appropriate Service Master Key, even if you have the correct
symmetric key password. Meaning you can't move a dataase backup of the
encrypted data from one server to another and decrypt it using the only the
symmetric key. Is this true?
I'm guessing I don't have this right because I can copy a database backup
from one server to another and still decrypt the encrypted data. Here is a
script I tested it with:
-- on server 1 do this:
use master
go
if exists (select * from master.sys.databases where name = 'enc1')
drop database enc1
create database enc1
go
use enc1
create table dbo.secrets(data varbinary(255))
go
create symmetric key signingKey with algorithm = triple_des encryption by
password = 'theKey'
open symmetric key signingkey decryption by password = 'theKey'
create symmetric key enc_Key with algorithm = triple_des encryption by
symmetric
key signingKey
close symmetric key signingKey
go
open symmetric key signingkey decryption by password = 'theKey'
open symmetric key enc_key decryption by symmetric key signingKey
close symmetric key signingKey
select name,key_guid,algorithm_desc from sys.symmetric_keys
insert into dbo.secrets values
(encryptByKey(key_guid('enc_key'),'beSur
eToDrinkYourOvaltine'))
select key_guid('enc_key'),data,cast(decryptByK
ey(data) as varchar(255))
from dbo.secrets
close symmetric key enc_key
backup database enc1 to disk = 'C:\temp\enc1.bak'
-- copy C:\temp\enc1.bak from server 1 to server 2
-- server 2 do this:
use master
go
if exists (select * from master.sys.databases where name = 'enc1')
drop database enc1
go
restore database enc1 from disk='c:\temp\enc1.bak'
go
use enc1
go
open symmetric key signingkey decryption by password = 'theKey'
open symmetric key enc_key decryption by symmetric key signingKey
close symmetric key signingKey
select name,key_guid,algorithm_desc from sys.symmetric_keys
select key_guid('enc_key'),data,cast(decryptByK
ey(data) as varchar(255))
from dbo.secrets
close symmetric key enc_key
Now so I'm wondering why I can move a database that has encrypted data from
one server to another by just doing a database backup and restore and then
issuing the open symmetric key using the password from the target server,
like so.
If you are looking for SQL Server examples check out my Website at
http://ww.sqlserverexamples.com
"Kent Tegels" wrote:

> Hello Greg,
> GL> If you encrypt some data using a symmetric key with a password. It
> GL> appears that the database master key is not used at all to encrypt
> GL> the data. Is this true?
> Strictly speaking, yes. But recall that the symmetric's key decryption dev
ice
> is stored encrypted by the Service Master (SMK) in its absence. So while
> the SMK isn't part the encryption vector per se, you aren't using going to
> be able to decrypt encrypted data without the correct SMK if you don't use
> a Database Master Key (DBMK).
> GL> Also it appears you can backup the
> GL> database and move it to another server, and retrain the password for
> GL> the symmetric key from the old server. Meaning that after you
> GL> restore the database to a new server you can use the symmetric key
> GL> password from the old server to open the symmetric key in the
> GL> database on the new server and decrypt the data.
> Yes, you wouldn't want to unrecoverable data, but you will still need to
> regenerate off that instance's SMK.
> GL> My basic question if you create a symmetric key with a password, and
> GL> encrypt data with that symmetric key, then is there any reason you
> GL> would need to create a master key for the database?
> Consider the following example. Although both DBs have the same keys, they
> really don't because the keys have different GUIDs. And if you look at the
> encrypted data carefully enough, its pretty obvious that the key guid is
> part of the encrypted data.
> use master
> go
> create database enc1
> create database enc2
> go
> use enc2
> create table dbo.secrets(data varbinary(255))
> go
> use enc1
> create symmetric key signingKey with algorithm = triple_des encryption by
> password = 'theKey'
> open symmetric key signingkey decryption by password = 'theKey'
> create symmetric key enc_Key with algorithm = triple_des encryption by sym
metric
> key signingKey
> close symmetric key signingKey
> go
> open symmetric key signingkey decryption by password = 'theKey'
> open symmetric key enc_key decryption by symmetric key signingKey
> close symmetric key signingKey
> select name,key_guid,algorithm_desc from sys.symmetric_keys
> insert into enc2.dbo.secrets values (encryptByKey(key_guid('enc_key'),'beS
ureToDrinkYourOvaltine'))
> select key_guid('enc_key'),data,cast(decryptByK
ey(data) as varchar(255))
> from enc2.dbo.secrets
> close symmetric key enc_key
> go
> use enc2
> create symmetric key signingKey with algorithm = triple_des encryption by
> password = 'theKey'
> open symmetric key signingkey decryption by password = 'theKey'
> create symmetric key enc_Key with algorithm = triple_des encryption by sym
metric
> key signingKey
> close symmetric key signingKey
> go
> open symmetric key signingkey decryption by password = 'theKey'
> open symmetric key enc_key decryption by symmetric key signingKey
> close symmetric key signingKey
> select name,key_guid,algorithm_desc from sys.symmetric_keys
> select key_guid('enc_key'),data,cast(decryptByK
ey(data) as varchar(255))
> from enc2.dbo.secrets
> close symmetric key enc_key
> go
>
>|||Hello Greg,
GL> So if I understand you correctly the encrypted data can not be decrypted
without the appropriate Service Master Key, even if you have the correct sy
mmetric key password. Meaning you ca
n't move a dataase backup of the encrypted data from one server to another a
nd decrypt it using the only the symmetric key. Is this true?
It was certainly the understanding I had from reading BOL and the testing I
did. I couldn't get your backup example to work and wondered if there wasn't
maybe so vodoo getting done during
the restore process so a did a dettach/attach insead (attachment #1.)
GL> Now so I'm wondering why I can move a database that has encrypted data f
rom one server to another by just doing a database backup and restore and th
en issuing the open symmetric key=2
0using the password from the target server, like so.
There's a note in BOL that gave me a different understanding of this:
"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. Be
cause of this, keys that are created20with a strong encryption algorithm, su
ch as AES, are themselves secured by a weaker algorithm."
This was added in December 2006. So when you sign a symmetric key with a pas
sword, it looks like it just internalizes the key under 3DES and makes it tr
ansportable. That sucks because n
ow its way easier to brute force attack that key. UGH!
Even more annoyingly, the same behavior seems to apply to symmetic keys at a
re encrypted by asymmetric keys where that key is encrypted by a password. S
ee attachment #2.|||Can't seem to see those attachments. But I think from your reply you
confirmed what I was saying.
Now what I wonder is why are you encrypting a symmetric key with another
symmetric key. What exactly does this accomplish?|||"Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
news:1DB4AD7C-69C7-4BD8-B48F-8166CD35DABE@.microsoft.com...
> Can't seem to see those attachments. But I think from your reply you
> confirmed what I was saying.
> Now what I wonder is why are you encrypting a symmetric key with another
> symmetric key. What exactly does this accomplish?
It provides layered protection for your keys. You can theoretically replace
any key in the mid- to upper-levels of your key hierarchy and only need to
decrypt and re-encrypt the keys it protects, until you reach the
bottom-level keys. The result is that you can theoretically change
intermediate and top-level keys on very often with very little effect on
your server or processes, and you can change bottom-level keys much less
often.
Of course if you change the bottom-level keys you have to decrypt and
re-encrypt all your protected data, which can be a resource-intensive
operation.sql

Monday, March 19, 2012

Encrypting and Decrypting Data

CREATE TABLE TabEncr (
id int identity (1,1),
NonEncrField varchar(30),
EncrField varchar(30)
)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OurSecretPassword'
CREATE CERTIFICATE my_cert with subject = 'Some Certificate'
CREATE SYMMETRIC KEY my_key with algorithm = triple_des encryption by certificate my_cert

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
INSERT INTO TabEncr (NonEncrField,EncrField)
VALUES ('Some Plain Value',encryptbykey(key_guid('my_key'),'Some Plain Value'))
CLOSE SYMMETRIC KEY my_key

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
SELECT NonEncrField,CONVERT(VARCHAR(30),DecryptByKey(EncrField))
FROM dbo.TabEncr
CLOSE SYMMETRIC KEY my_key

What is the problem with this code. It works fine , inserting the value encrypted but when i try to decrypt ,it returns a null value. What is missing. I also tried with symmetric key encryption with asymmetric key. Result is same, returns NULL value. I am using SQL 2005

Happy Coding...

The EncrField is of a wrong type; it should be varbinary, because the result of encryption is a varbinary value. If you replace the EncrField line with the following, then your script will work as expected:

EncrField varbinary(60)

Thanks
Laurentiu

|||

Hi Laurentiu Cristofor
Thanks for help. It works f?ne. But while trying your solution i also tried my original code and it worked fine. How can it be, i made some simple changes on code to see am i wrong but believe its working. Now there is big question, 1 week before it didn't work. But now its fine. Interesting and confusing.

(Modified; i tried again but it didn't worked. I think i miss somethink but what.)

|||

Maybe you are not recreating the table? The encryption code was correct - the table creation code was incorrect.

Thanks
Laurentiu

|||

how about batch update of data?

Edit:

Follow up on above@.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1365306&SiteID=1&mode=1 with the script

|||

What do you mean by batch update? Or do you mean batch insert?

Thanks
Laurentiu

|||what if i have a data (column) that i want to batch encrypt ?|||

You could do something similar to what you would do if you wanted to update all values of a non-encrypted column.

For example, you can issue an update statement like:

update t set c = encryptbykey(key_guid('skey'), c)

This assumes that c is varbinary and can accommodate the output of the encryption.

Thanks
Laurentiu

|||

Hi,

I got a similar issue with encrypt and decrypt.

In my case,

...

create table ( column Password varbinay(128) )

...

create symmetric key with certificate

...

OPEN SYMMETRIC KEY Sym_Key_01

DECRYPTION BY CERTIFICATE Cert;

UPDATE mytable

SET Password = EncryptByKey(Key_GUID('Password_01'),'ok')

select CONVERT(nvarchar, DecryptByKey(Password)) AS "Decrypted Password" from mytable

here, I didn't get the value 'ok' but a another wierd word (like a chinese word).

does someone know the reason?

Thanks,

Jone

Encrypting and Decrypting Data

CREATE TABLE TabEncr (
id int identity (1,1),
NonEncrField varchar(30),
EncrField varchar(30)
)

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
SELECT NonEncrField,CONVERT(VARCHAR(30),DecryptByKey(EncrField))
FROM dbo.TabEncr
CLOSE SYMMETRIC KEY my_key

Happy Coding...

EncrField varbinary(60)

Thanks
Laurentiu

|||

(Modified; i tried again but it didn't worked. I think i miss somethink but what.)

|||

Maybe you are not recreating the table? The encryption code was correct - the table creation code was incorrect.

Thanks
Laurentiu

|||

how about batch update of data?

Edit:

Follow up on above@.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1365306&SiteID=1&mode=1 with the script

|||

What do you mean by batch update? Or do you mean batch insert?

Thanks
Laurentiu

|||what if i have a data (column) that i want to batch encrypt ?|||

You could do something similar to what you would do if you wanted to update all values of a non-encrypted column.

For example, you can issue an update statement like:

update t set c = encryptbykey(key_guid('skey'), c)

This assumes that c is varbinary and can accommodate the output of the encryption.

Thanks
Laurentiu

|||

Hi,

I got a similar issue with encrypt and decrypt.

In my case,

...

create table ( column Password varbinay(128) )

...

create symmetric key with certificate

...

OPEN SYMMETRIC KEY Sym_Key_01

DECRYPTION BY CERTIFICATE Cert;

UPDATE mytable

SET Password = EncryptByKey(Key_GUID('Password_01'),'ok')

select CONVERT(nvarchar, DecryptByKey(Password)) AS "Decrypted Password" from mytable

here, I didn't get the value 'ok' but a another wierd word (like a chinese word).

does someone know the reason?

Thanks,

Jone

Encrypting and Decrypting Data

CREATE TABLE TabEncr (
id int identity (1,1),
NonEncrField varchar(30),
EncrField varchar(30)
)

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
SELECT NonEncrField,CONVERT(VARCHAR(30),DecryptByKey(EncrField))
FROM dbo.TabEncr
CLOSE SYMMETRIC KEY my_key

Happy Coding...

EncrField varbinary(60)

Thanks
Laurentiu

|||

(Modified; i tried again but it didn't worked. I think i miss somethink but what.)

|||

Maybe you are not recreating the table? The encryption code was correct - the table creation code was incorrect.

Thanks
Laurentiu

|||

how about batch update of data?

Edit:

Follow up on above@.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1365306&SiteID=1&mode=1 with the script

|||

What do you mean by batch update? Or do you mean batch insert?

Thanks
Laurentiu

|||what if i have a data (column) that i want to batch encrypt ?|||

You could do something similar to what you would do if you wanted to update all values of a non-encrypted column.

For example, you can issue an update statement like:

update t set c = encryptbykey(key_guid('skey'), c)

This assumes that c is varbinary and can accommodate the output of the encryption.

Thanks
Laurentiu

|||

Hi,

I got a similar issue with encrypt and decrypt.

In my case,

...

create table ( column Password varbinay(128) )

...

create symmetric key with certificate

...

OPEN SYMMETRIC KEY Sym_Key_01

DECRYPTION BY CERTIFICATE Cert;

UPDATE mytable

SET Password = EncryptByKey(Key_GUID('Password_01'),'ok')

select CONVERT(nvarchar, DecryptByKey(Password)) AS "Decrypted Password" from mytable

here, I didn't get the value 'ok' but a another wierd word (like a chinese word).

does someone know the reason?

Thanks,

Jone

Sunday, March 11, 2012

Encrypted Database transfer problem

Hi,

I have encrypted some columns of a table in a database. Following is the method which i applied for encryption.

I created a master key with a password and it is also encrypted by service master key. Now i created a certificate without password, so it is only encrypted by master key of the database. Now i created a symmetric key encrypted by the above certificate. The data is encrypted by this symmetric key.

To decrypt data i use DecryptByKeyAutoCert.

On my server this encryption & decryption is working perfectly.

But when i take this database to another server, it is not working.

What is the solution for this, should i drop service master key to encrypt master key or is there any soln.

Thank you.

Pls give me soln. i am worried abt it.

Gaurav

See last paragraph in: http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx.

After you restore your database, you need to readd the service master key encryption of the database master key.

Thanks
Laurentiu

|||

I have attached a database created with master key, certificate and symmetric key.

Now i have attached this database to another server but no master key, certificate or symmetric key is present there in the new server database.

Do i need to backup master key and certificate and then restore them to the new server database? Then what abt symmetric key. there is no option for symmetric key to be backedup.

what is the soln for symmetric key? and taking backup of master key and certificate, then restoring it is the only soln?

Thanks

Gaurav

|||

You don't need to do anything else when moving a database from one server to another, other than what I mentioned before: restore the SMK encryption of the DbMK, if such an encryption existed on the source server.

How did you verify whether the keys are present in the database after you reattached it? Did you look in the catalogs (sys.symmetric_keys, sys.certificates)?

Thanks
Laurentiu

Friday, February 24, 2012

Enable Service Broker for DB Mail on SQL 2005 Cluster

I'm having problems enabling service broker for DB Mail on a SQL 2005 cluster, when I try to execute this sql it just hangs. Any ideas?

USE master ;
GO

ALTER DATABASE AdventureWorks SET ENABLE_BROKER ;
GO

Make sure there are no other people in the database before you run that command. It requires exclusive access to the database to change the mode.

|||

See here: http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx

This statement completes imeadetly, but the problem is that is requires exclusive access to the database! Any connection that is using this database has a shared lock on it, even when idle, thus blocking the ALTER DATABASE from completing.

There is an easy trick for fix the problem: use the termination options of ALTER DATABASE:
ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT

- ROLLBACK
will close all existing sessions, rolling back any pending transaction.
- NO_WAIT will terminate the ALTER DATABASE statement with error if other connections are blocking it.

HTH,
~ Remus

|||

This did help me get past this part. Now I'm receiving this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (B4201B09-6358-4C65-8457-D6F50004A4D9) does not match the one in sys.databases (2527A339-BFB3-45C6-978D-412C4FA557CB).

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This got me to the next step. Now I'm getting this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This error means you have performed some operations on the msdb database without respecting proper procedures. Something like replacing the mdf and ldf files with MSDB file from another instance. Always follow the proper procedures to move/copy databases, always use attach/detach or backup/restore.

You cannot enable the existing broker in MSDB, you have to create a new one using ALTER DATABASE ... SET NEW_BROKER.

HTH,
~ Remus

|||This worked thanks. Do you think the the mismatched GUID will cause any other problems?|||

You shouldn't have any problem from now on. You have now created new GUIDs, and the one in the database matches the one in the sys.databases.

HTH,
~ Remus

|||Thanks Remus.|||

This as happened to me when using a disaster recovery procedure

- reinstall sql_engine with start/wait setup.exe ...

and restoring master from a previous backup

So it seems that when using "proper procedures". the guid is not recovered correctly.

Xavier

Enable Service Broker for DB Mail on SQL 2005 Cluster

I'm having problems enabling service broker for DB Mail on a SQL 2005 cluster, when I try to execute this sql it just hangs. Any ideas?

USE master ;
GO

ALTER DATABASE AdventureWorks SET ENABLE_BROKER ;
GO

Make sure there are no other people in the database before you run that command. It requires exclusive access to the database to change the mode.

|||

See here: http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx

There is an easy trick for fix the problem: use the termination options of ALTER DATABASE:
ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT

- ROLLBACK
will close all existing sessions, rolling back any pending transaction.
- NO_WAIT will terminate the ALTER DATABASE statement with error if other connections are blocking it.

HTH,
~ Remus

|||

This did help me get past this part. Now I'm receiving this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This got me to the next step. Now I'm getting this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

You cannot enable the existing broker in MSDB, you have to create a new one using ALTER DATABASE ... SET NEW_BROKER.

HTH,
~ Remus

|||This worked thanks. Do you think the the mismatched GUID will cause any other problems?|||

You shouldn't have any problem from now on. You have now created new GUIDs, and the one in the database matches the one in the sys.databases.

HTH,
~ Remus

|||Thanks Remus.|||

This as happened to me when using a disaster recovery procedure

- reinstall sql_engine with start/wait setup.exe ...

and restoring master from a previous backup

So it seems that when using "proper procedures". the guid is not recovered correctly.

Xavier

Enable Service Broker for DB Mail on SQL 2005 Cluster

I'm having problems enabling service broker for DB Mail on a SQL 2005 cluster, when I try to execute this sql it just hangs. Any ideas?

USE master ;
GO

ALTER DATABASE AdventureWorks SET ENABLE_BROKER ;
GO

Make sure there are no other people in the database before you run that command. It requires exclusive access to the database to change the mode.

|||

See here: http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx

There is an easy trick for fix the problem: use the termination options of ALTER DATABASE:
ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT

- ROLLBACK
will close all existing sessions, rolling back any pending transaction.
- NO_WAIT will terminate the ALTER DATABASE statement with error if other connections are blocking it.

HTH,
~ Remus

|||

This did help me get past this part. Now I'm receiving this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This got me to the next step. Now I'm getting this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

You cannot enable the existing broker in MSDB, you have to create a new one using ALTER DATABASE ... SET NEW_BROKER.

HTH,
~ Remus

|||This worked thanks. Do you think the the mismatched GUID will cause any other problems?|||

You shouldn't have any problem from now on. You have now created new GUIDs, and the one in the database matches the one in the sys.databases.

HTH,
~ Remus

|||Thanks Remus.|||

This as happened to me when using a disaster recovery procedure

- reinstall sql_engine with start/wait setup.exe ...

and restoring master from a previous backup

So it seems that when using "proper procedures". the guid is not recovered correctly.

Xavier

Enable Service Broker for DB Mail on SQL 2005 Cluster

I'm having problems enabling service broker for DB Mail on a SQL 2005 cluster, when I try to execute this sql it just hangs. Any ideas?

USE master ;
GO

ALTER DATABASE AdventureWorks SET ENABLE_BROKER ;
GO

Make sure there are no other people in the database before you run that command. It requires exclusive access to the database to change the mode.

|||

See here: http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx

There is an easy trick for fix the problem: use the termination options of ALTER DATABASE:
ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT

- ROLLBACK
will close all existing sessions, rolling back any pending transaction.
- NO_WAIT will terminate the ALTER DATABASE statement with error if other connections are blocking it.

HTH,
~ Remus

|||

This did help me get past this part. Now I'm receiving this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This got me to the next step. Now I'm getting this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

You cannot enable the existing broker in MSDB, you have to create a new one using ALTER DATABASE ... SET NEW_BROKER.

HTH,
~ Remus

|||This worked thanks. Do you think the the mismatched GUID will cause any other problems?|||

You shouldn't have any problem from now on. You have now created new GUIDs, and the one in the database matches the one in the sys.databases.

HTH,
~ Remus

|||Thanks Remus.|||

This as happened to me when using a disaster recovery procedure

- reinstall sql_engine with start/wait setup.exe ...

and restoring master from a previous backup

So it seems that when using "proper procedures". the guid is not recovered correctly.

Xavier

Sunday, February 19, 2012

emtpy sysperfinfo table

Hello!
I am getting no records when querying sysperinfo table:
select * from master..sysperfinfo
This is production server running SQL2000 SP3 onWindows 2002 . I saw many
people asking the same question but didn't find a definite answer. As a
result, I do not see any of the SQL Server counters in the system monitor. I
am pretty much sure server was installed with performance counters.
Any advice would be greatly appreciated.
IgorCheck the following article as it very likely may apply to
the problems you are having - note the message that is
logged to the error log, that's one way to verify if it
applies in your case:
FIX: "Performance monitor shared memory setup failed: -1"
error message when you start SQL Server
http://support.microsoft.com/?id=812915
-Sue
On Tue, 23 Nov 2004 17:05:05 -0800, "Igor"
<imarchenko@.bowneglobal.com> wrote:
>Hello!
>I am getting no records when querying sysperinfo table:
>select * from master..sysperfinfo
> This is production server running SQL2000 SP3 onWindows 2002 . I saw many
>people asking the same question but didn't find a definite answer. As a
>result, I do not see any of the SQL Server counters in the system monitor. I
>am pretty much sure server was installed with performance counters.
> Any advice would be greatly appreciated.
>
>Igor
>|||Hello Sue,
I do not get error you mentioned in SQL Server error log. I also do not
think this case applies to my problem.
Thanks,
Igor
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:6mp7q0pfkrjmp7mfgb8gdn3k4t7lka5ti1@.4ax.com...
> Check the following article as it very likely may apply to
> the problems you are having - note the message that is
> logged to the error log, that's one way to verify if it
> applies in your case:
> FIX: "Performance monitor shared memory setup failed: -1"
> error message when you start SQL Server
> http://support.microsoft.com/?id=812915
> -Sue
> On Tue, 23 Nov 2004 17:05:05 -0800, "Igor"
> <imarchenko@.bowneglobal.com> wrote:
> >Hello!
> >
> >I am getting no records when querying sysperinfo table:
> >
> >select * from master..sysperfinfo
> >
> > This is production server running SQL2000 SP3 onWindows 2002 . I saw
many
> >people asking the same question but didn't find a definite answer. As a
> >result, I do not see any of the SQL Server counters in the system
monitor. I
> >am pretty much sure server was installed with performance counters.
> > Any advice would be greatly appreciated.
> >
> >
> >Igor
> >
>|||Your symptoms do point to the issue in the KB Sue mentioned. Usually when
you don't get the counters (which come from syserfinfo) and your
lastwaittype column in sysprocesses is always Misc you blew away the
counters. The short term solution is to stop anything that is monitoring
the counters and restart SQL Server.
--
Andrew J. Kelly SQL MVP
"Igor" <imarchenko@.bowneglobal.com> wrote in message
news:elohshc0EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Hello Sue,
> I do not get error you mentioned in SQL Server error log. I also do not
> think this case applies to my problem.
> Thanks,
> Igor
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:6mp7q0pfkrjmp7mfgb8gdn3k4t7lka5ti1@.4ax.com...
>> Check the following article as it very likely may apply to
>> the problems you are having - note the message that is
>> logged to the error log, that's one way to verify if it
>> applies in your case:
>> FIX: "Performance monitor shared memory setup failed: -1"
>> error message when you start SQL Server
>> http://support.microsoft.com/?id=812915
>> -Sue
>> On Tue, 23 Nov 2004 17:05:05 -0800, "Igor"
>> <imarchenko@.bowneglobal.com> wrote:
>> >Hello!
>> >
>> >I am getting no records when querying sysperinfo table:
>> >
>> >select * from master..sysperfinfo
>> >
>> > This is production server running SQL2000 SP3 onWindows 2002 . I saw
> many
>> >people asking the same question but didn't find a definite answer. As a
>> >result, I do not see any of the SQL Server counters in the system
> monitor. I
>> >am pretty much sure server was installed with performance counters.
>> > Any advice would be greatly appreciated.
>> >
>> >
>> >Igor
>> >
>

Thursday, March 22, 2012

Encryption and recovery on standby machine

Encryption and recovery on standby machine

Wednesday, March 21, 2012

Encrypting Data using SQL Server 2005

Monday, March 19, 2012

Encrypting and Decrypting Data

Encrypting and Decrypting Data

Encrypting and Decrypting Data

Sunday, March 11, 2012

Encrypted Database transfer problem

Friday, February 24, 2012

Enable Service Broker for DB Mail on SQL 2005 Cluster

Enable Service Broker for DB Mail on SQL 2005 Cluster

Enable Service Broker for DB Mail on SQL 2005 Cluster

Enable Service Broker for DB Mail on SQL 2005 Cluster

Sunday, February 19, 2012

emtpy sysperfinfo table

Empty Column

Blog Archive

About Me