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 in SSIS Package

Hello,

I want to import data from a excel source to SQL Server 2005 using SSIS. Among all the calls columns to be imported, there is 1 column which needs to be encrypted using asymmetric encryption and stored in destination table. Can anybody guide me how to program SSIS package using encryption function.

JatinShah wrote:

Hello,

I want to import data from a excel source to SQL Server 2005 using SSIS. Among all the calls columns to be imported, there is 1 column which needs to be encrypted using asymmetric encryption and stored in destination table. Can anybody guide me how to program SSIS package using encryption function.

I think Donald Farmer's book contains some information on how to do this. You'll be able to find it at all the usual places.

-Jamie

|||

You would need to use the Script Component, so you can use VB.Net to do the work.

Have you found out how to write the encryption functions in VB.Net, if not try this-

Walkthrough: Encrypting and Decrypting Strings in Visual Basic
(http://msdn2.microsoft.com/en-us/library/ms172831.aspx)

Then just wrap that into a Script Component.

|||

Hello Jamie,

Could you please get me the name of the book.

Thank You

Jatin Shah

|||

JatinShah wrote:

Hello Jamie,

Could you please get me the name of the book.

Thank You

Jatin Shah

http://amazon.com/s/ref=nb_ss_gw/102-7891523-4086513?url=search-alias%3Daps&field-keywords=donald+farmer

Encryption in SSIS Package

Hello,

I want to import data from a excel source to SQL Server 2005 using SSIS. Among all the calls columns to be imported, there is 1 column which needs to be encrypted using asymmetric encryption and stored in destination table. Can anybody guide me how to program SSIS package using encryption function.

JatinShah wrote:

Hello,

I want to import data from a excel source to SQL Server 2005 using SSIS. Among all the calls columns to be imported, there is 1 column which needs to be encrypted using asymmetric encryption and stored in destination table. Can anybody guide me how to program SSIS package using encryption function.

I think Donald Farmer's book contains some information on how to do this. You'll be able to find it at all the usual places.

-Jamie

|||

You would need to use the Script Component, so you can use VB.Net to do the work.

Have you found out how to write the encryption functions in VB.Net, if not try this-

Walkthrough: Encrypting and Decrypting Strings in Visual Basic
(http://msdn2.microsoft.com/en-us/library/ms172831.aspx)

Then just wrap that into a Script Component.

|||

Hello Jamie,

Could you please get me the name of the book.

Thank You

Jatin Shah

|||

JatinShah wrote:

Hello Jamie,

Could you please get me the name of the book.

Thank You

Jatin Shah

http://amazon.com/s/ref=nb_ss_gw/102-7891523-4086513?url=search-alias%3Daps&field-keywords=donald+farmer

Encrpyt

Hi
What is the best method to encrypt data in tables?
Lets say if special program or user calls select * from names
then all data is visible and normal, but all athers get "crap" with select *
from names
;)
Best Regards;
Mex
Hello,
Take a look into Symmetric or Asymmetric encryptions in SQL 2005.The below
URL details various encryption methods and algorithems
which is available in SQL Server.
http://www.microsoft.com/technet/itshowcase/content/sqldatsec.mspx
Thanks
Hari
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uNYS7XSdHHA.4344@.TK2MSFTNGP02.phx.gbl...
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select
> * from names
>
> ;)
> Best Regards;
> Mex
>
|||let me explain what i want
this "encrpyt" function is needed for web application.
lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
and hackers can use sql injection attack
to get data from database.
when data is encrypted even with sqlinjection hackers can't get sensitive
data.
sry my english is not very good
Mex
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1175523112.383268.101970@.b75g2000hsg.googlegr oups.com...
> On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
> Do you really want to return "crap" to some users? I suspect not. I
> think your real requirement is to deny user access to data certain
> data. You can do that using GRANT / DENY and no encryption is
> required.
> Encryption is not a substitute for data access control.
> --
> 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
> --
>
|||Hi
"Redivivus" wrote:

> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
> sry my english is not very good
> Mex
>
Hari has suggested using SQL 2005 encryption features, but you have not said
if you are using SQL 2005!
If you have left yourself vulnerable to SQL Injection attacks then the
hacker may be able to find out what encryption keys are being used or in a
worse case if the victim procedure has opened the keys he may still be able
to see the data through the injection anyhow. This could apply to any method
of server based encryption, you may want to read
http://blogs.msdn.com/lcris/archive/2006/11/30/who-needs-encryption.aspx
Part of your solution should be to make sure that all code is reviewed and a
coding standards implemented and maybe justification should be required when
it is necessary to use dynamic SQL. You may want to read the following
http://www.sommarskog.se/dynamic_sql.html#good_practices
HTH
John
|||On 2 Apr, 17:01, "Redivivus" <meelis.lil...@.deltmar.ee> wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
>
You are mistaken. If you allow arbitrary code execution through SQL
injection then anything could be possible within the security context
of the connection. If the authentication layer for your encryption
(password, certificate or some other method) is compromised as a
result then your encryption is worthless. Encryption is no substitute
for controlling data access.
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
|||"Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
news:ew3rnAUdHHA.1080@.TK2MSFTNGP02.phx.gbl...
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad
> code and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
Sure they can. Because if you have programmers that bad, you'll also do
encryption that poorly.
Ok, granted, I do agree that some encryption is a good idea, but I'd spend
more time with better hiring. :-)
Note, regardless, you still do NOT want to encrypt every column. You really
can't get performance that way. (as in you'll get NONE).

> sry my english is not very good
> Mex
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1175523112.383268.101970@.b75g2000hsg.googlegr oups.com...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Encrpyt

Hi
What is the best method to encrypt data in tables?
Lets say if special program or user calls select * from names
then all data is visible and normal, but all athers get "crap" with select *
from names
;)
Best Regards;
MexHello,
Take a look into Symmetric or Asymmetric encryptions in SQL 2005.The below
URL details various encryption methods and algorithems
which is available in SQL Server.
http://www.microsoft.com/technet/it.../sqldatsec.mspx
Thanks
Hari
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uNYS7XSdHHA.4344@.TK2MSFTNGP02.phx.gbl...
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select
> * from names
>
> ;)
> Best Regards;
> Mex
>|||On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select
*
> from names
>
Do you really want to return "crap" to some users? I suspect not. I
think your real requirement is to deny user access to data certain
data. You can do that using GRANT / DENY and no encryption is
required.
Encryption is not a substitute for data access control.
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
--|||let me explain what i want
this "encrpyt" function is needed for web application.
lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
and hackers can use sql injection attack
to get data from database.
when data is encrypted even with sqlinjection hackers can't get sensitive
data.
sry my english is not very good
Mex
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1175523112.383268.101970@.b75g2000hsg.googlegroups.com...
> On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
> Do you really want to return "crap" to some users? I suspect not. I
> think your real requirement is to deny user access to data certain
> data. You can do that using GRANT / DENY and no encryption is
> required.
> Encryption is not a substitute for data access control.
> --
> 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
> --
>|||Hi
"Redivivus" wrote:

> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad co
de
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
> sry my english is not very good
> Mex
>
Hari has suggested using SQL 2005 encryption features, but you have not said
if you are using SQL 2005!
If you have left yourself vulnerable to SQL Injection attacks then the
hacker may be able to find out what encryption keys are being used or in a
worse case if the victim procedure has opened the keys he may still be able
to see the data through the injection anyhow. This could apply to any method
of server based encryption, you may want to read
http://blogs.msdn.com/lcris/archive...encryption.aspx
Part of your solution should be to make sure that all code is reviewed and a
coding standards implemented and maybe justification should be required when
it is necessary to use dynamic SQL. You may want to read the following
http://www.sommarskog.se/dynamic_sq...#good_practices
HTH
John|||On 2 Apr, 17:01, "Redivivus" <meelis.lil...@.deltmar.ee> wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad co
de
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
>
You are mistaken. If you allow arbitrary code execution through SQL
injection then anything could be possible within the security context
of the connection. If the authentication layer for your encryption
(password, certificate or some other method) is compromised as a
result then your encryption is worthless. Encryption is no substitute
for controlling data access.
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
--|||"Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
news:ew3rnAUdHHA.1080@.TK2MSFTNGP02.phx.gbl...
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad
> code and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
Sure they can. Because if you have programmers that bad, you'll also do
encryption that poorly.
Ok, granted, I do agree that some encryption is a good idea, but I'd spend
more time with better hiring. :-)
Note, regardless, you still do NOT want to encrypt every column. You really
can't get performance that way. (as in you'll get NONE).

> sry my english is not very good
> Mex
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1175523112.383268.101970@.b75g2000hsg.googlegroups.com...
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Encrpyt

Hi
What is the best method to encrypt data in tables?
Lets say if special program or user calls select * from names
then all data is visible and normal, but all athers get "crap" with select *
from names
;)
Best Regards;
MexHello,
Take a look into Symmetric or Asymmetric encryptions in SQL 2005.The below
URL details various encryption methods and algorithems
which is available in SQL Server.
http://www.microsoft.com/technet/itshowcase/content/sqldatsec.mspx
Thanks
Hari
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uNYS7XSdHHA.4344@.TK2MSFTNGP02.phx.gbl...
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select
> * from names
>
> ;)
> Best Regards;
> Mex
>|||On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select *
> from names
>
Do you really want to return "crap" to some users? I suspect not. I
think your real requirement is to deny user access to data certain
data. You can do that using GRANT / DENY and no encryption is
required.
Encryption is not a substitute for data access control.
--
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
--|||let me explain what i want
this "encrpyt" function is needed for web application.
lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
and hackers can use sql injection attack
to get data from database.
when data is encrypted even with sqlinjection hackers can't get sensitive
data.
sry my english is not very good :)
Mex
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1175523112.383268.101970@.b75g2000hsg.googlegroups.com...
> On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
>> Hi
>> What is the best method to encrypt data in tables?
>> Lets say if special program or user calls select * from names
>> then all data is visible and normal, but all athers get "crap" with
>> select *
>> from names
> Do you really want to return "crap" to some users? I suspect not. I
> think your real requirement is to deny user access to data certain
> data. You can do that using GRANT / DENY and no encryption is
> required.
> Encryption is not a substitute for data access control.
> --
> 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
> --
>|||Hi
"Redivivus" wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
> sry my english is not very good :)
> Mex
>
Hari has suggested using SQL 2005 encryption features, but you have not said
if you are using SQL 2005!
If you have left yourself vulnerable to SQL Injection attacks then the
hacker may be able to find out what encryption keys are being used or in a
worse case if the victim procedure has opened the keys he may still be able
to see the data through the injection anyhow. This could apply to any method
of server based encryption, you may want to read
http://blogs.msdn.com/lcris/archive/2006/11/30/who-needs-encryption.aspx
Part of your solution should be to make sure that all code is reviewed and a
coding standards implemented and maybe justification should be required when
it is necessary to use dynamic SQL. You may want to read the following
http://www.sommarskog.se/dynamic_sql.html#good_practices
HTH
John|||On 2 Apr, 17:01, "Redivivus" <meelis.lil...@.deltmar.ee> wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
>
You are mistaken. If you allow arbitrary code execution through SQL
injection then anything could be possible within the security context
of the connection. If the authentication layer for your encryption
(password, certificate or some other method) is compromised as a
result then your encryption is worthless. Encryption is no substitute
for controlling data access.
--
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
--|||"Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
news:ew3rnAUdHHA.1080@.TK2MSFTNGP02.phx.gbl...
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad
> code and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
Sure they can. Because if you have programmers that bad, you'll also do
encryption that poorly.
Ok, granted, I do agree that some encryption is a good idea, but I'd spend
more time with better hiring. :-)
Note, regardless, you still do NOT want to encrypt every column. You really
can't get performance that way. (as in you'll get NONE).
> sry my english is not very good :)
> Mex
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1175523112.383268.101970@.b75g2000hsg.googlegroups.com...
>> On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
>> Hi
>> What is the best method to encrypt data in tables?
>> Lets say if special program or user calls select * from names
>> then all data is visible and normal, but all athers get "crap" with
>> select *
>> from names
>>
>> Do you really want to return "crap" to some users? I suspect not. I
>> think your real requirement is to deny user access to data certain
>> data. You can do that using GRANT / DENY and no encryption is
>> required.
>> Encryption is not a substitute for data access control.
>> --
>> 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
>> --
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Enable Error Handling When writing Custom Source Component /custom error Handling component.

1) We are writing a custome Source component for Oracle with OCI calls, Could some one please let me know how to Enable Error Handling for the Same,

2) Is it possible to write Custome Error Handeling Component for SSIS? if yes could you please help me on how to write it.

Thanks in advance.

1 - http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.pipeline.pipelinecomponent.adderroroutput.aspx

If this not what you want can you be more specific?

2 Yes. For a more detailed answer you'd have to describe the functionality you want.

Your questions are very broad, so I'm not sure exactly what you want to do.