Encrypted Procs Not Syncyronizing

gknierim

I have a stored procedure that I used the WITH ENCRYPTION on. I compared it with my server that didn't have the same proc encrypted and it said that they needed to be sync'd. Ok, cool. So, I tried syncing the proc and when it was done, the proc on the server hadn't changed and you could still see the text.

Does anyone know why this isn't working? I have checked the Add WITH ENCRYPTION in the options and tried it both ways and it still doesn't work.

Also, if I delete the proc on the server and then do a sync, it doesn't even copy the proc over.

I have version 3.2.0.5 of SQL Compare installed.

Thanks,
Greg

Brian Donahue

Hi Greg,

I can say that this usually does work without a problem. If you have two stored procedures that are identical except for one being encrypted, then the unencryupted one will be recreated as encrypted if you synchronize the encrypted one to the unencrypted one.

The only things I can think of are:

• You're not using SQL Server 2000.
• There is some kind of wierd permissions issue. I'd try using an SA role.

Please look into these things and let us know if they help.

gknierim

I am using SQL Server 2000 SP3a.

However, I am using a user permission and not sa permissions because it is on a remote server. The user permission I am using however, has dbo access. So, I'm not sure what else I can do other than to delete the proc on the remote server, check the Add WITH ENCRYPTION option in SQL Compare and then syncronize the procs. That will copy the encrypted proc over to the remote server.

Thoughts?

Brian Donahue

Hello Greg,

Just to confirm, you are checking with Query Analyzer or Enterprise Manager to make sure the objects are actually being encrypted? If you rely on SQL Compare, it can always decrypt the encrypted stored procedure.

If you double-click the procedure in Enterprise Manager, it will tell you something like encrypted object text could not be shown.

gknierim

Yes I am checking with EM.

Lets try explaining this again.

Lets say I have Server1 and Server2. I am trying to copy an encrypted procedure to Server 2.

I go into EM on Server1 and the proc is encrypted. I check the proc on Server2 and it is not encrypted.

I run SQL Compare refresh and it tells me that the text is encrypted for the procedure on Server1 but not on Server2. I run Sync so that Server2 is the same as Server1. It runs with no error and then comes back and tells me that the procs are the same, yet they still show that the proc on Server1 is encrypted and the proc on Server2 is not. But yet SQL Compare says they are the same.

When I then check the procedure on Server2, it is still in plain text and not encrypted.

So, I'm confused. Thoughts?