MSSQL 2005 Encryption

Discussion in 'Database Support' started by dman, Oct 31, 2012.

  1. dman

    dman Perch

    Hello,

    I'm wondering if it is possible to symmetric encryption on MSSQL11 at Jodohost. I tried to create a new key and certificate via MS SMS but I get errors. If this can be done it would be very helpful. Is this something I can do in my Schema or with the help of Jodo's techs? Thanks!
  2. Stephen

    Stephen US Operations Staff Member

    I am looking into it and it seems it should work, but I am not 100% sure anyone has ever used it either.
  3. dman

    dman Perch

    Hey Stephen,

    Thanks for the reply and any help...

    When I try to create a master key, Certificate and Symmetric Key with the SQL below (removed real info).....

    Code:
    USE [user_DB];
    GO
    --If there is no master key, create one now.
    IF NOT EXISTS
        (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
        CREATE MASTER KEY ENCRYPTION BY
        PASSWORD = 'random password'
    GO
     
    CREATE CERTIFICATE CertName
      WITH SUBJECT = 'ExampleSubject';
    GO
     
    CREATE SYMMETRIC KEY Example_Key11
        WITH ALGORITHM = AES_256
        ENCRYPTION BY CERTIFICATE CertName;
    GO
    
    ... I get the following errors:


  4. dman

    dman Perch

    Hey Stephen,

    I think the issue is that I don't have access to the MASTER KEY. I can't create or OPEN the Master Key, so I can't create a certificate. If I had the permissions I think I could create a Master Key specific to my DB. Is there some way to create a Master Key for a specific database instead of the server, maybe under my Schema?

    There are alternatives like using EncryptByPassPhrase or encryption within the application but these have some weaknesses and may cause issues down the road. Any other ideas how I can encrypt data in a column at JodoHost? Thanks!
  5. dman

    dman Perch

    Another update... I think I can create a key using use EncryptByKey with a password but just not with a a certificate. This may work but is less secure. Any suggestions on this?
  6. Stephen

    Stephen US Operations Staff Member

    I am still reading up on this aspect both in general and then as applied to shared hosting.
  7. dman

    dman Perch

    Thanks for the reply. I'm doing the same... reading up on how to use and best practices. Using the MS SQL encryption is a new thing for me as I've generally used encryption from within my apps but securing it in the DB seems more scalable and future proof if the site programming changes. The Keys with certificate encryption seems stronger but a password key appears to work. Let me know your thoughts when you are clear how this can best work with shared hosting.

    Below is one link with decent information:

    http://msdn.microsoft.com/en-us/library/aa337557(v=sql.90).aspx

Share This Page

JodoHost - 26,000 hosting end-users in 100 countries
Plesk Web Hosting
VPS Hosting
H-Sphere Web Hosting
Other Services