Please test this scenario (based on my previous post).

In my test it works on the second server.

edited this warning from BOL topic "ALTER MASTER KEY"

use master

go

RESTORE DATABASE [TestEncryption3]

FROM DISK = N'X:\MSSQL.1\MSSQL\Backup\TestEncryption3.bak'

WITH FILE = 1

, MOVE N'TestEncryption3' TO N'X:\MSSQL.1\MSSQL\Data\TestEncryption3.mdf'

, MOVE N'TestEncryption3_log' TO N'X:\MSSQL.1\MSSQL\Data\TestEncryption3_1.LDF'

, NOUNLOAD, STATS = 10

GO

-- Create two logins that will be used for this demo

-- They are logins for two doctors in a clinic

--

create login Doc1 with password = 'Yukon90!'

create login Doc2 with password = 'Yukon90!'

go

-- Switch to the clinic database

--

use TestEncryption3

-- Create users for the doctors

--

sp_change_users_login @Action = 'Update_One',@UserNamePattern = 'Doc1', @LoginName = 'Doc1'

go

--*** opgelet : exec stmt staat in commentaar !!! ***

sp_change_users_login @Action = 'Update_One',@UserNamePattern = 'Doc2', @LoginName = 'Doc2'

go

---------------------------------

-- Simulate connecting as Doctor1

--

execute as login = 'Doc1'

-- Open the relevant key for usage

--

open symmetric key Doc1Key

DECRYPTION BY certificate Doc1Cert

-- Select from the table including decrypting from

-- encrypted columns

--

select

Id,

name,

Docname,

convert (varchar, decryptbykey(SSN)) as SSN,

convert (varchar, decryptbykey(Problem)) as Ailment

from PatientTable

/*

Msg 15581, Level 16, State 3, Line 8

Please create a master key in the database or open the master key in the session before performing this operation.

*/

-- Simulate logging out

--

close all symmetric keys

go

revert

go

print 'Is this you sitiation you are in now ?'

go

create master key

ENCRYPTION BY password = 'TestEncryption31'

go

/*

Msg 15578, Level 16, State 1, Line 1

There is already a master key in the database. Please drop it before performing this statement.

*/

Alter master key

ADD ENCRYPTION BY PASSWORD = 'TestEncryption31_TestServerDB'

/*

Msg 15581, Level 16, State 3, Line 1

Please create a master key in the database or open the master key in the session before performing this operation.

*/

go

ALTER MASTER KEY

REGENERATE WITH ENCRYPTION BY PASSWORD = 'TestEncryption31TEST';

/*

Msg 15329, Level 16, State 20, Line 1

The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.

*/

GO

Print 'Do not execute the following commented code because then you will not get to see the encrypted data !! '

--Alter master key

--FORCE REGENERATE WITH ENCRYPTION BY PASSWORD = 'TestEncryption31'

--/*

--The current master key cannot be decrypted. The error was ignored because the FORCE option was specified.

--*/

--go

-----------------------------------

---- Simulate connecting as Doctor1

----

--execute as login = 'Doc1'

---- Open the relevant key for usage

----

--open symmetric key Doc1Key

--DECRYPTION BY certificate Doc1Cert

---- Select from the table including decrypting from

---- encrypted columns

----

--select

-- Id,

-- name,

-- Docname,

-- convert (varchar, decryptbykey(SSN)) as SSN,

-- convert (varchar, decryptbykey(Problem)) as Ailment

--from PatientTable

--/*

--Msg 15581, Level 16, State 3, Line 8

--Please create a master key in the database or open the master key in the session before performing this operation.

--*/

---- Simulate logging out

----

--close all symmetric keys

--revert

--go

--/*

--Msg 15466, Level 16, State 1, Line 8

--An error occurred during decryption.

--*/

--print 'All encrypted data lost'

go

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'TestEncryption31' ; -- old db master key

ALTER MASTER KEY

REGENERATE WITH ENCRYPTION BY PASSWORD = 'TestEncryption31TEST'; -- renew db master key

go

---------------------------------

-- Simulate connecting as Doctor1

--

execute as login = 'Doc1'

-- Open the relevant key for usage

--

open symmetric key Doc1Key

DECRYPTION BY certificate Doc1Cert

-- Select from the table including decrypting from

-- encrypted columns

--

select

Id,

name,

Docname,

convert (varchar, decryptbykey(SSN)) as SSN,

convert (varchar, decryptbykey(Problem)) as Ailment

from PatientTable

/*

Id name Docname SSN Ailment

----------- ------------------------------ -------------------- ------------------------------ ------------------------------

1 Alice Doc1 111-11-1111 Migraine

2 Bob Doc1 222-22-2222 Stomach Ache

3 Charles Doc1 333-33-3333 Sore throat

4 Dave Doc2 NULL NULL

5 Emily Doc2 NULL NULL

6 Frank Doc2 NULL NULL

7 George Doc1 or Doc2 NULL NULL

(7 row(s) affected)

*/

-- Simulate logging out

--

close all symmetric keys

go

revert

go