Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cannot Add a Master Key to the Database


Cannot Add a Master Key to the Database

Author
Message
Hamlitte
Hamlitte
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 12
Hi,


I'm very new to SQL Server, or databases, in general, so bare with me.


I'm trying to create a symmetric key to encrypt a column as it is inserted to my table. I create a MASTER key, a CERTIFICATE, and a SYMMETRIC key accordingly. Then attempt to open up the SYMMETRIC key to encrypt a column (in a trigger).


However, what I found out was that I created my MASTER/CERTIFICATE/SYMMETRIC keys in "master" database, not my own. I can execute a query to encrypt my data if I run in "USE master" context, but obviously not the other databases. I dropped all of keys and attempted to recreate them in my database, i.e. testdb. That's where I am getting the following error when I "USE testdb" rather than "USE master":

Msg 916, Level 14, State 1, Line 1

The server principal "sa" is not able to access the database "testdb" under the current security context.


I've gone through countless of posts on the Internet but none has resolved the issue. Can somebody shed some light here?
Hamlitte
Hamlitte
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 12
So I'm going through various documents and it seems like I should be creating, for obvious reasons, a master key for 'master' database and also a certificate that signs the symmetric key.

However, the symmetric key must be created for the intended database (i.e. USE testdb but USE master for the master key and certificate creation).

I just don't understand how the users and roles work in SQL Server, and they get to be set in the context of New Query window in SSMS. When I execute SELECT CURRENT_USER, some times it is 'guest' user and other times 'dbo'.

I'm connecting to the database via 'sa'. How do I go about to grant permission to an appropriate user, whichever it is (or perhaps I should create one!), to be able to execute certificate creation for my database, i.e. testdb?

This is getting really frusturating.
k.thanigaivel
k.thanigaivel
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 852
post your sql scripts..
Hamlitte
Hamlitte
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 12
I don't think there is an issue with the commands I'm executing as much as how I'm executing them. I need to switch to another user to run some of these commands as the permission obviously is lacking.

I login via 'sa' (owner of 'testdb') but in context of SSMS (2008 R2) New Query window, I don't think I'm executing under 'sa'. How can I switch to 'sa' in this environment?


USE master
GO

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'Password' -- just imagine the password passes the criteria for now
GO

OPEN MASTER KEY DECRYPTION
BY PASSWORD = 'Password'
GO

CREATE CERTIFICATE MyCert
WITH SUBJECT = 'My Cert'
GO

USE testdb
GO

CREATE SYMMETRIC KEY MYTableKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE MyCert
GO


NJ-DBA
NJ-DBA
SSC Eights!
SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)

Group: General Forum Members
Points: 911 Visits: 1546
A couple of problems here. First, oddly enough, it looks like sa doesnt have permissions in testdb... sa is a 'login' which doesnt have a user in the database testdb... create one like:

use testdb
create user sa for login sa

and add it to the db_owner role:

sp_addrolemember 'db_owner', 'sa'

Or just use SSMS, and look at the properties of the sa login. In the usermapping section, check the box for testdb and add the db_owner role.

Also, I believe you need to create the DATABASE MASTER KEY in the database you are using... not in the "Master" database. the name is a little confusing... any database can have a "database master key" which is used for encryption, not just the master database.
vikingDBA
vikingDBA
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 929
See this post for my notes on SQL Server column-level encryption.

http://www.sqlservercentral.com/Forums/Topic1152176-391-1.aspx


The Database Master Key is a symmetric key that is secured by the Service Master Key. A database can only have one DBMK. Objects can then be created within that database that are secured by the DBMK. The keys for the DBMK are kept in the database, and in the instance's master database (this behavior can be changed, to only be kept in the primary database, in case keeping it in master is a security issue).

The certificates are then created that are secured by the DBMK, and the Symmetric Keys are created that are secured by the certificates. Then the data is secured by the Symmetric Keys. See my post above for a whole set of scripts that run through it from start to finish.

Note: The SMK is secured by the Windows security API’s (DPAPI), utilizing the credentials of the SQL Server service account user. If that user is changed, special commands must be used to change the SMK to re-associate the key with the new user’s credentials.

In order for a user to use the symmetric keys, they have to be granted REFERENCES, like in the following:

GRANT REFERENCES ON SYMMETRIC KEY::SymmKey1 TO someusername

Where SymmKey1 is the name of the symmetric key and someusername is the username of a user (or role name, and just assign users to that role, which is better).

Be sure to back up all keys, the Service Master Key, the Database Master Key, and the certificates (which you can back up, as opposed to asymmetric keys, which you cannot), and keep the backups in a safe place, along with the passphrases used to make the backups. See my scripts in the above post for code on backing them up.
These would be needed if you need to move the database to another server, or recreate your server in a DR situation.

Note of caution: Practice the scripts on a development box before you do anything on a production system. Learn the different aspects and details of encryption before putting it into production.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search