Post reply

Problem with encryption

  • September 17, 2008 at 11:07 pm

    #124724

    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?

  • September 17, 2008 at 11:16 pm

    #873389

    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

  • September 17, 2008 at 11:51 pm

    #873399

    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?

  • September 17, 2008 at 11:59 pm

    #873400

    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

  • September 18, 2008 at 12:12 am

    #873403

    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

  • September 18, 2008 at 12:25 am

    #873406

    I tryed to execute step 1 and reseive this message:

    Incorrect sysntax neas MASTER

  • September 18, 2008 at 12:45 am

    #873408

    Are you new to SQL Server.

    Rajesh Kasturi

  • September 18, 2008 at 12:46 am

    #873409

    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

  • September 18, 2008 at 2:36 am

    #873444

    Database test exist

  • September 18, 2008 at 4:15 am

    #873474

    really I did not understand what is your problem.

    Rajesh Kasturi

  • September 18, 2008 at 4:42 am

    #873486

    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

  • August 19, 2014 at 2:45 pm

    #1739393

    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

  • August 19, 2014 at 4:18 pm

    #1739417

    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

  • August 19, 2014 at 9:37 pm

    #1739458

    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