Encryption related overflow?

Recently restored a SQL 2000 database to a SQL 2005 Server. The database contains a series of user stored procs (one calls upto 5 other sps) which are all encrypted using 'WITH ENCRYPTION' clause. When run on SQL 2000 Server this runs without error. When run on SQL 2005 Server it reports an error:

Msg 565, Level 18, State 1, Procedure SPDM_MP1_SOURCE59, Line 5143

A stack overflow occurred in the server while compiling the query. Please simplify the query.

Investigating the error line reported does not reveal any problems with the sp and the error line number reported is not always consistent.

However, altering the stored procs so they are not encrypted and it all runs without error. Is there a compatibility issue running SPs encrypted on 2000 on a 2005 Server?

moving to engine forum, someone should be able to help.|||ok, how about sql security forum.|||

Can you try to repro this issue after starting the server with the -y565 argument, to have it produce a stack dump? Once you have the dump, please report this issue at http://connect.microsoft.com/feedback/default.aspx?SiteID=68 and attach the dump file to the report.

Thanks
Laurentiu

|||

Hello,

We encountered the same problem. It also depends on the computer. The same process with the same data can fail on a server and run correctly on a laptop.

A work-around is to make the stored procedure smaller. How can we be sure that the stored procedure is small enough that the problem is not produced at the client? What is the limit on the total lines of a stored procedure when using "With Encryption"?

Greetings

|||

Hello,

Example of a procedure:

CREATE PROCEDURE [dbo].[p_stack_overflow]

WITH ENCRYPTION

AS

CREATE TABLE #test_tmp2 (column0 INT, column1 INT, column2 INT, column3 INT, column4 INT, column5 INT, column6 INT, column7 INT, column8 INT, column9 INT, column10 INT, column11 INT, column12 INT, column13 INT, column14 INT, column15 INT, column16 INT, column17 INT, column18 INT, column19 INT, column20 INT, column21 INT, column22 INT, column23 INT, column24 INT, column25 INT, column26 INT, column27 INT, column28 INT, column29 INT, column30 INT, column31 INT, column32 INT, column33 INT, column34 INT, column35 INT, column36 INT, column37 INT, column38 INT, column39 INT, column40 INT, column41 INT, column42 INT, column43 INT, column44 INT, column45 INT, column46 INT, column47 INT, column48 INT, column49 INT, column50 INT, column51 INT, column52 INT, column53 INT, column54 INT, column55 INT, column56 INT, column57 INT, column58 INT, column59 INT, column60 INT, column61 INT, column62 INT, column63 INT, column64 INT, column65 INT, column66 INT, column67 INT, column68 INT, column69 INT, column70 INT, column71 INT, column72 INT, column73 INT, column74 INT, column75 INT, column76 INT, column77 INT, column78 INT, column79 INT, column80 INT, column81 INT, column82 INT, column83 INT, column84 INT, column85 INT, column86 INT, column87 INT, column88 INT, column89 INT, column90 INT, column91 INT, column92 INT, column93 INT, column94 INT, column95 INT, column96 INT, column97 INT, column98 INT, column99 INT, )

INSERT INTO #test_tmp2

VALUES (100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100)

This insert statement is written 3000 times in the procedure.

When executing the statement, SQL Server 2005 throws this error:

Msg 565, Level 18, State 1, Procedure p_stack_overflow, Line 973

A stack overflow occurred in the server while compiling the query. Please simplify the query.

Without encryption there is no problem. In SQL Server 2000 it works fine, even with encryption.

Using a while loop also solves the problem, but the case was produced as an example.

Thanks

Wouter

|||

Hello,

This is a known bug in SQL2005. It only affects encrypted modules that are very large in size; large enough to be the order of the stack size. Until the fix is released, you can workaround this issue by either not using encryption (if that is an option), or breaking up the module into smaller pieces.

Thanks

|||

Thanks for the answer.

The problem still remains that we don't know how many lines a procedure can contain on a specific configuration. Does this problem depend on hardware or software configuration?

Our product will be installed at the client in the near future. Can we presume this fix will be released in SP2?

Thanks

|||

This fix did not make it into SP2. You can consider contacting Customer Support Services and request a hotfix if this is blocking you.

Thanks

|||

Hi all,

Just for information, there was a fix available for this issue for 1 week.

Encryption related overflow?

Recently restored a SQL 2000 database to a SQL 2005 Server. The database contains a series of user stored procs (one calls upto 5 other sps) which are all encrypted using 'WITH ENCRYPTION' clause. When run on SQL 2000 Server this runs without error. When run on SQL 2005 Server it reports an error:

Msg 565, Level 18, State 1, Procedure SPDM_MP1_SOURCE59, Line 5143

A stack overflow occurred in the server while compiling the query. Please simplify the query.

Investigating the error line reported does not reveal any problems with the sp and the error line number reported is not always consistent.

However, altering the stored procs so they are not encrypted and it all runs without error. Is there a compatibility issue running SPs encrypted on 2000 on a 2005 Server?

moving to engine forum, someone should be able to help.|||ok, how about sql security forum.|||

Can you try to repro this issue after starting the server with the -y565 argument, to have it produce a stack dump? Once you have the dump, please report this issue at http://connect.microsoft.com/feedback/default.aspx?SiteID=68 and attach the dump file to the report.

Thanks
Laurentiu

|||

Hello,

We encountered the same problem. It also depends on the computer. The same process with the same data can fail on a server and run correctly on a laptop.

A work-around is to make the stored procedure smaller. How can we be sure that the stored procedure is small enough that the problem is not produced at the client? What is the limit on the total lines of a stored procedure when using "With Encryption"?

Greetings

|||

Hello,

Example of a procedure:

CREATE PROCEDURE [dbo].[p_stack_overflow]

WITH ENCRYPTION

AS

CREATE TABLE #test_tmp2 (column0 INT, column1 INT, column2 INT, column3 INT, column4 INT, column5 INT, column6 INT, column7 INT, column8 INT, column9 INT, column10 INT, column11 INT, column12 INT, column13 INT, column14 INT, column15 INT, column16 INT, column17 INT, column18 INT, column19 INT, column20 INT, column21 INT, column22 INT, column23 INT, column24 INT, column25 INT, column26 INT, column27 INT, column28 INT, column29 INT, column30 INT, column31 INT, column32 INT, column33 INT, column34 INT, column35 INT, column36 INT, column37 INT, column38 INT, column39 INT, column40 INT, column41 INT, column42 INT, column43 INT, column44 INT, column45 INT, column46 INT, column47 INT, column48 INT, column49 INT, column50 INT, column51 INT, column52 INT, column53 INT, column54 INT, column55 INT, column56 INT, column57 INT, column58 INT, column59 INT, column60 INT, column61 INT, column62 INT, column63 INT, column64 INT, column65 INT, column66 INT, column67 INT, column68 INT, column69 INT, column70 INT, column71 INT, column72 INT, column73 INT, column74 INT, column75 INT, column76 INT, column77 INT, column78 INT, column79 INT, column80 INT, column81 INT, column82 INT, column83 INT, column84 INT, column85 INT, column86 INT, column87 INT, column88 INT, column89 INT, column90 INT, column91 INT, column92 INT, column93 INT, column94 INT, column95 INT, column96 INT, column97 INT, column98 INT, column99 INT, )

INSERT INTO #test_tmp2

VALUES (100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100)

This insert statement is written 3000 times in the procedure.

When executing the statement, SQL Server 2005 throws this error:

Msg 565, Level 18, State 1, Procedure p_stack_overflow, Line 973

A stack overflow occurred in the server while compiling the query. Please simplify the query.

Without encryption there is no problem. In SQL Server 2000 it works fine, even with encryption.

Using a while loop also solves the problem, but the case was produced as an example.

Thanks

Wouter

|||

Hello,

This is a known bug in SQL2005. It only affects encrypted modules that are very large in size; large enough to be the order of the stack size. Until the fix is released, you can workaround this issue by either not using encryption (if that is an option), or breaking up the module into smaller pieces.

Thanks

|||

Thanks for the answer.

The problem still remains that we don't know how many lines a procedure can contain on a specific configuration. Does this problem depend on hardware or software configuration?

Our product will be installed at the client in the near future. Can we presume this fix will be released in SP2?

Thanks

|||

This fix did not make it into SP2. You can consider contacting Customer Support Services and request a hotfix if this is blocking you.

Thanks

|||

Hi all,

Just for information, there was a fix available for this issue for 1 week.

Encryption related overflow?

Recently restored a SQL 2000 database to a SQL 2005 Server. The database contains a series of user stored procs (one calls upto 5 other sps) which are all encrypted using 'WITH ENCRYPTION' clause. When run on SQL 2000 Server this runs without error. When run on SQL 2005 Server it reports an error:

Msg 565, Level 18, State 1, Procedure SPDM_MP1_SOURCE59, Line 5143

A stack overflow occurred in the server while compiling the query. Please simplify the query.

Investigating the error line reported does not reveal any problems with the sp and the error line number reported is not always consistent.

However, altering the stored procs so they are not encrypted and it all runs without error. Is there a compatibility issue running SPs encrypted on 2000 on a 2005 Server?

moving to engine forum, someone should be able to help.|||ok, how about sql security forum.|||

Can you try to repro this issue after starting the server with the -y565 argument, to have it produce a stack dump? Once you have the dump, please report this issue at http://connect.microsoft.com/feedback/default.aspx?SiteID=68 and attach the dump file to the report.

Thanks
Laurentiu

|||

Hello,

We encountered the same problem. It also depends on the computer. The same process with the same data can fail on a server and run correctly on a laptop.

A work-around is to make the stored procedure smaller. How can we be sure that the stored procedure is small enough that the problem is not produced at the client? What is the limit on the total lines of a stored procedure when using "With Encryption"?

Greetings

|||

Hello,

Example of a procedure:

CREATE PROCEDURE [dbo].[p_stack_overflow]

WITH ENCRYPTION

AS

CREATE TABLE #test_tmp2 (column0 INT, column1 INT, column2 INT, column3 INT, column4 INT, column5 INT, column6 INT, column7 INT, column8 INT, column9 INT, column10 INT, column11 INT, column12 INT, column13 INT, column14 INT, column15 INT, column16 INT, column17 INT, column18 INT, column19 INT, column20 INT, column21 INT, column22 INT, column23 INT, column24 INT, column25 INT, column26 INT, column27 INT, column28 INT, column29 INT, column30 INT, column31 INT, column32 INT, column33 INT, column34 INT, column35 INT, column36 INT, column37 INT, column38 INT, column39 INT, column40 INT, column41 INT, column42 INT, column43 INT, column44 INT, column45 INT, column46 INT, column47 INT, column48 INT, column49 INT, column50 INT, column51 INT, column52 INT, column53 INT, column54 INT, column55 INT, column56 INT, column57 INT, column58 INT, column59 INT, column60 INT, column61 INT, column62 INT, column63 INT, column64 INT, column65 INT, column66 INT, column67 INT, column68 INT, column69 INT, column70 INT, column71 INT, column72 INT, column73 INT, column74 INT, column75 INT, column76 INT, column77 INT, column78 INT, column79 INT, column80 INT, column81 INT, column82 INT, column83 INT, column84 INT, column85 INT, column86 INT, column87 INT, column88 INT, column89 INT, column90 INT, column91 INT, column92 INT, column93 INT, column94 INT, column95 INT, column96 INT, column97 INT, column98 INT, column99 INT, )

INSERT INTO #test_tmp2

VALUES (100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100)

This insert statement is written 3000 times in the procedure.

When executing the statement, SQL Server 2005 throws this error:

Msg 565, Level 18, State 1, Procedure p_stack_overflow, Line 973

A stack overflow occurred in the server while compiling the query. Please simplify the query.

Without encryption there is no problem. In SQL Server 2000 it works fine, even with encryption.

Using a while loop also solves the problem, but the case was produced as an example.

Thanks

Wouter

|||

Hello,

This is a known bug in SQL2005. It only affects encrypted modules that are very large in size; large enough to be the order of the stack size. Until the fix is released, you can workaround this issue by either not using encryption (if that is an option), or breaking up the module into smaller pieces.

Thanks

|||

Thanks for the answer.

The problem still remains that we don't know how many lines a procedure can contain on a specific configuration. Does this problem depend on hardware or software configuration?

Our product will be installed at the client in the near future. Can we presume this fix will be released in SP2?

Thanks

|||

This fix did not make it into SP2. You can consider contacting Customer Support Services and request a hotfix if this is blocking you.

Thanks

|||

Hi all,

Just for information, there was a fix available for this issue for 1 week.

Encryption related overflow?

Recently restored a SQL 2000 database to a SQL 2005 Server. The database contains a series of user stored procs (one calls upto 5 other sps) which are all encrypted using 'WITH ENCRYPTION' clause. When run on SQL 2000 Server this runs without error. When run on SQL 2005 Server it reports an error:

Msg 565, Level 18, State 1, Procedure SPDM_MP1_SOURCE59, Line 5143

A stack overflow occurred in the server while compiling the query. Please simplify the query.

Investigating the error line reported does not reveal any problems with the sp and the error line number reported is not always consistent.

However, altering the stored procs so they are not encrypted and it all runs without error. Is there a compatibility issue running SPs encrypted on 2000 on a 2005 Server?

moving to engine forum, someone should be able to help.|||ok, how about sql security forum.|||

Can you try to repro this issue after starting the server with the -y565 argument, to have it produce a stack dump? Once you have the dump, please report this issue at http://connect.microsoft.com/feedback/default.aspx?SiteID=68 and attach the dump file to the report.

Thanks
Laurentiu

|||

Hello,

We encountered the same problem. It also depends on the computer. The same process with the same data can fail on a server and run correctly on a laptop.

A work-around is to make the stored procedure smaller. How can we be sure that the stored procedure is small enough that the problem is not produced at the client? What is the limit on the total lines of a stored procedure when using "With Encryption"?

Greetings

|||

Hello,

Example of a procedure:

CREATE PROCEDURE [dbo].[p_stack_overflow]

WITH ENCRYPTION

AS

CREATE TABLE #test_tmp2 (column0 INT, column1 INT, column2 INT, column3 INT, column4 INT, column5 INT, column6 INT, column7 INT, column8 INT, column9 INT, column10 INT, column11 INT, column12 INT, column13 INT, column14 INT, column15 INT, column16 INT, column17 INT, column18 INT, column19 INT, column20 INT, column21 INT, column22 INT, column23 INT, column24 INT, column25 INT, column26 INT, column27 INT, column28 INT, column29 INT, column30 INT, column31 INT, column32 INT, column33 INT, column34 INT, column35 INT, column36 INT, column37 INT, column38 INT, column39 INT, column40 INT, column41 INT, column42 INT, column43 INT, column44 INT, column45 INT, column46 INT, column47 INT, column48 INT, column49 INT, column50 INT, column51 INT, column52 INT, column53 INT, column54 INT, column55 INT, column56 INT, column57 INT, column58 INT, column59 INT, column60 INT, column61 INT, column62 INT, column63 INT, column64 INT, column65 INT, column66 INT, column67 INT, column68 INT, column69 INT, column70 INT, column71 INT, column72 INT, column73 INT, column74 INT, column75 INT, column76 INT, column77 INT, column78 INT, column79 INT, column80 INT, column81 INT, column82 INT, column83 INT, column84 INT, column85 INT, column86 INT, column87 INT, column88 INT, column89 INT, column90 INT, column91 INT, column92 INT, column93 INT, column94 INT, column95 INT, column96 INT, column97 INT, column98 INT, column99 INT, )

INSERT INTO #test_tmp2

VALUES (100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100)

This insert statement is written 3000 times in the procedure.

When executing the statement, SQL Server 2005 throws this error:

Msg 565, Level 18, State 1, Procedure p_stack_overflow, Line 973

A stack overflow occurred in the server while compiling the query. Please simplify the query.

Without encryption there is no problem. In SQL Server 2000 it works fine, even with encryption.

Using a while loop also solves the problem, but the case was produced as an example.

Thanks

Wouter

|||

Hello,

This is a known bug in SQL2005. It only affects encrypted modules that are very large in size; large enough to be the order of the stack size. Until the fix is released, you can workaround this issue by either not using encryption (if that is an option), or breaking up the module into smaller pieces.

Thanks

|||

Thanks for the answer.

The problem still remains that we don't know how many lines a procedure can contain on a specific configuration. Does this problem depend on hardware or software configuration?

Our product will be installed at the client in the near future. Can we presume this fix will be released in SP2?

Thanks

|||

This fix did not make it into SP2. You can consider contacting Customer Support Services and request a hotfix if this is blocking you.

Thanks

|||

Hi all,

Just for information, there was a fix available for this issue for 1 week.

Encryption and database restore

Hi

Can anyone help?

I have a database using encryption with symmetric keys created using asymmetric keys.

I backed up this database and restored it on another machine along with the service master key.

I can read the encrypted data fine. But all the encrypted stored procs that are associated with the application that uses the data cannot read the data, they just return Nulls.

If I regenerate all the encrypted stored procs from script they work fine again.

Is there a way to restore the database without having to regenerate all the encrypted procs?

You are probably missing this sequence after you restore the database:

1) open master key using password

2) ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Your script probably creates the database master key too, so you end up recreating it with an encryption of the service master key, and everything works, but you only need the two commands above, to make your database work after restore.

See http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx for additional information on the master keys.

Thanks
Laurentiu

|||Thanks will give it a try and get back to you|||

Hi

Well now I am really puzzled. I created the same database on 2 machines then restored both databases back on to a third machine. I can read the eccrypted data from one of the backups but not from the other. All 3 machines are running XP and SQL 2005

On the both databases with encrypted data I ran the following

OPEN MASTER KEY DECRYPTION BY PASSWORD = asecretpassword

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Then ran the script to read my encrypted data

Just done some more checking. If i take a database that I created on one machine and can read on another then restore it onto the 3rd then I cannot read it on the 3rd.

(This is also the machine I created the database on that I could not read on other machines)

When I try to open my symmetric key I get the following error

This is after opening and altering the master key as above

Msg 15466, Level 16, State 1, Line 1

An error occurred during decryption.

It seems that their is something incompatable on the third machine yet it is running the same version of OS and SQL as the other machines

|||

Are your machines in the same time-zone? Is the time properly synchronized between them? There is a known issue with restoring databases on a machine that has the time set "in the past". See page 2 of this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=177863&SiteID=1. But I'm not sure you're hitting this issue.

Can you post the statement that you execute before getting the error? Also, can you tell me some details about the key: what algorithm are you using and how did you create the key.

Just to make sure I understand the scenario: what you see is that whatever you encrypt on the third machine, you cannot decrypt on the other machines, and what you encrypt on the other machines, you cannot decrypt on this third machines. Otherwise, for the other machines, encryption and decryption works ok when restoring databases from one to another. Is this correct?

Thanks
Laurentiu

|||

Also, can you execute winver on all three machines and post the version line including the build number information for all three of them? I just need the line under Microsoft Windows.

Thanks
Laurentiu

|||

Hi Laurentiu

I have not tried writing encrypting any data on the databases I cannot read encrypted data from, I am more concerned with being able to read the data but I doubt that I would be able to write data as the errors i get indicate that the asymetric keys cannot be opened.

All machines are in the same time zone and domain except the 2003 server which is in Holland whilst the rest are in the UK and we are not using certificates. So I dont think it is a time zone thing

I have created another database build on a 4th machine running 2003 Server and copied a backup of that database onto my XP machines and I cannot read the encrypted data from the 2003 backup on any of the XP machines I have tried this on.

However if I restore the master key from the any machine where the database was created with the force option I can read the data.
The restore of the master key gives me a message saying that it could not open the asymmetric keys

I run the following bit of code to read the data

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Asecretpassword'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Go

This appears to run OK

Open Symmetric Key NoKeySymetricKey
Decryption by ASYMMETRIC KEY NoKey

SELECT top 10
CONVERT(VARCHAR(50),
DecryptbyKey(EncryptedNo)) AS ClearNumber
From MyTable

Close Symmetric Key NoKeySymetricKey

I get the following error

Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.

(10 row(s) affected)
Msg 15315, Level 16, State 1, Line 8
The key 'NoKeySymetricKey' is not open. Please open the key before using it.

Winver from the machines I have tried this on

(Mine)
Version 5.1 (Build 2600.xpsp_sp2_grd.050301-1519 : Service Pack 2)

(Andrews)
Version 5.1 (Build 2600.xpsp_sp2_grd.050301-1519 : Service Pack 2)

(Chris's)
Version 5.1 (Build 2600.xpsp_sp2_rtm.04083-2158 : Service Pack 2)
Can backup and restore between above machines OK but not restore from the 2 below

(Build Machine)
Version 5.1 (Build 2600.xpsp_sp2_rtm.04083-2158 : Service Pack 2)
The database built on this machine cannot be read on the first 2 of the 3 above machines;
have not tried it on the server below or on the machine runing Version 5.1 (Build 2600.xpsp_sp2_rtm.04083-2158 : Service Pack 2) (Chris's)

2003 Server
Version 5.2 (Build 3790.srv03_sp1_rtm.050324-1447 : Service Pack 1)
The database created on this machine can not be read on any of the other machines

I built the keys on all machines/databases with the same bit of code below

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'asecretpassword'

CREATE ASYMMETRIC KEY PassKey
WITH ALGORITHM = RSA_512

CREATE ASYMMETRIC KEY NoKey
WITH ALGORITHM = RSA_2048

CREATE SYMMETRIC KEY NoKeySymetricKey
WITH ALGORITHM = Triple_Des
ENCRYPTION BY ASYMMETRIC KEY NoKey

CREATE SYMMETRIC KEY PassSymetricKey
WITH ALGORITHM = Triple_Des
ENCRYPTION BY ASYMMETRIC KEY PassKey

Hi

Just tried the restore of database from another XP maxhine and it fails. If I force restore the master key it works OK. However if I try to restore without the force option I get the following message

Msg 15320, Level 16, State 2, Line 2

An error occurred while decrypting asymmetric key PassKey that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable.

|||

So it looks like the asymmetric keys cannot be decrypted unless you force restore the master key. It's strange that the master key appears to be valid, given that you receive no error when you try to open it.

Can you try encrypting something with the master key immediately after restore? Try creating another asymmetric key, then use that asymmetric key to encrypt something and see if you get any errors.

I'd also like to know if the same problem affects certificates.

Also, note that the timezone issue that I mentioned earlier affects symmetric keys, it's not certificate related. But now I'm pretty positive you're seeing a different issue.

Here's something else I'd like to ask: can you create a simple test database with a master key (password = BugCrypto90, let's say), an asymmetric key, and a symmetric key, on both your machine and the build machine. The databases should not contain any other data and you should see the same issue for them when trying to restore them on the other machine (i.e., opening the symmetric key should fail with a decryption error). Then submit a bug report at http://lab.msdn.microsoft.com/productfeedback/, and attach compressed backups of these two databases to the bug. Add a link to this thread in the description and ask for the bug to be assigned to me. If you use a different password for the keys, mention it in the description as well. This way, I can also try to reproduce this problem on our machines.

Thanks
Laurentiu

|||

Hi Laurentiu

I will submit a bug report as asked.

However in the meantime I tried creating a new asymetric key on my restored database and encrypting some data. Everything went OK I was able to both write and read encrypted data created with my newly created asymetric key even though I couldnt read my old data.

Martin

|||

Hi Again

I have logged the problem as a bug report but I dont know if you are going to be able to reproduce it.

I tried as you asked and created a test database on my machine and the build machine and created keys using the same method I used on my live database and I can backup the databases and restore them on different machines and open the symetric keys OK.

However I still have a problem with my database - will continue testing and let you know if I can re-produce it

Martin

|||

Can you also look into whether the problem is specific to asymmetric keys or can be reproed with certificates as well?

Thanks for taking the time to file the report.

Laurentiu

|||

I've received the bug report, but there are no attachments. Have you attached the test databases when you filed the report?

Thanks
Laurentiu

|||

Hi

I have attached the databases again.

|||

I do not see the attachments yet. Have you pressed the "Add Attachment" button to actually attach the files? You need to press that button after you browse-select the file.

Thanks
Laurentiu

|||

Hi

will try again

It may be the security settings on our proxy server; they are pretty tight.

If it doesnt work let me know and I will do it from home

Martin

Encryption and database restore

Hi

Can anyone help?

I have a database using encryption with symmetric keys created using asymmetric keys.

I backed up this database and restored it on another machine along with the service master key.

I can read the encrypted data fine. But all the encrypted stored procs that are associated with the application that uses the data cannot read the data, they just return Nulls.

If I regenerate all the encrypted stored procs from script they work fine again.

Is there a way to restore the database without having to regenerate all the encrypted procs?

You are probably missing this sequence after you restore the database:

1) open master key using password

2) ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Your script probably creates the database master key too, so you end up recreating it with an encryption of the service master key, and everything works, but you only need the two commands above, to make your database work after restore.

See http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx for additional information on the master keys.

Thanks
Laurentiu

|||Thanks will give it a try and get back to you|||

Hi

Well now I am really puzzled. I created the same database on 2 machines then restored both databases back on to a third machine. I can read the eccrypted data from one of the backups but not from the other. All 3 machines are running XP and SQL 2005

On the both databases with encrypted data I ran the following

OPEN MASTER KEY DECRYPTION BY PASSWORD = asecretpassword

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Then ran the script to read my encrypted data

Just done some more checking. If i take a database that I created on one machine and can read on another then restore it onto the 3rd then I cannot read it on the 3rd.

(This is also the machine I created the database on that I could not read on other machines)

When I try to open my symmetric key I get the following error

This is after opening and altering the master key as above

Msg 15466, Level 16, State 1, Line 1

An error occurred during decryption.

It seems that their is something incompatable on the third machine yet it is running the same version of OS and SQL as the other machines

|||

Are your machines in the same time-zone? Is the time properly synchronized between them? There is a known issue with restoring databases on a machine that has the time set "in the past". See page 2 of this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=177863&SiteID=1. But I'm not sure you're hitting this issue.

Can you post the statement that you execute before getting the error? Also, can you tell me some details about the key: what algorithm are you using and how did you create the key.

Just to make sure I understand the scenario: what you see is that whatever you encrypt on the third machine, you cannot decrypt on the other machines, and what you encrypt on the other machines, you cannot decrypt on this third machines. Otherwise, for the other machines, encryption and decryption works ok when restoring databases from one to another. Is this correct?

Thanks
Laurentiu

|||

Also, can you execute winver on all three machines and post the version line including the build number information for all three of them? I just need the line under Microsoft Windows.

Thanks
Laurentiu

|||

Hi Laurentiu

I have not tried writing encrypting any data on the databases I cannot read encrypted data from, I am more concerned with being able to read the data but I doubt that I would be able to write data as the errors i get indicate that the asymetric keys cannot be opened.

All machines are in the same time zone and domain except the 2003 server which is in Holland whilst the rest are in the UK and we are not using certificates. So I dont think it is a time zone thing

I have created another database build on a 4th machine running 2003 Server and copied a backup of that database onto my XP machines and I cannot read the encrypted data from the 2003 backup on any of the XP machines I have tried this on.

However if I restore the master key from the any machine where the database was created with the force option I can read the data.
The restore of the master key gives me a message saying that it could not open the asymmetric keys

I run the following bit of code to read the data

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Asecretpassword'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Go

This appears to run OK

Open Symmetric Key NoKeySymetricKey
Decryption by ASYMMETRIC KEY NoKey

SELECT top 10
CONVERT(VARCHAR(50),
DecryptbyKey(EncryptedNo)) AS ClearNumber
From MyTable

Close Symmetric Key NoKeySymetricKey

I get the following error

Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.

(10 row(s) affected)
Msg 15315, Level 16, State 1, Line 8
The key 'NoKeySymetricKey' is not open. Please open the key before using it.

Winver from the machines I have tried this on

(Mine)
Version 5.1 (Build 2600.xpsp_sp2_grd.050301-1519 : Service Pack 2)

(Andrews)
Version 5.1 (Build 2600.xpsp_sp2_grd.050301-1519 : Service Pack 2)

(Chris's)
Version 5.1 (Build 2600.xpsp_sp2_rtm.04083-2158 : Service Pack 2)
Can backup and restore between above machines OK but not restore from the 2 below

(Build Machine)
Version 5.1 (Build 2600.xpsp_sp2_rtm.04083-2158 : Service Pack 2)
The database built on this machine cannot be read on the first 2 of the 3 above machines;
have not tried it on the server below or on the machine runing Version 5.1 (Build 2600.xpsp_sp2_rtm.04083-2158 : Service Pack 2) (Chris's)

2003 Server
Version 5.2 (Build 3790.srv03_sp1_rtm.050324-1447 : Service Pack 1)
The database created on this machine can not be read on any of the other machines

I built the keys on all machines/databases with the same bit of code below

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'asecretpassword'

CREATE ASYMMETRIC KEY PassKey
WITH ALGORITHM = RSA_512

CREATE ASYMMETRIC KEY NoKey
WITH ALGORITHM = RSA_2048

CREATE SYMMETRIC KEY NoKeySymetricKey
WITH ALGORITHM = Triple_Des
ENCRYPTION BY ASYMMETRIC KEY NoKey

CREATE SYMMETRIC KEY PassSymetricKey
WITH ALGORITHM = Triple_Des
ENCRYPTION BY ASYMMETRIC KEY PassKey

Hi

Just tried the restore of database from another XP maxhine and it fails. If I force restore the master key it works OK. However if I try to restore without the force option I get the following message

Msg 15320, Level 16, State 2, Line 2

An error occurred while decrypting asymmetric key PassKey that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable.

|||

So it looks like the asymmetric keys cannot be decrypted unless you force restore the master key. It's strange that the master key appears to be valid, given that you receive no error when you try to open it.

Can you try encrypting something with the master key immediately after restore? Try creating another asymmetric key, then use that asymmetric key to encrypt something and see if you get any errors.

I'd also like to know if the same problem affects certificates.

Also, note that the timezone issue that I mentioned earlier affects symmetric keys, it's not certificate related. But now I'm pretty positive you're seeing a different issue.

Here's something else I'd like to ask: can you create a simple test database with a master key (password = BugCrypto90, let's say), an asymmetric key, and a symmetric key, on both your machine and the build machine. The databases should not contain any other data and you should see the same issue for them when trying to restore them on the other machine (i.e., opening the symmetric key should fail with a decryption error). Then submit a bug report at http://lab.msdn.microsoft.com/productfeedback/, and attach compressed backups of these two databases to the bug. Add a link to this thread in the description and ask for the bug to be assigned to me. If you use a different password for the keys, mention it in the description as well. This way, I can also try to reproduce this problem on our machines.

Thanks
Laurentiu

|||

Hi Laurentiu

I will submit a bug report as asked.

However in the meantime I tried creating a new asymetric key on my restored database and encrypting some data. Everything went OK I was able to both write and read encrypted data created with my newly created asymetric key even though I couldnt read my old data.

Martin

|||

Hi Again

I have logged the problem as a bug report but I dont know if you are going to be able to reproduce it.

I tried as you asked and created a test database on my machine and the build machine and created keys using the same method I used on my live database and I can backup the databases and restore them on different machines and open the symetric keys OK.

However I still have a problem with my database - will continue testing and let you know if I can re-produce it

Martin

|||

Can you also look into whether the problem is specific to asymmetric keys or can be reproed with certificates as well?

Thanks for taking the time to file the report.

Laurentiu

|||

I've received the bug report, but there are no attachments. Have you attached the test databases when you filed the report?

Thanks
Laurentiu

|||

Hi

I have attached the databases again.

|||

I do not see the attachments yet. Have you pressed the "Add Attachment" button to actually attach the files? You need to press that button after you browse-select the file.

Thanks
Laurentiu

|||

Hi

will try again

It may be the security settings on our proxy server; they are pretty tight.

If it doesnt work let me know and I will do it from home

Martin