SysFlare: 11월 2012

2012/11/21

SQL, 암호화된 데이터 검색

암호화(대칭키)된 컬럼 데이터에 대해서 like 검색은 안되며, equal 검색을 해야 함. 2가지 방법이 있는듯.

1. 암호화된 컬럼을 복호화 하여 조건절 비교

ssn varbinary(256);
select * from table
where convert( varchar(256), decryptbykey(ssn) ) = '111-11-1111';

2. 암호화된 데이터에 대한 Hash 값을 별도의 컬럼에 저장후 Hash 값 비교

ssn varbinary(256);
ssnhash varchar(67);
select * from table
where ssnhash = convert( varchar(67), HashBytes('SHA2_256', '111-11-1111'));

2012/11/20

SQL Server TDE 예제

마스터키에는 2가지가 있는데,. 인스턴스 수준의 키와, Database 수준의 키가 있으며, 서비스 마스터키는 SQL Server 설치시에 생성이 됨. Database 수준의 마스터키는 사용자 DB를 생성후 해당 DB에서 생성.

Master Key 의 존재 여부는 select * from sys.symmetric_keys 를 통해서 확인 또는 Management Studio 에서 해당 DB의 보안에서 대칭 키 와 인증서 항목에서 확인이 가능 함.

SELECT * from sys.certificates
SELECT * FROM sys.dm_database_encryption_keys;

SERVICE MASTER KEY

1. Service Master Key Backup
BACKUP SERVICE MASTER KEY TO FILE = 'c:\mssql_service_master.key'
ENCRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4'; -- key 암호화용 패스워드

2. Service Master Key Restore
RESTORE SERVICE MASTER KEY FROM FILE = 'c:\mssql_service_master.key'
DECRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4' FORCE ;
-> 1.2, System DB 마이그레이션/복구 등외, 일반적으로 활용할 일이 거의 없음.

3. Service Master Key Regenerate
ALTER SERVICE MASTER KEY REGENERATE;
-> DPAPI 는 키를 SQL 서비스 실행 계정으로 부터 전달 받기에,
SQL 서비스 계정을 변경했거나, Master DB 를 이전한 경우에 해당.
http://technet.microsoft.com/en-us/library/ms187788.aspx

DATABASE MASTER KEY

1. Create Master Key
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'sfj5300osdVdgwdfkli7';

2. Backup Master key
USE master;
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'sfj5300osdVdgwdfkli7';
BACKUP MASTER KEY TO FILE = 'c:\DBmaster.key'
ENCRYPTION BY PASSWORD = 'sd092735k%jnadsg'; -- DB key 암호화용 패스워드. 암호정책적용.

3. Restore Master key
USE master;
RESTORE MASTER KEY FROM FILE = 'c:\DBmaster.key'
DECRYPTION BY PASSWORD = 'sd092735k%jnadsg' -- DB key 암호화시 사용한 패스워드(복호화)
ENCRYPTION BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD'; -- DB key 암호화 패스워드

4. Drop Master Key
USE master;
DROP MASTER KEY;

5. Alter Master Key
USE master;
ALTER MASTER KEY REGENERATE
WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003'; -- DB 마스터키 재생성. 키 백업필요.

CERTIFICATE

1. Crate Certificates
USE master;
CREATE CERTIFICATE testDBcert
ENCRYPTION BY PASSWORD = 'Gvbd2439587y' -- private key 암호화용 패스워드
WITH SUBJECT = 'testDB Company', EXPIRY_DATE = '20201231';

2. Backup Certificates [매우중요]
USE master;
BACKUP CERTIFICATE testDBcert TO FILE = 'D:\SQL DATA\testDB.cer'
WITH PRIVATE KEY (
-- DECRYPTION BY PASSWORD = 'Gvbd2439587y', -- private key 암호화용 패스워드
FILE = 'D:\SQL DATA\testDB.pvk' ,
ENCRYPTION BY PASSWORD = 'f34535sdD9d' );
--> 개인키와 같이 백업, 다른 서버에 DB 이전/복원시 필요.

3. Restore Certificates
USE master;
CREATE CERTIFICATE testDBcert FROM FILE = 'D:\SQL DATA\testDB.cer'
WITH PRIVATE KEY (
FILE = 'D:\SQL DATA\testDB.pvk',
ENCRYPTION BY PASSWORD = 'Gvbd2439587y',
DECRYPTION BY PASSWORD = 'f34535sdD9d');

4. Drop Certificates
USE master;
DROP CERTIFICATE testDBcert;

DATABASE ENCRYPTION

1. Create Database Encryption Key (DEK)
USE testDB;
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE testDBcert;

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

2. Enable TDE
USE testDB;
ALTER DATABASE testDB SET ENCRYPTION ON

3. Disable TDE
USE testDB;
ALTER DATABASE testDB SET ENCRYPTION OFF;
-- 암호화 해제가 완료될때 까지 기다림

4. Delete Database Encryption Key (DEK)
USE testDB;
DROP DATABASE ENCRYPTION KEY;

ps,
다른 시스템에, 사용자 DB 복원이나 Attach 하는 경우, 복원 시스템에는 인스턴스 Master Key 가 설치한후(안되어 있는 경우) 해당 DB를 암호화에 사용한 인증서 복원후, DB복원이나 Attach를 진행 하면 됨.

개인정보보호법에서는, 블록 암호화는 TDE 로 만족되며, 패스워드는 SHA-256 이상의 일방향 암호 알고리즘을 사용해야 하므로 응용프로그램에서는 해당 알고리즘을 이용하여 문자열 암호화는 문제가 없을것이고, SQL 쿼리문에서 직접 암호화를 하는 경우 SQL Server 2012 에서 지원하는 HashBytes('SHA2_256', 'string'); 함수를 이용하면 됨.

그외 주민등록번호나 계좌번호, 신용카드 번호, 여권 번호, 바이오 정보, 운전면허 번호등 복호화가 필요한 경우 인증서를 이용한 대칭키 encryptbykey/descryptbykey 함수를 활용 (대칭키 활용시, MasterKey 및 인증서는 해당 DB에서 생성) .

2012/11/13

IIS 미디어 서비스 4.1 - 64비트

ddd

2012/11/12

jQuery, CheckBox, DropDownList/ComboBox

DropDownList/ComboBox

$("#ComboBox").val();
$("#ComboBox option:selected").text();
$("#ComboBox").change(function() { /* */ });
$("#ComboBox").val(1);

CheckBox

if ($("#IsCharge").attr("checked")) { };
$("#IsCharge").attr('checked', true);
$('input[name=foo]').attr('checked', true);
$("input[type='checkbox']:checked").each(
      function() {
            /* */
      }
);

SQL 2012 SP1 설치후, SQL_Tools_ANS 관련 이벤트

OS : Windows Server 2012 Standard
DB : SQL Server 2012 Enterprise

MsiInstaller / 1004
제품 '{A7037EB2-F953-4B12-B843-195F4D988DA1}', 기능 'SQL_Tools_ANS', 구성 요소 '{0CECE655-2A0F-4593-AF4B-EFC31D622982}'을(를) 검색하지 못했습니다. 리소스 ''이(가) 존재하지 않습니다.

MsiInstaller / 1001
구성 요소 '{6E985C15-8B6D-413D-B456-4F624D9C11C2}'을(를) 요청하는 동안 제품 '{A7037EB2-F953-4B12-B843-195F4D988DA1}'의 기능 SQL_Tools_ANS을(를) 검색하지 못했습니다.

Feedback 이 이미 제출되어 있는 상태로, SP1 Installer 의 구성요소 검사 버그로 추정됨.

본제품 설치시 해당 구성요소를 미설치 했는데 sp1 에서는 설치 여부 검사 과정에 오류가 있는듯하며, 현재 Windows Installer 에서 지속적으로 설치 재시도를 함. 해당 내용에 대해서 Microsoft 에서 코멘트가 있기 전 까지 SP1 설치 중단을 하는 것이 좋아 보임.

서버 운영에 영향을 주는 정도라면, 백업이 있는 경우 복원하거나 또는 임시 조치로 Installer 관련 프로세스를 강제 종료 처리를 하면 됨. (msiexec.exe, ngen.exe, ngentask.exe 등)

업데이트 ==> Microsoft® SQL Server® 2012 서비스 팩 1(SP1), CU1,2 fix 포함. (배포일자 2013-01-04)
http://www.microsoft.com/ko-kr/download/details.aspx?id=35575 (sp1)
http://www.microsoft.com/ko-kr/download/details.aspx?id=36215 (update for sp1)

2012/11/08

SQL Server 2012 서비스팩 1 다운로드

11월 6일자로 SQL Server 2012 SP1 다운로드가 가능.

sp1 다운로드
http://www.microsoft.com/en-us/download/details.aspx?id=35575

sp1 에서 수정된 사항
http://support.microsoft.com/kb/2674319?wa=wsignin1.0

SysFlare