Using ADO.NET SqlClient library : how to access the plain-text value of an encrypted column in SQL Server?

Question

I have implemented symmetric key in my TestDb. I want to open the keys in C# code, for that I wrote the code shown here. It uses SQL Server Management object library to open key from the database.

using Microsoft.SqlServer.Management.Common;  
using Microsoft.SqlServer.Management.Smo;  
  
SqlConnection sqlConnection = new SqlConnection(connectionString)  
          
string certificateName = "CLECertificate";  
  
ServerConnection serverConnection = new ServerConnection(sqlConnection);  
Server srv1 = new Server(serverConnection);  
  
Database database = srv1.Databases[sqlConnection.Database.ToString()];  
  
if (database.SymmetricKeys.Count > 0)  
{  
    foreach (SymmetricKey item in database.SymmetricKeys)  
    {  
        item.OpenWithCertificate(certificateName);  
    }  
}  

What I want to achieve

I want to achieve this without a database hit if possible. Or I want to open the key with connection and keep it open even after connection is closed. so that next database select wont have to open the key first.

I used Always Encrypted in SQL Server Enterprise. It works fine when this key added in connection string

"Column Encryption Setting=Enabled"  

This keyword is consumed in System.Data.SqlClient to open the connection.

There should be a way to do something like this for column level encryption with symmetric keys.

Any help would be much appreciated.

Answer 1

I want to achieve this without a database hit if possible

That's not possible, the key is stored in SQL Server.

keep it open even after connection is closed.

That's also not possible. The key gets opend in a client connection and if you close the connection, it's gone.