Re: CREATE SYMMETRIC KEY

AES is only supported by SQL Server on Windows 2003.

--
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/

This posting is provided "AS IS" with no warranties, and confers no rights.

"Mike C#" <xyz@xxxxxxx> wrote in message
news:uYZddkbhGHA.4892@xxxxxxxxxxxxxxxxxxxxxxx
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
    ... To test it out using encryption, I created a database, TestEncrypt, using ... CREATE SYMMETRIC KEY SSN_Key_01 ... Either no algorithm has been specified or the bitlength and the algorithm ... DECRYPTION BY CERTIFICATE HumanResources037; ...
    (microsoft.public.sqlserver.security)
  • Re: Encrypting Data using SQL Server 2005
    ... symmetric key password. ... create symmetric key signingKey with algorithm = triple_des encryption by ... open symmetric key signingkey decryption by password = 'theKey' ...
    (microsoft.public.sqlserver.security)
  • Re: how to proceed
    ... I think I have to go to Symmetric Key becuase I also want good performance, ... SQL Server 2005 as a Named Instace. ... If you are looking for an encryption (which is not as powerful as the ... appreciate if you can send me any step by step Installation & Encryption ...
    (microsoft.public.sqlserver.clients)
  • CREATE SYMMETRIC KEY
    ... Server 2005, with the main purpose to use the encryption capabilities of SQL ... CREATE SYMMETRIC KEY SSN_Key_01 ... DECRYPTION BY CERTIFICATE HumanResources037; ...
    (microsoft.public.sqlserver.security)
  • Re: SQL-Server and Content encryption
    ... SQL Server does not have any encryption features built in like that. ... the encryption and decryption in the application. ... > read the data stored in the SQL-Server database? ...
    (microsoft.public.sqlserver.security)