So you are running into a blocking issue, try to find a solution and you gets back a reply like ‘Sorry, I cannot repro your problem’. You go back and try again, and sure the problem is still there. You dig deeper, go to the Advanced search options on Google, but still cannot find the answer. Desperately, you try again and, surprise, the problem has vanished. Sounds familiar?

I know of one such problem that appears again and again when discussing Service Broker, Database Mirroring and, to a lesser extent, SQL Server 2005 Cryptographic facilities. I want to show you what the problem is, what’s causing it and even drill a bit into how something so basic has slipped into the product.

Certificate Not Yet Valid

Lets do a very simple example of a Service Broker conversation that uses a certificate. First create a test database, then create a service that we will use for our test:

create master key
	encryption by password = 'Password#123';
go

create certificate test with subject = 'test';
go

backup certificate test to file = 'test.cer';
go

create queue testQueue;
create service testService
	on queue testQueue ([DEFAULT]);
create remote service binding testBinding
	to service 'testService' with user = [dbo];
go

This setup allows us to send a message on a conversation from service testService to service testService:

declare @h uniqueidentifier;
begin dialog conversation @h
	from service [testService]
	to service 'testService';
send on conversation @h;
go

And now check the sys.transmission_queue:

waitfor delay '00:00:05';
select transmission_status from sys.transmission_queue;
go

And you will see this message: The security certificate bound to database principal (Id: 1) is not yet valid. Either wait for the certificate to become valid or install a certificate that is currently valid. Or you won’t see anything. Wait! Is there an error or isn’t there one? Well.. it depends on where you live. And it also depends whether is summer or winter outside.

Universal Time

Any sort of communication between machines must rely on UTC, because machines can be distributed across the globe and have different local times. Using UTC timestamps solves this issue. This also applies to certificates. The fields Validity/Not Before and Validity/Not After must be expressed in UTC so that the certificate issued on one part of the globe is safely used on another part of the globe. As I write this post from Bucharest that is on the GMT+2 time zone and summer daylight saving time is in effect, my local time is 3 hours ahead of the UTC time. I can test this from SQL:

select getutcdate(), getdate()

I get back the values 2008-08-25 09:25:16 (my local time) and 2008-08-25 06:25:16 (UTC time). When I created the certificate I did not specify a the START_DATE nor the EXPIRY_DATE values, so the certificate was created using the creation moment as Validity/Not Before and creation moment + 1 year as Validity/Not After, which are the X.509 fields corresponding to START_DATE and EXPIRY_DATE. Is there some way to verify this? Yes, we can export the certificate and then open it in the system certificate viewer:

backup certificate test to file = 'test.cer';
go

The test.cer file was saved in the same location your master files are. Double-click this file and you get to see all the details of the certificate, and we can check the Valid from:

But you can see that the time displayed is … Monday, August 25, 2008 12:25:19 PM. That is not the 9:25 AM reported by GETDATE() neither the 6:25 AM reported by GETUTCDATE(). OK, so my computer local time is 3 hours ahead of UTC and the Certificate Viewer displays the times in local time, that means that the certificate has it’s UTC time stamp at 9:25. Isn’t this wrong? It sure is. And in fact that is a SQL Server 2005 bug. When creating a new certificate, it stamps the certificate valid and expiration dates with the local time values instead of the UTC time values. However, when it uses the certificate the time fields are used correctly, so the time value in the field is interpreted as a UTC time value. This is why the certificate I created at 9:25 AM is not valid until 12:25 PM the same day!. And this is because I did the test in Bucharest during summer. If I’d repeat the same test in Moscow in winter, the certificate would not be valid for 3 hours. In London during summer is not valid one hour. In Mumbai would be 5 hours and 30 minutes. As I said, the result depends on where you’re doing the test and also whether daylight saving time is in effect or not!

So why some of you did not see any problem? Because if you live on the Western Hemisphere and you are behind the UTC clock, then the certificate is valid immediately as you create it. In fact, is it stamped as being valid few hours in the past, depending on your time zone.

Redmond, WA, USA

So when you post your problem on the forums, somebody from the SQL team is looking at it, trying to repro, and doesn’t see any problem. Of course, they are located in Redmond so they don’t see the problem :). Or perhaps an MVP or a forum moderator is trying to help you. Again, if they are US based (and many are), they would not repro the issue, since their local time is safe from this problem.

Is this problem still in SQL Server 2005? I tested on build 9.00.3042 which is SP2, and the problem is present. How could Microsoft not be aware of this issue when they released the product? Very simple: the entire test infrastructure is running on Redmond time! In fact the problem was reported early by users: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125262 but was closed as not repro. Of course, the person who tried to repro was based on Redmond, where the issue does not repro. Other people are still running into this issue today, look at this post on the MSDN forums, or here.

Workaround

How do you resolve the issue? Very simply, specify an explicit START_DATE when you create the certificate:

create certificate test
	with subject = 'test',
	start_date = '2008-08-24';

Why does the problem seem to sometimes magically fix itself when you try again later? Because after the number of hours that is the difference between your local time and the UTC time has passed, the certificate is valid.

And one last note: what start date is displayed in the certificate meta data view?

select * from sys.certificates

Yes indeed, that is 2008-08-25 09:25:19.000. Confusing? You bet. Correct? Not a chance…