Menu
I'm trying to implement an encrypted column as in this following MSDN example. I understand most of the code except for the very first line:
--If there is no master key, create one now.
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
When I run that on the AdventureWorks database on my local machine, the key already exists, which is fine; I got the example to run fine and encrypt/decrypt properly on my machine. However in that code there is no mention of a symmetric_key_id of 101 any at any point.
Is 101 some kind of reserved ID for symmetric keys?
424
The Service Master Key is the root of the SQL Server encryption hierarchy. The SMK is automatically generated the first time the SQL Server instance is started and is used to encrypt a linked server password, credentials, and the database master key in each database.
Information about the database master key is visible in the sys. symmetric_keys catalog view. For SQL Server and Parallel Data Warehouse, the master key is typically protected by the service master key and at least one password.
To generate a master key that provides access to all user accounts in the system, navigate to the System Admin panel and open the System Settings tab. Open Generate MasterKey. Choose a strong password and enter in the password field and click Generate SHA-256 to generate the SHA-256 hash of the master password.
As seen HERE, the symmetric_key_id 101 is the id for the Database Master Key.
The number of database master keys is limited only by the number of databases you have - you can have one master key per database and its id is 101.
170
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
