If your database is missing a master key, service broker will try to access it and will throw errors in SQL Server Error log. In this article, lets learn how to verify keys in your database and how to install keys if they are missing.
Entry in SQL Server Error log:
If your SQL Server error log has entries as mentioned below, then the target database is missing its master key.
|Service Broker needs to access the master key in the database ‘%.*ls’. Error code:%d. The master key has to exist and the service master key encryption is required.|
Applications that have queuing or messaging in their modules use SQL Server service broker. This helps applications to use components of SQL Server database engine and communicate across databases that are disparate.
Does your database have a key?
To check if your database has a key, you can use the sys table symmetric_keys. This table would return one row for each symmetric key.
Select * from [DatabaseName].sys.symmetric_keys
If there is no symmetric key in your database but still the service broker is trying to access a master key from your database, then the only solution is to create a database master key and it should be a symmetric key.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<anypassword>’
SQL Server Service broker not just supports the communication, it also provides additional security by encrypting messages and blocking unauthorized connections from networks. It is very easy to maintain service broker. You can do it as part of your routine SQL Server database administration tasks. Conversation_endpoints, conversation_groups, conversation_priorities, message_type_xml_schema_collection_usages, remote_service_bindings, routes, service_contract_message_usages, service_contract_usages, service_contracts, service_message_types, service_queue_usages, service_queues, services, transmission_queue, dm_broker_activated_tasks, dm_broker_forwarded_messages, dm_broker_connections and dm_broker_queue_monitors are catalog view and dynamic management views with which you can get more info about SQL Server service broker.
Protecting key with a key
The key_encryptions is a SQL server system catalog view with which we can easily identify the mechanism that is used to protect database’s symmetric keys. Symmetric keys of your database can be protected by any method listed below
- Asymmetric keys
- Other symmetric keys
Random bits of a string form a symmetric key. The number of bits in your symmetric key depends on the algorithm that is used during the creation of the key. If the algorithm is AES_256, then the bit count is 256.
To avoid error messages related to service broker and database master keys, you might want to track symmetric keys in your databases. The catalog view has to be run from each database. If you have several hundreds of databases, then you can easily automate it using the query below.
DECLARE @keycheck varchar(1000) SELECT @keycheck = 'USE ? Select * from sys.symmetric_keys' EXEC sp_MSforeachdb @keycheck
Run this query from a new Query window and if your database has a symmetric key, the result table would have an entry in it otherwise it would be empty. Please note, though symmetric keys help you with security, they cannot help you with complete sql recovery as backups do. So never miss to have a backup plan for your SQL environment.
Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair pst and excel recovery software products. For more information visit www.datanumen.com