Copy a encrypted data from one row to another row

The purpose of an anthenticator is to ensure when you copy the encrypted value from one row onto another row within a table, the data is not valid any more.

Table structure:

USE [AdventureWorks]
GO
CREATE TABLE [Sales].[Card](
[CreditCardID] [int] NOT NULL,
[CardNumber] [varbinary](256) NOT NULL,
PRIMARY KEY CLUSTERED ( [CreditCardID] )
)

Insert two records into the [Sales].[Card] without authenticator:

USE AdventureWorks;
GO
-- First get the passphrase from the user.
DECLARE @PassphraseEnteredByUser nvarchar(128);
DECLARE @CreditCardID INT;
DECLARE @CreditCardNumber NVARCHAR(25);
DECLARE @Encrypted varbinary(256);
SET @PassphraseEnteredByUser = 'A little learning is a dangerous thing!';
SET @CreditCardID=1;
SET @CreditCardNumber='66663635401028';

SET @Encrypted = EncryptByPassPhrase(@PassphraseEnteredByUser
, @CreditCardNumber);
INSERT Sales.Card
(CreditCardID,CardNumber)
VALUES(@CreditCardID,@Encrypted);
GO


USE AdventureWorks;
GO
-- First get the passphrase from the user.
DECLARE @PassphraseEnteredByUser nvarchar(128);
DECLARE @CreditCardID INT;
DECLARE @CreditCardNumber NVARCHAR(25);
DECLARE @Encrypted varbinary(256);
SET @PassphraseEnteredByUser = 'A little learning is a dangerous thing!';
SET @CreditCardID=2;
SET @CreditCardNumber='55553635401028';

SET @Encrypted = EncryptByPassPhrase(@PassphraseEnteredByUser
, @CreditCardNumber);
INSERT Sales.Card
(CreditCardID,CardNumber)
VALUES(@CreditCardID,@Encrypted);
GO

Insert two records into the [Sales].[Card] with authenticator:



USE AdventureWorks;
GO
-- First get the passphrase from the user.
DECLARE @PassphraseEnteredByUser nvarchar(128);
DECLARE @CreditCardID INT;
DECLARE @CreditCardNumber NVARCHAR(25);
DECLARE @Encrypted varbinary(256);
SET @PassphraseEnteredByUser = 'A little learning is a dangerous thing!';
SET @CreditCardID=3;
SET @CreditCardNumber='77773635401028';

SET @Encrypted = EncryptByPassPhrase(@PassphraseEnteredByUser
, @CreditCardNumber, 1, CONVERT( varbinary, @CreditCardID));
INSERT Sales.Card
(CreditCardID,CardNumber)
VALUES(@CreditCardID,@Encrypted);
GO

USE AdventureWorks;
GO
-- First get the passphrase from the user.
DECLARE @PassphraseEnteredByUser nvarchar(128);
DECLARE @CreditCardID INT;
DECLARE @CreditCardNumber NVARCHAR(25);
DECLARE @Encrypted varbinary(256);
SET @PassphraseEnteredByUser = 'A little learning is a dangerous thing!';
SET @CreditCardID=4;
SET @CreditCardNumber='88883635401028';

SET @Encrypted = EncryptByPassPhrase(@PassphraseEnteredByUser
, @CreditCardNumber, 1, CONVERT( varbinary, @CreditCardID));
INSERT Sales.Card
(CreditCardID,CardNumber)
VALUES(@CreditCardID,@Encrypted);
GO

Without Authenticator

Copy the encrypted data from record 1 onto record 2

UPDATE Sales.Card
SET CardNumber=(SELECT CardNumber FROM Sales.Card WHERE CreditCardID='1')
WHERE CreditCardID='2'

Using the same passphrase, you can decrypt the data

DECLARE @PassphraseEnteredByUser nvarchar(128);
SET @PassphraseEnteredByUser = 'A little learning is a dangerous thing!';
SELECT CONVERT(nvarchar(25),
DecryptByPassphrase(@PassphraseEnteredByUser, CardNumber))
FROM Sales.Card
WHERE CreditCardID = '2';

With Authenticator

Copy the encrypted data of row 3 onto row 4

UPDATE Sales.Card
SET CardNumber=(SELECT CardNumber FROM Sales.Card WHERE CreditCardID='3')
WHERE CreditCardID='4'

The following batch returns NULL --cannot decrypt the data --the data is not valid.

DECLARE @PassphraseEnteredByUser nvarchar(128);
SET @PassphraseEnteredByUser = 'A little learning is a dangerous thing!';
SELECT CONVERT(nvarchar(25),
DecryptByPassphrase(@PassphraseEnteredByUser, CardNumber, 1
, CONVERT(varbinary, CreditCardID)))
FROM Sales.Card
WHERE CreditCardID = '4';
============
Observation:
By using the primary key (CreditCardID) of the table to authenticate the encryption and decryption, you can ensure that the encrypted value is valid only within the row in which it resides and invalid if the encrypted text is copied to any other row.

The phrase "Preventing the encrypted data from being copied" is wrong. You can copy the encrypted data but the data is useless.