{"id":2259,"date":"2014-03-17T11:00:30","date_gmt":"2014-03-17T15:00:30","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2259"},"modified":"2014-11-13T13:10:54","modified_gmt":"2014-11-13T18:10:54","slug":"crypt_gen_random","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2259\/crypt_gen_random\/","title":{"rendered":"CRYPT_GEN_RANDOM: Generating Cryptographically Secure Random Numbers in T-SQL"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nA few days ago I wrote about Pseudo Random Number Generators<\/a> (PRNG) and what it means for a PRNG to be cryptographically secure (CSPRNG). Today I would like to show you a way to generate cryptographically secure random numbers in T-SQL.\n<\/p>\n

Cryptographically Secure Random Numbers in T-SQL<\/h3>\n

\nThe built-in T-SQL function CRYPT_GEN_RANdOM()<\/span> is a wrapper of the CryptGenRandom<\/span> function in Window's Crypto-API. It takes two parameters. The first is the length in bytes of the value you want to generate. The second parameter is optional and can be used to provide additional entropy. Let us look at an example:\n<\/p>\n

\n[sql]\nSELECT CRYPT_GEN_RANDOM(8) AS CSPRNG;
\n[\/sql]\n<\/div>\n

\nThe output is an eight byte varbinary value:\n<\/p>\n

\n\"An<\/a>\n<\/p>\n

\nThe data type of the return value is VARBINARY(8000)[\/t]] and the maximum number of bytes that can be returned is therefore 8000.\n<\/p>\n

\nThe second optional parameter is also a [tt]VARBINARY(8000)<\/span> value. It will be used as a seed. However, this is not a seed in the usual sense of a PRNG. Instead, CRYPT_GEN_RANDOM()<\/span> combines the values in that second parameter with other data before using it. That means that calling the function twice with the same seed will still return two different values.\n<\/p>\n

\nThe PRNG used for CRYPT_GEN_RANDOM()<\/span> is actually a good source of random numbers for non-cryptographic use cases too. You just have to cast the returned value to the desired data type. CRYPT_GEN_RANDOM()<\/span> has a particular advantage over the RAND()<\/span> function. If called within a multi-row SELECT<\/span> or UPDATE<\/span> statement, RAND()<\/span> returns the same value for each row. CRYPT_GEN_RANDOM()<\/span> on the other hand gets re-invoked for every row. To see that in action let us look at this example code:\n<\/p>\n

\n[sql]\nSELECT CAST(CRYPT_GEN_RANDOM(8000) AS INT) [CRYPT_GEN_RANDOM()],
\n RAND() [RAND()]\n FROM sys.columns;
\n[\/sql]\n<\/div>\n

\nThe example selects rows from sys.columns<\/span>, but which table is used is not actually relevant. For each row both RAND()<\/span> and CRYPT_GEN_RANDOM()<\/span> are invoked. The output looks like this:\n<\/p>\n

\n\"Using<\/a>\n<\/p>\n

\nYou can clearly see that the same value is returned by RAND()<\/span> for each row, while CRYPT_GEN_RANDOM()<\/span> produces different values.\n<\/p>\n

Security of CRYPT_GEN_RANDOM()<\/h3>\n

\nCRYPT_GEN_RANDOM()<\/span> directly calls CryptGenRandom of the Windows Crypt-API. This API is widely used in many applications and in general considered well implemented. However, the details of the implementation of this CSPRNG are not published. That poses a significant risk. Most cryptographic algorithms are considered secure exactly because they have been reviewed and under attack of many security specialists. Every time an algorithm is kept a secret, that review cannot happen. Often that leads to vulnerabilities and CryptGenRandom is no exception. In 2007, Leo Dorrendorf reverse engineered the CryptGenRandom function for his master thesis on Cryptanalysis of the Windows Random Number Generator<\/a>. He discovered several vulnerabilities. Microsoft later that year acknowledged the problems and changed the algorithm in later windows versions. However, it is still a secret algorithm.\n<\/p>\n

\nOne workaround that Leo suggested was, to always generate more randomness than required and discard the rest. That is the reason why I used CRYPT_GEN_RANDOM(8000)<\/span> in the previous example to generate a random integer where four bytes would have been enough. In high security situations however I can only recommend to use a different provider for your CSPRN generation, a provider that is using a well-known and tested algorithm.\n<\/p>\n

Summary<\/h3>\n

\nThe built-in T-SQL function CRYPT_GEN_RANDOM()<\/span> provides an easy way to generate cryptographically secure random numbers. While there is a security risk involved as the underlying algorithm is not published, it is far better than any other random data source readily available from T-SQL. If for nothing else, it is a better PRNG than RAND()<\/span> and you should consider using it in non-secure contexts as well.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

While not perfect, the T-SQL function CRYPT_GEN_RANDOM() generates cryptographically secure random number values using the Windows Crypt-API. Read on to get all the details on how to use it and when to avoid it.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[32,5,34],"tags":[273,134,135,130,38,15],"class_list":["post-2259","post","type-post","status-publish","format-standard","hentry","category-cryptography","category-general","category-security","tag-cryptography","tag-csprng","tag-prng","tag-random","tag-security-2","tag-sql-server"],"acf":[],"yoast_head":"\nCRYPT_GEN_RANDOM: Generating Cryptographically Secure Random Numbers in T-SQL - sqlity.net<\/title>\n<meta name=\"description\" content=\"While not perfect, the T-SQL function CRYPT_GEN_RANDOM() generates cryptographically secure random number values. Read on to get all the details.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/2259\/crypt_gen_random\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"CRYPT_GEN_RANDOM: Generating Cryptographically Secure Random Numbers in T-SQL - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"While not perfect, the T-SQL function CRYPT_GEN_RANDOM() generates cryptographically secure random number values. Read on to get all the details.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2259\/crypt_gen_random\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2014-03-17T15:00:30+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:10:54+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/An_8_byte_cryptographically_secure_pseudo_random_number_value.jpg\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2259\\\/crypt_gen_random\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2259\\\/crypt_gen_random\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"CRYPT_GEN_RANDOM: Generating Cryptographically Secure Random Numbers in T-SQL\",\"datePublished\":\"2014-03-17T15:00:30+00:00\",\"dateModified\":\"2014-11-13T18:10:54+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2259\\\/crypt_gen_random\\\/\"},\"wordCount\":682,\"commentCount\":1,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2259\\\/crypt_gen_random\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/An_8_byte_cryptographically_secure_pseudo_random_number_value.jpg\",\"keywords\":[\"Cryptography\",\"CSPRNG\",\"PRNG\",\"Random\",\"security\",\"SQL Server\"],\"articleSection\":[\"Cryptography\",\"General\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2259\\\/crypt_gen_random\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2259\\\/crypt_gen_random\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2259\\\/crypt_gen_random\\\/\",\"name\":\"CRYPT_GEN_RANDOM: Generating Cryptographically Secure Random Numbers in T-SQL - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2259\\\/crypt_gen_random\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2259\\\/crypt_gen_random\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/An_8_byte_cryptographically_secure_pseudo_random_number_value.jpg\",\"datePublished\":\"2014-03-17T15:00:30+00:00\",\"dateModified\":\"2014-11-13T18:10:54+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"While not perfect, the T-SQL function CRYPT_GEN_RANDOM() generates cryptographically secure random number values. Read on to get all the details.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2259\\\/crypt_gen_random\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2259\\\/crypt_gen_random\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2259\\\/crypt_gen_random\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/An_8_byte_cryptographically_secure_pseudo_random_number_value.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/An_8_byte_cryptographically_secure_pseudo_random_number_value.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2259\\\/crypt_gen_random\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"CRYPT_GEN_RANDOM: Generating Cryptographically Secure Random Numbers in T-SQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"CRYPT_GEN_RANDOM: Generating Cryptographically Secure Random Numbers in T-SQL - sqlity.net","description":"While not perfect, the T-SQL function CRYPT_GEN_RANDOM() generates cryptographically secure random number values. Read on to get all the details.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/2259\/crypt_gen_random\/","og_locale":"en_US","og_type":"article","og_title":"CRYPT_GEN_RANDOM: Generating Cryptographically Secure Random Numbers in T-SQL - sqlity.net","og_description":"While not perfect, the T-SQL function CRYPT_GEN_RANDOM() generates cryptographically secure random number values. Read on to get all the details.","og_url":"https:\/\/sqlity.net\/en\/2259\/crypt_gen_random\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-03-17T15:00:30+00:00","article_modified_time":"2014-11-13T18:10:54+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/An_8_byte_cryptographically_secure_pseudo_random_number_value.jpg","type":"","width":"","height":""}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2259\/crypt_gen_random\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2259\/crypt_gen_random\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"CRYPT_GEN_RANDOM: Generating Cryptographically Secure Random Numbers in T-SQL","datePublished":"2014-03-17T15:00:30+00:00","dateModified":"2014-11-13T18:10:54+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2259\/crypt_gen_random\/"},"wordCount":682,"commentCount":1,"image":{"@id":"https:\/\/sqlity.net\/en\/2259\/crypt_gen_random\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/An_8_byte_cryptographically_secure_pseudo_random_number_value.jpg","keywords":["Cryptography","CSPRNG","PRNG","Random","security","SQL Server"],"articleSection":["Cryptography","General","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2259\/crypt_gen_random\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2259\/crypt_gen_random\/","url":"https:\/\/sqlity.net\/en\/2259\/crypt_gen_random\/","name":"CRYPT_GEN_RANDOM: Generating Cryptographically Secure Random Numbers in T-SQL - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2259\/crypt_gen_random\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2259\/crypt_gen_random\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/An_8_byte_cryptographically_secure_pseudo_random_number_value.jpg","datePublished":"2014-03-17T15:00:30+00:00","dateModified":"2014-11-13T18:10:54+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"While not perfect, the T-SQL function CRYPT_GEN_RANDOM() generates cryptographically secure random number values. Read on to get all the details.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2259\/crypt_gen_random\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2259\/crypt_gen_random\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2259\/crypt_gen_random\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/An_8_byte_cryptographically_secure_pseudo_random_number_value.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/An_8_byte_cryptographically_secure_pseudo_random_number_value.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2259\/crypt_gen_random\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"CRYPT_GEN_RANDOM: Generating Cryptographically Secure Random Numbers in T-SQL"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-Ar","jetpack-related-posts":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2259","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=2259"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2259\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2259"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2259"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2259"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}