{"id":2441,"date":"2014-06-14T11:05:56","date_gmt":"2014-06-14T15:05:56","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2441"},"modified":"2014-11-13T12:23:22","modified_gmt":"2014-11-13T17:23:22","slug":"create-symmetric-key","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2441\/create-symmetric-key\/","title":{"rendered":"How to Create a Symmetric Key"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nAsymmetric key encryption<\/a> has many advantages over symmetric key encryption<\/a>. However, there is one significant disadvantage. The mathematical calculations required to encrypt and decrypt data are a lot more complex and resource intensive. That means that asymmetric encryption is slow. For that reason, it is recommended to use symmetric key encryption when it comes to encrypting a lot of data. To use symmetric key encryption in SQL Server you first need to create a symmetric key.\n<\/p>\n

The CREATE SYMMETRIC KEY Statement<\/h3>\n

\nTo create a symmetric key in SQL Server you can use the CREATE SYMMETRIC KEY<\/span> statement. In its simples form it looks like this:\n<\/p>\n

\n[sql]\nCREATE SYMMETRIC KEY ASymmetricKey
\n WITH ALGORITHM = AES_256
\n ENCRYPTION BY CERTIFICATE ACertificate;
\n[\/sql]\n<\/div>\n

\nThe above statement has three parts. The first part specifies the name of the symmetric key we want to create. That name has to be unique within the current database.\n<\/p>\n

\nThe second part, the WITH ALGORITHM<\/span> clause specifies the algorithm with which you want to use the key. You can choose between AES_128<\/span>, AES_192<\/span> and AES_256<\/span>. There are also a bunch of older algorithms available, but they are not secure anymore and hence their use for new development is not recommended.\n<\/p>\n

\nThe third part is the ENCRYPTION BY<\/span> clause. In the CREATE CERTIFICATE<\/span><\/a> and CREATE ASYMMETRIC KEY<\/span><\/a> statements that clause was optional. Omitting it causes the new key to be protected by the database master key<\/a>. With symmetric keys, that is not an option. A symmetric key has to be protected by either a password, a certificate, an asymmetric key or another symmetric key. That means that the ENCRYPTION BY<\/span> clause has to always be present. The above example uses an existing certificate for this purpose.\n<\/p>\n

Finding Existing Symmetric Keys with sys.symmetric_keys<\/h3>\n

\nTo see if the creation was successful, a query agains the sys.symmetric_keys<\/span> catalog view can be used:\n<\/p>\n

\n[sql]\nSELECT SK.name,SK.algorithm_desc,SK.create_date,SK.modify_date,SK.key_guid
\n FROM sys.symmetric_keys AS SK
\n WHERE name = 'ASymmetricKey';
\n [\/sql]\n<\/div>\n

\nThe output of that query will look similar to this:\n<\/p>\n

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

\nBesides of the name, the above query also returns the key algorithm and a few more interesting properties.\n<\/p>\n

Summary<\/h3>\n

\nWhen performance is important, for example when encryption large amounts of data, it is recommended to use symmetric key encryption over other encryption types. The symmetric key required for this can be created with the CREATE SYMMETRIC KEY<\/span> T-SQL statement.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Encrypting large amounts of data in SQL Server should be done with symmetric key encryption. Discover how to create the necessary key for this encryption type.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":2442,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_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}},"categories":[32,167,5,34,27],"tags":[192,273,172,38,15,140,141],"class_list":["post-2441","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cryptography","category-encryption-hierarchy","category-general","category-security","category-series","tag-aes","tag-cryptography","tag-database-master-key","tag-security-2","tag-sql-server","tag-symmetric-key","tag-symmetric-key-encryption"],"acf":[],"yoast_head":"\nHow to Create a Symmetric Key - sqlity.net<\/title>\n<meta name=\"description\" content=\"Encrypting large amounts of data in SQL Server should be done with symmetric key encryption. Discover how to create a key for this encryption type.\" \/>\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\/2441\/create-symmetric-key\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Create a Symmetric Key - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Encrypting large amounts of data in SQL Server should be done with symmetric key encryption. Discover how to create a key for this encryption type.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2441\/create-symmetric-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-06-14T15:05:56+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T17:23:22+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/CREATE_SYMMETRIC_KEY_in_action.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"768\" \/>\n\t<meta property=\"og:image:height\" content=\"468\" \/>\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=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2441\\\/create-symmetric-key\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2441\\\/create-symmetric-key\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"How to Create a Symmetric Key\",\"datePublished\":\"2014-06-14T15:05:56+00:00\",\"dateModified\":\"2014-11-13T17:23:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2441\\\/create-symmetric-key\\\/\"},\"wordCount\":435,\"commentCount\":1,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2441\\\/create-symmetric-key\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/CREATE_SYMMETRIC_KEY_in_action.jpg\",\"keywords\":[\"AES\",\"Cryptography\",\"Database Master Key\",\"security\",\"SQL Server\",\"symmetric key\",\"symmetric key encryption\"],\"articleSection\":[\"Cryptography\",\"Encryption Hierarchy\",\"General\",\"Security\",\"Series\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2441\\\/create-symmetric-key\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2441\\\/create-symmetric-key\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2441\\\/create-symmetric-key\\\/\",\"name\":\"How to Create a Symmetric Key - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2441\\\/create-symmetric-key\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2441\\\/create-symmetric-key\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/CREATE_SYMMETRIC_KEY_in_action.jpg\",\"datePublished\":\"2014-06-14T15:05:56+00:00\",\"dateModified\":\"2014-11-13T17:23:22+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Encrypting large amounts of data in SQL Server should be done with symmetric key encryption. Discover how to create a key for this encryption type.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2441\\\/create-symmetric-key\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2441\\\/create-symmetric-key\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2441\\\/create-symmetric-key\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/CREATE_SYMMETRIC_KEY_in_action.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/CREATE_SYMMETRIC_KEY_in_action.jpg\",\"width\":768,\"height\":468,\"caption\":\"CREATE SYMMETRIC KEY in Action.\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2441\\\/create-symmetric-key\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Create a Symmetric 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":"How to Create a Symmetric Key - sqlity.net","description":"Encrypting large amounts of data in SQL Server should be done with symmetric key encryption. Discover how to create a key for this encryption type.","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\/2441\/create-symmetric-key\/","og_locale":"en_US","og_type":"article","og_title":"How to Create a Symmetric Key - sqlity.net","og_description":"Encrypting large amounts of data in SQL Server should be done with symmetric key encryption. Discover how to create a key for this encryption type.","og_url":"https:\/\/sqlity.net\/en\/2441\/create-symmetric-key\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-06-14T15:05:56+00:00","article_modified_time":"2014-11-13T17:23:22+00:00","og_image":[{"width":768,"height":468,"url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/CREATE_SYMMETRIC_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":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2441\/create-symmetric-key\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2441\/create-symmetric-key\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"How to Create a Symmetric Key","datePublished":"2014-06-14T15:05:56+00:00","dateModified":"2014-11-13T17:23:22+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2441\/create-symmetric-key\/"},"wordCount":435,"commentCount":1,"image":{"@id":"https:\/\/sqlity.net\/en\/2441\/create-symmetric-key\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/CREATE_SYMMETRIC_KEY_in_action.jpg","keywords":["AES","Cryptography","Database Master Key","security","SQL Server","symmetric key","symmetric key encryption"],"articleSection":["Cryptography","Encryption Hierarchy","General","Security","Series"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2441\/create-symmetric-key\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2441\/create-symmetric-key\/","url":"https:\/\/sqlity.net\/en\/2441\/create-symmetric-key\/","name":"How to Create a Symmetric Key - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2441\/create-symmetric-key\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2441\/create-symmetric-key\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/CREATE_SYMMETRIC_KEY_in_action.jpg","datePublished":"2014-06-14T15:05:56+00:00","dateModified":"2014-11-13T17:23:22+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Encrypting large amounts of data in SQL Server should be done with symmetric key encryption. Discover how to create a key for this encryption type.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2441\/create-symmetric-key\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2441\/create-symmetric-key\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2441\/create-symmetric-key\/#primaryimage","url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/CREATE_SYMMETRIC_KEY_in_action.jpg","contentUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/CREATE_SYMMETRIC_KEY_in_action.jpg","width":768,"height":468,"caption":"CREATE SYMMETRIC KEY in Action."},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2441\/create-symmetric-key\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"How to Create a Symmetric 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_featured_media_url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/CREATE_SYMMETRIC_KEY_in_action.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-Dn","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2441","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=2441"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2441\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media\/2442"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2441"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2441"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2441"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}