Okay, I DID manage to move ahead a bit, and in the process answered at least some of my questions. For starters, the certificate and symmetric keys are indeed automatically saved to their respective folders under the Security folder of the spefic database.

But while this T-SQL...

SELECT d.is_master_key_encrypted_by_server

FROM sys.databases AS d

WHERE d.name = 'AdventureWorks';

... shows that I have successfully created a Database Master Key (DMK) on AdventureWorks, I cannot see any keys logged in the sys.symmetric_keys catalog. I return a 0 results, no-error results set. When our DBA elevates me temporarily to SA I can see the Service Master Key (SMK) using this query, but not the DMK. As soon as SA rghts are pulled the sys.symmetric_keys catalog is invisible to me again.

Encryption seems to just fail without error as well. neither of these statements returns anything but nulls.

UPDATE

Sales.CreditCard_ENCRYPTION

SET

CardNumbENC = EncryptByKey(Key_GUID('TestSymmetricKey'), CardNumber);

SELECT

'Enc' = EncryptByKey(Key_GUID('TestSymmetricKey'), CardNumber)

FROM

Sales.CreditCard_ENCRYPTION;

Next I tried to first open the symmetric key using:

OPEN SYMMETRIC KEY TestSymmetricKey

DECRYPTION BY CERTIFICATE TestCert;

But I received this error:

Msg 15334, Level 16, State 1, Line 2

The certificate has a private key that is protected by a user defined password. That password needs to be provided to enable the use of the private key.

But none of the examples I have seen show a password being needed to open the key. They all use the syntax above.

If I open the symmetric key with the cert + password it works:

OPEN SYMMETRIC KEY TestSymmetricKey

DECRYPTION BY CERTIFICATE TestCert

WITH PASSWORD = 'thisIsAP@$$w0rd';

But doesn't this mean I need the password inside EVERY proc I write that touches an encrypted column? isn't this adding insecurity into the system? What if the password needs to change, it will be scattered throughout the system.

Iam sure there must be a way to do this without explicitly calling the password.