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 on stored procedures problem

Help! We do not use encryption at all on our stored procedure code, but all
the sudden "WITH ENCRYPTION" has been appearing in our stored procedures by
itself. I don't know if it is a 3rd party tool we are useing or what (we use
a few sql tools from red-gate), but now we can not view or edit our own
stored procedures. all the DBO's are gettin "Error 20585: [SQL-DMO]/******
Encrypted object is not transferable, and script can not be generate.
*******/ what in the world can I do to fix this? because it is saying this
on our development database and our live database both, which are mirror
images of each other (well, dev is a mirror of live by a backup process that
occurs on a scheduled timeframe).
How can we see our code?!You mean you don't keep separate copies of your scripts? In that case:
http://www.securiteam.com/tools/6J00S003GU.html
[url]http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5[/ur
l]
Then get yourself a source control system if you don't already have
one.
David Portas
SQL Server MVP
--|||> but now we can not view or edit our own stored procedures.
Why is your code not in source control?

Encryption not supported on SQL Server - Error Message

Hi,
When I click on the MSDB node, under Stored Packages (in Object Explorer | my local server's Integration Services), I get the following error message:
Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)
I can, however successfully, enumerate File System packages.
I am running...
Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86)
May 24 2005 18:22:46
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
...on Windows Server 2003 SP1
Does anyone know what is causing this, and how to fix it?
Thanks,
krog
Appears to be related to the following post:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=19019
...so I need to install a certificate on my computer. Am now checking whether I can use the MakeCert utility to address this.
|||Did MakeCert resolve this - if so, would you please post the entire process - having same problem - thx|||If you installed your SQL Server 2005 as an named instance with a default SQL Server 2000 on the machine, you might need to change the SSIS configuration file to point the MSDB to the right instance. After changing the configuration file, you need to restart the SSIS service.|||To Add to Roro

Edit the config file MsDtsSrvr.ini.xml in C:\Program Files\Microsoft SQL Server\90\DTS\Binn as follwos:

=====================================
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>SQL_2K5_SERVER_NAME</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>
===============================

The original value for <ServerName> is just a dot (.)|||

Thanks a lot for your replies, people! I ran into the same problem and this thread helped me saved a bunch of time!! :)

|||

Yep, agree to that, thanks for the post. Restarted SSIS and it worked a treat.

Cheers - R.

|||What was resolution? Even I am also running into same problem as I have 2 SQL instances on same server

Encryption not supported on SQL Server - Error Message

Hi,
When I click on the MSDB node, under Stored Packages (in Object Explorer | my local server's Integration Services), I get the following error message:
Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)
I can, however successfully, enumerate File System packages.
I am running...
Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86)
May 24 2005 18:22:46
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
...on Windows Server 2003 SP1
Does anyone know what is causing this, and how to fix it?
Thanks,
krog
Appears to be related to the following post:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=19019
...so I need to install a certificate on my computer. Am now checking whether I can use the MakeCert utility to address this.
|||Did MakeCert resolve this - if so, would you please post the entire process - having same problem - thx|||If you installed your SQL Server 2005 as an named instance with a default SQL Server 2000 on the machine, you might need to change the SSIS configuration file to point the MSDB to the right instance. After changing the configuration file, you need to restart the SSIS service.|||To Add to Roro

Edit the config file MsDtsSrvr.ini.xml in C:\Program Files\Microsoft SQL Server\90\DTS\Binn as follwos:

=====================================
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>SQL_2K5_SERVER_NAME</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>
===============================

The original value for <ServerName> is just a dot (.)|||

Thanks a lot for your replies, people! I ran into the same problem and this thread helped me saved a bunch of time!! :)

|||

Yep, agree to that, thanks for the post. Restarted SSIS and it worked a treat.

Cheers - R.

|||What was resolution? Even I am also running into same problem as I have 2 SQL instances on same server

Encryption not supported on SQL Server - Error Message

Hi,
When I click on the MSDB node, under Stored Packages (in Object Explorer | my local server's Integration Services), I get the following error message:
Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)
I can, however successfully, enumerate File System packages.
I am running...
Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86)
May 24 2005 18:22:46
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
...on Windows Server 2003 SP1
Does anyone know what is causing this, and how to fix it?
Thanks,
krog
Appears to be related to the following post:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=19019
...so I need to install a certificate on my computer. Am now checking whether I can use the MakeCert utility to address this.
|||Did MakeCert resolve this - if so, would you please post the entire process - having same problem - thx|||If you installed your SQL Server 2005 as an named instance with a default SQL Server 2000 on the machine, you might need to change the SSIS configuration file to point the MSDB to the right instance. After changing the configuration file, you need to restart the SSIS service.|||To Add to Roro

Edit the config file MsDtsSrvr.ini.xml in C:\Program Files\Microsoft SQL Server\90\DTS\Binn as follwos:

=====================================
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>SQL_2K5_SERVER_NAME</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>
===============================

The original value for <ServerName> is just a dot (.)|||

Thanks a lot for your replies, people! I ran into the same problem and this thread helped me saved a bunch of time!! :)

|||

Yep, agree to that, thanks for the post. Restarted SSIS and it worked a treat.

Cheers - R.

|||What was resolution? Even I am also running into same problem as I have 2 SQL instances on same server

Encryption not supported on SQL Server - Error Message

Hi,
When I click on the MSDB node, under Stored Packages (in Object Explorer | my local server's Integration Services), I get the following error message:
Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)
I can, however successfully, enumerate File System packages.
I am running...
Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86)
May 24 2005 18:22:46
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
...on Windows Server 2003 SP1
Does anyone know what is causing this, and how to fix it?
Thanks,
krog
Appears to be related to the following post:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=19019
...so I need to install a certificate on my computer. Am now checking whether I can use the MakeCert utility to address this.
|||Did MakeCert resolve this - if so, would you please post the entire process - having same problem - thx|||If you installed your SQL Server 2005 as an named instance with a default SQL Server 2000 on the machine, you might need to change the SSIS configuration file to point the MSDB to the right instance. After changing the configuration file, you need to restart the SSIS service.|||To Add to Roro

Edit the config file MsDtsSrvr.ini.xml in C:\Program Files\Microsoft SQL Server\90\DTS\Binn as follwos:

=====================================
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>SQL_2K5_SERVER_NAME</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>
===============================

The original value for <ServerName> is just a dot (.)|||

Thanks a lot for your replies, people! I ran into the same problem and this thread helped me saved a bunch of time!! :)

|||

Yep, agree to that, thanks for the post. Restarted SSIS and it worked a treat.

Cheers - R.

|||What was resolution? Even I am also running into same problem as I have 2 SQL instances on same serversql

Encryption not supported on SQL Server - Error Message

Hi,
When I click on the MSDB node, under Stored Packages (in Object Explorer | my local server's Integration Services), I get the following error message:
Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)
I can, however successfully, enumerate File System packages.
I am running...
Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86)
May 24 2005 18:22:46
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
...on Windows Server 2003 SP1
Does anyone know what is causing this, and how to fix it?
Thanks,
krog
Appears to be related to the following post:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=19019
...so I need to install a certificate on my computer. Am now checking whether I can use the MakeCert utility to address this.
|||Did MakeCert resolve this - if so, would you please post the entire process - having same problem - thx|||If you installed your SQL Server 2005 as an named instance with a default SQL Server 2000 on the machine, you might need to change the SSIS configuration file to point the MSDB to the right instance. After changing the configuration file, you need to restart the SSIS service.|||To Add to Roro

Edit the config file MsDtsSrvr.ini.xml in C:\Program Files\Microsoft SQL Server\90\DTS\Binn as follwos:

=====================================
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>SQL_2K5_SERVER_NAME</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>
===============================

The original value for <ServerName> is just a dot (.)|||

Thanks a lot for your replies, people! I ran into the same problem and this thread helped me saved a bunch of time!! :)

|||

Yep, agree to that, thanks for the post. Restarted SSIS and it worked a treat.

Cheers - R.

|||What was resolution? Even I am also running into same problem as I have 2 SQL instances on same server

encryption ?

Hello,
My application will have more than 100 stored procedures and I want to use the "WITH ENCRYPTION" clause so that they are not visible to the my application's customer using EM. I will be writing the stored procedures in VS.Net server explorer to write the
se stored procedures. The problem is that if I add "WITH ENCRYP.." right there while coding the stored procedures then after saving the sto. proc. even I cannot access it, as it is encrypted. I will be using the "Create Script" utility of SQL Server to
create scripts for tables, stored procedures etc and then will execute this script on client's machines. Is there any way I can continue seeing the stored procedure but not the client.
Thanks
On Thu, 20 May 2004 12:56:03 -0700, dev wrote:

>Hello,
>My application will have more than 100 stored procedures and I want to use the "WITH ENCRYPTION" clause so that they are not visible to the my application's customer using EM. I will be writing the stored procedures in VS.Net server explorer to write th
ese stored procedures. The problem is that if I add "WITH ENCRYP.." right there while coding the stored procedures then after saving the sto. proc. even I cannot access it, as it is encrypted. I will be using the "Create Script" utility of SQL Server to
create scripts for tables, stored procedures etc and then will execute this script on client's machines. Is there any way I can continue seeing the stored procedure but not the client.
>Thanks
Hi Dev,
Two options:
1) Store your stored procedures as text files on your computers. Copy and
paste the code into and out of your development tool, unless it provides
load and save facilities (like Query Analyzer does).
2) Don't use encryption on your development database. Reexecute all
procedures with encryption on a seperate database, then ship that database
to your customers.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo,
In the 1st idea, do you mean save in separate .sql files ? The 2nd idea wont work for me I think because I won't be shipping the database, instead I will include the .sql files generated after using "Create Scripts".
dev

Encryption

Is there any way I can encrypt the stored procedure and that only I can only
decrypt it none of other user can do?
I can't use Ecryption reserve word becuase that can also be decrypted
through third party tool, is there any other way I can encrypt my stored
procedure with my own key or like that?
Thanks in advance.
Rogers wrote:
> Is there any way I can encrypt the stored procedure and that only I
> can only decrypt it none of other user can do?
> I can't use Ecryption reserve word becuase that can also be decrypted
> through third party tool, is there any other way I can encrypt my
> stored procedure with my own key or like that?
> Thanks in advance.
There are some thrid-party solutions for object encryption. In general,
you should be working/developing your stored procs (and other objects)
using external files and version control software like PVCS or
SourceSafe. It's not advisable to edit objects directly in the database.
If you use one of the third-party solutions, that should help secure the
objects, but I don't know if those tools support decryption or a more
permanent type of encryption.
I just came across one of these tools today (I have no experience with
the product or affiliation with them):
http://www.sql-shield.com/
David Gugick - SQL Server MVP
Quest Software
|||Thanks David.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%237MM5gwQGHA.4900@.TK2MSFTNGP09.phx.gbl...
> Rogers wrote:
> There are some thrid-party solutions for object encryption. In general,
> you should be working/developing your stored procs (and other objects)
> using external files and version control software like PVCS or SourceSafe.
> It's not advisable to edit objects directly in the database. If you use
> one of the third-party solutions, that should help secure the objects, but
> I don't know if those tools support decryption or a more permanent type of
> encryption.
> I just came across one of these tools today (I have no experience with the
> product or affiliation with them):
> http://www.sql-shield.com/
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>

Encrypting the configuration file values stored in SQL server

Hi All,

I have the following requirement. I need to store the password for the connection manager in the configuration file. The sink for the configuration file is SQL Server. Though the password field appears as "******" the actual value is being taken as ""******" itself. If i update the SQL server table with the correct value, then the package starts working. But, the password is shown as clear text.

If i write logic to encrypt the password column in the configuration table, is there a way to tell the SSIS execute engine to decrypt the password before using the same for making the connection.

Is there a place holder, where i can write the decrypt code so that the decrypted password can be sent to the execution engine?

Thanks In Advance,

Madhu

I think the short answer to this is no, and no code hooks either.

I think though that there is also an argument, that says it would not be more secure than what you have now. If you encrypt the data, you need to then secure the key. So what will you do to secure the key? Why not use strong security to secure the password data instead of worrying about how to secure the key? I accept that the encryption adds an extra step, but I'm not convinced it will actually be any safer.

|||I'm not sure if it's a good idea, but couldn't he create a script task to decrypt the password and reset the connection manager's connectionstring property before the connection manager is used in the package?|||

Yes and no. Some connections are used before your script task could run, such as connections used for logging.

How would you secure the key used to decrypt the password? You need to secure the encryption/decryption key, so why not just secure the password to start with?

|||DarrenSQLIS is right the recommended way to do this is to store the password in the connection. SSIS will automatically encrypt these so that they are not stored in cleartext.|||Thanks for the thoughts Darren. As suggested by you, way to go is to store the password in SQL server and make sure that the access to the configuration table is only for administrators.|||

Denise, I think you are talking about the package level encryption, protection levels and such like. Nice though it is, it is not very useful, as I think you should "externalise" any kind of security information.

Using package encryption becomes unfeasible when you have to migrate packages between environments. Configurations solve that migration issue, but don't give you the encryption that is often seen as a requirement for some organisations. I'd argue that is should not be a big deal, secure the password so you don't have to worry about the key, but often it is an internal "standard" that must be complied with.

Still we have the choice of package encryption, which is better than not!

Encrypting stroredprocedures

Anybody can help me for encrypting & decrypting Stored procedures...This is straight from the help file.

If you are creating a stored procedure and you want to make sure that the procedure definition cannot be viewed by other users, you can use the WITH ENCRYPTION clause. The procedure definition is then stored in an unreadable form.

After a stored procedure is encrypted, its definition cannot be decrypted and cannot be viewed by anyone, including the owner of the stored procedure or the system administrator

Here is an example:
CREATE PROCEDURE FactorAddRecord2
(
@.iSecurityId dINTEGER,
@.iFactor dNUMERIC_15_8
)
/**************************************************
DESCRIPTION:
AUTHOR:
DATE:
CHANGE LOG:
************************************************** /
WITH ENCRYPTION
AS
DECLARE @.vExchangeRateId dINTEGER;
BEGIN
BEGIN TRANSACTION
UPDATE
Security
SET
Current_Factor_N8 = @.iFactor
WHERE
Security_Id = @.iSecurityId;

INSERT INTO Data_Point_Hist
(As_Of_Date,
Security_Id,
Factor_N8)
VALUES
(dbo.fn_Today(),
@.iSecurityId,
@.iFactor);
COMMIT;
RETURN 0;

END|||oooops - was supposed to be a new post: DELETED

Encrypting Stored Procedure Code

Hi,
Is it possible to encrypt the code within a stored procedure in
Microsoft SQL Server?
My example is:
I've written a stored procedure. I don't want anyone to be able to
view the contents/code within this stored procedure unless I allow them
to see what is in it.
Thanks,
Darrin> Is it possible to encrypt the code within a stored procedure in
> Microsoft SQL Server?
Sure, but it is not very secure. A google search will yield plenty of
decryption algorithms. For example:
http://searchsqlserver.techtarget.c...1056869,00.html|||Once you encrypt a stored procedure you can not decrypt it (within SQL
Server)
So you can't show it to people who you want to show it to
example
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO
But like Aaron said this is very weak, there are some vb apps out there
that will decrypt this
http://sqlservercode.blogspot.com/|||I know there are decryptors out there for SQL2000 but do you know of nay tha
t
have been written for SQL2005?
We encrypt our procs mainly as a safeguard against accidentally altering
them but do need a decryptor when we need to edit them. Our upgrade to
SQL2005 will be delayed untill we have a decryptor.
-- cranfield, DBA
"Aaron Bertrand [SQL Server MVP]" wrote:

> Sure, but it is not very secure. A google search will yield plenty of
> decryption algorithms. For example:
> [url]http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1056869,00.html[/url
]
>
>|||>I know there are decryptors out there for SQL2000 but do you know of nay
>that
> have been written for SQL2005?
I will admit that I have only spent 5 minutes looking, but I have yet to
find where the encrypted text is stored, since sys.sql_modules.definition,
sys.syscomments.ctext and object_definition() all return NULL. My guess is,
to make the encoding a little more obscure, that they stuff this into
mssqlsystemresource, or hide it in some obscure system view. So, you may be
able to get to it, you may not.

> We encrypt our procs mainly as a safeguard against accidentally altering
> them but do need a decryptor when we need to edit them.
Isn't that what source control is for? And doesn't that defeat the purpose
of encrypting them in the first place? If someone can accidentally alter a
production procedure, they can also do accidentally after using a decryption
method to view the text. Encryption does not, and will never, solve the
problem of lack of adherence to proper process. My suggestion is to correct
the process.
A|||Thanks for the response. Yes, I fully appreciate the importance of source
control and am confident that our dev department use it properly.
As a production DBA, though, looking after 100+ SQL Servers, when called at
2am to fix a performance "issue", the Decryptor is essential as you dont hav
e
the time to delve into VSS. Also its essential when you need to compare an
existing proc to a proc in Source Safe.
With the move to SQL2005, we will, as suggested, need to look at our
process. It may mean moving away from encrypting procs.
-- cranfield, DBA
"Aaron Bertrand [SQL Server MVP]" wrote:

> I will admit that I have only spent 5 minutes looking, but I have yet to
> find where the encrypted text is stored, since sys.sql_modules.definition,
> sys.syscomments.ctext and object_definition() all return NULL. My guess i
s,
> to make the encoding a little more obscure, that they stuff this into
> mssqlsystemresource, or hide it in some obscure system view. So, you may
be
> able to get to it, you may not.
>
> Isn't that what source control is for? And doesn't that defeat the purpos
e
> of encrypting them in the first place? If someone can accidentally alter
a
> production procedure, they can also do accidentally after using a decrypti
on
> method to view the text. Encryption does not, and will never, solve the
> problem of lack of adherence to proper process. My suggestion is to corre
ct
> the process.
> A
>
>

Encrypting SPs and maybe triggers, how?

Don't want my sa to be able to tinker with Stored procedures or triggers on
the production machine. I saw some software that had them encrypted. Could
not go in and see their text. How can this be done?
Thanks for any help.
BobUse the WITH ENCRYPTION clause. However, I believe there are tools
available on the Internet to unencrypt the text. Also, note that once
encrypted the object cannot be scripted out for other purposes. Be sure to
save the original DDL in a secure location if needed in the future.
HTH
Jerry
"RDufour" <rdufour@.sgiims.com> wrote in message
news:O9FtvxuuFHA.4040@.TK2MSFTNGP10.phx.gbl...
> Don't want my sa to be able to tinker with Stored procedures or triggers
> on
> the production machine. I saw some software that had them encrypted. Could
> not go in and see their text. How can this be done?
> Thanks for any help.
> Bob
>

Encrypting SPs and maybe triggers, how?

Don't want my sa to be able to tinker with Stored procedures or triggers on
the production machine. I saw some software that had them encrypted. Could
not go in and see their text. How can this be done?
Thanks for any help.
Bob
Use the WITH ENCRYPTION clause. However, I believe there are tools
available on the Internet to unencrypt the text. Also, note that once
encrypted the object cannot be scripted out for other purposes. Be sure to
save the original DDL in a secure location if needed in the future.
HTH
Jerry
"RDufour" <rdufour@.sgiims.com> wrote in message
news:O9FtvxuuFHA.4040@.TK2MSFTNGP10.phx.gbl...
> Don't want my sa to be able to tinker with Stored procedures or triggers
> on
> the production machine. I saw some software that had them encrypted. Could
> not go in and see their text. How can this be done?
> Thanks for any help.
> Bob
>

Encrypting DECRYPTED Stored Procedure.....

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

Thanks in advance.
Kind Regards.

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

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

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

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

encrypting data

Does MS Server 2000 have any way of encrypting data (not just passwords) in
a table, or can it only be done by a function/stored procedure? If only by a
function - does anyone have any advice/suggestions on a good resource about
this?
thanks muchSSL allows you to encrypt data sent in packets so intercepted data is not de
cipherable.|||So the data is encrypted enroute but not in the database?|||There are 3rd party products that will do this but MS SQL will not do it
natively.
Christian Smith
"Jan" <anonymous@.discussions.microsoft.com> wrote in message
news:1320869D-7219-4799-B4CC-4A5648DD663B@.microsoft.com...
> So the data is encrypted enroute but not in the database?|||Hi,
How passwords can be encrypted in MS SQL? Thanks
-- Jan wrote: --
Does MS Server 2000 have any way of encrypting data (not just passwords) in
a table, or can it only be done by a function/stored procedure? If only by a
function - does anyone have any advice/suggestions on a good resource about
this?
thanks much|||Jen,
Password can be encrypted using the undocumented pwdencrypt() and pwdcompare
()
functions. pwdencrypt() is used to create a one-way hash of the data and
pwdcompare() is used to test for comparison.
I believe that Microsoft recommends against using these functions because th
e
algorithms could change from version to version.
If you are looking for a third-party solution check out:
Whamware.Crypt from www.whamware.com
ActiveCrypt from www.activecrypt.com
Encryptionizer for SQL Server from www.netlib.com
Tom
"Jen" wrote:
> Hi,
> How passwords can be encrypted in MS SQL? Thanks
> -- Jan wrote: --
> Does MS Server 2000 have any way of encrypting data (not just passwords)[/col
or]
in a table, or can it only be done by a function/stored procedure? If only b
y a
function - does anyone have any advice/suggestions on a good resource about
this?
> thanks much

encrypting data

Does MS Server 2000 have any way of encrypting data (not just passwords) in a table, or can it only be done by a function/stored procedure? If only by a function - does anyone have any advice/suggestions on a good resource about this?
thanks muchSSL allows you to encrypt data sent in packets so intercepted data is not decipherable.|||So the data is encrypted enroute but not in the database?|||There are 3rd party products that will do this but MS SQL will not do it
natively.
Christian Smith
"Jan" <anonymous@.discussions.microsoft.com> wrote in message
news:1320869D-7219-4799-B4CC-4A5648DD663B@.microsoft.com...
> So the data is encrypted enroute but not in the database?|||Hi
How passwords can be encrypted in MS SQL? Thank
-- Jan wrote: --
Does MS Server 2000 have any way of encrypting data (not just passwords) in a table, or can it only be done by a function/stored procedure? If only by a function - does anyone have any advice/suggestions on a good resource about this
thanks much|||Jen,
Password can be encrypted using the undocumented pwdencrypt() and pwdcompare()
functions. pwdencrypt() is used to create a one-way hash of the data and
pwdcompare() is used to test for comparison.
I believe that Microsoft recommends against using these functions because the
algorithms could change from version to version.
If you are looking for a third-party solution check out:
Whamware.Crypt from www.whamware.com
ActiveCrypt from www.activecrypt.com
Encryptionizer for SQL Server from www.netlib.com
Tom
"Jen" wrote:
> Hi,
> How passwords can be encrypted in MS SQL? Thanks
> -- Jan wrote: --
> Does MS Server 2000 have any way of encrypting data (not just passwords)
in a table, or can it only be done by a function/stored procedure? If only by a
function - does anyone have any advice/suggestions on a good resource about
this?
> thanks muchsql