SQL Symmetric Key and opening it from C#

Question

I am trying to encrypt data in SQL Server via a Symmetric key. When a user submits data through a web form, I would like for the data to be encrypted, using my Symmetric Key that I have saved inside the SQL Server. I am trying to find out how to do this. Currently, I run the following:

USE myDb
GO

OPEN SYMMETRIC KEY myKey
DECRYPTION BY CERTIFICATE myCert
GO

INSERT INTO [myDb].[dbo].[passData]
           ([userid]
           ,[passName]
           ,[passLogin1]
           ,[passLogin2]
           ,[passPass1]
           ,[passPass2]
           ,[passWebsite]
           ,[passNote])
     VALUES
           ('1'
           ,'test_2'
           ,ENCRYPTBYKEY(KEY_GUID('myKey'),'somedata1')
           ,NULL
           ,ENCRYPTBYKEY(KEY_GUID('myKey'),'somedata2')
           ,NULL
           ,NULL
           ,NULL)
GO

this works great - however, when I try to open the key from my Web.Config file, I get an error.

C# code:

    private void openKey(Dictionary<String, String> inputStrings)
    {
        try
        {
            SqlCommand seeqlCmd = new SqlCommand();
            String sqlInfo = ConfigurationManager.AppSettings.Get("OpenKey");
            seeqlCmd.CommandText = sqlInfo;
            seeqlCmd.CommandType = CommandType.Text;
            seeqlCmd.Connection = __SQLConn;

            seeqlCmd.ExecuteNonQuery();
            submitDataToDb(inputStrings);
            __SQLConn.Close();
        }
        catch (SqlException err)
        {
            // show error commands here
        }
    }

    private void submitDataToDb(Dictionary<String, String> sqlString)
    {
        try
        {
            SqlCommand sqlCmd = new SqlCommand();
            String confSet = ConfigurationManager.AppSettings.Get("DepositPasswordData");
            sqlCmd.CommandText = String.Format(confSet, sqlString["userID"], sqlString["passName"], sqlString["loginOne"], sqlString["loginTwo"], sqlString["passOne"], sqlString["passTwo"], sqlString["webSite"], sqlString["passNote"]);
            sqlCmd.CommandType = CommandType.Text;
            sqlCmd.Connection = __SQLConn;

            sqlCmd.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            // show error commands here
        }
    }

Web.Config file

<add key="OpenKey" value="OPEN SYMMETRIC KEY myKey DECRYPTION BY CERTIFICATE myCert"/>
<add key="DepositPasswordData" value="INSERT INTO Passwords.dbo.userPassData{0} userid, passName, passLogin1, passLogin2, passPass1, passPass2, passWebsite, passNote) VALUES ('{0}', '{1}', 'ENCRYPTBYKEY(KEY_GUID('myKey '),'{2}')', 'ENCRYPTBYKEY(KEY_GUID('myKey'),'{3}')', 'ENCRYPTBYKEY(KEY_GUID('myKey'),'{4}')', 'ENCRYPTBYKEY(KEY_GUID('myKey'),'{5}')', '{6}', '{7}')" />

Error from the try/catch statement:

Error Number: 102, Error Message: Incorrect syntax near 'myKey'. in: -2146232060 and System.Data.SqlClient.SqlErrorCollection

The question/problem:

• Why am I getting the error?
• Is there a different way I should be accessing the key or encrypting the data?

IN ADDITION: I tried changing the key name from "myKey" to "myKeya" or something like that and than I got this error:

Error Number: 15151, Error Message: Cannot find the symmetric key 'myKeya', because it does not exist or you do not have permission. in: -2146232060 and System.Data.SqlClient.SqlErrorCollection

naturally i'm using a different word than 'myKey' but I checked to see if the word I am using is any kind of keyword, and it doesn't come up in any search on google, bing and msdn... so I guess I'm safe there. Also this clues me in that the db is actually recieving the request, but it wants the key in some different fasion. hmmm....

Answer 1

May be you should escape or delete the semicolons in the web config? just an idea, not tested.

Answer 2

You don't want singly quotes around 'myKey' in your config file. Compare your config file to the statement that you said worked correctly, the only thing different is the quotes. Looking at the SQL Server documentation also indicates that neither the key nor certificate should have quotes around it.

Answer 3

Try running a trace with SQL Server Profiler. Then you'll be able to see the exact statement that your web app is running. From there you can cut/paste that statement into SQL Server Management Studio and see if it runs or has a syntax error. If it does run then you'll have a hint that it's a permissions issue like Donnie suggested.

Answer 4

Are you sure the error is on the open of the key and not the call to submitDataToDb?