Securely generate a UNIQUEIDENTIFIER in SQL Server

Question

I intend to be using a UNIQUEIDENTIFIER as an access key that users can use to access certain data. The key will act as a password in that sense.

I need to generate multiple such identifiers as part of an INSERT...SELECT statement. For architectural reasons I want to generate the identifiers server-side in this case.

How can I generate a securely random UNIQUEIDENTIFIER? Note, that NEWID would not be random enough as it does not promise any security properties at all. I'm looking for the SQL Server equivalent of System.Security.Cryptography.RandomNumberGenerator because I need unguessable IDs. Anything based on CHECKSUM, RAND or GETUTCDATE would also not qualify.

Answer 1

SELECT CAST(CRYPT_GEN_RANDOM(16) AS UNIQUEIDENTIFIER)

Should do the trick I would have thought.

CRYPT_GEN_RANDOM

Returns a cryptographic random number generated by the Crypto API (CAPI).

Answer 2

According to https://blogs.msdn.microsoft.com/sqlprogrammability/2006/03/23/newsequentialid-histrorybenefits-and-implementation/, the NEWID() function just wraps the Windows function CoCreateGuid, which returns a v4-style GUID. And according to https://msdn.microsoft.com/en-us/library/bb417a2c-7a58-404f-84dd-6b494ecf0d13#id11, since Windows 2000 back in 1999,

"the random bits for all version 4 GUIDs built in Windows are obtained via the Windows CryptGenRandom cryptographic API or the equivalent, the same source that is used for generation of cryptographic keys"

So I'd say you could consider NEWID() cryptographically secure -- at least to the extent of the 122 bits of entropy it provides.

Answer 3

Just my two cents, but this may not be a good idea. To paraphrase Eric Lippert's excellent series on GUID's (part 1, part 2, part 3), the acronym is GUID, not GSUID - Globally Unique Identifier, not Globally Secure Unique Identifier.

The problem lies in that when GUIDs are generated within a non-hostile scope, such as everyone using NEWID(), all values are guaranteed to be unique (well, sort of, see Eric's article, part 3). But if a hostile entity enters that scope, they can both predict the next generated GUID, as well as cause collisions on their own.

By creating your own method of generating a value that you store inside a structure that looks like a GUID, you have essentially become a hostile entity. You have changed the contract of a GUID from being unique to being random. While someone better at math than I could probably prove you are still unique, that is only within the confines of your generation method. If you mix these pseudo-GUIDs with NEWID() GUIDs, all bets are off.

I say this may not be a good idea only because I don't know the entire scope of how you are using the values. If you are the only entity generating the values (no mix and match), and/or you aren't persisting the values, and/or you don't care about collisions, this may not be an issue. If any of those items aren't true, you may want to re-evaluate.