Re: CREATE SYMMETRIC KEY

Depends on the version of Windows you're running. Different versions have
different variations of CryptoAPI. I believe all versions of CryptoAPI have
some basic algorithms available (RC2, DES), but AES is not available on all
platforms.

"Gerhard" <acsla@xxxxxxxxxxxxxxxx> wrote in message
news:C771AB35-74D3-40D5-A94C-33C9F08A40FB@xxxxxxxxxxxxxxxx
I found the problem on the on the encryption inconsistency, still would
like
to know about the AES_256. Thanks.

"Gerhard" wrote:

Hi,

I am in the process of switching an application from SQL Server 2000 to
SQL
Server 2005, with the main purpose to use the encryption capabilities of
SQL
Server 2005.

To test it out using encryption, I created a database, TestEncrypt, using
all the defaults.

I then worked with the script from the help file in encryption[SQL
Server] /
columns / Simple Symmetric Encryption.

When I run

CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE HumanResources037;
GO

from the script, I get the following error:

Msg 15314, Level 16, State 1, Line 2
Either no algorithm has been specified or the bitlength and the algorithm
specified for the key are not available in this installation of Windows.

When I change this to

CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO

it completes successfully.

However, the Decrypted ID (here is the output):

NationalIDNumber: 002020002
Decrypted ID Number: 2

This does not make sense (the decrypted value should be the same as the
original value).

Full script is below.

Can you tell me why the AES_256 doesn't work (I'm on an XP Pro machine)
and
why the decrypted value is different from the original value?

Thanks.

Bob


/* To prevent any potential data loss issues, you should review this
script
in detail before running it outside the context of the database
designer.*/
BEGIN TRANSACTION

Use TestEncrypt

SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Employee
(
NationalIDNumber varchar(50) NULL
) ON [PRIMARY]
GO
COMMIT

Use TestEncrypt
GO

INSERT INTO dbo.Employee (NationalIDNumber) SELECT '002020002'

GO

SELECT * FROM dbo.Employee

GO

--If there is no master key, create one now
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478dDkjdahflkujaslekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
GO

CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO

CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO

USE [TestEncrypt];
GO

-- Create a column in which to store the encrypted data
ALTER TABLE Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO

-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO

-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO

-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM Employee;
GO












.

Relevant Pages

  • Re: CREATE SYMMETRIC KEY
    ... AES is only supported by SQL Server on Windows 2003. ... To test it out using encryption, I created a database, TestEncrypt, ... CREATE SYMMETRIC KEY SSN_Key_01 ... DECRYPTION BY CERTIFICATE HumanResources037; ...
    (microsoft.public.sqlserver.security)
  • Length of the data to decrypt is invalid
    ... I found this code on a site for doing string encryption/decryption. ... // Create a symmetric algorithm. ... // This is done to make encryption more secure. ... // This will tell it that we have done our decryption ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Encryption key changing the encryption logic.
    ... >the algorithm itselfs. ... It uses a different for encryption and decryption. ... "symmetric" cipher and a "reciprocal" cipher. ...
    (sci.crypt)
  • Re: Crypto Question
    ... (Password Based Encryption) ... parameter for generating a random symmetric key. ... When choosing the algorithm to use you can also choose the length of the ... PGP / XML GATEWAY APPLIANCE ...
    (Security-Basics)
  • Re: Crypto Question
    ... (Password Based Encryption) ... parameter for generating a random symmetric key. ... When choosing the algorithm to use you can also choose the length of the ... PGP / XML GATEWAY APPLIANCE ...
    (Security-Basics)