April 13, 2009 at 9:38 am
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.
April 13, 2009 at 9:43 am
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?
April 13, 2009 at 9:50 am
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.
April 13, 2009 at 10:04 am
doh! never mind, I did not think that last question through.
April 13, 2009 at 10:04 am
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.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 24, 2009 at 10:10 pm
Hi,
Can some one send me the article which was talking about in the below.
"Found this article useful as a newbie to encryption methods. A dept in our company has asked for an application to store sensitive information in a SQL2005 database, however they do not even want myself (DBA) and developers to see the encrypted data. From what I can gather one certificate and key can be created and used in a stored procedure to save and encrypt the data( developers would know) and another to decrypt the data. But I can see a way of how myself with the sysadmin access cannot easily decrypt the data as I will know the cert and keys. I wonder if anyone could clarify if my thinking is correct or if there is any option?"
Thanks in advance..
Nagendra
May 27, 2009 at 6:18 pm
nagendrareddy.kolli (5/24/2009)
Hi,Can some one send me the article which was talking about in the below.
"Found this article useful as a newbie to encryption methods. A dept in our company has asked for an application to store sensitive information in a SQL2005 database, however they do not even want myself (DBA) and developers to see the encrypted data. From what I can gather one certificate and key can be created and used in a stored procedure to save and encrypt the data( developers would know) and another to decrypt the data. But I can see a way of how myself with the sysadmin access cannot easily decrypt the data as I will know the cert and keys. I wonder if anyone could clarify if my thinking is correct or if there is any option?"
Thanks in advance..
Nagendra
I don't think it's possible using SQL Server's native encryption toolset. By design, the DBA as sysadmin has ultimate access on the data whether encrypted or not. Ultimately, the DBA's job is to safeguard data access. If they are not trusted with that key, then I'm not sure who would...
I have had to grapple with this issue myself recently, but I haven't been able to find a way around it.
Also, let's not forget that one of the DBA's duties is data recovery in case of system failure. If DBAs are kept out of the loop of how to decrypt sensitive information, it will ultimately be their employer who will suffer (potentially catastrophic) loss of data.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 15, 2010 at 1:09 pm
Not sure you've gained anything.
select ... convert(varchar(16), DecryptByKeyAutoCert(cert_id('MyCert'),
But now, isn't the key actually "MyCert"? If somebody were clandestinely querying your database, all they'd need to figure out is "MyCert" which is visible in the stored procedure.
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
January 31, 2010 at 4:44 am
thnaks for the DecryptByKeyAutoCert !
October 26, 2010 at 11:49 am
Hi Mike,
Thanks for an excellent article.. I am a developer and not a DBA. However, I cannot implement encryption without knowing the details in-depth about the same. Your article helped immensely in that. I have a few questions here - why do I need to create a certificate to create a symmetric key? Can I just use ENCRYPTION BY PASSWORD to create a Symmetric key? Also, I don't have a master key. Is it necessary to create one?
I searched online and just used Created the Symmetric Key and used the option ENCRYPTION BY PASSWORD for that and was able to encrypt and decrypt the data. That seemed very simple to me, but I am not sure if I am missing anything here in this approach. I am a novice in encryption.. Our client demanded encryption so, I am diving into it without knowing anything.. deadlines are always tight.. I need it yeterday - kind of..
Thanks in advance for the help
October 26, 2010 at 12:05 pm
We used certs because we did not want to have to worry about managing passwords in source code, or in config files, etc. By using certs, our development team did not have to concern themselves with this stuff, and I think it made it easier for us to address the compliance auditors.
If you don't mind managing the passwords, then I believe what you've described is all there is to it.
PS - This is an older article, relevant only to SQL 2005. I believe this has changed quite a bit (for the better) in SQL 2008.
October 26, 2010 at 12:08 pm
spraocs (10/26/2010)
Hi Mike,Thanks for an excellent article.. I am a developer and not a DBA. However, I cannot implement encryption without knowing the details in-depth about the same. Your article helped immensely in that. I have a few questions here - why do I need to create a certificate to create a symmetric key? Can I just use ENCRYPTION BY PASSWORD to create a Symmetric key? Also, I don't have a master key. Is it necessary to create one?
I searched online and just used Created the Symmetric Key and used the option ENCRYPTION BY PASSWORD for that and was able to encrypt and decrypt the data. That seemed very simple to me, but I am not sure if I am missing anything here in this approach. I am a novice in encryption.. Our client demanded encryption so, I am diving into it without knowing anything.. deadlines are always tight.. I need it yeterday - kind of..
Thanks in advance for the help
If you are simply using a symmetric key with a password, you need to declare the password every time in your code you need to open the symmetric key to encrypt/decrypt data. This password is then visible to anyone with access to that code.
Symmetric keys are not as secure as certificates/asymmetric keys; they are fast for encrypting/decrypting data but an extra layer is required to protect them.
I would use a certificate to protect the symmetric key; certificates can be backed up independently, which provides another layer of safety in case either the certificate or symmetric key gets corrupted.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 26, 2010 at 2:38 pm
Thanks a lot! Yes, I need to install the DB on SQL Server 2008 at the client's place. But I have only 2005 on my system to work on.. I will need to see how this is done in SQL Server 2008.
October 26, 2010 at 2:40 pm
Marios, Thanks for the information. I would definitely consider using the Certificates.
October 26, 2010 at 2:43 pm
I have another question.. Is it possible to use the ENCRYPTION BY PASSWORD temporarily and later rerplace it with the certificate?
Viewing 15 posts - 46 through 60 (of 65 total)
You must be logged in to reply to this topic. Login to reply