InfoPath Dev

I have an online web form that when submitted dumps into a SQLDB. I have created an InfoPath template that connects to that DB with all the associated fields. I have one query field (application date).

 When I query the date all applications that were submittted come in correctly. This is where I am stuck-

 One of the fields on the form is SSN- When the web form is submitted to the DB the SSN is encrypted. When I query the application date-all the data comes in but the SSN comes in encrypted.

 How do I get the SSN converted back to what was typed in on the form. I have read to use custom code but I am new to InfoPath so where does it go or can someone point me to an article explaining how to do this?

Russ

Decrypting will require knowledge of how the SSN was originally encrypted. Do you have access to the code that the web application uses to encrypt the SSN? Or can the developer for that application tell you how the information is encrypted?

You will need to use code to decrypt. You can find information on this at sites like StackOverflow, which may help you get started.

Yes I have the code

Great! How is the SSN being encrypted?

Its is being done with a SSL cert on the web server.

 I have the code to decrypt and it works but this is the issue.

I created the InfoPath form via connection to the SQL DB on another server. Once the form was linked I have the Query and Mydata fields. I created the entire form with only one query field (Application Date). When I look at the SQL Statement it is like this (abbreviated).

 select "EmpLName","EmpFName","EmpMI","AppDate"," ApplicationID","SSN_Encrypt" from "dbo"."Tablename" as "Tablename"

I tried doing this-

OPEN SYMMETRIC KEY "Keyname" (no quotes)

   DECRYPTION BY CERTIFICATE "Certname"; (no quotes)

Hope this helps

I think you may need to alter your query statement in code prior to running it. It seems to me that you are instead altering the SQL statement for the main data connection. Here's an MSDN page that may help.