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...
>
>
>
>
>
>
>

Database Mail Confusion

I am confused about the Service Broker and Database mail configuration. In
the MS Book SQL 2005 Implementation and Maintenance Training Kit, on page 63,
Chapter 2 Lesson tool it states â'The default Database Mail host database is
msdb, and Service Broker is enabled on msdb by default.â'.
However when I use, SQL Server Surface Area Configuration, Surface Area
Configuration for Features, and click Service Broker, it states â'This
instance does not have a Service Broker endpoint.â'
The Database Mail has been configured. However, it is not sending e-mail
nor returning any error messages in the Database Mail Logs.
I set up the mail using Lesson 2 from the book, step by step. After it
didnâ't work, I worked thru the Troubleshooting on-line help. So,
1. should there be a service broker endpoint setup somewhere
2. any suggestions to what I do next?
ThanksPlease ignore...I had an e-mail rule for the sql service account that moved
the test e-mail out of my mail box.
"brymer28303" wrote:
> I am confused about the Service Broker and Database mail configuration. In
> the MS Book SQL 2005 Implementation and Maintenance Training Kit, on page 63,
> Chapter 2 Lesson tool it states â'The default Database Mail host database is
> msdb, and Service Broker is enabled on msdb by default.â'.
> However when I use, SQL Server Surface Area Configuration, Surface Area
> Configuration for Features, and click Service Broker, it states â'This
> instance does not have a Service Broker endpoint.â'
> The Database Mail has been configured. However, it is not sending e-mail
> nor returning any error messages in the Database Mail Logs.
> I set up the mail using Lesson 2 from the book, step by step. After it
> didnâ't work, I worked thru the Troubleshooting on-line help. So,
> 1. should there be a service broker endpoint setup somewhere
> 2. any suggestions to what I do next?
> Thanks
>

Database Mail Confusion

I am confused about the Service Broker and Database mail configuration. In
the MS Book SQL 2005 Implementation and Maintenance Training Kit, on page 63
,
Chapter 2 Lesson tool it states “The default Database Mail host database i
s
msdb, and Service Broker is enabled on msdb by default.”.
However when I use, SQL Server Surface Area Configuration, Surface Area
Configuration for Features, and click Service Broker, it states “This
instance does not have a Service Broker endpoint.”
The Database Mail has been configured. However, it is not sending e-mail
nor returning any error messages in the Database Mail Logs.
I set up the mail using Lesson 2 from the book, step by step. After it
didn’t work, I worked thru the Troubleshooting on-line help. So,
1. should there be a service broker endpoint setup somewhere
2. any suggestions to what I do next?
ThanksPlease ignore...I had an e-mail rule for the sql service account that moved
the test e-mail out of my mail box.
"brymer28303" wrote:

> I am confused about the Service Broker and Database mail configuration. I
n
> the MS Book SQL 2005 Implementation and Maintenance Training Kit, on page
63,
> Chapter 2 Lesson tool it states “The default Database Mail host database
is
> msdb, and Service Broker is enabled on msdb by default.”.
> However when I use, SQL Server Surface Area Configuration, Surface Area
> Configuration for Features, and click Service Broker, it states “This
> instance does not have a Service Broker endpoint.”
> The Database Mail has been configured. However, it is not sending e-mail
> nor returning any error messages in the Database Mail Logs.
> I set up the mail using Lesson 2 from the book, step by step. After it
> didn’t work, I worked thru the Troubleshooting on-line help. So,
> 1. should there be a service broker endpoint setup somewhere
> 2. any suggestions to what I do next?
> Thanks
>

Database Mail Confusion

I am confused about the Service Broker and Database mail configuration. In
the MS Book SQL 2005 Implementation and Maintenance Training Kit, on page 63,
Chapter 2 Lesson tool it states “The default Database Mail host database is
msdb, and Service Broker is enabled on msdb by default.”.
However when I use, SQL Server Surface Area Configuration, Surface Area
Configuration for Features, and click Service Broker, it states “This
instance does not have a Service Broker endpoint.”
The Database Mail has been configured. However, it is not sending e-mail
nor returning any error messages in the Database Mail Logs.
I set up the mail using Lesson 2 from the book, step by step. After it
didn’t work, I worked thru the Troubleshooting on-line help. So,
1.should there be a service broker endpoint setup somewhere
2.any suggestions to what I do next?
Thanks
Please ignore...I had an e-mail rule for the sql service account that moved
the test e-mail out of my mail box.
"brymer28303" wrote:

> I am confused about the Service Broker and Database mail configuration. In
> the MS Book SQL 2005 Implementation and Maintenance Training Kit, on page 63,
> Chapter 2 Lesson tool it states “The default Database Mail host database is
> msdb, and Service Broker is enabled on msdb by default.”.
> However when I use, SQL Server Surface Area Configuration, Surface Area
> Configuration for Features, and click Service Broker, it states “This
> instance does not have a Service Broker endpoint.”
> The Database Mail has been configured. However, it is not sending e-mail
> nor returning any error messages in the Database Mail Logs.
> I set up the mail using Lesson 2 from the book, step by step. After it
> didn’t work, I worked thru the Troubleshooting on-line help. So,
> 1.should there be a service broker endpoint setup somewhere
> 2.any suggestions to what I do next?
> Thanks
>