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 Serve
r] /
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%94467GRdkjuw54
ie5y01478dDkjdahflkujaslekjg5k3fd117
r$$#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;
GOI 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 SQ
L
> Server 2005, with the main purpose to use the encryption capabilities of S
QL
> 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 Ser
ver] /
> 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) an
d
> why the decrypted value is different from the original value?
> Thanks.
> Bob
>
> /* To prevent any potential data loss issues, you should review this scrip
t
> 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%94467GRdkjuw54
ie5y01478dDkjdahflkujaslekjg5k3fd1
17r$$#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
>
>
>
>
>|||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@.community.nospam> wrote in message
news:C771AB35-74D3-40D5-A94C-33C9F08A40FB@.microsoft.com...[vbcol=seagreen]
>I found the problem on the on the encryption inconsistency, still would
>like
> to know about the AES_256. Thanks.
> "Gerhard" wrote:
>|||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@.xyz.com> wrote in message
news:uYZddkbhGHA.4892@.TK2MSFTNGP02.phx.gbl...
> 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@.community.nospam> wrote in message
> news:C771AB35-74D3-40D5-A94C-33C9F08A40FB@.microsoft.com...
>
sql
