Shalini's Knowledge Share

Decrypt data in a column in a table residing in a MS SQL database.

1. Create a class library project

2. Write the code with a SQL Function annotation as below.

namespace DecryptionProject
{

    public class ScriptMain
    {
     
        [SqlFunction(DataAccess = DataAccessKind.Read)]
        public static String Decrypt(String input)
        {
            try
            {
                // Decrytpion code

            }
            catch (Exception ex)
            {
                return string.Empty;

            }
        }
    }
}

2. Debug, and get the DLL file.

3. In the MS SQL Database, go to Programmability -> Assemblies.

4. Write-click and create a new Assembly (DecryptionProject). Give the path to the assembly file and click OK.

5. Create the function as below:
CREATE FUNCTION [dbo].[fn_DecryptSSN](@EncryptedSSN [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [DecryptionProject].[DecryptionProject.ScriptMain].[Decrypt]

6. Call the function the usual way:
SELECT     dbo.fn_DecryptSSN(p.SSN) AS SSN