MSSQL 2005 Encryption

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!
 
I am looking into it and it seems it should work, but I am not 100% sure anyone has ever used it either.
 
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:


Msg 15466, Level 16, State 2, Line 4
An error occurred during decryption.

Msg 15581, Level 16, State 1, Line 2
Please create a master key in the database or open the master key in the session before performing this operation.

Msg 15151, Level 16, State 1, Line 2
Cannot find the certificate 'CertName', because it does not exist or you do not have permission.
Click to expand...
 
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!
 
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?
 
I am still reading up on this aspect both in general and then as applied to shared hosting.
 
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
 
You must log in or register to reply here.
Back
Top