Hi, 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....