CREATE SYMMETRIC KEY
- From: Gerhard <acsla@xxxxxxxxxxxxxxxx>
- Date: Thu, 1 Jun 2006 10:00:02 -0700
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
.
- Follow-Ups:
- RE: CREATE SYMMETRIC KEY
- From: Gerhard
- RE: CREATE SYMMETRIC KEY
- Prev by Date: deny permissions issues
- Next by Date: RE: CREATE SYMMETRIC KEY
- Previous by thread: deny permissions issues
- Next by thread: RE: CREATE SYMMETRIC KEY
- Index(es):
Relevant Pages
|
