Database Master Key Error

Hi,

In sys.transmission_queue is showing the transmission_status with error. "The session keys for this conversation could not be created or accessed. The database master key is required for this operation."

But I do have a Master Key in the database, and many External Assemblies depend on that. Is there an option to use the existing master key. How to use the existing key ?

Thanks in advance.

Regards

Babu

In case your conversation spans between two databases, both databases need to have a master key. Also, the database master key has to be encrypted with the service master key. If the service master key encryption of the database master key is missing, you can add it like this:

alter master key add encryption by service master key

HTH,
~ Remus

|||

Hi Remus,

It worked.. Thank you..

Regards

Babu

|||Also had this problem on moving databases between servers using restore. Had to run this after the restore and re enable broker on each restored database.

Database Master Key Error

Hi,

In sys.transmission_queue is showing the transmission_status with error. "The session keys for this conversation could not be created or accessed. The database master key is required for this operation."

But I do have a Master Key in the database, and many External Assemblies depend on that. Is there an option to use the existing master key. How to use the existing key ?

Thanks in advance.

Regards

Babu

In case your conversation spans between two databases, both databases need to have a master key. Also, the database master key has to be encrypted with the service master key. If the service master key encryption of the database master key is missing, you can add it like this:

alter master key add encryption by service master key

HTH,
~ Remus

|||

Hi Remus,

It worked.. Thank you..

Regards

Babu

|||Also had this problem on moving databases between servers using restore. Had to run this after the restore and re enable broker on each restored database.

Database master key

There's a conflict on my understanding of the database master key in
SQL Server 2005.
Reading from the book, it mention database master key is optional.
But when I try to create certificate, it prompt me error:
======
Msg 15581, Level 16, State 1, Line 1
Please create a master key in the database or open the master key in
the session before performing this operation.
======
After I create the master key, then create the certificate again, it
works fine. It means that the database master key is mandatory IF I'm
going to use encryption.
Anyone can clarify this?
Thanks.
wodoy.peter
You need to use the ENCRYPTION BY PASSWORD clause.
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1170010225.064765.151440@.l53g2000cwa.googlegr oups.com...
> There's a conflict on my understanding of the database master key in
> SQL Server 2005.
> Reading from the book, it mention database master key is optional.
> But when I try to create certificate, it prompt me error:
> ======
> Msg 15581, Level 16, State 1, Line 1
> Please create a master key in the database or open the master key in
> the session before performing this operation.
> ======
> After I create the master key, then create the certificate again, it
> works fine. It means that the database master key is mandatory IF I'm
> going to use encryption.
> Anyone can clarify this?
> Thanks.
>
> wodoy.peter
>
|||Thanks.
On Jan 29, 12:46 pm, "Mike C#" <x...@.xyz.com> wrote:[vbcol=seagreen]
> You need to use the ENCRYPTION BY PASSWORD clause.
> "wodoy.peter" <wodoy.pe...@.gmail.com> wrote in messagenews:1170010225.064765.151440@.l53g2000cwa.g ooglegroups.com...
>
>
>
>

Database master key

There's a conflict on my understanding of the database master key in
SQL Server 2005.
Reading from the book, it mention database master key is optional.
But when I try to create certificate, it prompt me error:
====== Msg 15581, Level 16, State 1, Line 1
Please create a master key in the database or open the master key in
the session before performing this operation.
======
After I create the master key, then create the certificate again, it
works fine. It means that the database master key is mandatory IF I'm
going to use encryption.
Anyone can clarify this?
Thanks.
wodoy.peterYou need to use the ENCRYPTION BY PASSWORD clause.
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1170010225.064765.151440@.l53g2000cwa.googlegroups.com...
> There's a conflict on my understanding of the database master key in
> SQL Server 2005.
> Reading from the book, it mention database master key is optional.
> But when I try to create certificate, it prompt me error:
> ======> Msg 15581, Level 16, State 1, Line 1
> Please create a master key in the database or open the master key in
> the session before performing this operation.
> ======> After I create the master key, then create the certificate again, it
> works fine. It means that the database master key is mandatory IF I'm
> going to use encryption.
> Anyone can clarify this?
> Thanks.
>
> wodoy.peter
>|||Thanks.
On Jan 29, 12:46 pm, "Mike C#" <x...@.xyz.com> wrote:
> You need to use the ENCRYPTION BY PASSWORD clause.
> "wodoy.peter" <wodoy.pe...@.gmail.com> wrote in messagenews:1170010225.064765.151440@.l53g2000cwa.googlegroups.com...
>
> > There's a conflict on my understanding of the database master key in
> > SQL Server 2005.
> > Reading from the book, it mention database master key is optional.
> > But when I try to create certificate, it prompt me error:
> > ======> > Msg 15581, Level 16, State 1, Line 1
> > Please create a master key in the database or open the master key in
> > the session before performing this operation.
> > ======> > After I create the master key, then create the certificate again, it
> > works fine. It means that the database master key is mandatory IF I'm
> > going to use encryption.
> > Anyone can clarify this?
> > Thanks.
> > wodoy.peter- Hide quoted text -- Show quoted text -

Database master key

Hi

For encytion in database, the following statement is correct?

I found it in msdn book.

The database master key is not mandatory if you want to encrypt data.

I think the SQL Server Security forum is more appropriate for your question.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

Yes that is correct. The database master key is not required for data encryption.

Sung

|||

When I tried creating certificate without having database master key, it complained.

After I created, it worked fine.

|||

If you don't have a database master key, you will need to specify password encryption. You can use the following to create the certificate:

create certificate <cert_name> encryption by password = '<your_password>' with subject = '<subject>'

For symmetric keys, asymmetric keys, and certificates, you can always optionally specify and use a password for encryption instead of using the encryption hierarchy. Please check BOL for the proper syntax. They should be similar to " ENCRYPTION BY PASSWORD = '<password>' ". This is very useful for restricting access to this data from database owner (who otherwise automatically has rights to the database master key).

We also have two built-ins that allow you to completely bypass using keys: EncryptByPassPhrase and DecryptByPassPhrase. This is NOT the recommended method for encrypting data, but it may be useful depending on your use scenarios.

Hope this helps, please let me know if you have any further questions,

Sung

Database master key

There's a conflict on my understanding of the database master key in
SQL Server 2005.
Reading from the book, it mention database master key is optional.
But when I try to create certificate, it prompt me error:
======
Msg 15581, Level 16, State 1, Line 1
Please create a master key in the database or open the master key in
the session before performing this operation.
======
After I create the master key, then create the certificate again, it
works fine. It means that the database master key is mandatory IF I'm
going to use encryption.
Anyone can clarify this?
Thanks.
wodoy.peterYou need to use the ENCRYPTION BY PASSWORD clause.
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1170010225.064765.151440@.l53g2000cwa.googlegroups.com...
> There's a conflict on my understanding of the database master key in
> SQL Server 2005.
> Reading from the book, it mention database master key is optional.
> But when I try to create certificate, it prompt me error:
> ======
> Msg 15581, Level 16, State 1, Line 1
> Please create a master key in the database or open the master key in
> the session before performing this operation.
> ======
> After I create the master key, then create the certificate again, it
> works fine. It means that the database master key is mandatory IF I'm
> going to use encryption.
> Anyone can clarify this?
> Thanks.
>
> wodoy.peter
>|||Thanks.
On Jan 29, 12:46 pm, "Mike C#" <x...@.xyz.com> wrote:[vbcol=seagreen]
> You need to use the ENCRYPTION BY PASSWORD clause.
> "wodoy.peter" <wodoy.pe...@.gmail.com> wrote in messagenews:1170010225.0647
65.151440@.l53g2000cwa.googlegroups.com...
>
>
>
>
>
>
>