Trying to create cetificate in SQL 2008 R2 gives error

• Shabnam Gupta

  SSC Rookie

  Points: 31

  More actions

  May 17, 2012 at 8:55 am

  #392278

  Hi Everyone,

  I have create a master key and am trying to create a certifiacte and keep getting this error "Msg 15581, Level 16, State 3, Line 4

  Please create a master key in the database or open the master key in the session before performing this operation."

  Below is my code to create the certificate.

  IF NOT EXISTS (SELECT * FROM sys.certificates

  WHERE [name] = 'config_cert')

  CREATE CERTIFICATE config_cert

  --AUTHORIZATION enc_user

  WITH SUBJECT = 'X.509 encryption certificate for securing configuration symmetric keys.';

  GO

  Code to create the master key

  IF NOT EXISTS (SELECT * FROM sys.symmetric_keys

  WHERE [name] Like '%DatabaseMasterKey%')

  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 1234xyz!';

  GO

  What am I doing wrong?

  Please help.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736128

  More actions

  May 17, 2012 at 9:01 am

  #1489086

  Your master key is not automatically opening. Perhaps it wasn't created with encryption by the SMK as well?

  Try:

  OPEN MASTER KEY BY PASSWORD = 'XXX'

  CREATE CERTIFICATE XXX

  http://msdn.microsoft.com/en-us/library/ms174433.aspx]OPEN MASTER KEY

• Shabnam Gupta

  SSC Rookie

  Points: 31

  More actions

  May 17, 2012 at 10:18 am

  #1489153

  so you have to open the master key to create a certificate?

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736128

  More actions

  May 17, 2012 at 10:40 am

  #1489185

  You shouldn't have to explicitly. It does have to be open, but if you just ran this, then the SMK should automatically open it.

  If this is all your code, then you have a typo in the password (missing quote).

  I assume you are running the code in the reverse order, correct? Master key first, then cert?

• kapfundestanley

  SSCertifiable

  Points: 5637

  More actions

  July 13, 2012 at 5:32 am

  #1512473

  Just a tip also when create the mirror make sure you specify the expiry date for the certificate otherwise it will default to one year.

  “When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  July 16, 2012 at 5:03 am

  #1512998

  Shabnam Gupta (5/17/2012)

  ---

  so you have to open the master key to create a certificate?

  What does the following query return?

  select name, is_master_key_encrypted_by_server from sys.databases

  where database_id = 1

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs" 😉