I am using following Scaler-valued function to decrypt data that is encrypted using a symmetric key:
USE [DBNAME] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[DECRYPTDATA] ( @CipherText NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @Result NVARCHAR(MAX) SELECT @Result = CONVERT(VARCHAR(MAX),DECRYPTBYKEY(@CipherText)) RETURN @Result END
And I am using following query to get the data:
EXEC [dbo].OpenKeys SELECT ([dbo].DECRYPTDATA([Value])) AS VALUE FROM [Table1] WHERE [UsrId] =2 AND [GroupID] = 44
I executed this query in SQL Server 2012 Management Studio, it's showing only the first letter of the decrypted text:
And when I checked the
DataTable in C# code, it's showing space between characters:
Actual data must be like this:
╔═══════╗ ║ VALUE ║ ╠═══════╣ ║ 232 ║ ║ hgjhg ║ ║ 12 ║ ║ 53 ║ ║ 0 ║ ╚═══════╝
What's happening here?
I tried inserting directly like
INSERT INTO Table1([Value]) VALUES([dbo].ENCRYPTISDATA('432')) but when I fetch this row it shows
4 only. So I think the problem is at the SQL Server side. What it is I don't know. Hope somebody can figure out soon. I have to submit this project in some days.
I am using TripleDES algorithm.