So I have been struggling for hours trying to figure out why my SQLPackage.exe task thinks it needs drop my Certificates even though I just deployed them and haven’t made any changes. Look at the example below, this is not the SSDT project or output it is only used for illustrating the problem and solution:
create certificate [YourCertificateName] authorization [dbo] with subject = N'Some description about your encryption certificate' , start_date = N'01/01/2012 00:00:00' , expiry_date = N'12/31/2050 00:00:00' active for begin_dialog = on;
If you run this from SSDT or SQLPackage where you have the above Certificate scripted as part of a Database Project and deploy this more than once you will notice that it is dropping and recreating the Certificate every time. This happens without you making any changes and the object already existing. My colleague Simon Sabin point out the following “The reason for this is that the dates in the above script aren’t ISO Standard.” This means that if you run a Schema Comparison you will notice the Certificate on the server looks like this:
create certificate [YourCertificateName] authorization [dbo] with subject = N'Some description about your encryption certificate' , start_date = N'2012-01-01T00:00:00' , expiry_date = N'2050-12-31T00:00:00' active for begin_dialog = on;
When a date is stored as a property or into a Date column it is converted to ISO standards and thus when you script an object using a different format they will look different even though they are the same and cause the object to be dropped and recreated.