How predictable is NEWSEQUENTIALID?

Question

According to Microsoft's documentation on NEWSEQUENTIALID, the output of NEWSEQUENTIALID is predictable. But how predictable is predictable? Say I have a GUID that was generated by NEWSEQUENTIALID, how hard would it be to:

• Calculate the next value?
• Calculate the previous value?
• Calculate the first value?
• Calculate the first value, even without knowing any GUID's at all?
• Calculate the amount of rows? E.g. when using integers, /order?id=842 tells me that there are 842 orders in the application.

Below is some background information about what I am doing and what the various tradeoffs are.

One of the security benefits of using GUID's over integers as primary keys is that GUID's are hard to guess. E.g. say a hacker sees a URL like /user?id=845 he might try to access /user?id=0, since it is probable that the first user in the database is an administrative user. Moreover, a hacker can iterate over /user?id=0..1..2 to quickly gather all users.

Similarly, a privacy downside of integers is that they leak information. /order?id=482 tells me that the web shop has had 482 orders since its implementation.

Unfortunately, using GUID's as primary keys has well-known performance downsides. To this end, SQL Server introduced the NEWSEQUENTIALID function. In this question, I would like to learn how predictable the output of NEWSEQUENTIALID is.

Armour

Answer 1

The underlying OS function is UuidCreateSequential. The value is derived from one of your network cards MAC address and a per-os-boot incremental value. See RFC4122. SQL Server does some byte-shuffling to make the result sort properly. So the value is highly predictable, in a sense. Specifically, if you know a value you can immediately predict a range of similar value.

However one cannot predict the equivalent of id=0, nor can it predict that 52DE358F-45F1-E311-93EA-00269E58F20D means the store sold at least 482 items.

The only 'approved' random generation is CRYPT_GEN_RANDOM (which wraps CryptGenRandom) but that is obviously a horrible key candidate.

Trichiasis

Excellent and thorough response! – Armour

Answer 2

In most cases, the next newsequentialid can be predicted by taking the current value and adding one to the first hex pair.

In other words:

1E29E599-45F1-E311-80CA-00155D008B1C

is followed by

1F29E599-45F1-E311-80CA-00155D008B1C

is followed by

2029E599-45F1-E311-80CA-00155D008B1C

Occasionally, the sequence will restart from a new value.

So, it's very predictable

NewSequentialID is a wrapper around the windows function UuidCreateSequential

Nogging

I have observed similar behavior in my database. A series of rows seems to follow a pattern but then suddenly jumps to a new value. Can this new value be guessed as well? – Armour

Answer 3

• Calculate the next value? Yes

Microsoft says:

If privacy is a concern, do not use this function. It is possible to guess the value of the next generated GUID and, therefore, access data associated with that GUID.

SO it's a possibility to get the next value. I don't find information if it is possible to get the prevoius one.

from: http://msdn.microsoft.com/en-us/library/ms189786.aspx

edit: another few words about NEWSEQUENTIALID and security: http://vadivel.blogspot.com/2007/09/newid-vs-newsequentialid.html

Edit: NewSequentialID contains the server's MAC address (or one of them), therefore knowing a sequential ID gives a potential attacker information that may be useful as part of a security or DoS attack. from: Are there any downsides to using NewSequentialID?

Bromal

Thank you for your reply. I am aware that NEWSEQUENTIALID is predictable. In this question, I would like to find out how predictable. – Armour

Answer 4

You can try this code:

DECLARE @tbl TABLE (
    PK uniqueidentifier DEFAULT NEWSEQUENTIALID(),
    Num int
)
INSERT INTO @tbl(Num) values(1),(2),(3),(4),(5)
select * from @tbl

On my machine in this time is result:

PK                                      Num
52DE358F-45F1-E311-93EA-00269E58F20D    1
53DE358F-45F1-E311-93EA-00269E58F20D    2
54DE358F-45F1-E311-93EA-00269E58F20D    3
55DE358F-45F1-E311-93EA-00269E58F20D    4
56DE358F-45F1-E311-93EA-00269E58F20D    5

You should try it several times in different time/date to interpolate the behaviour. I tried it run several times and the first part is changing everytime (you see in results: 52...,53...,54...,etc...). I waited some time to check it, and after some time the second part is incremented too. I suppose the incementation continues to the all parts. Basically it look like simple +=1 incementation transformed into Guid.

EDIT:

If you want sequential GUID and you want have control over the values, you can use Sequences.

Sample code:

select cast(cast(next value for [dbo].[MySequence] as varbinary(max)) as uniqueidentifier)

Traynor

What's the use then? Why not just use 1,2,3,4,5? – Incrassate