1

What is the best method to encrypt a column that exists in two tables (BUID) in two different database on a 2012 server, at the end should be able to join on the encrypted columns between two tables across databases.

use testdb1

create table table1
(
    BUID number(10),
    ACCOUNT_BALANCE Money
)

use testdb2

Create table table2
(
    BUID number(10),
    CUSTOMER_FNAME varchar2(30),
    CUSTOMER_LNAME varchar2(30)
)

Basically want to encrypt the BUID in both tables and be able to crate a view that joins across two databases.

create view vw_cust_balance
as 
    select 
        a.buid ,
        a.account_balance 
        b.customer_fname, 
        b.customer_lname 
    from 
        testdb1..table1 a 
    inner join 
        testdb2..table2 b on a.buid = b.buid

Can someone point me to a resource or example that resolves situation above?

  • I hope this can help – McNets Apr 18 at 14:42
  • Does the BUID have meaning? For instance, if it's a surrogate key, is there any real need to encrypt it? – John Eisbrener Apr 18 at 16:38
0

The answer to your question depends on whether or not you empasize security over performance and potentially how many records we are talking about. You can create a secure solution that won't scale very well by creating the same symmetric key on both servers by using identical values for key_souce and identity_value for the CREATE SYMMETRIC KEY command. All server side encryption in SQL Server is non-deterministic, meaning that the same value encrypted twice with the same key will result in different encrypted values. Therefore, you won't be able to join the data without decrypting it. If the data is decrpted on each server before it's combined, then you will want to add an SSL Certificate directly to the SQL Server to encrypt at the transport level.

Alternatively, you could put the key on only one server and always perform a remote join on the server with the key after encrypting both tables with it. You would still want an SSL certificate on the instance if you require that the result is returned unencrypted.

Another idea would be to use an Extensible Key Management system, which is a hardware security module that offloads encryption processing and would allow the same key to be used by both systems.

Since server side encryption is non-deterministic, you won't be able to index it. There are two potential solutions to that issue. One is to create a hash on part of the original data and index that to help improve performance and the other would be to use an deterministic method for encryption that is either external or encapsulated in a CLR that is installed on both systems. Be careful with hashes because they are deterministic and can be reproduced easily, so avoid hashing the entire value. If anyone can use the CLR or external encryption who is not authorized, then it's possible that a hash table can be created by running all possible values through the system and then it can be joined back to the original data.

To encrypt in SQL Server, you will also need to use varbinary columns instead of numerics.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.