August 26, 2012 at 8:52 pm
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?
August 26, 2012 at 11:44 pm
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.
August 27, 2012 at 4:40 am
post your sql scripts..
August 27, 2012 at 5:34 am
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
August 27, 2012 at 10:18 am
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.
August 27, 2012 at 12:22 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply