Problem with encryption

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Problem with encryption

11 posts, Page 1 of 2

1 2 »»

Problem with encryption

Rate Topic

Display Mode

Topic Options

Author

Message

maykabog

Posted Wednesday, September 17, 2008 11:07 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, April 06, 2010 2:32 AM
Points: 6, Visits: 21

Hi,
I can't execute any of encryption commands.
For example,when I write :
CREATE CERTIFICATE ElmerEncryptionCertificate
WITH
SUBJECT = 'Elmer Certificate',
EXPIRY_DATE = '01/01/2006 '

I receice :
Incorrect syntax near 'CERTIFICATE'.

I have installed SP2.
Can anyone help me pls?

Post #571477

Rajesh kasturi

Posted Wednesday, September 17, 2008 11:16 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 12:26 PM
Points: 185, Visits: 725

You are giving expiry date '01/01/2006' Execute as below it will be ok

CREATE CERTIFICATE ElmerEncryptionCertificate
WITH
SUBJECT = 'Elmer Certificate',
EXPIRY_DATE = '01/01/2009'

Regards
Rajesh Kasturi

Rajesh Kasturi

Post #571481

maykabog

Posted Wednesday, September 17, 2008 11:51 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, April 06, 2010 2:32 AM
Points: 6, Visits: 21

Hi,thanks for your replay,but it didn't help me.
I think that there is a problem with some settings.
Maybe I have to install some service pack?Or to do something in operating system?

Post #571492

Rajesh kasturi

Posted Wednesday, September 17, 2008 11:59 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 12:26 PM
Points: 185, Visits: 725

I do not think you need to do some thing with sp or operating system.

Please tell me little bit more what is the exact problem you are facing.

Rajesh Kasturi

Post #571494

Rajesh kasturi

Posted Thursday, September 18, 2008 12:12 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 12:26 PM
Points: 185, Visits: 725

Please execute the below script step by step and let me know where the scrip is getting fail.

--Step 1: Create database masterkey for Test
USE Test
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Raj3sH'

--Step 2: Create database certificate
CREATE CERTIFICATE Certificate1 WITH SUBJECT = 'Test'

--Step 3: Create a symetric key encrypted by the certificate
CREATE SYMMETRIC KEY TestKey09 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE Certificate1;
GO
--Step 4: Create a Column in which to store the encrypted data
CREATE TABLE TEST (ID VARCHAR(80), EncrypIDN varbinary(128))
go
ALTER TABLE TEST ADD EncrypIDN varbinary(128)

--Step 5: Open the symetric key with which to encrypt the data
OPEN SYMMETRIC KEY TestKey09 DECRYPTION BY CERTIFICATE Certificate1;

--Step 6
DECLARE @I INT
SET @I = 100

WHILE @I <>0
BEGIN
INSERT INTO TEST(ID) VALUES(@I)
SET @I = @I -1
END
GO
UPDATE TEST SET EncrypIDN = EncryptByKey(Key_GUID('TestKey09'),ID)

--Step 7: Open the symetric key with which to decrypt the data
OPEN SYMMETRIC KEY TestKey09 DECRYPTION BY CERTIFICATE Certificate1;

--Step 8: Compare the data
SELECT ID, EncrypIDN, CONVERT(varchar,DECRYPTBYKEY(EncrypIDN)) FROM TEST

Regards
Rajesh Kasturi

Rajesh Kasturi

Post #571500

maykabog

Posted Thursday, September 18, 2008 12:25 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, April 06, 2010 2:32 AM
Points: 6, Visits: 21

I tryed to execute step 1 and reseive this message:
Incorrect sysntax neas MASTER

Post #571506

Rajesh kasturi

Posted Thursday, September 18, 2008 12:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 12:26 PM
Points: 185, Visits: 725

Are you new to SQL Server.

Rajesh Kasturi

Post #571511

Rajesh kasturi

Posted Thursday, September 18, 2008 12:46 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 12:26 PM
Points: 185, Visits: 725

may be you do not have test database in your server.
use your database name in place of test.

or execute the below statement

CREATE DATABASE test

Rajesh Kasturi

Post #571512