Decrypting data in SQL Server and pushing to a view

Question

I am trying to join a few different tables where most of the keys for which I would join are encrypted.

I want to decrypt the columns, join the tables and then push to a view.

Here is what my current query looks like:

OPEN symmetric KEY decryption_key decryption BY password = 'password';

CREATE VIEW new_view 
AS
    SELECT 
        id,  
        yearid, 
        quarterid,  
        CONVERT(VARCHAR(200), Decryptbykey(accountno)) AS 'AccountNo', 
        CONVERT(NVARCHAR(200), Decryptbykey(firstname)) AS 'FirstName', 
        CONVERT(NVARCHAR(200), Decryptbykey(lastname)) AS 'LastName', 
        CONVERT(NVARCHAR(200), Decryptbykey(dateofbirth)) AS 'DateOfBirth', 
    FROM   
        table1 
    JOIN 
        table2 ON table1.AccountNo = table2.AccountNo

The error I get is:

Incorrect syntax near the keyword 'OPEN'

I can't seem how to decrypt data and push it in to a view. I have tried multiple combinations. Thanks for any help.

Answer 1

As @TT mentioned, you need a GO before CREATE VIEW. That's why the syntax error. To create the view you don't the key to be open. So CREATE VIEW should work ok. To execute the view you need to have the key open:

OPEN SYMMETRIC KEY decryption_key decryption BY password = 'password';
SELECT * FROM new_view;
CLOSE SYMMETRIC KEY decryption_key;

The key remains open for the rest of the connection session.