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.