Wednesday, March 11, 2020

Oracle EBS 12.2.x Adding a node in Shared File System

The Oracle E-Business Suite Release 12.2 architecture is a framework for multi-tiered, distributed computing. In a shared file system, all application tier files including the INSTANCE_TOP are installed on a shared disk resource, which is mounted on each application tier node. Any application tier node can be configured to perform any of the standard application tier services, such as Forms, Web and Concurrent Processing (Batch) services. All changes made to the shared file system are immediately accessible to all application tier nodes.
Note:
•All application tier nodes in a shared file system must be running the same operating system.
•Shared Application Tier File System can not be a read-only-file system unlike in the previous releases.
•User ID and Group ID should be consistent across all nodes to avoid file access permission issues.
•The same absolute path must be retained for the shared file system mount points on each node.
•The value for the context variable "s_atName" must be same across all the application tier node context files.

Step by Step Configuration
1. Start weblogic admin server and then Run pre-clone on application tier node 1 on both RUN (fs2) and PATCH (fs1) file systems.

On the RUN file system:
[applmgr@ed-olapplin1 ~]$ ./t1/tst/fs2/EBSapps/appl/APPStst_ed-olapplin1.env
[applmgr@ed-olapplin1 ~]$ cd $ADMIN_SCRIPTS_HOME
[applmgr@ed-olapplin1 scripts]$ adadminsrvctl.sh start 
[applmgr@ed-olapplin1 scripts]$ adadminsrvctl.sh status
[applmgr@ed-olapplin1 scripts]$ adpreclone.pl appsTier
On the PATCH file system:
[applmgr@ed-olapplin1 ~]$ . /t1/tst/fs1/EBSapps/appl/APPStst_ed-olapplin1.env
[applmgr@ed-olapplin1 ~]$ cd $ADMIN_SCRIPTS_HOME
[applmgr@ed-olapplin1 scripts]$ adadminsrvctl.sh status
[applmgr@ed-olapplin1 scripts]$ adadminsrvctl.sh start forcepatchfs

[applmgr@ed-olapplin1 scripts]$ adpreclone.pl appsTier
2. Check below context file parameters settings on both RUN & PATCH files systems.
<shared_file_system oa_var="s_shared_file_system">true</shared_file_system>
<APPL_TOP_NAME oa_var="s_atName">ed-olapplin1</APPL_TOP_NAME>

3. On the new node (ed-olapplin2) that is to be added do the below steps. The node to be added now will be known as the secondary application tier node. This node can be configured to run all services except for the Oracle WebLogic Administration Server.
[applmgr@ed-olapplin2 ~]$  more /etc/oraInst.loc
inventory_loc=/t1/tst/oraInventory
inst_group=oinstall
[applmgr@ed-olapplin2 ~]$ export PATH=/t1/tst/fs2/FMW_Home/webtier/perl/bin:$PATH
4. It is recommended to keep the Instance Top (INST_TOP=/t1/tst/fs2/inst/apps/tst_edolapplin1 AND /t1/tst/fs2/inst/apps/tst_ed-olapplin2) on the shared file system for Ease of maintenance and The number of application tier processes writing to this location is limited when compared with the previous releases. 

5. Ensure the WebLogic Administration Server is running from both run and Patch file system on the primary application tier node [on ED-OLAPPLIN1] if not run it as shown in Step 1 above.
[applmgr@ed-olapplin2 ~]$ export PATH=/t1/tst/fs2/FMW_Home/webtier/perl/bin:$PATH
6. A sample pairsfile for the run/patch file system is instantiated into the instance home on the primary application tier node. The file is called <SID>_<primary_node_name>_.txt, and located in the <inst_top>/appl/admin/ directory. For a configuration with an instance name of VIS, the pairsfile will be /u01/12.2/fs1/inst/apps/VIS_appstier1/appl/admin/VIS_appstier1.txt.
/t1/tst/fs2/inst/apps/tst_ed-olapplin1/appl/admin/tst_ed-olapplin1.txt
# cd $INST_TOP/appl/admin
# pwd
/t1/tst/fs2/inst/apps/tst_ed-olapplin1/appl/admin
# mkdir –p /t1/tst/pairsfile
# cp /t1/tst/fs2/inst/apps/tst_ed-olapplin1/appl/admin/tst_ed-olapplin1.txt /t1/tst/pairsfile/mypairsfile.txt
7. Execute adclonectx utility to configure both run and Patch file system. Note that since we have to execute the adconectx.pl script from the new node [ED-OLAPPLIN2] that will be added that is why we are copying the mypairsfile.txt and tst_ed-olapplin1.xml file on common shared location.
[applmgr@ed-olapplin2 ~]$ export PATH=/t1/tst/fs2/FMW_Home/webtier/perl/bin:$PATH
[applmgr@ed-olapplin2 bin]$ cd /t1/tst/fs2/EBSapps/comn/clone/bin
[applmgr@ed-olapplin2 bin]$ perl adclonectx.pl addnode contextfile=/t1/tst/fs2/inst/apps/tst_ed-olapplin1/appl/admin/tst_ed-olapplin1.xml pairsfile=/t1/tst/pairsfile/mypairsfile.txt outfile=/t1/tst/pairsfile /mypairsOUTPUT.txt dualfs=yes
Below is the mypairsfile.txt file content,
# $Header: pairs_file_dualfsclone_ux_txt.tmp 120.0.12020000.2 2015/07/29 19:36:02 jmajumde noship $
# Copyright (c) 2005, 2015  Oracle and/or its affiliates.
# All rights reserved.
# Version 12.0.0
#
# This is a sample pairs file generated by the autoconfig tool based on the
# context values set on the primary application tier node.
# This pairs file can be modified and used when adding additional
# application tier nodes. Some of the parameters set in this pairs file
# assume that you are using a shared file system.
[Base]
s_base=/t1/tst
s_current_base=/t1/tst/fs2
s_other_base=/t1/tst/fs1
s_ne_base=/t1/tst/fs_ne
[General]
s_applptmp=/usr/tmp
s_appsgroup=oinstall
s_appsuser=applmgr
s_dbuser=oracle
s_dbgroup=dba
s_dbdomain=samiora.blogspot.com
s_at=/t1/tst/fs2/EBSapps/appl
s_com=/t1/tst/fs2/EBSapps/comn
s_tools_oh=/t1/tst/fs2/EBSapps/10.1.2
s_weboh_oh=/t1/tst/fs2/FMW_Home/webtier
s_fmw_home=/t1/tst/fs2/FMW_Home
s_dbGlnam=tst
s_dbSid=tst
s_dbhost=edolraclin-dbsrv
s_clonestage=/t1/tst/fs2/EBSapps/comn/clone
s_dbport=1522
s_options_symlinks=Options -FollowSymLinks
s_proxyhost=
s_proxybypassdomain=samiora.blogspot.com
s_proxyport=
s_nonproxyhosts=
s_javamailer_imapdomainname=NoImapDomain
s_javamailer_imaphost=NoImapHost
s_javamailer_reply_to=NoReplyTo
s_javamailer_outbound_user=changeOnJavaMailerInstall
s_smtphost=ed-olapplin1
s_smtpdomainname=samiora.blogspot.com
s_file_edition_type=run
s_port_pool=1
patch_s_port_pool=2
s_admhost=ed-olapplin1
s_atName=ed-olapplin1
s_shared_file_system=true
[Web Entry Point Configuration]
s_webentryurlprotocol=http
s_webentryhost=ed-olapplin2
s_webentrydomain=samiora.blogspot.com
s_active_webport=8001
s_endUserMonitoringURL=http://ed-olapplin2.samiora.blogspot.com:8001/oracle_smp_chronos/oracle_smp_chronos_sdk.gif
s_external_url=http://ed-olapplin2.samiora.blogspot.com:8001
s_login_page=http://ed-olapplin2.samiora.blogspot.com:8001/OA_HTML/AppsLogin
[Instance Specific]
# Please provide values for the context variables listed below. On the source
# instance they are instantiated as shown in the comment section below.
# These values should only be used as reference to fill out the instance
# values for the new node.
#s_temp=/t1/tst/fs2/inst/apps/tst_ed-olapplin1/temp
#s_contextname=tst_ed-olapplin1
#s_hostname=ed-olapplin1
#s_domainname=samiora.blogspot.com
#s_cphost=ed-olapplin1
#s_webhost=ed-olapplin1
#s_config_home=/t1/tst/fs2/inst/apps/tst_ed-olapplin1
#s_inst_base=/t1/tst
#s_display=ed-olapplin1:0.0
#s_forms-c4ws_display=ed-olapplin1:0.0
#s_ohs_instance=EBS_web_erp_OHS1
#s_webport=8001
#s_http_listen_parameter=8001
#s_https_listen_parameter=4444
s_temp=/t1/tst/fs2/inst/apps/tst_ed-olapplin2/temp
s_contextname=tst_ed-olapplin2
s_hostname=ed-olapplin2
s_domainname=samiora.blogspot.com
s_cphost=ed-olapplin2
s_webhost=ed-olapplin2
s_config_home=/t1/tst/fs2/inst/apps/tst_ed-olapplin2
s_inst_base=/t1/tst
s_display=ed-olapplin2:0.0
s_forms-c4ws_display=ed-olapplin2:0.0
s_ohs_instance=EBS_web_erp_OHS2
s_webport=8031
s_http_listen_parameter=8001
s_https_listen_parameter=4444
[Services]
# Please provide values for the context variables listed below .
# Enter "enabled" without the quotes to enable the service on the new node .
# Enter "disabled" without the quotes to disable the service on the new node .
# The Root service include the Node Manager .
# The Web Application Services include the Node Manager, Admin Server,
# Managed Servers ( oacore, forms, oafm, formsc4-ws).
# To enable the configuration of Node Manager and the Managed Servers,
# set s_web_applications_status to enabled
# The Web Entry Services include the OPMN and Oracle HTTP Server .
# To enable the configuration of OPMN,OHS , set s_webentry_status and s_apcstatus to enabled.
# The Batch Processing Services include the FNDFS Listener, Concurrent Mgr, ICSM# and JTF FullFillment Server
# To enable the configuration of Concurrent Manager# set s_batch_status to enabled.
# The Other Services group include the Forms Metric Server, Forms Metric Clients# Forms Server for the socket mode configuration and Mobile Web Application
# Server (MWA).
# s_adminserverstatus is set to disabled since this service can only be enabled
# on the primary application tier.
# The services enabled on the primary application tier node are as shown
# below in the comment section.
[Services Enabled on the Primary Application Tier Node]
#s_web_applications_status=enabled
#s_web_entry_status=enabled
#s_apcstatus=enabled
#s_root_status=enabled
#s_batch_status=enabled
#s_other_service_group_status=disabled
#s_adminserverstatus=enabled
#s_web_admin_status=enabled
[Services To be Enabled on the Secondary Application Tier Node]
s_web_applications_status=enabled
s_web_entry_status=enabled
s_apcstatus=enabled
s_root_status=enabled
s_batch_status=enabled
s_other_service_group_status=disabled
s_adminserverstatus=disabled
s_web_admin_status=enabled
# Additional variables can be added below as per your requirement.

8. This command adclonectx.pl needs to be run only 1 time on 2nd node without setting any environment.
Verify using the below SQL Script,
SQL> select * from APPS.FND_OAM_CONTEXT_FILES where name not in( 'METADATA','TEMPLATE') order by creation_date desc;If there is ERROR WHILE ADDING A NODE DUE TO any reason then use below command to DELETE THE NODE AND THEN ADD IT AGAIN.  Run this from node1,
[applmgr@ed-olapplin1 bin]$ cd /t1/tst/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/
[applmgr@ed-olapplin1 bin]$ perl adProvisionEBS.pl ebs-delete-node -contextfile=/t1/tst/fs2/inst/apps/tst_ed-olapplin1/appl/admin/tst_ed-olapplin1.xml -hostname=ed-olapplin2 -logfile=ebs-delete-node2_I.logAlso delete the inst directory that was created while we added the node for first time /t1/tst/fs2/inst/apps/tst_ed-olapplin2
Now Register the new topology from the newly added application tier node. Get port from Below file and change accordingly to below perl scripts.
[applmgr@ed-olapplin2]$ vi /t1/tst/fs2/FMW_Home/webtier/instances/EBS_web_tst_OHS2/config/OHS/EBS_web_tst/mod_wl_ohs.conf
[applmgr@ed-olapplin2]$. /t1/tst/fs2/EBSapps/appl/APPStst_ed-olapplin2.env
cd /t1/tst/fs2/EBSapps/comn/clone/bin
perl adclonectx.pl addnode contextfile=/t1/tst/fs2/inst/apps/tst_ed-olapplin1/appl/admin/tst_ed-olapplin1.xml pairsfile=/t1/tst/pairsfiles/mypairsfile.txt outfile=/t1/tst/pairsfile/mypairsOUTPUT.txt dualfs=yes
[applmgr@ed-olapplin2 EBS_web_tst]$ . /t1/tst/fs2/EBSapps/appl/APPStst_ed-olapplin2.env
perl /t1/tst/fs2/EBSapps/appl/fnd/12.0.0/patch/115/bin/txkSetAppsConf.pl -contextfile=/t1/tst/fs2/inst/apps/tst_ed-olapplin2/appl/admin/tst_ed-olapplin2.xml -configoption=removeMS -oacore=ed-olapplin2.samiora.blogspot.com:7202,ed-olapplin2.samiora.blogspot.com:7203,ed-olapplin2.samiora.blogspot.com:7204,ed-olapplin2.samiora.blogspot.com:7205 -oafm=ed-olapplin2.samiora.blogspot.com:7602 -forms=ed-olapplin2.samiora.blogspot.com:7402 -formsc4ws=ed-olapplin2.samiora.blogspot.com:7802 -ekanban=ed-olapplin2.samiora.blogspot.com:6802 -accessgate=ed-olapplin2.samiora.blogspot.com:6802 -yms=ed-olapplin2.samiora.blogspot.com:6802
perl /t1/tst/fs2/EBSapps/appl/fnd/12.0.0/patch/115/bin/txkSetAppsConf.pl -contextfile=/t1/tst/fs2/inst/apps/tst_ed-olapplin2/appl/admin/tst_ed-olapplin2.xml -configoption=addMS -oacore=ed-olapplin2.samiora.blogspot.com:7202,ed-olapplin2.samiora.blogspot.com:7205,ed-olapplin2.samiora.blogspot.com:7207,ed-olapplin2.samiora.blogspot.com:7209 -oafm=ed-olapplin2.samiora.blogspot.com:7602 -forms=ed-olapplin2.samiora.blogspot.com:7402 -formsc4ws=ed-olapplin2.samiora.blogspot.com:7802
more /t1/tst/fs2/FMW_Home/webtier/instances/EBS_web_tst_OHS2/config/OHS/EBS_web_tst/mod_wl_ohs.conf
perl $AD_TOP/patch/115/bin/adProvisionEBS.pl ebs-create-managedserver -contextfile=/t1/tst/fs2/inst/apps/tst_ed-olapplin2/appl/admin/tst_ed-olapplin2.xml -managedsrvname=oacore_server2 -servicetype=oacore -managedsrvport=7205 -logfile=/tmp/oacore2log.log
perl $AD_TOP/patch/115/bin/adProvisionEBS.pl ebs-create-managedserver -contextfile=/t1/tst/fs2/inst/apps/tst_ed-olapplin2/appl/admin/tst_ed-olapplin2.xml -managedsrvname=oacore_server5 -servicetype=oacore -managedsrvport=7209 -logfile=/tmp/oacore5log.log
perl $AD_TOP/patch/115/bin/adProvisionEBS.pl ebs-create-managedserver -contextfile=/t1/tst/fs2/inst/apps/tst_ed-olapplin2/appl/admin/tst_ed-olapplin2.xml -managedsrvname=oacore_server7 -servicetype=oacore -managedsrvport=7207 -logfile=/tmp/oacore7log.log
perl $AD_TOP/patch/115/bin/adProvisionEBS.pl ebs-create-managedserver -contextfile=/t1/tst/fs2/inst/apps/tst_ed-olapplin2/appl/admin/tst_ed-olapplin2.xml -managedsrvname=oacore_server9 -servicetype=oacore -managedsrvport=7202 -logfile=/tmp/oacore9log.log
for oacore_server9>>>ERROR: There is already a managed server with the name oacore_server9>>>>>>ignore this and continue below.
>>>now run autoconfig on node2 (ed-olapplin2)
>>>now without stopping node1 servies execute autoconfig on node 1 (ed-olapplin1)
>>>>start all services on NODE 2 (ed-olapplin2)
[applmgr@ed-olapplin2 ~]$ more $CONTEXT_FILE | grep oacore_server
      <oacore_server>
      </oacore_server>
         <oacore_server_ports oa_var="s_oacore_server_ports">oacore_server9:7202,oacore_server2:7205,oacore_server5:7209,oacore_server7:7207</oacore_server_ports>
            <oa_service_name oa_var="s_oacorename" type="managed_server">oacore_server9,oacore_server2,oacore_server5,oacore_server7</oa_service_name>
            <oa_managed_server_name oa_var="s_oacore_managed_servers">oacore_server9,oacore_server2,oacore_server5,oacore_server7</oa_managed_server_name>
>>>Below Additional command only to delete a managed server
perl $AD_TOP/patch/115/bin/adProvisionEBS.pl ebs-delete-managedserver -contextfile=/t1/tst/fs2/inst/apps/tst_ed-olapplin2/appl/admin/tst_ed-olapplin2.xml -managedsrvname=oacore_server2 -servicetype=oacore -logfile=/tmp/oacore_server1_remove.log


9. On all Application tier nodes, perform the following steps to register the newly added Application tier node with the Application tier TNS Listener (FNDFS listener) on each node:
On ed-olapplin2:
[applmgr@ed-olapplin2 ~]$ kill -9 -1
[applmgr@ed-olapplin2 ~]$ cd $ADMIN_SCRIPTS_HOME
[applmgr@ed-olapplin2 scripts]$ adautocfg.sh
[applmgr@ed-olapplin2 scripts]$ echo $TWO_TASK
[applmgr@ed-olapplin2 scripts]$ sh adalnctl.sh start
[applmgr@ed-olapplin2 admin]$ lsnrctl reload APPS_tst
[applmgr@ed-olapplin2 scripts]$ sh adalnctl.sh stop
On ed-olapplin1:
[applmgr@ed-olapplin1 tstuat]$ cd $ADMIN_SCRIPTS_HOME
[applmgr@ed-olapplin1 scripts]$ adautocfg.sh
[applmgr@ed-olapplin1 scripts]$ echo $TWO_TASK
[applmgr@ed-olapplin1 scripts]$ sh adalnctl.sh start
[applmgr@ed-olapplin1 admin]$ lsnrctl reload APPS_tst
[applmgr@ed-olapplin1 scripts]$ sh adalnctl.sh stop
10.If the Node Manager service is up on the Patch Edition File System of the newly added Application tier node, shut it down as follows:
[applmgr@ed-olapplin2 ~]$ . /t1/tst/fs1/EBSapps/appl/APPStst_ed-olapplin2.env
[applmgr@ed-olapplin2 ~]$ adnodemgrctl.sh stop
11. Shut down the Admin Server and the Node Manager on the Patch Edition File System of the primary node as follows:
[applmgr@ed-olapplin1 ~]$ . /t1/tst/fs1/EBSapps/appl/APPStst_ed-olapplin1.env
[applmgr@ed-olapplin1 ~]$ echo $FILE_EDITION
Patch
[applmgr@ed-olapplin1 ~]$ adadminsrvctl.sh stop forcepatchfs
[applmgr@ed-olapplin1 ~]$ adnodemgrctl.sh stop
[applmgr@ed-olapplin1 scripts]$ ps -ef | grep wlsport verify wls for patchfs is down.
12.Perform the following steps on all Database tier nodes to add the newly added node to the Access Control List
on Database server ed-olraclin-db:
[oracle@ed-olraclin-db~]$ cd /u01/oracle/12.2.0.1/tsthome
[oracle@ed-olraclin-dbtsthome1]$ . ed-olraclin-db.env
[oracle@ed-olraclin-dbtsthome1]$ cd appsutil/scripts/ed-olraclin-db/
[oracle@ed-olraclin-dbtst2_ed-olraclin-db]$ adautocfg.sh
[oracle@ed-olraclin-dbtst2_ed-olraclin-db]$ lsnrctl reload tst
13. Now Load balancer configuration for both URLs from two nodes
14. Now SSH Connectivity Requirements for Online Patching (Required)
# su - applmgr
# . /t1/tst/EBSapps.env RUN
On node 1 & node 2 both execute the below script respectively,
On ed-olapplin1# perl $AD_TOP/patch/115/bin/txkRunSSHSetup.pl enablessh -contextfile=/t1/tst/fs2/inst/apps/tst_ed-olapplin1/appl/admin/tst_ed-olapplin1.xml -hosts=ed-olapplin1,ed-olapplin2
On ed-olapplin2# perl $AD_TOP/patch/115/bin/txkRunSSHSetup.pl enablessh -contextfile=/t1/tst/fs2/inst/apps/tst_ed-olapplin2/appl/admin/tst_ed-olapplin2.xml -hosts=ed-olapplin2,ed-olapplin1
This finishes the ADDING NODE IN SHARED FILE SYSTEM SETUP.
Reference: Sharing the application tier file system in oracle EBS release 12.2. 1375769.1
For any questions on this article please email me on samiappsdba@gmail.com, and do subscribe to this blog as well. 

Sunday, March 8, 2020

Encrypting SQL Server Database: Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE), a built-in feature for encrypting data at rest. TDE protects the physical media that hold the data associated with a user database, including the data and log files and any backups or snapshots. Encrypting data at rest can help prevent those with malicious intent from being able to read the data should they manage to access the files.

Windows Data Protection API (DPAPI) sits at the top of the hierarchy and is used to encrypt the service master key (SMK), a symmetric key that resides in the master database. SQL Server creates the SMK the first time the instance is started. You can use the key to encrypt credentials, linked server passwords, and the database master keys (DMKs) residing in different databases.


So to implement TDE on a user database, we must take the following steps:
  1. Create the DMK in the master database, if it doesn’t already exist.
  2. Create a certificate in the master database for securing the DEK.
  3. Create the DEK in the user database to be encrypted.
  4. Enable TDE on the user database.
Let's setup a test database such as the one shown in the following T-SQL script:
 
USE master;
GO
 
CREATE DATABASE EmpData2;
GO
 
USE EmpData2;
GO
 
CREATE TABLE EmpInfo(
  EmpID INT PRIMARY KEY,
  NatID NVARCHAR(15) NOT NULL,
  LoginID NVARCHAR(256) NOT NULL);
GO
 
INSERT INTO EmpInfo(EmpID, NatID, LoginID)
SELECT BusinessEntityID, NationalIDNumber, LoginID
FROM AdventureWorks2014.HumanResources.Employee
WHERE NationalIDNumber IS NOT NULL;

1. CREATE THE DMK
To create the DMK that will support a TDE-enabled database, you take the same steps you take when creating the DMK to support column-level encryption, except for one important difference. You must create the key in the master database, as shown in the following T-SQL code:


USE master;
GO
 
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'samimalik123';
 
To verify that the DMK has been created, we can query the sys.symmetric_keys catalog view:
SELECT name KeyName,
  symmetric_key_id KeyID,
  key_length KeyLength,
  algorithm_desc KeyAlgorithm
FROM sys.symmetric_keys;

The SELECT statement returns the results shown in the following table.
 
KeyName
KeyID
KeyLength
KeyAlgorithm
##MS_DatabaseMasterKey##
101
256
AES_256
##MS_ServiceMasterKey##
102
256
AES_256
 
Notice that the results include both the DMK and SMK. As already noted, SQL Server creates the SMK in the master database automatically. As you can see, the two keys are based on the 256-bit AES encryption algorithm.
 
2.  CREATE THE CERTIFICATE
 The next step is to create a certificate in the master database using a CREATE CERTIFICATE statement. In SQL Server, a certificate is a digitally signed, database-level securable that binds the public and private keys.
To keep things simple, we’ll create a self-signed certificate, which is automatically protected by the DMK. Normally, a certificate authority (CA) would issue and sign the certificate, which we would then incorporate into our encryption infrastructure, but a self-signed certificate can be handy for developing and testing, as well as checking out functionality like we’re doing here.
To create a self-signed certificate, we need only provide a name for the certificate and a WITH SUBJECT clause, as shown in the following statement:
 
CREATE CERTIFICATE TdeCert
WITH SUBJECT = 'TDE certificate';
The WITH SUBJECT clause supposedly specifies the issuer name; however, it can be just about any value, although a relevant description is normally the best option. In this case, I’ve gone with TDE certificate.
Note that, in addition to self-signed certificates, the CREATE CERTIFICATE statement lets us define a certificate based on a certificate file as well as retrieve the private key from a file or use a password to encrypt the certificate.
After we run the CREATE CERTIFICATE statement, we can verify that the certificate has been created by querying the sys.certificates catalog view:

SELECT name CertName,
  certificate_id CertID,
  pvt_key_encryption_type_desc EncryptType,
  issuer_name Issuer
FROM sys.certificates
WHERE issuer_name = 'TDE certificate';
 
the SELECT statement returned the results shown in the following table. 
 
CertName
CertID
EncryptType
Issuer
TdeCert
258
ENCRYPTED_BY_MASTER_KEY
TDE certificate
 
 The value in the EncryptType column is ENCRYPTED_BY_MASTER_KEY, which confirms that SQL Server has used the DMK to encrypt the certificate.
 
3. CREATE THE DEK

Now we switch over to our EmpData2 database to create the DEK, the next level down our hierarchy. When we create the DEK, we must specify the algorithm to use for the encryption key and the certificate to use to encrypt the DEK. Starting with SQL Server 2016, all algorithms have been deprecated except 128-bit AES, 192-bit AES, and 256-bit AES. (The higher the number of bits, the stronger the algorithm.)
To create the DEK, we can use a CREATE DATABASE ENCRYPTION KEY statement, as shown in the following example:
USE EmpData2;
GO
 
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TdeCert;
 
In this case, we’ve specified the 256-bit AES algorithm and the TdeCert certificate we created in the previous step. When you run the statement, you should receive the following warning.
 
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.
 
This is an important message and one you should heed. We’ll discuss backing up your keys and certificates later in the article, but know that it is something you should be doing whenever you’re using them as part of your encryption process.
Now let’s return to our DEK. Once we’ve created the key, we can verify its existence by querying the sys.dm_database_encryption_keys dynamic management view:

SELECT DB_NAME(database_id) DbName,
  encryption_state EncryptState,
  key_algorithm KeyAlgorithm,
  key_length KeyLength,
  encryptor_type EncryptType
FROM sys.dm_database_encryption_keys;
 
The sys.dm_database_encryption_keys view returns details about a database’s encryption state and its associated DEKs. The following table shows the results returned by our SELECT statement.
DbName
EncryptState
KeyAlgorithm
KeyLength
EncryptType
EmpData2
1
AES
256
CERTIFICATE
Notice that the EncryptType column has a value of CERTIFICATE, which confirms that a certificate was used to encrypt the DEK.
Also notice that the EncryptState column shows a value of 1. This indicates that the database is in an unencrypted state. According to SQL Server documentation, the column can display any one of the values described in the following table.
 
Value
Description
0
No database encryption key present, no encryption
1
Unencrypted
2
Encryption in progress
3
Encrypted
4
Key change in progress
5
Decryption in progress
6
The certificate or asymmetric key encrypting the DEK is being changed
 
 4. ENABLE TDE ON THE USER DATABASE
We now have all the pieces in place to enable TDE on the EmpData2 database. The only step left is to turn encryption on.
Before we do that, I want to point out that there are many considerations to take into account before actually enabling TDE. For example, if any filegroups associated with the database are set as read-only, the encryption operation will fail. You’ll also run up against a number of restrictions when trying to implement TDE, such as not being able to drop a database during the initial encryption process.
Before you implement encryption on anything other than a test database in a test environment, I highly recommend that you review the MSDN article Transparent Data Encryption (TDE), which explains the various considerations and restrictions to take into account before implementing TDE.
With that in mind, let’s return to the matter at hand, which is to enable TDE on the EmpData2 database. To do so, we need only run a simple ALTER DATABASE statement that sets encryption on, as shown in the following example:

ALTER DATABASE EmpData2
SET ENCRYPTION ON;
 
 That’s all there is to it. Because our database is so small, the encryption process will be very quick. Not surprisingly, the larger the database, the longer this process will take.
If we again query the sys.dm_database_encryption_keys view, we’ll get the results shown in the following table, which verify that the EncryptState value is now 3.
DbName
EncryptState
KeyAlgorithm
KeyLength
EncryptType
tempdb
3
AES
256
ASYMMETRIC KEY
EmpData2
3
AES
256
CERTIFICATE
The results also show something else that’s very important to note—the addition of a row for the tempdb database. When you implement TDE on any user table, SQL Server also encrypts the tempdb database.
If you consider the logic behind this, you can see why Microsoft has taken this step. The database contains such items as temporary user objects, internal objects, and row versions, any of which can expose sensitive data. The downside to this is that unencrypted databases can take a performance hit, although Microsoft claims that the impact is minimal.
This issue aside, as long as our certificate and keys are in place, we can query the TDE-encrypted database just like we did before we enabled TDE. For example, we can run the following SELECT statement against the EmpInfo table:

SELECT TOP 5 * FROM EmpInfo;
 
Notice that we do not have to take any special steps with our query like we do with column-level encryption. We simply run the query as before, a fact that should make application developers happy. The following table shows the results returned by our SELECT statement.
EmpID
NatID
LoginID
1
295847284
adventure-works\ken0
2
245797967
adventure-works\terri0
3
509647174
adventure-works\roberto0
4
112457891
adventure-works\rob0
5
695256908
adventure-works\gail0
As you can see, we’re getting exactly the results we would expect. From the user/application perspective, it’s business as usual.

5. DISABLE TDE ON THE USER DATABASE
At some point, you might decide that you want to disable encryption on a user database. The process is as simple as enabling it. You again run an ALTER DATABASE statement, only this time turning off the encryption, as shown in the following example:
 
ALTER DATABASE EmpData2
SET ENCRYPTION OFF;
 
You can verify that encryption has been disabled by again querying the sys.dm_database_encryption_keys dynamic management view, which now returns the results shown in the following table:
DbName
EncryptState
KeyAlgorithm
KeyLength
EncryptKey
tempdb
3
AES
256
ASYMMETRIC KEY
EmpData2
1
AES
256
CERTIFICATE
As you can see, the EncryptState value for the EmpData2 database is now 1, indicating that it is in an unencrypted state. But notice that the tempdb database is still encrypted. As it turns out, the database will stay encrypted until it is re-created, which occurs whenever the SQL Server service restarts.

For a development or test environment, restarting the service might not be a big deal, but restarting a production instance is an entirely different matter. One more reason to give careful consideration to implementing TDE.
In the meantime, if you do have control over your instance of SQL Server, you can restart the service to see for yourself what happens with the tempdb database. From there, you can again query the sys.dm_database_encryption_keys view, which should return the results shown in the following table.
DbName
EncryptState
KeyAlgorithm
KeyLength
EncryptKey
EmpData2
1
AES
256
CERTIFICATE
As you can see, the tempdb database is no longer included in the results because the database has not been encrypted or subjected to TDE.
If you disable TDE on your database, you’re also free to drop the DMK, certificate, and DEK, using the DROP MASTER KEY, DROP CERTIFICATE, and DROP DATABASE ENCRYPTION KEY statements, respectively. Or you can re-enable TDE on the user database at any point. Just keep in mind the impact on the tempdb database.

6. BACKUP THE CERTIFICATE AND KEYS
As already noted, you should back up your certificates and keys, preferably right after you create them. This is also true for the SMK, before you start relying on it to protect your DMKs.
To back up the SMK, you can use a BACKUP SERVICE MASTER KEY statement, as shown in the following example:

Use master;
GO
 
BACKUP SERVICE MASTER KEY
TO FILE = 'C:\DataFiles\MsSqlServer\SvcMasterKey.key'
ENCRYPTION BY PASSWORD = 'samimalik123';


The statement itself is fairly straightforward. You must provide the full path for the backup file and a password for encrypting the key in that file. One thing to note, however, is that not all the examples in the Microsoft documentation clearly demonstrate that you must provide a full path, including the file name and its extension. Without these, you will receive an error.
It’s also worth noting that the BACKUP SERVICE MASTER KEY statement includes no logic for what to do when the file already exists. If it does exist, you’ll again receive an error.
Backing up the DMK works much the same way, except that you use a BACKUP MASTER KEY statement:

BACKUP MASTER KEY
TO FILE = 'C:\DataFiles\MsSqlServer\DbMasterKey.key'
ENCRYPTION BY PASSWORD = 'samimalik123'

 gain, you must provide the full path and file name, along with a password. In addition, the file cannot already exist. Backing up a certificate is a little different because you want to be sure to explicitly back up the private key along with the certificate. For that, you must use a BACKUP CERTIFICATE statement that includes the WITH PRIVATE KEY clause, as shown in the following example:

BACKUP CERTIFICATE TdeCert
TO FILE = 'C:\DataFiles\MsSqlServer\TdeCert.cer'
WITH PRIVATE KEY(
  FILE = 'C:\DataFiles\MsSqlServer\TdeCert.key',
  ENCRYPTION BY PASSWORD = 'pw1234!'
);
 
In this case, we’re generating a file for both the certificate and the private key, as well as providing a password for the private key. That’s all there is to backing up the certificate and keys. Of course, you should store your backup files in a remote locate separate from the database files, ensuring that they’re protected from any sort of mischief or recklessness.

Under the right circumstances, TDE can be a useful addition to your data protection strategies. However, TDE protects only data at rest, such as the data and log files. It does not protect data in memory or data transmitted between an application and SQL Server. As a result, your database is still vulnerable to such risks as SQL injection attacks or hijacked administrative permissions. Once data has been unencrypted for use, it is just as vulnerable as it has always been.
If you decide to implement TDE, you should do so only as one piece of a much more comprehensive security strategy that takes into account all possible risks to your data. You might also consider alternatives to TDE, such as the new Always Encrypted feature introduced in SQL Server 2016 (a topic we’ll cover in a future article).
When Microsoft introduced TDE in SQL Server 2008, it represented an important step in data protection, but it was only one step and was never meant to be the sole means of safeguarding your data. Be sure to keep that in mind when working with TDE.