Stored Procedures with Encryption will not be scripted

compesoft

Hi all,

I have a problem with stored procedures and encryption. When I use the SQL Compare application to update a database with new encrypted stored procedures, the SQL Compare works great. BUT, when I try using the compare and updated API libraries with VB.NET toolkit with the options set at "options.default", any encrypted stored procedure will fail.

The execution script will hault the because the stored procedure won't be found to apply the permissions to (it won't be created because it was encrypted). I have tried adding stored procedures that are not encypted and they work great. I don't understand why the SQL compare will work fine, but the VB.NET application compare will fail running the script.

How do I deal with this? I am guessing that there is a setting for this in the options, but I cannot find it.

Thank you for any help you can get me. I have to have this running by Monday.

Thanks!!
-Sean

Neil Davidson

Hi,

Can you tell me what version of SQL Server you are using please. SQL Compare will only decrypt stored procedures in SQL 2000 - it won't work in SQL 7 or SQL 2005.

The behaviour of the toolkit and the UI should be the same though.

compesoft

Sorry - I didn't mention it. I am using SQL server 2000 and Visual Studio .NET 2003. My computer will do the compare correctly if I use the SQL Compare, but I need to do some custom connections using the Red-gate API. Thanks!

compesoft

Also just as a note, I tested using the toolkit's compare (default settings) with stored procedures that are not encrypted. They will compare, script, and update correctly.

It is only with a stored procedure that is encrypted and that SQL toolkit that will not work saying that the stored procedure does not exist when it tries to apply security to it. It wasn't able to create the procedure.

compesoft

Here is the script that was created by the API - notice the line below that says:

"/*
Script created by SQL Compare version 5.0.0.1622 from Red Gate Software Ltd at 5/14/2006 7:32:30 AM
Please back up your database before running this script
*/

{code snipped}

PRINT N'Creating [dbo].[Encrypted_test]'
GO
SET ANSI_NULLS OFF
GO
-- Text was encrypted
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GroupRelationsCount]'
GO
SET ANSI_NULLS ON
GO
-- Text was encrypted
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[Encrypted_test]'
GO
SET ANSI_NULLS OFF
GO
-- Text was encrypted

{snip}

IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO
"

Neil Davidson

Is there any chance you can check this behaviour on SQL Compare 5.1.0? If you go to help->check for updates you should be able to download it.

Neil Davidson

Also, does the user you are accessing the database with have sa permissions, and is it a member of the sysadmins role for the server?

Is this running as a console app, or as a web page / part of a service?

compesoft

I am currently using 5.1.0.80. The VB.Net App is using SA with full admin. The application that is using the Red-gate APIs is a desktop application in Microsoft VB.NET 2003. This application will succesfully update the schemas, update stored procedures (if they are not encrypted) and create new stored procedures (if they are not encrypted).

The problem with the created script is this:

PRINT N'Creating [dbo].[Encrypted_test]'
GO
SET ANSI_NULLS OFF
GO
-- Text was encrypted

If I run the same compare from SQL Compare, the generated script does NOT show "text was encrypted". It shows the real procedure with the code: "WITH ENCRYPTION" as it should.

Any idea why or what I have to do to make the Toolkit read the stored procedures that are encrypted? They do not have a private password on them. They were simply created "WITH ENCRYPTION".

Thanks,

Sean

Andras B

compesoft wrote:

I am currently using 5.1.0.80.

....

Any idea why or what I have to do to make the Toolkit read the stored procedures that are encrypted? They do not have a private password on them. They were simply created "WITH ENCRYPTION".

Thanks,

Sean

Hi Sean,

It seems that in the toolkit version of your program the SQL Compare engine is not able to retrieve the database version and/or the database GUID correctly. We need these two pieces of information for decrypting stored procedures on SQL Server 2000. For the GUID we must be able to execute a dbcc command (DBCC DBINFO).

Could you tell me the values of “MajorVersion” and “ServerMajorVersion” on the database object after it is registered? (The Guid property is not accessible publicly.)

Regards,
Andras

compesoft

I figured it out!! We had used a user name in one place on the script that was NOT sa. You have to use the SA account because it is the only account that has the rights to decrypt stored procdures.

Thanks for everyone for their help!!