독까의 이야기

SQL DB 암호화 / TDE(Transparent Data Encryption)

Database/MS-SQL2018. 12. 7. 15:03

MS-SQL 2008 ENT 이상 버전에서 지원되는 TDE 기능에 대한 테스트를 진행 한다. 
STD 에서도 복원은 가능하나, DB 는 사용 할 수 없다. 

OS : Windows 2016
DBMS : MS-SQL 2014 ENT

1. DB 생성
DB 명 : gunnm 
USE master;
CREATE DATABASE [gunnm]
ON
( NAME = gunnm, FILENAME = 'D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\gunnm.mdf')
LOG ON
( NAME = gunnm_log, FILENAME = 'D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\gunnm_log.ldf')
GO




2. 암호화 진행

마스터 키 생성
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PassWord!2#'
GO

마스터 키로 보호 된 인증서 생성
CREATE CERTIFICATE gunnm_cert WITH SUBJECT = 'gunnm_cert'
GO

마스터 키 백업
USE master
BACKUP SERVICE MASTER KEY TO FILE = 'H:\service_master.key' ENCRYPTION BY PASSWORD = 'Service_PW@1119'
GO
BACKUP MASTER KEY TO FILE = 'H:\db_master.key' ENCRYPTION BY PASSWORD = 'DB_PW#1119'
GO

DB 암호화 키 생성
USE gunnm
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE gunnm_cert 
GO

실행 메시지 
경고: 데이터베이스 암호화 키를 암호화하는 데 사용된 인증서가 백업되지 않았습니다. 인증서와 인증서에 연결된 개인 키를 즉시 백업해야 합니다. 인증서를 사용할 수 없게 되거나 다른 서버에서 데이터베이스를 복원하거나 연결해야 할 경우 인증서와 개인 키의 백업본이 있어야 합니다. 그렇지 않으면 데이터베이스를 열 수 없습니다.

DB 암호화 적용
USE gunnm
GO
ALTER DATABASE gunnm
SET ENCRYPTION ON
GO

DB 암호화 키 백업
USE master
BACKUP CERTIFICATE gunnm_cert TO FILE = 'H:\gunnm_cert.cer' WITH PRIVATE KEY ( FILE = 'H:\gunnm_cert.pvk' , ENCRYPTION BY PASSWORD = 'gunnm_PW$1119' )
GO

DB 백업
BACKUP DATABASE [gunnm] TO  DISK = 'H:\gunnm.bak';
GO



3. 대상 서버에서 DB 복원 진행

원본 서버에서 생성 된 DB 인증서 파일 (.cer / .pvk) 및 DB 백업 파일 (.bak) 을 복사한다. 

마스터 키 생성 : 원본 서버와 동일한 패스워드를 입력할 필요는 없다. 
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PassWord1@3'
GO
CREATE CERTIFICATE gunnm_cert WITH SUBJECT = 'DB_Enc'
GO

원본 서버 인증서 파일을 통한 인증서 생성 및 DB 복원
CREATE CERTIFICATE gunnm_cert
  FROM FILE = 'H:\gunnm_cert.cer'
  WITH PRIVATE KEY ( 
    FILE = 'H:\gunnm_cert.pvk',
 DECRYPTION BY PASSWORD = 'gunnm_PW$1119'
  );

실행 메시지 : 
메시지 15232, 수준 16, 상태 1, 줄 2
이름이 'gunnm_cert'인 인증서가 이미 있거나 이 인증서가 데이터베이스에 이미 추가되었습니다.
테스트이므로 서버간 이동을 하지 않고, 동일 서버에서 진행되어 발생 된 오류로 추정

-- 등록 인증서 제거
USE master; 
DROP CERTIFICATE gunnm_cert;


CREATE CERTIFICATE gunnm_cert
  FROM FILE = 'H:\gunnm_cert.cer'
  WITH PRIVATE KEY ( 
    FILE = 'H:\gunnm_cert.pvk',
 DECRYPTION BY PASSWORD = 'gunnm_PW$1119'
  );

RESTORE DATABASE [gunnm]
  FROM DISK = 'H:\gunnm.bak'
  WITH MOVE 'gunnm' TO 'D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\gunnm.mdf',
       MOVE 'gunnm_log' TO 'D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\gunnm_log.ldf';
 



4. TDE 모니터링 스크립트
SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
         WHEN '0'  THEN  'No database encryption key present, no encryption'
         WHEN '1'  THEN  'Unencrypted'
         WHEN '2'  THEN  'Encryption in progress'
         WHEN '3'  THEN  'Encrypted'
         WHEN '4'  THEN  'Key change in progress'
         WHEN '5'  THEN  'Decryption in progress'
         WHEN '6'  THEN  'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
         ELSE 'No Status'
         END,
percent_complete,encryptor_thumbprint, encryptor_type  FROM sys.dm_database_encryption_keys 


USE master
GO
SELECT * FROM sys.certificates

-- encryption_state = 5 is encrypted
SELECT * FROM sys.dm_database_encryption_keys
WHERE encryption_state = 3; 



5. 암호화 제거

DB 암호화 비활성화
USE gunnm
ALTER DATABASE gunnm SET ENCRYPTION OFF
GO

DB 암호화 키 제거
USE gunnm
GO
DROP DATABASE ENCRYPTION KEY  

마스터 키 및 인증서 제거
-- 마스터 키 제거
USE master;
drop master key;
GO

-- 등록 인증서 제거
USE master; 
DROP CERTIFICATE gunnm_cert;
GO  



저작자표시 비영리 동일조건

'Database > MS-SQL' 카테고리의 다른 글

AlwaysOn 가용성 그룹 구축 # 2 : Windows Server 2016 + MS SQL 2016 / None AD  (1) 2020.07.07
AlwaysOn 가용성 그룹 구축 # 1 : Windows Server 2016 + MS SQL 2016 / None AD  (6) 2020.07.03
MS-SQL CPU 사용량이 높은 쿼리문 확인  (0) 2018.06.22
현재 사용 중인 DB 중 제한 없는 DB 확인 쿼리  (0) 2016.07.25
DDL, DML, DCL  (0) 2016.07.25

MS-SQL CPU 사용량이 높은 쿼리문 확인

Database/MS-SQL2018. 6. 22. 14:37

MS-SQL DB 를 이용하는 서버의 CPU 사용량이 100% 가 출력되어 점검을 진행했다.


윈도우에서 성능 모니터에 카운터 추가를 해서 했는데 손이 너무 많이 간다. 


성능 모니터에서 카운터 추가

성능 개체 : Thread

카운터 : % Processor Time / ID Thread 

인스턴스 : sqlservr 리스트 전체 선택

CPU 사용량이 높은 SQL 스레드 ID 확인 : 4280

스레드 ID 로 spid 확인 

SELECT spid, kpid, dbid, cpu, memusage FROM sysprocesses WHERE kpid=4280

SPID : 240

spid 로 쿼리문 확인 

dbcc inputbuffer (240)


이렇게 했는데, 일을 두 번 할 필요는 없으니깐, SSMS 에서 한 번에 확인이 가능한 방법을 검색해서 정리해 보았다. 

SSMS 에 접속, tempdb 에서 새쿼리 실행 후 진행을 한다. 

그러면 tempdb 의 임시 테이블에 해당 테이블이 저장이 되는데, 다 확인하고 나면 맨 하단 drop 명령어로 테이블 제거를 하면 된다. 

use tempdb
CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255),
      BlkBy  VARCHAR(255),DBName  VARCHAR(255),
      Command VARCHAR(255),CPUTime INT,
      DiskIO INT,LastBatch VARCHAR(255),
      ProgramName VARCHAR(255),SPID2 INT,
      REQUESTID INT)
INSERT INTO #sp_who2 EXEC sp_who2
SELECT      *
FROM        #sp_who2
-- Add any filtering of the results here :
WHERE       Status = 'RUNNABLE'
-- Add any sorting of the results here :
ORDER BY    CPUTime ASC

CPU 사용량이 높은 spid 가 확인 됐으면, dbcc inputbuffer (SPID) 를 실행하여 쿼리문을 확인하면 된다. 

다 완료 되었으면 아래의 명령어를 통해 임시 테이블을 제거 한다. 

DROP TABLE #sp_who2


# 현재 실행 중인 쿼리문 확인하는 명령어

SELECT
   sqltext.TEXT,
   req.session_id,
   req.status,
   req.command,
   req.cpu_time,
   req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext 


위에 생성 된 임시테이블의 select 구문과 현재 실행 중인 쿼리문을 조회하는 실행문을 동시에 실행해서 일치하는 spid 를 찾는 것도 좋은 방법일 것 같다. 



저작자표시 비영리 동일조건

'Database > MS-SQL' 카테고리의 다른 글

AlwaysOn 가용성 그룹 구축 # 1 : Windows Server 2016 + MS SQL 2016 / None AD  (6) 2020.07.03
SQL DB 암호화 / TDE(Transparent Data Encryption)  (0) 2018.12.07
현재 사용 중인 DB 중 제한 없는 DB 확인 쿼리  (0) 2016.07.25
DDL, DML, DCL  (0) 2016.07.25
X 행에 데이터가 커밋되지 않았습니다.  (0) 2016.07.25

현재 사용 중인 DB 중 제한 없는 DB 확인 쿼리

Database/MS-SQL2016. 7. 25. 14:34

select * from sys.master_files
where max_size =-1 and file_id ='1' --and type_desc='ROWS'

 


복구모델이 '단순'이 아닌거 찾기 쿼리

select * from sys.databases
where recovery_model_desc = 'full'

저작자표시 비영리 동일조건

'Database > MS-SQL' 카테고리의 다른 글

SQL DB 암호화 / TDE(Transparent Data Encryption)  (0) 2018.12.07
MS-SQL CPU 사용량이 높은 쿼리문 확인  (0) 2018.06.22
DDL, DML, DCL  (0) 2016.07.25
X 행에 데이터가 커밋되지 않았습니다.  (0) 2016.07.25
DB 오프라인, 온라인  (0) 2016.07.25

티스토리툴바