Probleme decryptage avec certificat

Version imprimable

06/07/2010, 11h40
peaceandloveman01

Probleme decryptage avec certificat
Bonjour a tous,

J'ai creer un certificat et une cle symetrique pour encrypter certains champs de ma base de donnees. L'encryptage s'effectue parfaitement.
Code:

1 2 3 4 open symmetric key SensitiveDataSymmetricKey Decryption by certificate EmployeeDataCertificate SET @EncryptedData = convert(varbinary,encryptbykey(key_guid('SensitiveDataSymmetricKey'), @Data))
Le probleme se situe au niveau de la recuperation des ces donnees encryptees... Lorsque j'effectue la commande suivante :
Code:

1 2 3 4 open symmetric key SensitiveDataSymmetricKey Decryption by certificate EmployeeDataCertificate SET @DecryptedData = CONVERT(nvarchar, decryptbykey(@Data))
le serveur me retourne une valeure NULL alors que si je fais un select dans le champ, il me retourne bien une valeure. C'est donc le DecryptByKey qui ne marcherait pas ?! Une idee de pourquoi l'encryptage fonctionne mais pas le decryptage ?? :aie:

Merci d'avance
06/07/2010, 12h25
SQLpro

comment avez vous créé votre clef ?
Y a t-il une master key dans la base ??

A +

Bonjour,

voici le script utilise:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
use [Toto]
GO
 
/*--- Create a new master key ---*/
if not exists (
select * from sys.symmetric_keys where name = N'##MS_DatabaseMasterKey##'
)
create master key encryption by password ='testTOTO';
GO
 
/*--- Create the certificate ---*/
if not exists (
select * from sys.certificates where name = N'EmployeeDataCertificate'
)
Create certificate EmployeeDataCertificate With subject = 'SensitiveData';
GO
 
/*--- Create symmetric key for certificiate ---*/
create symmetric key SensitiveDataSymmetricKey
with algorithm = AES_256
encryption by certificate EmployeeDataCertificate;
GO
 
GRANT CONTROL ON CERTIFICATE:: EmployeeDataCertificate TO [All USP EXEC];
GO
GRANT CONTROL ON SYMMETRIC KEY:: SensitiveDataSymmetricKey TO [All USP EXEC];
GO

Donc oui j'utilise bien une master key que je definit moi meme

Si ca peut aider a comprendre...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
use test
 
declare @normal nvarchar(50),
@encrypt varbinary(256),
@decrypt nvarchar(50)
 
set @normal = N'test codage'
open symmetric key SensitiveDataSymmetricKey
Decryption by certificate EmployeeDataCertificate;
set @encrypt = convert(varbinary,encryptbykey(key_guid('SensitiveDataSymmetricKey'),@normal))
set @decrypt = convert(nvarchar , decryptbykey(@encrypt))
 
SELECT @normal as 'Normal'
 ,@encrypt as 'Encrypt'
,@decrypt as 'Decrypt'

Et le resultat:

Code:

1
2
3
4
 
      |     Normal     |         Encrypt        |  Decrypt
------------------------------------------------------------
   1  |  test codage   |  0x0099326E47...       |   NULL

06/07/2010, 17h25
SQLpro

Vous utilisez une base puis une autre... toto et test !

A +
06/07/2010, 17h29
peaceandloveman01
Finalement je vais changer de methode et opter pour un cryptage et decryptage par certificat uniquement
Code:

1 2 set @encrypt = encryptByCert(Cert_Id('EmployeeDataCertificate'),@normal) set @decrypt = decryptByCert(Cert_Id('EmployeeDataCertificate'),@encrypt)
Mais si quelqu'un q une idee de pourquoi ca marche pas avec DecryptByKey, je suis preneur :aie:

Vous ne typez pas correctement vos données !!!!

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
declare @normal nvarchar(50),
@encrypt varbinary(256),
@decrypt nvarchar(50);
 
SET @normal = N'test codage';
open symmetric KEY SensitiveDataSymmetricKey
Decryption BY certificate EmployeeDataCertificate;
SET @encrypt = convert(varbinary(256), encryptbykey(key_guid('SensitiveDataSymmetricKey'), @normal));
SET @decrypt = convert(nvarchar (50), decryptbykey(@encrypt));

/* VOTRE ERREUR :
SET @normal = N'test codage';
open symmetric KEY SensitiveDataSymmetricKey
Decryption BY certificate EmployeeDataCertificate;
SET @encrypt = convert(varbinary, encryptbykey(key_guid('SensitiveDataSymmetricKey'), @normal));
SET @decrypt = convert(nvarchar , decryptbykey(@encrypt));
*/
 
SELECT @normal AS 'Normal'
 ,@encrypt AS 'Encrypt'
,@decrypt AS 'Decrypt';

Si vous ne passez pas la longueur à un type, SQL prend le minimum, ce qui n'est pas compatible avec la longueur de vos données !!!!

A +

06/07/2010, 17h31
peaceandloveman01

Citation:

Envoyé par SQLpro

Vous utilisez une base puis une autre... toto et test !

A +

Oui et non, en fait j'ai juste donne un nom lambda. Mais dans la realite j'utilise la meme base
06/07/2010, 17h33
peaceandloveman01

:oops: Et bien oui tout simplement ...

Merci du coup de main ! :ccool:
06/07/2010, 18h05
peaceandloveman01

Finalement j'ai retire le resolu car le probleme persiste lorsque je recupere ma valeur crypte (varbinary) grace a une requete "SELECT", et que je tente de la decrypter ... :aie:

Une Idee ??
06/07/2010, 21h32
SQLpro

postez votre requête !

A +

Je sais pas si ca va aider mais voila:

La procedure appelee:

Code:

1
2
3
4
5
6
7
8
9
SET @AnswerTemp = (SELECT Answer FROM tblEmployeeAnswer WHERE UserID = @UserID)
 
EXEC [dbo].[usp_Employee_DecryptData]
     @AnswerTemp,
     @RequestingUser,
     @ApplicationID,
     @DecryptedData = @DecryptedData OUTPUT
 
SET @Answer = @DecryptedData

La procedure de decryptage:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE PROCEDURE dbo.usp_Employee_DecryptData
     @data varbinary(256)
    ,@RequestingUser nvarchar(51)
    ,@ApplicationID int
    ,@DecryptedData nvarchar(50) OUTPUT
 
bla bla ...
 
open symmetric key SensitiveDataSymmetricKey
Decryption by certificate EmployeeDataCertificate
 
SET @DecryptedData = CONVERT(nvarchar(50), decryptbykey(@Data))

et enfin la definition de ma table :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE [dbo].[tblEmployeeAnswer](
     [UserID] [int] NOT NULL,
     [QuestionID] [int] NOT NULL,
     [LanguageID] [int] NULL,
     [Answer] [varbinary](256) NULL,
     [LastUpdatedBy] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
     [DateTimeLastUpdated] [datetime] NOT NULL DEFAULT (getdate()),
     [Deleted] [bit] NOT NULL DEFAULT ((0)),
CONSTRAINT [tblEmployeeSecretQuestionAnswer_PK] PRIMARY KEY CLUSTERED 
(
     [UserID] ASC,
     [QuestionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

07/07/2010, 18h02
peaceandloveman01

Bon, j'est cherche pendant pas mal de temps mais je ne trouve toujours pas la reponse. Je penses que le probleme vient d'une conversion de types... Mais j'en suis pas sur ! :calim2:

Alors pour le moment je me contente d'un encodage par certificat... Cela fonctionne et devrait suffir pour le moment.

Cependant, si vous avez une reponse a m'apporter je suis preneur ! :ccool:

Bonne soiree,

Lyonel