Stored Procedures with Encryption will not be scripted

Comments

• Neil Davidson Posts: 281

  May 14, 2006 5:16AM

  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.

  - Neil Davidson
  Red Gate Software Ltd

  0 · Share on Twitter
• compesoft Posts: 12

  May 14, 2006 8:03AM

  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!

  0 · Share on Twitter
• compesoft Posts: 12

  May 14, 2006 8:07AM

  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.

  0 · Share on Twitter
• compesoft Posts: 12

  May 14, 2006 8:44AM

  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
  "

  0 · Share on Twitter
• Neil Davidson Posts: 281

  May 14, 2006 9:05AM

  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
  Red Gate Software Ltd

  0 · Share on Twitter
• Neil Davidson Posts: 281

  May 14, 2006 9:06AM

  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?

  - Neil Davidson
  Red Gate Software Ltd

  0 · Share on Twitter
• compesoft Posts: 12

  May 14, 2006 9:38AM

  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

  0 · Share on Twitter
• Andras B Redgate › Posts: 249

  May 15, 2006 4:08AM

  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

  András Belokosztolszki, PhD
  Red Gate Software Ltd.

  0 · Share on Twitter
• compesoft Posts: 12

  May 15, 2006 10:28AM

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

  0 · Share on Twitter