{"id":315,"date":"2014-07-22T11:08:25","date_gmt":"2014-07-22T03:08:25","guid":{"rendered":"http:\/\/suherman.asia\/w2\/?p=315"},"modified":"2014-07-22T11:08:25","modified_gmt":"2014-07-22T03:08:25","slug":"meng-enkripsi-database","status":"publish","type":"post","link":"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html","title":{"rendered":"Meng-enkripsi Database"},"content":{"rendered":"<p>Biasanya kita punya kekhawatiran jika database kita tercuri oleh orang lain kemudian dapat di restore di Server Instance yang lain.<!--more-->Sejak versi SQL Server 2008 sudah tersedia sebuah fitur untuk dapat melakukan enkripsi terhadap database yaitu dengan menggunakan <strong>Transparent Data Encryption (TDE)<\/strong>.\u00a0Fitur ini hanya bisa ditemukan pada SQL Server versi 2008 keatas dan hanya untuk edisi Data Center atau Enterprise. Enkripsi dilakukan pada level file mdf dan log, sehingga jika suatu waktu media penyimpanan database tersebut hilang tercuri, maka database tersebut tidak dapat di restore tanpa adanya certificate untuk membukanya.<\/p>\n<p>Arsitektur dari mekanisme TDE ini adalah sb:<\/p>\n<p><a href=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/07\/TDE.gif\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-316\" src=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/07\/TDE.gif\" alt=\"TDE\" width=\"504\" height=\"543\" \/><\/a><\/p>\n<p>Enkripsi yang digunakan oleh TDE menggunakan algoritma\u00a0<a title=\"Advanced Encryption Standard\" href=\"http:\/\/en.wikipedia.org\/wiki\/Advanced_Encryption_Standard\" target=\"_blank\">AES <\/a>dan <a title=\"Triple DES\" href=\"http:\/\/en.wikipedia.org\/wiki\/Triple_DES\" target=\"_blank\">3DES<\/a>.\u00a0proses enkripsi dan dekripsinya terjadi secara background oleh SQL Server. Dalam menerapkan TDE ini pun tidak ada perubahan yang perlu dilakukan di sisi aplikasi, karena semua berlangsung di SQL Server.<\/p>\n<p>Mari kita mulai bagaimana menerapkan enkripsi database, dalam demo ini saya menggunakan SQL Server 2014 Developer. pertama kita siapkan terlebih dahulu 1 buah database [TesTDE]\u00a0dan 1 buah table Customers<\/p>\n<pre title=\"Create Database and Table\" class=\"lang:tsql decode:true \">USE master\r\nGO\r\nCREATE DATABASE [TesTDE]\r\nGO\r\n\r\nUSE [TesTDE]\r\nGO\r\nCREATE TABLE [dbo].[Customers](\r\n\t[Id] [int] NOT NULL,\r\n\t[Name] [nvarchar](50) NULL,\r\n\t[Address] [nvarchar](150) NULL,\r\n CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED \r\n(\r\n\t[Id] ASC\r\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, \r\nALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\r\n) ON [PRIMARY]\r\nGo\r\n<\/pre>\n<p>Kemudian kita buat sebuah master key yang akan digunakan\u00a0untuk proses enkripsi database nya.<\/p>\n<pre title=\"Create Master Key Encryption\" class=\"lang:tsql decode:true \">USE master;\r\nGO\r\nCREATE MASTER KEY ENCRYPTION\r\n    BY PASSWORD = 'Suherm4nBl0g';\r\nGO\r\nCREATE CERTIFICATE TesTDEDbCert\r\n  WITH SUBJECT = 'TesTDE_Db Certificate';<\/pre>\n<p>Selanjutnya kita siapkan sebuah Database Encryption Key. dalam proses ini juga kita menentukan jenis algorima enkripsi apa yang akan dalam mekanisme enkripsinya, disini saya mencontohkan menggunakan algoritma 3DES.<\/p>\n<p>Saat kita mengeksekusi TSQL diatas, akan ada peringatan sebagai berikut :<\/p>\n<pre title=\"Warning\" class=\"lang:tsql decode:true \">Warning: The certificate used for encrypting the database encryption key \r\nhas not been backed up. You should immediately back up the certificate \r\nand the private key associated with the certificate. \r\nIf the certificate ever becomes unavailable or if you must restore \r\nor attach the database on another server, you must have backups of \r\nboth the certificate and the private key or you will not be able \r\nto open the database.\r\n<\/pre>\n<p>Pada peringatan tampak jelas bahwa sangat direkomendasikan untuk melakukan backup terhadap Master Key. Ini untuk mengantisipasi jika kita ingin melakukan restore atau attach database ke instance server yang berbeda.<\/p>\n<pre class=\"lang:tsql decode:true \">USE master;\r\nGO\r\nBACKUP CERTIFICATE TesTDEDbCert TO FILE = 'D:\\TesTDEDbCert.cert';<\/pre>\n<p>kemudian langkah terakhir adalah mengaktivasi database TesTDE untuk di enkripsi<\/p>\n<pre title=\"Activated\" class=\"lang:tsql decode:true\">USE TesTDE;\r\nGO\r\nALTER DATABASE TesTDE SET ENCRYPTION ON;\r\n<\/pre>\n<p>Perlu diingat, jika ini diterapkan pada database yang besar, maka akan dibutuhkan waktu yang lama untuk mengaktifkan enkripsi pada sebuah database karena seluruh data akan di enkripsi oleh SQL Server.<\/p>\n<p>Untuk meyakinkan bahwa database TesTDE telah terenkripsi dapat kita lihat sebagai berikut<\/p>\n<pre class=\"lang:tsql decode:true\">SELECT NAME, IS_ENCRYPTED\r\n FROM sys.databases;<\/pre>\n<p>Jika nanti database TesTDE bernilai 1 maka itu menunjukkan database tersebut terenkripsi.<\/p>\n<p>Demikian, semoga bermanfaat.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Biasanya kita punya kekhawatiran jika database kita tercuri oleh orang lain kemudian dapat di restore di Server Instance yang lain.<\/p>\n<p class=\"continue-reading-button\"> <a class=\"continue-reading-link\" href=\"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html\">Continue reading<i class=\"crycon-right-dir\"><\/i><\/a><\/p>\n","protected":false},"author":1,"featured_media":318,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[8,22],"class_list":["post-315","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server","tag-encrypt","tag-security"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v24.7 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Meng-enkripsi Database - Suherman Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Meng-enkripsi Database - Suherman Blog\" \/>\n<meta property=\"og:description\" content=\"Biasanya kita punya kekhawatiran jika database kita tercuri oleh orang lain kemudian dapat di restore di Server Instance yang lain. Continue reading\" \/>\n<meta property=\"og:url\" content=\"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html\" \/>\n<meta property=\"og:site_name\" content=\"Suherman Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/emantin34\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/emantin34\" \/>\n<meta property=\"article:published_time\" content=\"2014-07-22T03:08:25+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/07\/data-encryption-595x3351.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"500\" \/>\n\t<meta property=\"og:image:height\" content=\"282\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\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\":\"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html#article\",\"isPartOf\":{\"@id\":\"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html\"},\"author\":{\"name\":\"admin\",\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"headline\":\"Meng-enkripsi Database\",\"datePublished\":\"2014-07-22T03:08:25+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html\"},\"wordCount\":312,\"commentCount\":0,\"publisher\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"image\":{\"@id\":\"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html#primaryimage\"},\"thumbnailUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/07\/data-encryption-595x3351.jpg\",\"keywords\":[\"Encrypt\",\"Security\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html\",\"url\":\"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html\",\"name\":\"Meng-enkripsi Database - Suherman Blog\",\"isPartOf\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#website\"},\"primaryImageOfPage\":{\"@id\":\"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html#primaryimage\"},\"image\":{\"@id\":\"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html#primaryimage\"},\"thumbnailUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/07\/data-encryption-595x3351.jpg\",\"datePublished\":\"2014-07-22T03:08:25+00:00\",\"breadcrumb\":{\"@id\":\"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html#primaryimage\",\"url\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/07\/data-encryption-595x3351.jpg\",\"contentUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/07\/data-encryption-595x3351.jpg\",\"width\":500,\"height\":282},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/suherman.asia\/w2\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Meng-enkripsi Database\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/suherman.asia\/w2\/#website\",\"url\":\"http:\/\/suherman.asia\/w2\/\",\"name\":\"Suherman Blog\",\"description\":\"Just Another Geek Site\",\"publisher\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/suherman.asia\/w2\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g\",\"caption\":\"admin\"},\"logo\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/\"},\"sameAs\":[\"http:\/\/suherman.asia\",\"https:\/\/www.facebook.com\/emantin34\",\"https:\/\/x.com\/emantin34\"],\"url\":\"http:\/\/suherman.asia\/w2\/author\/admin\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Meng-enkripsi Database - Suherman Blog","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":"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html","og_locale":"en_US","og_type":"article","og_title":"Meng-enkripsi Database - Suherman Blog","og_description":"Biasanya kita punya kekhawatiran jika database kita tercuri oleh orang lain kemudian dapat di restore di Server Instance yang lain. Continue reading","og_url":"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html","og_site_name":"Suherman Blog","article_publisher":"https:\/\/www.facebook.com\/emantin34","article_author":"https:\/\/www.facebook.com\/emantin34","article_published_time":"2014-07-22T03:08:25+00:00","og_image":[{"width":500,"height":282,"url":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/07\/data-encryption-595x3351.jpg","type":"image\/jpeg"}],"author":"admin","twitter_misc":{"Written by":"admin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html#article","isPartOf":{"@id":"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html"},"author":{"name":"admin","@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"headline":"Meng-enkripsi Database","datePublished":"2014-07-22T03:08:25+00:00","mainEntityOfPage":{"@id":"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html"},"wordCount":312,"commentCount":0,"publisher":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"image":{"@id":"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html#primaryimage"},"thumbnailUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/07\/data-encryption-595x3351.jpg","keywords":["Encrypt","Security"],"articleSection":["SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html#respond"]}]},{"@type":"WebPage","@id":"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html","url":"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html","name":"Meng-enkripsi Database - Suherman Blog","isPartOf":{"@id":"http:\/\/suherman.asia\/w2\/#website"},"primaryImageOfPage":{"@id":"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html#primaryimage"},"image":{"@id":"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html#primaryimage"},"thumbnailUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/07\/data-encryption-595x3351.jpg","datePublished":"2014-07-22T03:08:25+00:00","breadcrumb":{"@id":"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html#primaryimage","url":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/07\/data-encryption-595x3351.jpg","contentUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/07\/data-encryption-595x3351.jpg","width":500,"height":282},{"@type":"BreadcrumbList","@id":"http:\/\/suherman.asia\/w2\/meng-enkripsi-database.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/suherman.asia\/w2"},{"@type":"ListItem","position":2,"name":"Meng-enkripsi Database"}]},{"@type":"WebSite","@id":"http:\/\/suherman.asia\/w2\/#website","url":"http:\/\/suherman.asia\/w2\/","name":"Suherman Blog","description":"Just Another Geek Site","publisher":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/suherman.asia\/w2\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g","caption":"admin"},"logo":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/"},"sameAs":["http:\/\/suherman.asia","https:\/\/www.facebook.com\/emantin34","https:\/\/x.com\/emantin34"],"url":"http:\/\/suherman.asia\/w2\/author\/admin"}]}},"_links":{"self":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/315","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/comments?post=315"}],"version-history":[{"count":1,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/315\/revisions"}],"predecessor-version":[{"id":319,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/315\/revisions\/319"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/media\/318"}],"wp:attachment":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/media?parent=315"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/categories?post=315"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/tags?post=315"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}