Empty Column: procs

Showing posts with label procs. Show all posts

Tuesday, March 27, 2012

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

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?

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

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.

Greetings

|||

Hello,

Example of a procedure:

CREATE PROCEDURE [dbo].[p_stack_overflow]

WITH ENCRYPTION

INSERT INTO #test_tmp2

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,

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?

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

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.

Greetings

|||

Hello,

Example of a procedure:

CREATE PROCEDURE [dbo].[p_stack_overflow]

WITH ENCRYPTION

INSERT INTO #test_tmp2

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,

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?

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

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.

Greetings

|||

Hello,

Example of a procedure:

CREATE PROCEDURE [dbo].[p_stack_overflow]

WITH ENCRYPTION

INSERT INTO #test_tmp2

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,

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 question, sql 2000

Situation: We are required to encrypt procs in part of our production
environment. We have the
unencrypted procs in test environment. As a sanity check (when a problem
arises), is there a way
to verify that an encrypted proc (ctext) in production matches a specific
unencrypted version?
If the procs were first encrypted with Alter Proc ... With Encryption, then
the same script can be
run again, and before/after syscomments.ctext values compared. But this is
a destructive test
that potentially changes prod environment.
Is there some benign alternative?
Thanks,
Craig Hessel
WPS, Madison, WICraig,
Two possibilities:
1) Restore db to development machine. Unencrypt stored procs there and check them
( unencryption algo is straightforward but destructive )
2) Check the length of the procedures in syscomments - most edits will change this
Regards
AJ
"Craig Hessel" <craig_hessel@.hotmail.com> wrote in message news:10181dttf4ta204@.corp.supernews.com...
> Situation: We are required to encrypt procs in part of our production
> environment. We have the
> unencrypted procs in test environment. As a sanity check (when a problem
> arises), is there a way
> to verify that an encrypted proc (ctext) in production matches a specific
> unencrypted version?
> If the procs were first encrypted with Alter Proc ... With Encryption, then
> the same script can be
> run again, and before/after syscomments.ctext values compared. But this is
> a destructive test
> that potentially changes prod environment.
> Is there some benign alternative?
> Thanks,
> Craig Hessel
> WPS, Madison, WI
>sql

Encryption question, sql 2000

quote:

> Situation: We are required to encrypt procs in part of our production
> environment. We have the
> unencrypted procs in test environment. As a sanity check (when a problem
> arises), is there a way
> to verify that an encrypted proc (ctext) in production matches a specific
> unencrypted version?
> If the procs were first encrypted with Alter Proc ... With Encryption, the
n
> the same script can be
> run again, and before/after syscomments.ctext values compared. But this i
s
> a destructive test
> that potentially changes prod environment.
> Is there some benign alternative?
> Thanks,
> Craig Hessel
> WPS, Madison, WI
>

Monday, March 19, 2012

Encrypted Stored Procs.

Hi Everybody,
I have a quick question as follows:
Does it take longer to create a stored procedure when it is encrypted versus
when it is not encrypted?
Thanks.
Regards,
Soumitra Banerjee
Hi Soumitra,
To create an encrypted Stored procedure include the keyword "With
encryption".
For more details visit the following URL :
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
create_4hk5.asp
Example :
The WITH ENCRYPTION clause hides the text of a stored procedure from users.
This example creates an encrypted procedure, uses the sp_helptext system
stored procedure to get information on that encrypted procedure, and then
attempts to get information on that procedure directly from the syscomments
table.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'encrypt_this' AND type = 'P')
DROP PROCEDURE encrypt_this
GO
USE pubs
GO
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Do you mean create as in CREATE PROCEDURE.. or in executing?
Persumably there is some tiny overhead in encrypting the text for syscomments but you won't notice it. I've never seen nor head of performance issues. Out of curiosity, why the question?
Alicia
http://www.sqlporn.co.uk

Encrypted Stored Procs.

Hi Everybody,
I have a quick question as follows:
Does it take longer to create a stored procedure when it is encrypted versus
when it is not encrypted?
Thanks.
Regards,
Soumitra BanerjeeHi Soumitra,
To create an encrypted Stored procedure include the keyword "With
encryption".
For more details visit the following URL :
http://msdn.microsoft.com/library/d...-us/tsqlref/ts_
create_4hk5.asp
Example :
The WITH ENCRYPTION clause hides the text of a stored procedure from users.
This example creates an encrypted procedure, uses the sp_helptext system
stored procedure to get information on that encrypted procedure, and then
attempts to get information on that procedure directly from the syscomments
table.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'encrypt_this' AND type = 'P')
DROP PROCEDURE encrypt_this
GO
USE pubs
GO
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.|||Do you mean create as in CREATE PROCEDURE.. or in executing?
Persumably there is some tiny overhead in encrypting the text for syscomment
s but you won't notice it. I've never seen nor head of performance issues. O
ut of curiosity, why the question?
Alicia
http://www.sqlporn.co.uk

Encrypted Stored Procs.

Hi Everybody,
I have a quick question as follows:
Does it take longer to create a stored procedure when it is encrypted versus
when it is not encrypted?
Thanks.
Regards,
Soumitra BanerjeeHi Soumitra,
To create an encrypted Stored procedure include the keyword "With
encryption".
For more details visit the following URL :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
create_4hk5.asp
Example :
The WITH ENCRYPTION clause hides the text of a stored procedure from users.
This example creates an encrypted procedure, uses the sp_helptext system
stored procedure to get information on that encrypted procedure, and then
attempts to get information on that procedure directly from the syscomments
table.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'encrypt_this' AND type = 'P')
DROP PROCEDURE encrypt_this
GO
USE pubs
GO
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.|||Do you mean create as in CREATE PROCEDURE.. or in executing
Persumably there is some tiny overhead in encrypting the text for syscomments but you won't notice it. I've never seen nor head of performance issues. Out of curiosity, why the question
Alici
http://www.sqlporn.co.uk|||It does take 'slightly' longer yes.
Personally I feel that Encryping SP is a waste of time as
the encryption SQL Server uses is not very good and can be
broken quite easily.
J
>--Original Message--
>Hi Everybody,
>I have a quick question as follows:
>Does it take longer to create a stored procedure when it
is encrypted versus
>when it is not encrypted?
>Thanks.
>Regards,
>Soumitra Banerjee
>
>.
>

Encrypted Stored Procs

Hey yall

I ran into something interesting today and was wondering how one would do this. I have some 3rd party stored procs and one was kicking out a truncate error so I took it upon myself to investigate the stored proc that was kicking out this error. So when I tried viewing the sp, I received an error:*****Encrypted object is not transferable, and script could not be generated.***** and then it brings up a blank editing screen.

First I believe this was a custom error message as it just doesnt seem like the way SQL Server would have presented it.

So how would someone prohibit viewing of a stored proc like this?

Thanksthe create procedure statement has a WITH ENCRYPTION arguement. the encryption is one way and there is stuff on the internet to crack the encryption in sql 2k if do a little googling.|||Neat, thanks

So I guess if I did that to one of mine I would be locked out as well unless I had an unencrypted backup?|||well hopefully you manage your source code outside of sql server with something like visual source safe, source gears vault or team foundation server.

Tuesday, March 27, 2012

Encryption related overflow?

Encryption related overflow?

Encryption related overflow?

Encryption related overflow?

Encryption question, sql 2000

Encryption question, sql 2000

Monday, March 19, 2012

Encrypted Stored Procs.

Encrypted Stored Procs.

Encrypted Stored Procs.

Encrypted Stored Procs

Empty Column

Blog Archive

About Me