17

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.

Improve this question
14
  • 2
    @AaronBertrand at the very least, one if the digits is always 4. But the fact that I don't have strong evidence that they might be guessable does not mean that they are not. I cannot base this security decision on this observation.
    – usr
    Apr 4, 2013 at 14:22
  • 1
    Maybe NEWID is just as random as the "Debian weak SSH keys": en.wikinews.org/wiki/… They certainly looked random to the developer who tested them...
    – usr
    Apr 4, 2013 at 14:39
  • 2
    That 4 tells you what algorithm is used in generating the GUID. en.wikipedia.org/wiki/Globally_unique_identifier#Algorithm
    – Mr.Mindor
    Apr 4, 2013 at 15:07
  • 1
    You do understand that the only determining factor in the level of your security is the bit entropy? The idea that NEWID is not random enough for your needs would imply that you have some idea of the amount of bit entropy which is required to be "secure" for your use case. What is that number?
    – Cade Roux
    Apr 4, 2013 at 15:07
  • 2
    @usr - "given full knowledge of the internal state", one can crack any approach.
    – user22168
    Apr 4, 2013 at 21:05

3 Answers 3

Reset to default
27 
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).

Improve this answer
1
  • This answer from SO might be a better general solution since it actually tries to conform to the UUIDv4 structure.
    – Bacon Bits
    Feb 15, 2022 at 17:07
5

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.

Improve this answer
5
  • That's interesting. I personally would not trust this for security purposes. Neither Windows nor SQL Server make a guarantee about this.
    – usr
    May 29, 2018 at 9:14
  • @usr I'm not sure what a guarantee would look like. It doesn't use the word "guarantee", but it appears to be standard MSDN Windows SDK documentation. It would be extremely unlikely for MS to downgrade the cryptographic randomness of GUIDs in some future version of Windows. There would be nothing to gain.
    – Jordan Rieger
    May 29, 2018 at 19:21
  • That MSDN page merely documents historic choices but it does not say that this will be kept in the future. Same for SQL Server. I cannot imagine circumstances under which this would break down. But there have been many catastrophic RNG debacles. Assumptions like this one have often turned out false. It's a heuristic argument.
    – usr
    May 29, 2018 at 20:05
  • @usr But how could any MSDN documentation guarantee future behavior of Windows? The most we can ever hope for in these cases is documentation of the current behavior. If the behavior ever changes, well then they update the documentation. That's why these Stack Overflow Q&As have version tags and get updated from time to time.
    – Jordan Rieger
    May 29, 2018 at 20:37
  • They do make guarantees about things they will not change. They could change the product but they won't if they say "here's a security critical function with these properties". I interpret that documentation in question more like a historic view than a statement about the future.
    – usr
    May 30, 2018 at 9:06
4

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.

Improve this answer
2
  • Interesting points. My data type choice UNIQUEIDENTIFIER is mostly a coincidence. It is just convenient to handle a 16 byte quantity using that type. I think of it as a password. It has to be unique, though. I'm confident that I will never see a collision (and even if there is the app will just crash - so that's safe, too).
    – usr
    Apr 5, 2013 at 11:50
  • I disagree, it depends 'how' you encrypt it to if it becomes random rather than unique. It can easily be the same.
    – Dawesi
    Sep 27, 2015 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.