How to get encrypted records from table in sql server by symmetryk key using sqlserver.management.smo

Question

I am using SymmetricKey for my password field. and now I want to get all records from that table with decrypted passwords. and I am using Microsoft.SqlServer.Management.Smo.

var tbls = new Table();
tbls = db.Tables[tblname];
ScriptingOptions options = new ScriptingOptions();
options.ScriptData = true;
options.ScriptDrops = false;
options.EnforceScriptingOptions = true;
options.ScriptSchema = false;
options.IncludeHeaders = true;
options.AppendToFile = true;
options.Indexes = true;
options.WithDependencies = true;                                
ServerVersion sv = new ServerVersion(2008, 2005);
options.SetTargetServerVersion(sv);
var script = tbls.EnumScript(options);
string queryes ="";                                 
foreach (var line in script)
{
   if (line.Contains("VALUES"))
   {
      queryes += line;                                        
   }
}

How to get decrypted record's data?

Answer 1

If that field already had been encrypted by using a symmetric key, then you should open it with the symmetric key by using the below statement.

    OPEN SYMMETRIC KEY [Key Name] Decryption by [Decryption Mechanism]

the following link may help you to understand Symmetric Key, Asymmetric Key and creating Certificates to encrypt & decrypt the data. [https://msdn.microsoft.com/en-us/library/ms190499.aspx]