Thursday, March 22, 2012
encryption
CREATE TABLE [dbo].[WebSecurity](
[WebSecurityID] [int] IDENTITY(1,1) NOT NULL,
[webIdentity] [varchar](50) NOT NULL,
[webPassword_encrypted] [varbinary](256) NULL)
delete from websecurity
insert into WebSecurity (WebIdentity,webpassword_encrypted)
values('vanwagenen\WebServer',EncryptByP
assPhrase ('myphrase','mypassword'))
select webidentity, decryptByPassPhrase
('myphrase',webpassword_encrypted)from webSecurity
--
Arne Garvander
Certified Geek
Professional Data DudeHello Arne,
It did, you just didn't get the answer you expected. Try:
select webidentity, cast(decryptByPassPhrase ('myphrase',webpassword_encrypt
ed)
as varchar(255)) from webSecurity
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
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
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)
)
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
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)
)
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
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
Wednesday, February 15, 2012
empty a table
i was just wondering, is there anyway to empty a table...like, in mysql u can go EMPTY TABLE
and it resets the identity stuff, so your id's start at zero again...?
Thanks, JustinYou can use one of the following statements:
DELETE FROM YourTable
TRUNCATE TABLE YourTable|||DELETE will not reset the Identity value.
TRUNCATE will.|||...like, in mysql u can go EMPTY TABLE no you can't
but thankfully, TRUNCATE TABLE works in mysql too
:)|||no you can't
but thankfully, TRUNCATE TABLE works in mysql too
:)
hi, well, havent used mysql in a little while..
maybe it was known as empty table in 3.x and 4.x versions of mysql...if not, then im not sure where i got it from...or maybe that just what phpmyadmin called it.
but thats for the replies,
Cheers, Justin