{"id":2417,"date":"2014-05-23T11:00:03","date_gmt":"2014-05-23T15:00:03","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2417"},"modified":"2014-11-13T12:25:36","modified_gmt":"2014-11-13T17:25:36","slug":"create-asymmetric-key","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2417\/create-asymmetric-key\/","title":{"rendered":"CREATE ASYMMETRIC KEY \u2013 How to Generate an Asymmetric Key"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nCertificates<\/a> and asymmetric keys<\/a> are very similar in SQL Server. Both can be used to protect symmetric keys<\/a> in the database. Whilst this not being a good practice, they can also both be used to encrypt data directly. One of the bigger differences is that the certificate can have additional information attached to it, like a signature that proves the identity of the certificate owner.\n<\/p>\n

\nIf you are just using the certificate to protect keys in a single database, you do not need all that additional information. In that case, you can use an asymmetric key instead.\n<\/p>\n

The CREATE ASYMMETRIC KEY statement<\/h3>\n

\nTo generate a new asymmetric key you can use the CREATE ASYMMETRIC KEY<\/span> statement. In its simplest form, the statement looks like this:\n<\/p>\n

\n[sql]\nCREATE ASYMMETRIC KEY AnAsymmetricKey
\n WITH ALGORITHM = RSA_2048;
\n[\/sql]\n<\/div>\n

\nThe WITH ALGORITHM<\/span> clause determines the algorithm that the key should be created for. SQL Server knows three algorithms for asymmetric key encryption:\n<\/p>\n

\n[sql]\nCREATE ASYMMETRIC KEY AnAsymmetricKey
\n WITH ALGORITHM = RSA_512;<\/p>\n

CREATE ASYMMETRIC KEY AnAsymmetricKey
\n WITH ALGORITHM = RSA_1024;<\/p>\n

CREATE ASYMMETRIC KEY AnAsymmetricKey
\n WITH ALGORITHM = RSA_2048;
\n[\/sql]\n<\/p><\/div>\n

\nAll three algorithms are all based on the RSA cryptosystem<\/a>. The difference between them is the key length: 512, 1024 or 2048 bits. The longer the key (the more bits it has) the more secure the encrypted data is. However, more bits also mean that the encryption process uses more CPU resources.\n<\/p>\n

\nThe algorithm has to be specified when generating an asymmetric key. This is another big difference to certificates and maybe the biggest reason to use asymmetric keys at all. Certificates that are generated in SQL Server always use 1024 bit as key length. Asymmetric keys allow you to generate a longer and hence more secure key. (Certificates that where generated outside of SQL Server with a longer key can however be imported.)\n<\/p>\n

Finding your Asymmetric Key<\/h3>\n

\nTo see if an asymmetric key exists you can use the sys.asymmetric_keys<\/span> catalog view:\n<\/p>\n

\n[sql]\nSELECT AK.name,AK.asymmetric_key_id,AK.pvt_key_encryption_type_desc
\n FROM sys.asymmetric_keys AS AK
\n WHERE name = 'AnAsymmetricKey';
\n[\/sql]\n<\/div>\n

\nThat query returns a result like this:\n<\/p>\n

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

\nThe last column tells us how the private key is protected.\n<\/p>\n

Protecting the Private Key<\/h3>\n

\nLike certificates, asymmetric keys can be protected either with the database master key or with a password. The CREATE ASYMMETRIC KEY<\/span> statement above does not specify a protection mechanism. That results in the key being protected by the database master key. If you instead want to protect the private key with a password, you can use the following syntax:\n<\/p>\n

\n[sql]\nCREATE ASYMMETRIC KEY AnAsymmetricKey
\n WITH ALGORITHM = RSA_2048
\n ENCRYPTION BY PASSWORD = '**********';
\n[\/sql]\n<\/div>\n

\nThe specified password needs to comply with the windows password policies. The asymmetric key created by this statement is indeed password protected:\n<\/p>\n

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

Summary<\/h3>\n

\nAsymmetric keys and certificates in SQL Server are very similar. However, asymmetric keys make it easier to use a longer and hence more secure key length. Asymmetric keys in SQL Server are created with the CREATE ASYMMETRIC KEY<\/span> statement. The private key can be protected either with the database master key or with a password.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

What is the difference between certificates and asymmetric keys in SQL Server and how do you generate asymmetric keys in T-SQL? Read on to get all the details.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":2418,"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,167,5,34,27],"tags":[188,143,49,273,122,38,15],"class_list":["post-2417","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cryptography","category-encryption-hierarchy","category-general","category-security","category-series","tag-asymmet","tag-asymmetric-key","tag-certificates","tag-cryptography","tag-cv","tag-security-2","tag-sql-server"],"acf":[],"yoast_head":"\nCREATE ASYMMETRIC KEY \u2013 How to Generate an Asymmetric Key - sqlity.net<\/title>\n<meta name=\"description\" content=\"What is the difference between certificates and asymmetric keys in SQL Server and how do you generate asymmetric keys? 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\/2417\/create-asymmetric-key\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"CREATE ASYMMETRIC KEY \u2013 How to Generate an Asymmetric Key - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"What is the difference between certificates and asymmetric keys in SQL Server and how do you generate asymmetric keys? Read on to get all the details.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2417\/create-asymmetric-key\/\" \/>\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-05-23T15:00:03+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T17:25:36+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/CREATE_ASYMMETRIC_KEY_in_action.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"768\" \/>\n\t<meta property=\"og:image:height\" content=\"479\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\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\\\/2417\\\/create-asymmetric-key\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2417\\\/create-asymmetric-key\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"CREATE ASYMMETRIC KEY \u2013 How to Generate an Asymmetric Key\",\"datePublished\":\"2014-05-23T15:00:03+00:00\",\"dateModified\":\"2014-11-13T17:25:36+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2417\\\/create-asymmetric-key\\\/\"},\"wordCount\":537,\"commentCount\":1,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2417\\\/create-asymmetric-key\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/05\\\/CREATE_ASYMMETRIC_KEY_in_action.jpg\",\"keywords\":[\"asymmet\",\"asymmetric key\",\"Certificates\",\"Cryptography\",\"CV\",\"security\",\"SQL Server\"],\"articleSection\":[\"Cryptography\",\"Encryption Hierarchy\",\"General\",\"Security\",\"Series\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2417\\\/create-asymmetric-key\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2417\\\/create-asymmetric-key\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2417\\\/create-asymmetric-key\\\/\",\"name\":\"CREATE ASYMMETRIC KEY \u2013 How to Generate an Asymmetric Key - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2417\\\/create-asymmetric-key\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2417\\\/create-asymmetric-key\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/05\\\/CREATE_ASYMMETRIC_KEY_in_action.jpg\",\"datePublished\":\"2014-05-23T15:00:03+00:00\",\"dateModified\":\"2014-11-13T17:25:36+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"What is the difference between certificates and asymmetric keys in SQL Server and how do you generate asymmetric keys? Read on to get all the details.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2417\\\/create-asymmetric-key\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2417\\\/create-asymmetric-key\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2417\\\/create-asymmetric-key\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/05\\\/CREATE_ASYMMETRIC_KEY_in_action.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/05\\\/CREATE_ASYMMETRIC_KEY_in_action.jpg\",\"width\":768,\"height\":479,\"caption\":\"The CREATE ASYMMETRIC KEY statement in Action.\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2417\\\/create-asymmetric-key\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"CREATE ASYMMETRIC KEY \u2013 How to Generate an Asymmetric Key\"}]},{\"@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":"CREATE ASYMMETRIC KEY \u2013 How to Generate an Asymmetric Key - sqlity.net","description":"What is the difference between certificates and asymmetric keys in SQL Server and how do you generate asymmetric keys? 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\/2417\/create-asymmetric-key\/","og_locale":"en_US","og_type":"article","og_title":"CREATE ASYMMETRIC KEY \u2013 How to Generate an Asymmetric Key - sqlity.net","og_description":"What is the difference between certificates and asymmetric keys in SQL Server and how do you generate asymmetric keys? Read on to get all the details.","og_url":"https:\/\/sqlity.net\/en\/2417\/create-asymmetric-key\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-05-23T15:00:03+00:00","article_modified_time":"2014-11-13T17:25:36+00:00","og_image":[{"width":768,"height":479,"url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/CREATE_ASYMMETRIC_KEY_in_action.jpg","type":"image\/jpeg"}],"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\/2417\/create-asymmetric-key\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2417\/create-asymmetric-key\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"CREATE ASYMMETRIC KEY \u2013 How to Generate an Asymmetric Key","datePublished":"2014-05-23T15:00:03+00:00","dateModified":"2014-11-13T17:25:36+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2417\/create-asymmetric-key\/"},"wordCount":537,"commentCount":1,"image":{"@id":"https:\/\/sqlity.net\/en\/2417\/create-asymmetric-key\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/CREATE_ASYMMETRIC_KEY_in_action.jpg","keywords":["asymmet","asymmetric key","Certificates","Cryptography","CV","security","SQL Server"],"articleSection":["Cryptography","Encryption Hierarchy","General","Security","Series"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2417\/create-asymmetric-key\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2417\/create-asymmetric-key\/","url":"https:\/\/sqlity.net\/en\/2417\/create-asymmetric-key\/","name":"CREATE ASYMMETRIC KEY \u2013 How to Generate an Asymmetric Key - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2417\/create-asymmetric-key\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2417\/create-asymmetric-key\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/CREATE_ASYMMETRIC_KEY_in_action.jpg","datePublished":"2014-05-23T15:00:03+00:00","dateModified":"2014-11-13T17:25:36+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"What is the difference between certificates and asymmetric keys in SQL Server and how do you generate asymmetric keys? Read on to get all the details.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2417\/create-asymmetric-key\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2417\/create-asymmetric-key\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2417\/create-asymmetric-key\/#primaryimage","url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/CREATE_ASYMMETRIC_KEY_in_action.jpg","contentUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/CREATE_ASYMMETRIC_KEY_in_action.jpg","width":768,"height":479,"caption":"The CREATE ASYMMETRIC KEY statement in Action."},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2417\/create-asymmetric-key\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"CREATE ASYMMETRIC KEY \u2013 How to Generate an Asymmetric Key"}]},{"@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-CZ","jetpack-related-posts":[],"jetpack_featured_media_url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/CREATE_ASYMMETRIC_KEY_in_action.jpg","_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2417","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=2417"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2417\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media\/2418"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2417"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2417"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2417"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}