Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»»

A Simple Approach to SQL Server 2005 Encryption Expand / Collapse
Author
Message
Posted Monday, March 23, 2009 9:00 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:16 PM
Points: 316, Visits: 812
I don't think so. A sysadmin is always going to be able to grant herself rights required to open the key.

So I think you'd need to use passwords instead of certificates. Nothing wrong with that, but you'll need to figure out how to securely manage the password (outside of SQL)...this was one of the big potential problem areas we were trying to avoid when we came up with the approach presented here.

Check notes at bottom of http://blogs.msdn.com/lcris/archive/2005/12/16/sql-server-2005-yet-another-column-encryption-demo-quot-clinic-quot.aspx, where Laurentiu Cristofor answers same question.



Post #682029
Posted Tuesday, March 24, 2009 2:14 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, August 16, 2014 5:48 AM
Points: 1,862, Visits: 3,602
Mike Good (3/23/2009)
I don't think so. A sysadmin is always going to be able to grant herself rights required to open the key.

So I think you'd need to use passwords instead of certificates. Nothing wrong with that, but you'll need to figure out how to securely manage the password (outside of SQL)...this was one of the big potential problem areas we were trying to avoid when we came up with the approach presented here.

Check notes at bottom of http://blogs.msdn.com/lcris/archive/2005/12/16/sql-server-2005-yet-another-column-encryption-demo-quot-clinic-quot.aspx, where Laurentiu Cristofor answers same question.


Thank you, I will look at that link. It seems to me that ultimately SOMEONE will need to hold the key to the treasure box, and if not the sysadmin, then who else? I wonder if there is a way to design a tamper-free auditing system around encryption, to ensure that people with admin rights are monitored. I think that may be the answer to this.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #682124
Posted Tuesday, March 24, 2009 6:18 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:16 PM
Points: 316, Visits: 812
I agree with all your statements. And for every post I've seen explaining how to do this in a manner that DBAs cannot decrypt, there is another post saying if you can't trust DBAs then maybe need new DBAs.

Before I started at my last job, they'd already implemented Idera SQL compliance manager to audit activity. As long as you don't put the same DBAs you're trying to monitor in charge of this tool too, I believe it's a very good solution. .



Post #682234
Posted Friday, April 10, 2009 4:44 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 2, 2011 10:19 AM
Points: 320, Visits: 160
Mike,
Thanks for your Wonderful article on SQL Server Encryption.

Please give me your inputs for the following scenario.
We have log shipping set up between server A and Server B. Server B is in read only mode.
There is no other encryption in any other databases on Server B.

I have performed the following steps on the primary server:

1) Created database master key with encryption using password.
2) Created a certificate
3) Created a symmetric key and encrypted it using certificate created in step 2( above)
4) Opened and Used the symmetric key to encrypt sensitive data in log shipping primary server.

After performing the above steps, I am able to decrypt the data in Primary Server (A) and receiving NULLs for the encrypted columns in the secondary Server(B).

Is there any solution available for the above problem.
I tried backing up the service master key from primary and restored it on secondary server. This works fine and able to decrypt the values on secondary server.

Will this affect anything else like linked servers or credentials on the secondary server?

Thanks,
Prem




Post #695102
Posted Sunday, April 12, 2009 6:22 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, August 16, 2014 5:48 AM
Points: 1,862, Visits: 3,602
Mike, thanks again for your insightful article, and thanks all for this discussion. I have learned a lot.

Lately, I have been able to research the subject of database encryption extensively and have come up with a plan for implementing encryption, suited to our environment. The plan is outlined in http://www.sqlservercentral.com/Forums/Topic694420-359-1.aspx, along with the links that helped me.

My plan incorporates many of the points made in Mike's article, especially anything to do with performance optimization. The main differences lie in the security aspect of the en(de)cryption procedure, primarily, the digital signing of the en(de)cryption code.

Digital signing of T-SQL code achieves 2 important objectives (http://msdn.microsoft.com/en-us/library/cc837966.aspx):
(1) Enables the code to run at an elevated security context, that of the digital-signing principal, relative to that of the user actually executing the code;
(2) Prohibits modifications to the signed code without first breaking the digital signature; if the latter is protected with a password, as recommended, only those with knowledge of that password can make any code changes.

In this plan:
- A code-signing certificate is created (with a complex password) and assigned to a user.
- A signature is added to each of the encryption procedures specifying the code-signing certificate.
- The code-signing user is granted permissions to control the data-access certificate, open the data-access symmetric key, and read/modify data in the tables that hold the encrypted data.

EXECUTE permissions on the encryption code are granted, as usual, to a database role containing the users permitted to execute the code through an application. These are the only permissions granted to the application role. However, because the code is digitally signed, it is executed in the security context of the code-signing certificate user, who has the power to access the encryption objects and database tables.

In that way, at least in our environment, encryption/decryption of the data can only occur through the signed code and not through ad-hoc queries issued in Management Studio. I should mention that in our organization we have created an in-house solution that enables users to gain authorization to an application through their Windows credentials, while at the same time disallowing these same users from executing the same SQL code through ad-hoc queries. The above scheme ensures that these users will ONLY be able to encrypt/decrypt data through sanctioned applications and not through SSMS (or other client tools).

The DBA team holds the keys to this process.

I must admit, I haven't yet fully tested all this, and would welcome any comments/suggestions.

The main inspiration for the above came from: http://www.thelastpickle.com/2008/01/15/how-i-think-sql-encryption-is-meant-to-be-used/


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #695424
Posted Monday, April 13, 2009 9:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:16 PM
Points: 316, Visits: 812
Prem, you are fine. I've had to do the same thing more than once. But it is unnerving, and since then I have made it a point to synchronize the service master keys as early as possible in situations like this, e.g. when initially building the standby server.


Post #695897
Posted Monday, April 13, 2009 9:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:16 PM
Points: 316, Visits: 812
Prem, while I have not experienced any problems with restoring the service master key on a standby server, I don't know for certain that it does not affect linked servers or other credentials.

Should be easy enough for you to verify though? I don't have an environment here to test this right now, could you please report what you find out?



Post #695903
Posted Monday, April 13, 2009 9:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:16 PM
Points: 316, Visits: 812
Marios, I like the way you think. This is going to take a little while to digest.

One question I have right away, though: was this the answer to the problem of preventing DBAs from decrypting the data? You didn't come right out and say this. And I have no experience with digiatally signing code, so not sure what a DBA could or could not do with this.



Post #695911
Posted Monday, April 13, 2009 10:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:16 PM
Points: 316, Visits: 812
doh! never mind, I did not think that last question through.


Post #695919
Posted Monday, April 13, 2009 10:04 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, August 16, 2014 5:48 AM
Points: 1,862, Visits: 3,602
Mike Good (4/13/2009)
Marios, I like the way you think. This is going to take a little while to digest.

One question I have right away, though: was this the answer to the problem of preventing DBAs from decrypting the data? You didn't come right out and say this. And I have no experience with digiatally signing code, so not sure what a DBA could or could not do with this.


Oh no, DBAs still have (for better or worse, and I am a DBA btw ) free reign over the encryption process. This scheme is more intended to block unauthorized access to the encryption objects (and data) by any non-sysadmin users, except through a sanctioned application.

As mentioned, in our organization we have a way of preventing users from issuing ad-hoc queries that mimick code calls from an application. For example, queries like "EXEC sprocName" cannot be issued by a non-sysadmin user from Management Studio, even if that same user has been granted EXEC permissions on that same sproc. That, along with code signing and granting the code-signing user SOLE access on the encryption objects, ensures that a non-sysadmin application user cannot issue T-SQL statements to open a symmetric key and encrypt/decrypt data ad hoc from Management Studio. I think that's a significant security improvement.

But I can't take credit for all this. Other links, including your article, helped me shape this scheme into how it stands at the moment.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #695920
« Prev Topic | Next Topic »

Add to briefcase «««34567»»»

Permissions Expand / Collapse