Meng-enkripsi Database

Biasanya kita punya kekhawatiran jika database kita tercuri oleh orang lain kemudian dapat di restore di Server Instance yang lain.Sejak versi SQL Server 2008 sudah tersedia sebuah fitur untuk dapat melakukan enkripsi terhadap database yaitu dengan menggunakan Transparent Data Encryption (TDE). Fitur 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.

Arsitektur dari mekanisme TDE ini adalah sb:

Enkripsi yang digunakan oleh TDE menggunakan algoritma AES dan 3DES. proses 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.
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] dan 1 buah table Customers

USE master
GO
CREATE DATABASE [TesTDE]
GO

USE [TesTDE]
GO
CREATE TABLE [dbo].[Customers](
 [Id] [int] NOT NULL,
 [Name] [nvarchar](50) NULL,
 [Address] [nvarchar](150) NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Go

Kemudian kita buat sebuah master key yang akan digunakan untuk proses enkripsi database nya.

USE master;
GO
CREATE MASTER KEY ENCRYPTION
    BY PASSWORD = 'Suherm4nBl0g';
GO
CREATE CERTIFICATE TesTDEDbCert
  WITH SUBJECT = 'TesTDE_Db Certificate';

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.

Saat kita mengeksekusi TSQL diatas, akan ada peringatan sebagai berikut :

Warning: The certificate used for encrypting the database encryption key 
has not been backed up. You should immediately back up the certificate 
and the private key associated with the certificate. 
If the certificate ever becomes unavailable or if you must restore 
or attach the database on another server, you must have backups of 
both the certificate and the private key or you will not be able 
to open the database.

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.

USE master;
GO
BACKUP CERTIFICATE TesTDEDbCert TO FILE = 'D:\TesTDEDbCert.cert';

kemudian langkah terakhir adalah mengaktivasi database TesTDE untuk di enkripsi

USE TesTDE;
GO
ALTER DATABASE TesTDE SET ENCRYPTION ON;

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.

Untuk meyakinkan bahwa database TesTDE telah terenkripsi dapat kita lihat sebagai berikut

SELECT NAME, IS_ENCRYPTED
 FROM sys.databases;

Jika nanti database TesTDE bernilai 1 maka itu menunjukkan database tersebut terenkripsi.
Demikian, semoga bermanfaat.

Read More

Cara Install Sharepoint 2013 step by step

1 - Introduction

panduan instalasi SharePoint 2013 pada single server dengan SQL Server.

instalasi 1 buah server tunggal terdiri dari satu server yang menjalankan SQL Server dan SharePoint 2013.   kita dapat menginstal dan mengkonfigurasi SharePoint 2013 pada satu buah server jika Anda hosting hanya beberapa situs untuk sejumlah pengguna terbatas atau jika kita ingin membuat percobaan atau farm pengembangan. Konfigurasi ini juga berguna jika kita ingin mengkonfigurasi sebuah farm untuk memenuhi kebutuhan kita terlebih dahulu, dan kemudian menambahkan server ke farm pada tahap berikutnya.

berikut requirment untuk virtual machine yang akan di pasang sharepoint :

Server : Virtual machine based on VMWare Player 4.0.1
Server OS : Windows 2008 server R2 64 bit
SQL Server : SQL Server 2012 64 bit
SharePoint : SharePoint Server 2013 Preview
Duration : 10 hours if you haven't downloaded the OS and SQL Server 2012 softwares

langkah ideal untuk pengenmbangan pada lingkungan sharepoint 2013

Seperti biasa Semua produk sebelumnya seringkali gratis (VMWare Player) atau dapat didownload sebagai versi percobaan (Windows Server 2088 R2, SQL Server 2012), sehingga Kita dapat menginstal SharePoint 2013 menggunakan panduan ini tanpa harus membeli perangkat lunak apapun. SharePoint 2013 tersedia sebagai versi Beta.

Seperti kita mengalami masalah pertunjukan untuk membangun lingkungan atau group website dengan SharePoint dari versi 2010 Saya akan memberikan dalam panduan, operasi, tips dan trik yang dapat meningkatkan kinerja untuk lingkungan percobaan atau pengembangan.

 saya juga akan menggunakan beberapa several local service accounts agar compliant setidaknya dengan privilege administration policy.

Apa yang baru di instalasi sahrepoint 2013

1. Kita tidak perlu mengkonfigurasikan server kita untuk sharepoint 2013 , semuanya dapat dilakukan oleh software preinstallation pada SharePoint 2013.

2. The preinstallation sudah sempurna. Berbeda dengan versi Beta Seperti pada SharePoint 2013.

2 - Membuat Virtual Machine

2.1 - Menggunakan sebuah external drive

Jika kita akan meningkatkan kinerja mesin pertamatama dengan memilih drive eksternal yang baik yaitu menggunakan drive e-Sata SSD.

2.2 - Downloading VMWare Player

kita dapat download VM ware versi (5.0) disini. 

2.3 - Download Windows Server 2008 R2 Evaluation 

2.4 - Creating the virtual machine

Setelah Kita memiliki file iso untuk OS, buka VMWare Player dan klik "Create a new Virtual Machine".

Pilih "Installer file gambar disk (iso)" tombol radio dan browse untuk file iso download sebelumnya. wizard akan mendeteksi versi dari OS.

Click next

wizard akan diambil secara otomatis akun pengguna.

Tidak memberikan kunci produk Kita tidak perlu untuk versi evaluasi ini, tetapi mengambil waktu untuk memberikan password karena akun ini akan menjadi salah satu Administrator menyumbang Virtual Machine baru Anda dan menggunakan akun Administrator yang belum password akan menimbulkan banyak masalah ketika mengkonfigurasi mesin Kita dan lebih banyak kesulitan ketika bekerja dengan SharePoint 2013.

Pilih versi Windows 2008 R2 OS. untuk lingkungan developement,

klik next


Saya sarankan untuk memilih versi standar untuk mengurangi fitur dan penggunaan sumber daya.

Click OK for the "no license key" warning message

pilih nama virtual machine dan lokasi penyimpanan path.

Menentukan utama Hard Disk settings.For ukuran HD, saya sarankan 35 GB terutama jika Kita ingin menginstal visual studio 2010 atau 2012 nanti. Kami juga akan menginstal SQL Server 2012 kemudian dan itu adalah ruang disk lebih menuntut daripada versi sebelumnya.


Setelah melakukan semua instalasi (tanpa Visual Studio), kita memiliki 13 GB tersisa.
Jadi kita mungkin bisa mengurangi ruang disk preallocated untuk 30GB atau setidaknya 27 GB.

Seperti yang akan kita lihat nanti, semua file database dan log file akan ditempatkan pada drive lain, sehingga ruang disk utama tidak harus menurun begitu banyak setelah instalasi.

Very important : for performance reasons use a single file option for storing the disk 

click Next

wizard meminta Anda siap untuk membuat mesin virtual dan meringkas
pilihan Anda, tapi jangan memulai penciptaan mesin sekarang.


Klik pada tombol "Customize Hardware ...", karena kita memiliki konfigurasi penting
operasi yang akan dilakukan untuk meningkatkan kinerja lingkungan.

Untuk mesin Memory, itu tergantung pada memori host Anda.

Saya memiliki 4GB RAM pada komputer host saya (laptop) dan aku punya hasil terbaik dengan 2,5 GB RAM untuk mesin virtual saya.

Jangan lupa untuk mengatur jumlah yang sama dari prosesor sebagai tuan rumah Anda jika Anda ingin penampilan terbaik

Sekarang, selalu untuk masalah kinerja, lepaskan semua yang dapat terputus untuk lingkungan developement SharePoint 2013, gigi biru dan akhirnya USB

sound card

printer

Kemudian Anda dapat menutup pop up, kembali ke layar utama Wizard dan memulai instalasi OS pada virtual machine

Aku melihat manfaat menggunakan disk SSD. instalasi hanya berlangsung 20 menit. Dengan disk normal, biasanya 30 sampai 45 menit.


3 - Configuring your Virtual machine

3.1 - Windows Activating

Go back to the "Initial Configuration tasks" windows and we are going to go through the standard configuration operations easily because the windows lits them.

Kembali ke "Initial Configuration tasks" windows dan kita akan pergi melalui operasi konfigurasi standar dapat dengan mudah karena jendela lits mereka.

 Pastikan mesin virtual memiliki acess internet.

 

Kita harus mengaktifkan Windows untuk mengambil keuntungan dari 180 hari dari process.Click evaluasi pada link "Aktifkan Windows" .

Do not look for any product key, as said before, you do not need any, just click next.

aktivasi akan dilakukan secara otomatis melalui internet. Anda akan melihat kemudian jumlah aktivasi dan jumlah hari yang tersisa di sudut kanan bawah desktop ..

3.2 - Changing Computer Name

Now, always on the "Initial Configuration Tasks" panel, click the "Provide computer name and domain" link,then set your new Virtual machine name.

3.3 - Installing updates

Click the "Download and install updates link", and proceed to the latest updates installation.


Then set automatic updates to only warn you when new updates are available for download, because you don't want the download and updates installation to bother you unexpectedly when you are doing a important operation of development on your development environment.

3.4 - Disabling Internet Explorer Enhanced Security

To disable Enhanced Security Configuration to specific users by using a computer running Windows Server 2008:

1. Click Start, point to Administrative Tools, and then click Server Manager.
2. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.
3. Under Security Information, click Configure IE ESC.
4. Under Administrators, click On (Recommended) or Off, depending on your desired configuration.
5. Under Users, click On (Recommended) or Off, depending on your desired configuration.
6. Click OK.
7. Restart Internet Explorer to apply Enhanced Security Configuration.

3.5 - Adding Desktop Experience feature

It would be interesting for a development machine to be able to use the Windows Photo Viewer in order to check some screenshots taken during configuration phases, or when wanting to report an issue. To be able to use this feature of Windows server 2008 R2 you have to activate the Desktop Experience feature.

Go back to the  "Initial Configuration tasks" window and click "Add feature", then locate "Desktop Experience" check box and check it.

3 - Configuring your Virtual machine for best performances

Warning: most of the following operations are worth for a trial or  development environment, but you shouldn't do it for other environment (QA, staging, test, build, production,etc.)

3.1 Network configuration

In the network configuration options, uninstall the QoS Packet Scheduler and the Link Layer Topology Discovery components, and uncheck IPv6.

3.2 Turning off firewalls

There is three firewalls: Private, domain, Public. Turn them off.

3.3 Settings the system properties for best performances

Go to the computer properties:

Set the system for the best performances

Then in the "Advanced" tab adjust for best performances for program because you don't want a SharePoint or Windows process to bother you when you are programming on your development environment.

4 - Adding 3 new Hard Disks to your machine

We are now going to add disks to our virtual machine in order some files to be stored on them and increase the virtual machine performances.

There are 3 kinds of files we want to be stored on separated hard drives:

The paging file
The log files
The database files

4.1 How to add a new hard drive to a virtual machine

Go to the VMWare settings available at the top of your desktop. On the Hardware tab click the "Add..." button, select Hard disk and click next.

In this disk we are going to store the paging file, some of the database files and some of the log files, so set a preallocated disk space of 15GB.


Preallocate disk space and storing the disk using a single file increase the performances, so let's do it that way.

You can rename the disk if you want and choose a specific location on your external hard drive. I personally let the default options.

You can see the new disk in the VMWare settings when it is created.

4.2 How to add a new volume to the machine with the created hard drive

Now open the Computer Management panel

Under the Storage node, open the Disk Management. The system prompt you to Initialize the new hard drive.

Then right click the "Disk 1" lable and launch the "New Simple Volume" wizard

We are going to call this volume "Data". for best performances, select an allocation unit size of 64K

The new Simple volume wizard is the summarizing your choices. And when you click "Finish" the new volume will be formatted and added to your system.

4.3 Adding two new Hard drives to the machine

We are going to add 2 more hard drives to the machine. 5 GB each and called log and TempDB. You should obtain this after having finished the operations:

4.4 Storing the pagefile on the first hard disk

Go back to the computer properties, then on the performances settings, then on the Virtual Memory group click the "Change.." button.

1. Set the paging file to be stored on the Hard Drive D.

2. The initial size is equal to the RAM of your virtual machine

3. The maximum size is three times the initial size value.

5 - Installing the windows 2008 server R2 SP1

If you had an old version of the Windows 2008 Server R2 trial version you should now have to use the Windows automatic updates to pass the SP1. 
If you have downloaded the trial version of the OS recently, skip this step because the new trial version comes with the SP1 included.

6 - Installing SQL Server 2012

6.1 Creating the SQL_Service account - about least privilege administration

When installing software,  it is safer to use service account that has no permissions at all and to use different accounts for each process or service. This is the least privilege administration policy.

For a SQL Server installation within a SharePoint farm, Microsoft  recommend creating a service account.

6.2 Installing SQL Server 2012

Download the three files of the 64 bit version.

Running the install executable will unzipped the installation files

then run the the SETUP of SQL Server 2012

then, on the SQL Server Installation Center select Installation and "New SQL Server Stand alone installation"

Pass the Product Key screen

the License Terms

Allow Setup Files to be installed

Pass the Setup Support Rules

On the Setup Role screen, let the first default option checked

Pass the installation rules

Then on the feature selection, for a basic SharePoint installation you have ONLY 2 checkboxes to check:Database Engine ServicesManagement Tools - Basic

Then for performances purpose set the root instance directory to be on the D drive

Pass the Disk Space Requirements

now is the time to use the service account previously created. In the next screenshot, I have made a mistake. The service account should have been used for the SQL Server Agent and the SQL Server Browser services.

Then set the collation for SharePoint

On the Database Engine Configuration Screen, choose mixed mode because SharePoint sometimes allows it, and because it is useful to be able to use the sa admin account if there is some problems with the administrator domain or local account. 
Add the current user as administrator because when installing SQL Server you often use the administrator account.

Now is the time to split our database files for them to be stored on the different hard drives in order to increase our machine performances.

regarding the filestream it is not clear yet. Here is a link on the topic (FILESTREAM with SharePoint 2010) where you can read this comment:"Microsoft reckonts its a bad idea to have filestream enabled in Sharepoint." 
I personally let it unchecked on this installation:

Pass the Error Reporting screen

Pass the Installation configuration Rules

The installation wizard summarize our choices and you can run the installation

You should obtain this screen after the installation of SQL Server 2012 is complete:

7 - Configuring SQL Server 2012 for best performances

7.1 Within SQL Server Management Studio

 Open SQL Server management studio

Right click the Database engine and click "Properties".

In the "Memory" screen set the maximum Server Memory to 500 MB.

In the "Advanced" screen set the "Maximum Degree of Parallelism" to 1.

 Locate the tempdb database node, right click it and click properties

In the "Files" screen,

1. For performances reasons, always use, at most, one data file for each CPU core of the system. Ideally, you should use one data file for each 2 CPU cores.
So, if you have two CPU core on your host machine as I have, add a new file that you will name tempdev2, it will be automatically created on the D drive and that is a good thing for performances. 

2. Set the initial size of the three files to 1000 MB (1 GB).
3. Set the autogrowth to 512 MB

Warning.

Don't forget. You will have to do this for other databases as long as you are adding web applications and search services to your farm : 
Use more than one data file in the primary file group for the TempDB, search DBs and content DBs of SharePoint.

7.2 Within SQL Server Configuration Manager

 Open SQL Configuration Manager.

Locate Protocols for MSSQLSERVER and enable "Named pipes"

7.3 The "locked pages" topic

In his post about  increasing the performances of a SharePoint development environment, André Vala recommends to authorize SQL Server to use the Lock Pages in Memory feature to keep Windows from unloading the memory pages that might still be needed, leading to a performance degradation.

I won't illustrate this topic in this post rignt now, but maybe later on an updated version. I provide you with the:

Generic link on the topic

SQL Server 2012 updated link

8 - Installing SharePoint 2013

8.1 Starting the SharePoint 2013 Server Installation

Download the .img file of SharePoint 2013 Server.

 Go to the Virtual Machine Settings of VMWare and navigate to the "CD/DVD (IDE)" settings

In the "Use ISO image file" field, reference the downloaded image of the SharePoint 2013 Server software, and check the "Connected" check box.

When returning to tour machine, the SharePoint 2013 Server installation software will automatically start up.

8.2 Running the SharePoint 2013 Preview Products Preparation Tool (installing the SharePoint 2013 prerequisites)

Click the "install software prerequisites" link, and pass the License terms

Pass the summary

You will have to reboot your virtual machine

the installation will continue then,

and will complete.

8.3 Running the Software Update KB2759112

In the Microsoft official installation guide for SharePoint 2013, after you complete the Microsoft SharePoint Products Preparation Tool, you must also install the following.

KB 2554876
KB 2708075
KB 2759112

The two first items are included in the Windows 2008 Server R2 SP1, so, no need to install them. This is the link to get the third one KB 2759112.

Run the KB 2759112.

8.4 Creating the database access account in compliance with the least privilege administration policy

You can check the  Microsoft recommendations about this topic and then needed accounts that I have already referenced previously in this post when we have installed SQL Server 2012.

Create a new local account and call it SPS_Farm

We are now done with the accounts creation required for the main installation of SharePoint 2013.

The first account, SQL Server service account, SQL_Service was created previously. The setup user account is the one you are currently logged in with, the only one that has the administration permissions for the virtual machine. And we have just created SPS_Farm, the server farm or database
access account.

Go back to the SharePoint 2013 installation screen and this time click on the "Install SharePoint Server" link. You are prompted for the preview license key that is available for free so I did not blur it in my screen shot.

Pass the License terms

In the "Server Type" screen, choose the "Complete" installation.

In the File Location screen, make the search index files to be located on your previously created D drive.

Then, launch the installation.

On the Run Configuration Wizard, UNCHECK the check box, because, as we are using local accounts the SharePoint 2013 Farm creation will fail after we have typed the Server Farm Account with this error message "the specified user SPS_Farm is a local account. Local accounts should only be used in stand alone mode."

So  UNCHECK the check box and CLOSE the wizard

8.5 Using SharePoint 2010 Management Shell to create the SharePoint 2010 Configuration databases

Open the SharePoint 2013 Management Shell

Type the following command

New-SPConfigurationDatabase

run the command by pressing the Carriage Return Key

The Shell willl ask you for:
The database name --> choose any name, for example SharePoint_Config
The database Server name --> type the name of your Virtual Machine in my case VMDEV-016
You will be then prompted for the Farm credentials. This the time to use the Server Farm Account, SPS_Farm. So type the account login and password.

Then you are asked for a passphrase. You can use P@ssw0rd that matches the security policies
required. (This passphrase will be asked when you will add a new server to the SharePoint Farm)

The system will take a LONG time (something like 15 minutes) to create the SharePoint Farm databases, so don't panic and just wait.

You will obtain this screen when it is complete:

8.6 Finalizing the SharePoint installation

Now is the time to finally run the SharePoint 2013 Products Configuration Wizard that we have closed previously.

Click, yes on the warning dialog

do not disconnect from this server farm

I recommend to specify a port number for your trial and development environments that will allow you to always know how to access the SharePoint 2013 central administration by typing an url. I personally always use the 55555 port number.

The wizard is summarizing the farm settings,

then perform the last configuration tasks, for finally announcing a successful configuration.

When you click "Finish" the Central Administration site for SharePoint 2013 is going to be provisioned. Then, the Central Administration site for SharePoint 2013 automatically opens.

You are prompted for your credentials.

The SharePoint 2013 Central Administration site is displayed and you are asked to participate to the Customer Experience Improvment Program.

Then you are asked to use the configuration wizard. For always performance reasons, don't use this wizard, because you want the minimal services and processes to run in your trial or development environement.

you are finally able to display the Sharepoint 2013 Central Administration site while knowing your virtual machine will run with the best performances!

Very well don

Read More