Encryption

I have tried to encrypt by certificate and by symmetric key. In all cases the decryption comes back as null. Any ideas why?

I have used the code from a learnin tree course and the encryption works OK. I have also added a grant to the certificate to the login

Please post the code you used. Without it we would have to guess every possible problem.

Encrypting DECRYPTED Stored Procedure.....

Hi
At the moment i don't remember but some times back i found an stored procedure that can DECRYPT
all ENCRYPTED objects in sqlServer2000 ( i will try to put URL here) such as stored procedures,Triggers and even View(s).
Now i'm writing a very confidential StoredProcedure and i don't want to be hack in this way.
Is teher any way to prevent this.Has this Bug been fixed by any of Service Packs.?

Thanks in advance.
Kind Regards.

I think you can easily decrypt SQL encryptions because the SQL rand function is not really random this is not just SQL Server because there are infinite numbers between 6 and 13 but all SQL random functions Oracle and MySQL included can only give you whole numbers which makes it easy to be decrypted. And Microsoft tells you it is not deterministic and not to use it to encrypt anything of value.

That said if you don't want your stored proc decrypted go into the first link and download the free book from Microsoft with ready to use encryption code convert that to CLR stored proc so you know the content cannot be decrypted. The second link is a cleaned up version of the free code in Microsoft book, there are encoding problems with the original code. Hope this helps.

http://msdn2.microsoft.com/en-us/library/aa302415.aspx

http://www.obviex.com/Resources/Samples.aspx

Encrypted DB -- Restore Question

Hi,

I have a DB in which I encrypt a few columns in a table. I am using a Symmetric key to encrypt and decrypt the data. When I take a back up of this DB and restore on another server ... my decryption doesn't work. I have dropped the master key and recreated it with same password and that didn't help either.

What are the rules to follow when we restore a db on a different server that has encrypted data ? Thanks.

You will need to open the master key in the new database once ,so that a copy of the same is saved in the master database. After that the decryption should work. Let me know if this helped?|||How do I open Master Key? Thanks!|||

Hi,

You shouldn't have to do anything special to restore a db with encrypted values. The one thing you may need to do is to alter the master key of the database and re-encrypt with the service master key. You can do this with:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'your_password_here';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

CLOSE MASTER KEY;

(for more info, take a look http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx).

EDIT: whoops, wrong link, sorry, this should now have the correct link.

|||

I tried and that didn't help ...

I used OPEN MASTER KEY DECRYPTION BY PASSWORD = 'my pwd'

|||

OPEN MASTER KEY DECRYPTION BY PASSWORD = ['password']

|||What's the specific error you are seeing? Are you getting "NULL" back as the decrypted value or are you receiving a server error?|||Yes ... I get "NULL" and when I look at the data in the column, it does have encrypted data. Thanks for your help!!!|||Does the login that you are using having appropriate rights... I mean on the symmetric keys, certificates.|||Yes .. I have logged in as SysAdmin ...|||

One other tying you can try, before you decrypt, run "SELECT * FROM sys.openkeys" to verify that the symmetric key is ready for decryption.

|||Are the operating system on both the machines the same, because certain encryption types like AES are supported on certain type of OS.|||I looked at the owner info again ... and the original DB was created by "sa" account itself ... where as the attached DB is owned by another user that is of SysAdmin group. Does that matter for decryption? I really appreciate your help!!|||

The owner of the db shouldn't matter for decryption (especially since you confirmed you can select from the table without problems) as long as the user has not been denied view rights for the symmetric key. You also need rights on whatever is encrypting the symmetric key.

If the symmetric key shows up in sys.openkeys though, you should be fine as it means the key has been opened and ready for decryption.

Can you share the SQL you are using to decrypt the data?

|||Just a suggestion, try restore and backup instead of attachdb.

Encrypted database deployment

I have an encrypeted database that I need to move from one development
environment to another. If I back it up and restore it to the new
environment, I get decryption errors, even though the Master Keys have been
created using the same password CREATE MASTER KEY ENCRYPTION BY PASSWORD = .
.
How can I move the database to the new environment?
Thanks.This issue is that the password is used to encrypt the database master key -
it isn't the database master key so you now have two different master keys
encrypted with the same password which doesn't help you a lot. If you
haven't already blown away the master key, you can re-encrypt it with the
new service master key like this:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Gerhard" <acsla@.community.nospam> wrote in message
news:0F2B0D28-2A97-4E60-B6B8-1F821177CA87@.microsoft.com...
>I have an encrypeted database that I need to move from one development
> environment to another. If I back it up and restore it to the new
> environment, I get decryption errors, even though the Master Keys have
> been
> created using the same password CREATE MASTER KEY ENCRYPTION BY PASSWORD =
> ...
> How can I move the database to the new environment?
> Thanks.|||Thanks. I did still have the password, so ran the below which ran without
error.
However, I still get:
Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.
when I try to open a symmetric key.
Can you help?
Thanks.
"Roger Wolter[MSFT]" wrote:

> This issue is that the password is used to encrypt the database master key
-
> it isn't the database master key so you now have two different master keys
> encrypted with the same password which doesn't help you a lot. If you
> haven't already blown away the master key, you can re-encrypt it with the
> new service master key like this:
> OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
> ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Gerhard" <acsla@.community.nospam> wrote in message
> news:0F2B0D28-2A97-4E60-B6B8-1F821177CA87@.microsoft.com...
>
>|||Did you run a CREATE MASTER KEY in this database before trying the OPEN
operation? If so, you just opened the new key you created - not the key the
symmetric keys were encrypted with. You may have to go back to your backup
that contains the original key.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Gerhard" <acsla@.community.nospam> wrote in message
news:54EF76EA-F4ED-44CD-BB7C-3EC017139AAD@.microsoft.com...[vbcol=seagreen]
> Thanks. I did still have the password, so ran the below which ran without
> error.
> However, I still get:
> Msg 15466, Level 16, State 1, Line 1
> An error occurred during decryption.
> when I try to open a symmetric key.
> Can you help?
> Thanks.
> "Roger Wolter[MSFT]" wrote:
>|||There's more information on OPEN here:
http://msdn2.microsoft.com/en-us/library/ms174433.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:evRJycVxGHA.3892@.TK2MSFTNGP03.phx.gbl...
> Did you run a CREATE MASTER KEY in this database before trying the OPEN
> operation? If so, you just opened the new key you created - not the key
> the symmetric keys were encrypted with. You may have to go back to your
> backup that contains the original key.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Gerhard" <acsla@.community.nospam> wrote in message
> news:54EF76EA-F4ED-44CD-BB7C-3EC017139AAD@.microsoft.com...
>|||I restored the database, ran the open and alter commands as below, but still
get the error:
Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.
Still looking for a way to get this successfully moved...
"Roger Wolter[MSFT]" wrote:

> There's more information on OPEN here:
> http://msdn2.microsoft.com/en-us/library/ms174433.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:evRJycVxGHA.3892@.TK2MSFTNGP03.phx.gbl...
>
>|||I'll bow out at this point. This should have worked if the problem was
indeed an issue with your mater key encryption so I assume something else is
going on. Maybe someone smarter than me can see what's happening.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Gerhard" <acsla@.community.nospam> wrote in message
news:F60CD778-4E38-4209-8E88-E960CB530579@.microsoft.com...[vbcol=seagreen]
>I restored the database, ran the open and alter commands as below, but
>still
> get the error:
> Msg 15466, Level 16, State 1, Line 1
> An error occurred during decryption.
> Still looking for a way to get this successfully moved...
>
> "Roger Wolter[MSFT]" wrote:
>|||I'm also getting the same error now on another database that was working in
this new environment, even when I restore a backup...
Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.
"Roger Wolter[MSFT]" wrote:

> There's more information on OPEN here:
> http://msdn2.microsoft.com/en-us/library/ms174433.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:evRJycVxGHA.3892@.TK2MSFTNGP03.phx.gbl...
>
>|||Did you back up all your Service Master Keys and Database Master Keys, like
BOL suggests? If so, you might see if you can successfully restore them
without the FORCE option.
"Gerhard" <acsla@.community.nospam> wrote in message
news:7D9F7228-93F7-48E9-B776-2E90EDB54C0F@.microsoft.com...[vbcol=seagreen]
> I'm also getting the same error now on another database that was working
> in
> this new environment, even when I restore a backup...
> Msg 15466, Level 16, State 1, Line 1
> An error occurred during decryption.
>
> "Roger Wolter[MSFT]" wrote:
>|||Hello,
The issue might be related to SQL service account. Is the service account
the same on both SQL development/test server instances? If not, you may try
to change the service account to the same domain user to test. The
following link is for your reference:
http://groups.google.com/group/micr...ls/browse_threa
d/thread/8596eefe3ed0edfc/a0979b976b3c7a57?lnk=st&q=Encrypted+database+peter
+yang&rnum=2&hl=en#a0979b976b3c7a57
If you have any update, please feel free to let's know. We look forward to
your reply.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Enabling file growth

I am trying to keep my log file at unrestricted file growth, but every time
I save the settings it switches back to restricted growth with a default
file size. I tried to keep it restricted, and change the size llimit, but
it changed back to it'd default limit. Why is this changing automaically by
itself.
I want to keep file growth enabled at 5% with unrestricted growth, but it
keeps changing back to 5% and resticted file growth with a default size. I
don't understand why it changes!!!!ALTER DATABASE [database_name] MODIFY FILE ( NAME = 'file_name',
FILEGROWTH = 5%)
What do you get when you run the above statement with your
database_name and file_name changed?
On Jun 14, 9:35 am, "Matt Fritz" <mafr...@.state.pa.us> wrote:
> I am trying to keep my log file at unrestricted file growth, but every time
> I save the settings it switches back to restricted growth with a default
> file size. I tried to keep it restricted, and change the size llimit, but
> it changed back to it'd default limit. Why is this changing automaically by
> itself.
> I want to keep file growth enabled at 5% with unrestricted growth, but it
> keeps changing back to 5% and resticted file growth with a default size. I
> don't understand why it changes!!!!
On Jun 14, 9:35 am, "Matt Fritz" <mafr...@.state.pa.us> wrote:
> I am trying to keep my log file at unrestricted file growth, but every time
> I save the settings it switches back to restricted growth with a default
> file size. I tried to keep it restricted, and change the size llimit, but
> it changed back to it'd default limit. Why is this changing automaically by
> itself.
> I want to keep file growth enabled at 5% with unrestricted growth, but it
> keeps changing back to 5% and resticted file growth with a default size. I
> don't understand why it changes!!!!|||On 14 Jun, 14:35, "Matt Fritz" <mafr...@.state.pa.us> wrote:
> I am trying to keep my log file at unrestricted file growth, but every time
> I save the settings it switches back to restricted growth with a default
> file size. I tried to keep it restricted, and change the size llimit, but
> it changed back to it'd default limit. Why is this changing automaically by
> itself.
> I want to keep file growth enabled at 5% with unrestricted growth, but it
> keeps changing back to 5% and resticted file growth with a default size. I
> don't understand why it changes!!!!
Why do you want to grow the log at 5%? Log growth is something you
should try to avoid. My advice is to set it to a sufficient size so
that it won't grow.
If you need more help, please post the actual ALTER statements you
ran. Don't rely on the management tools to do it correctly for you.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Enabling file growth

I am trying to keep my log file at unrestricted file growth, but every time
I save the settings it switches back to restricted growth with a default
file size. I tried to keep it restricted, and change the size llimit, but
it changed back to it'd default limit. Why is this changing automaically by
itself.
I want to keep file growth enabled at 5% with unrestricted growth, but it
keeps changing back to 5% and resticted file growth with a default size. I
don't understand why it changes!!!!ALTER DATABASE [database_name] MODIFY FILE ( NAME = 'file_name',
FILEGROWTH = 5%)
What do you get when you run the above statement with your
database_name and file_name changed?
On Jun 14, 9:35 am, "Matt Fritz" <mafr...@.state.pa.us> wrote:
> I am trying to keep my log file at unrestricted file growth, but every tim
e
> I save the settings it switches back to restricted growth with a default
> file size. I tried to keep it restricted, and change the size llimit, but
> it changed back to it'd default limit. Why is this changing automaically
by
> itself.
> I want to keep file growth enabled at 5% with unrestricted growth, but it
> keeps changing back to 5% and resticted file growth with a default size.
I
> don't understand why it changes!!!!
On Jun 14, 9:35 am, "Matt Fritz" <mafr...@.state.pa.us> wrote:
> I am trying to keep my log file at unrestricted file growth, but every tim
e
> I save the settings it switches back to restricted growth with a default
> file size. I tried to keep it restricted, and change the size llimit, but
> it changed back to it'd default limit. Why is this changing automaically
by
> itself.
> I want to keep file growth enabled at 5% with unrestricted growth, but it
> keeps changing back to 5% and resticted file growth with a default size.
I
> don't understand why it changes!!!!|||On 14 Jun, 14:35, "Matt Fritz" <mafr...@.state.pa.us> wrote:
> I am trying to keep my log file at unrestricted file growth, but every tim
e
> I save the settings it switches back to restricted growth with a default
> file size. I tried to keep it restricted, and change the size llimit, but
> it changed back to it'd default limit. Why is this changing automaically
by
> itself.
> I want to keep file growth enabled at 5% with unrestricted growth, but it
> keeps changing back to 5% and resticted file growth with a default size.
I
> don't understand why it changes!!!!
Why do you want to grow the log at 5%? Log growth is something you
should try to avoid. My advice is to set it to a sufficient size so
that it won't grow.
If you need more help, please post the actual ALTER statements you
ran. Don't rely on the management tools to do it correctly for you.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Enabling file growth

I am trying to keep my log file at unrestricted file growth, but every time
I save the settings it switches back to restricted growth with a default
file size. I tried to keep it restricted, and change the size llimit, but
it changed back to it'd default limit. Why is this changing automaically by
itself.
I want to keep file growth enabled at 5% with unrestricted growth, but it
keeps changing back to 5% and resticted file growth with a default size. I
don't understand why it changes!!!!
ALTER DATABASE [database_name] MODIFY FILE ( NAME = 'file_name',
FILEGROWTH = 5%)
What do you get when you run the above statement with your
database_name and file_name changed?
On Jun 14, 9:35 am, "Matt Fritz" <mafr...@.state.pa.us> wrote:
> I am trying to keep my log file at unrestricted file growth, but every time
> I save the settings it switches back to restricted growth with a default
> file size. I tried to keep it restricted, and change the size llimit, but
> it changed back to it'd default limit. Why is this changing automaically by
> itself.
> I want to keep file growth enabled at 5% with unrestricted growth, but it
> keeps changing back to 5% and resticted file growth with a default size. I
> don't understand why it changes!!!!
On Jun 14, 9:35 am, "Matt Fritz" <mafr...@.state.pa.us> wrote:
> I am trying to keep my log file at unrestricted file growth, but every time
> I save the settings it switches back to restricted growth with a default
> file size. I tried to keep it restricted, and change the size llimit, but
> it changed back to it'd default limit. Why is this changing automaically by
> itself.
> I want to keep file growth enabled at 5% with unrestricted growth, but it
> keeps changing back to 5% and resticted file growth with a default size. I
> don't understand why it changes!!!!
|||On 14 Jun, 14:35, "Matt Fritz" <mafr...@.state.pa.us> wrote:
> I am trying to keep my log file at unrestricted file growth, but every time
> I save the settings it switches back to restricted growth with a default
> file size. I tried to keep it restricted, and change the size llimit, but
> it changed back to it'd default limit. Why is this changing automaically by
> itself.
> I want to keep file growth enabled at 5% with unrestricted growth, but it
> keeps changing back to 5% and resticted file growth with a default size. I
> don't understand why it changes!!!!
Why do you want to grow the log at 5%? Log growth is something you
should try to avoid. My advice is to set it to a sufficient size so
that it won't grow.
If you need more help, please post the actual ALTER statements you
ran. Don't rely on the management tools to do it correctly for you.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx