I have a table where I stored encrypted passwords. In order to decrypt the passwords I wrote a UDF (user defined function) which utilizes SQL Server's DecryptByPassphrase function. By having the password encrypted in the table it prevents people from browsing the table and seeing the passwords in plain text.

When you need to retrieve a password you have to issue a sql statement like this:

select dbo.Decrypt(encryptedPassword) as Password from Password

I control the security on the UDF's so only a select few people can execute them. In any case, I have an application which used LINQ to SQL to access the passwords. The problem I ran into was that the SQL statements generated by LINQ have no idea to how to encrypt/decrypt my passwords. Therefore I had to build some custom procedures for LINQ to use.

The Insert Statement:

The two important things to notice here are:

1. I call the UDF to encrypt the password when I insert the record
2. The identity column is an output parameter. This way I can reference the primary key after the record is inserted.

In order to hook this stored procedure in with your LINQ Entity you need to do the following:

1. Open your dbml file (LINQ Data Model).
2. Make sure the methods pane is visible by right clicking on the design surface and choosing "Show Methods Pane"
3. Open Server Explorer (View—>Server Explorer) and browse to the database where you created the procedure. Drag the procedure into the method pane.
4. Open the LINQ entity that is going to use the SPROC and view the properties. Bring up the designer for the insert statement.
5. When the designer appears, select the customize radio button. In the drop down list choose the stored procedure you dragged in the method pane.
6. Make sure the arguments map correctly to the properties.

Repeat these steps for the Update and Delete statements if applicable.