Decrypt by key not working ASP.NET sql command
<p>I have the following problem:</p>

<p>I have encrypted a column in my SQL Database by using a Symmetric Key. Now I have the encrypted value in the database and when I open the key and execute the query:</p>

<pre><code>SELECT Convert(varchar, DECRYPTBYKEY(ColumnName)) as ClientSecret 
FROM dbo.Sample
</code></pre>

<p>In Management Studio query editor it is ok and it returns the decrypted value.</p>

<p>But when I try to execute the same thing as a <code>SqlCommand</code> from my ASP.NET application it returns null.</p>

<p>Code:</p>

<pre><code>string query = "SELECT Convert(varchar, DECRYPTBYKEY(ColumnName)) as ClientSecret FROM dbo.Sample";

var con = ConfigurationManager.ConnectionStrings["connectionName"].ConnectionString;

using (SqlConnection connection = new SqlConnection(con))
{
    SqlCommand command = new SqlCommand(query, connection);

    connection.Open();

    SqlDataReader reader = command.ExecuteReader();

    try
    {
        while (reader.Read())
        {
            //the value of client secret here is null
            var readerTest = reader["ClientSecret"];
        }
    }
    finally
    {
        // Always call Close when done reading.
        reader.Close();
    }
}
</code></pre>

<p>My questions are the following:</p>

<ul>
<li><p>First, why this does not work?</p></li>
<li><p>Second, is there a better way to decrypt the value of the column in the application (not by asking the server to do it) by using its encryption algorithm?</p></li>
</ul>