Problem with encryption

  • 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?

  • 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

  • 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?

  • 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

  • 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

  • I tryed to execute step 1 and reseive this message:

    Incorrect sysntax neas MASTER

  • Are you new to SQL Server.

    Rajesh Kasturi

  • 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

  • Database test exist

  • really I did not understand what is your problem.

    Rajesh Kasturi

  • Please run the following and post the results:

    SELECT @@Version

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi,

    I created Master Key,Certificate and Symmetric Key.

    Both Encrypt and decrypt works fine.But the issue is though the View definition on symmetric key is granted only to me,other users also are able to decrypt the data by opening the symmetric key .

    How to restrict this. ?

    PS : Decryption access is given only to me.But even then , all other users are able to decrypt the data.

    How to restrict this and what would be the cause for this ?

    GRANT VIEW DEFINITION ON SYMMETRIC KEY::symkey to [domain\abc]

    GRANT VIEW DEFINITION ON CERTIFICATE::Certific to [domain\abc]

    GRANT CONTROL ON CERTIFICATE::Certific to [domain\abc]

    Any help will be appreciated.

    Thanks

    Nisha.V

  • They're probably database owner or sysadmin.

    New questions in new thread please.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Yes you are right.Thanks for your reply

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply