{"id":2368,"date":"2014-04-25T11:00:14","date_gmt":"2014-04-25T15:00:14","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2368"},"modified":"2014-11-13T12:28:54","modified_gmt":"2014-11-13T17:28:54","slug":"service-master-key","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2368\/service-master-key\/","title":{"rendered":"How to Create a Service Master Key in SQL Server"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nThe Service Master Key is the root of SQL Server's Encryption Hierarchy<\/a>. As such, there can only be one service master key per SQL Server instance. The service master key is used to protect (encrypt) other keys, mainly the database master keys. It cannot be used directly to encrypt data. And – you can't create one yourself...\n<\/p>\n

Creating the Service Master Key<\/h3>\n

\nWait, did you not just say that you could not create the service master key yourself? Well, you can and you cannot. However, before we go there, let us look at what this mysterious thing is used for.\n<\/p>\n

\nI already said that the service master key is used to protect database master keys. However, they can be protected with just a plain old password too. Another thing that actually requires the service master key is the storage of linked server login credentials. Their passwords are encrypted using the service master key. There is not a lot of documentation about this available, but I assume that actually any kind of credential<\/a> is protected by the service master key.\n<\/p>\n

\nSo, how do you create one? You do not need to create on yourself. Any time SQL Server has to protect one of those entities, it automatically creates a new service master key, if none already exists. If you for some reason want to force its creation, just create a linked server login or a database master key.<\/p>\n

What is the Service Master Key and How is it Protected?<\/h3>\n

\nThe service master key itself is a symmetric encryption key. For SQL Server version 2005 to 2008 R2 it was used with the triple DES encryption algorithm. Since SQL Server 2012 new service master keys are AES keys. However, upgrading an existing SQL Server installation does not change the key.\n<\/p>\n

\nThe service master key itself is protected by the Windows data protection API, using the password of SQL Server's service account as a key. This fact has two important implications<\/b>: First, your entire encryption hierarchy hinges on the strength of that very password. Make sure it is a very strong (and long) and truly random password. (You will never have to type it again after all.) Second, only the Windows account that was executing SQL Server at the time the key was created is able to access that key. If you change the service account, you will not be able to access the entire encryption hierarchy anymore. That potentially includes entire databases, if they are protected with TDE.\n<\/p>\n

\nHowever, if you use the SQL Server Configuration Manager to change the service account, it will automatically take care of decrypting the service master key before the account change and re-encrypting it afterwards. This is another reason, why you always want to use that tool when changing SQL Server service level settings.\n<\/p>\n

No Service Master Key Example<\/h3>\n

\nThere is no direct way to see if the current instance already has a service master key.<\/strike> (See Update below.) Therefore, I cannot really show you a good example either. There are however telltale signs that we can use, for example the is_master_key_encrypted_by_server<\/span> column in sys.databases<\/span>:\n<\/p>\n

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

\nA value of 1<\/span> in this column for any database means that its database master key is encrypted using the service master key, therefore the service master key must have been created before.\n<\/p>\n

Summary<\/h3>\n

\nThe service master key is the root of SQL Server's encryption hierarchy and potentially protects many other keys. The service master key itself is protected by the Windows data protection API and is bound to the service account executing SQL Server. It is created automatically when it is needed for the first time.\n<\/p>\n

Update (2014-04-26)<\/h3>\n

\nThere is a way after all to see if the service master key has been created yet. The service master key is a symmetric key stored in the master<\/span> database. As such it can be found using the sys.symetric_keys<\/span> catalog view in the that very database. You can for example use this query:\n<\/p>\n

\n[sql]\nSELECT *
\n FROM master.sys.symmetric_keys AS SK
\n WHERE SK.name = '##MS_ServiceMasterKey##'
\n[\/sql]\n<\/div>\n

\nIt will return a result set like the following:\n<\/p>\n

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

Encryption Hierarchy Series<\/h3>\n

\nThis article is part of the Encryption Hierarchy<\/a> series. Check out The SQL Server Encryption Hierarchy<\/a> for an overview of the hierarchy and a list of all posts that are part of the series.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Discover how the service master key is created, what it protects and how it is protected itself, and learn about the two important security implications that arise from the protection mechanism used.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":2369,"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":[273,170,38,171,15,83,140],"class_list":["post-2368","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cryptography","category-encryption-hierarchy","category-general","category-security","category-series","tag-cryptography","tag-master-key","tag-security-2","tag-service-master-key","tag-sql-server","tag-sql-server-service-account","tag-symmetric-key"],"acf":[],"yoast_head":"\nHow to Create a Service Master Key in SQL Server - sqlity.net<\/title>\n<meta name=\"description\" content=\"Discover how the service master key is created, what it protects and how it is protected itself, and learn about the two important security implications.\" \/>\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\/2368\/service-master-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 Service Master Key in SQL Server - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Discover how the service master key is created, what it protects and how it is protected itself, and learn about the two important security implications.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2368\/service-master-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-04-25T15:00:14+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T17:28:54+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/does_a_service_master_key_exist.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"816\" \/>\n\t<meta property=\"og:image:height\" content=\"483\" \/>\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=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2368\\\/service-master-key\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2368\\\/service-master-key\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"How to Create a Service Master Key in SQL Server\",\"datePublished\":\"2014-04-25T15:00:14+00:00\",\"dateModified\":\"2014-11-13T17:28:54+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2368\\\/service-master-key\\\/\"},\"wordCount\":750,\"commentCount\":2,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2368\\\/service-master-key\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/04\\\/does_a_service_master_key_exist.jpg\",\"keywords\":[\"Cryptography\",\"Master Key\",\"security\",\"Service Master Key\",\"SQL Server\",\"SQL Server Service Account\",\"symmetric key\"],\"articleSection\":[\"Cryptography\",\"Encryption Hierarchy\",\"General\",\"Security\",\"Series\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2368\\\/service-master-key\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2368\\\/service-master-key\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2368\\\/service-master-key\\\/\",\"name\":\"How to Create a Service Master Key in SQL Server - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2368\\\/service-master-key\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2368\\\/service-master-key\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/04\\\/does_a_service_master_key_exist.jpg\",\"datePublished\":\"2014-04-25T15:00:14+00:00\",\"dateModified\":\"2014-11-13T17:28:54+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Discover how the service master key is created, what it protects and how it is protected itself, and learn about the two important security implications.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2368\\\/service-master-key\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2368\\\/service-master-key\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2368\\\/service-master-key\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/04\\\/does_a_service_master_key_exist.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/04\\\/does_a_service_master_key_exist.jpg\",\"width\":816,\"height\":483,\"caption\":\"Does the Service Master Key exist yet?\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2368\\\/service-master-key\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Create a Service Master Key in SQL Server\"}]},{\"@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 Service Master Key in SQL Server - sqlity.net","description":"Discover how the service master key is created, what it protects and how it is protected itself, and learn about the two important security implications.","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\/2368\/service-master-key\/","og_locale":"en_US","og_type":"article","og_title":"How to Create a Service Master Key in SQL Server - sqlity.net","og_description":"Discover how the service master key is created, what it protects and how it is protected itself, and learn about the two important security implications.","og_url":"https:\/\/sqlity.net\/en\/2368\/service-master-key\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-04-25T15:00:14+00:00","article_modified_time":"2014-11-13T17:28:54+00:00","og_image":[{"width":816,"height":483,"url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/does_a_service_master_key_exist.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":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2368\/service-master-key\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2368\/service-master-key\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"How to Create a Service Master Key in SQL Server","datePublished":"2014-04-25T15:00:14+00:00","dateModified":"2014-11-13T17:28:54+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2368\/service-master-key\/"},"wordCount":750,"commentCount":2,"image":{"@id":"https:\/\/sqlity.net\/en\/2368\/service-master-key\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/does_a_service_master_key_exist.jpg","keywords":["Cryptography","Master Key","security","Service Master Key","SQL Server","SQL Server Service Account","symmetric key"],"articleSection":["Cryptography","Encryption Hierarchy","General","Security","Series"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2368\/service-master-key\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2368\/service-master-key\/","url":"https:\/\/sqlity.net\/en\/2368\/service-master-key\/","name":"How to Create a Service Master Key in SQL Server - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2368\/service-master-key\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2368\/service-master-key\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/does_a_service_master_key_exist.jpg","datePublished":"2014-04-25T15:00:14+00:00","dateModified":"2014-11-13T17:28:54+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Discover how the service master key is created, what it protects and how it is protected itself, and learn about the two important security implications.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2368\/service-master-key\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2368\/service-master-key\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2368\/service-master-key\/#primaryimage","url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/does_a_service_master_key_exist.jpg","contentUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/does_a_service_master_key_exist.jpg","width":816,"height":483,"caption":"Does the Service Master Key exist yet?"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2368\/service-master-key\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"How to Create a Service Master Key in SQL Server"}]},{"@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\/04\/does_a_service_master_key_exist.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-Cc","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2368","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=2368"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2368\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media\/2369"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2368"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2368"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2368"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}