SQL Q&A: Login Triggers, Data File Defrags, and More

아티클
08/24/2016

SQL Q&A로그인 트리거, 데이터 파일 조각 모음 외

편집자: Nancy Michell

서비스 계정 설정

팁: 보다 안전한 암호

SQL Server 2000 엔진은 각 SQL Server 로그인 암호의 복사본을 두 개씩 유지합니다. 하나는 사용자가 실제로 입력한 암호이고 나머지 하나는 모두 대문자로 된 암호입니다.

사용자는 대/소문자를 혼합하거나 대문자로만 암호를 입력하여 로그인하고 서버에 대한 액세스 권한을 얻을 수 있으므로 이 방식은 대/소문자를 구분하지 않는 암호 유효성 검사 시 도움이 됩니다. 그러나 이러한 편리함과 더불어 주의를 기울여야 할 부분도 있습니다. 즉, 암호를 모두 대문자로만 저장할 경우 사용 가능한 암호 수가 줄기 때문에 무차별 암호 대입 공격에 취약해질 수 있습니다.

SQL Server 2005에서는 암호의 원본만 저장합니다. 즉, 사용자가 입력한 암호가 서버에 저장된 암호와 일치해야 합니다. 암호가 일치하지 않으면 사용자는 로그인에 실패하고 서버에 대한 액세스가 거부됩니다. 암호의 대/소문자를 정확히 기억하지 못하는 경우에는 암호를 다시 설정해야 합니다.

사용자의 로그인 이름이 SQLCOMMUNITY라고 가정할 경우 다음 명령을 사용하여 사용자의 SQL Server 암호를 다시 설정할 수 있습니다.

Use Master;
ALTER LOGIN SQLCOMMUNITY WITH PASSWORD = 'k3t9h4s8wJF7t';

이 명령은 SQLCOMMUNITY SQL Server 로그인 암호를 "k3t9h4s8wJF7t"로 다시 설정합니다.

Q: SQL Server™ 2000에서는 관리 도구의 서비스 애플릿을 사용하여 SQL Server 엔진 및 에이전트의 서비스 계정을 설정했습니다. SQL Server 2005에서는 구성 관리자 도구를 사용한다고 들었습니다. 이전처럼 Windows 도구를 계속 사용할 수는 없나요?

A: SQL Server 2005는 이전 버전보다 보안이 더욱 강화되었습니다. 대부분의 경우 사용자는 SQL Server를 실행하기 위해 LocalSystem과 같은 내부 계정을 설정합니다. 하지만 Windows®에서 이러한 계정에는 대개 필요한 것보다 많거나 적은 권한이 부여됩니다. SQL Server 2005 엔진 및 에이전트 서비스를 실행하려면 상승된 권한이 없는 Windows 계정을 만들어야 합니다. 구성 관리자를 사용하여 이러한 계정을 선택하면 SQL Server와 운영 체제 모두에서 적절한 권한이 자동으로 해당 계정에 부여됩니다. Windows 도구를 사용하여 SQL Server 서비스를 관리하는 경우 적절한 권한을 부여하지 않거나 너무 많은 권한을 부여하게 될 수도 있습니다.

자세한 내용은 서비스 계정 변경 팁을 참조하십시오.

누가 내 서버에 로그온하고 있나요?

Q: 누가, 언제 내 서버에 로그온하는지 알고 싶습니다. 또한 특정 시간대에는 특정 사용자만 서버에 로그온하도록 제한하고 싶습니다. 그리고 추적 기능을 실행하여 사용자 작업을 추적할 수 있는지 알고 싶습니다. 이러한 작업이 가능한가요?

A: 가능합니다. 서비스 팩 2가 설치되어 있는 SQL Server 2005에서는 이러한 작업을 모두 수행할 수 있습니다.

SQL Server 2005에서는 LOGON 이벤트에 응답하여 T-SQL 또는 저장 프로시저를 실행하는 로그인 트리거를 만들 수 있습니다. 로그인 트리거를 사용하면 로그인 작업을 추적하고 SQL Server에 대한 로그인을 제한하거나 특정 로그인에 대한 세션 수를 제한하여 사용자를 감사하고 제어할 수 있습니다. 이벤트는 로그인이 성공적으로 인증된 후 사용자 세션이 실제로 설정되기 전에 실행된다는 점에 주의하십시오. 따라서 PRINT 문의 트리거(예: 메시지 또는 오류) 내부에서 시작되는 모든 메시지는 SQL Server 오류 로그로 보내집니다. 로그인에 대한 인증이 실패할 경우에는 Logon 트리거가 실행되지 않습니다.

다음 예에서는 로그인 트리거를 만들어 사용자가 로그인하면 곧바로 SQL Server 오류 로그로 메시지를 보내는 방법을 보여 줍니다.

ALTER TRIGGER Ops_Login
ON ALL SERVER
AFTER LOGIN
AS
PRINT SUSER_SNAME() + ' has just logged in to ' + LTRIM(@@ServerName) + ' SQL Server at '+LTRIM(getdate())
GO

서버 수준에서 설정된 트리거를 모두 보려면 다음 쿼리를 사용합니다.

SELECT * FROM sys.server_triggers;

유용한 조각 모음 방법

Q: SQL Server에서 데이터 파일 조각화를 해결하는 가장 좋은 방법은 무엇입니까? Windows의 조각 모음 도구를 사용하면 SQL 데이터 파일 전체가 하나로 간주되므로 세부적으로 조각 모음되지 않습니다.

A: 데이터베이스를 백업한 다음 복원하는 방법을 사용할 수 있습니다. 연속된 파일을 저장할 공간이 있는 경우에는 데이터베이스가 연속적으로 쓰여집니다. 일반적으로 실제 파일에 대해 조각 모음을 수행하기 위해 시스템 작동을 중단시킬 필요는 없습니다. 대개의 경우 외부 조각화는 그다지 많지 않습니다. 정기적으로 데이터에 대한 인덱스를 다시 작성하여 가급적 내부 조각화를 줄이는 것이 더 유용한 방법입니다. 이렇게 하면 미리 읽기의 효과가 극대화되고 더 많은 데이터를 버퍼링할 수 있습니다.

효율적인 디스크 I/O를 위한 가장 중요한 요소는 디스크 배열과 RAID 구성을 올바르게 설정하고 I/O 부하를 적절히 처리하도록 디스크 배열을 확장하고 로그, 데이터, TempDB 및 백업 파일의 레이아웃을 적절하게 유지 관리하는 것입니다. 데이터 파일의 크기 조정을 위한 기본적인 방법으로 자동 증가와 자동 축소를 사용하지 않으면 생성되는 볼륨 수준 파일 조각의 수가 줄어듭니다. 예를 들어 500MB에 대해 10번의 자동 증가를 수행하면 10개의 새로운 실제 파일 조각이 추가되지만 5GB를 수동으로 한 번 증가시키면 한 개의 파일 조각만 추가됩니다.

팁: 서비스 계정 변경

Windows NT® 계정을 사용하여 SQL Server 서비스 로그인 계정을 구성할 경우 SQL Server에서는 몇 가지 파일, 폴더 및 레지스트리 키에 Windows 사용자 권한 및 사용 권한을 설정한다는 사실을 알고 계십니까? 관리 도구 내의 서비스 콘솔을 사용하여 SQL Server 서비스 계정을 설정할 수도 있습니다. 하지만 서비스 콘솔을 통해 서비스 계정을 설정할 경우 앞서 언급한 SQL Server 및 Windows 항목에 대한 권한 및 사용 권한이 설정되지 않게 되고 그 결과 이러한 항목에 대한 적절한 보안 설정이 없어서 심각한 문제가 발생할 수도 있습니다.

따라서 SQL Server 또는 SQL Server 에이전트 서비스 계정을 변경할 때는 서비스 콘솔이 아니라 SQL Server 구성 관리자를 사용해야 합니다. 그러나 서비스 콘솔을 통해 계정을 이미 변경한 경우에도 문제를 해결할 수 있습니다.

1단계: 다음 레지스트리 키 및 하위 키에 모든 권한을 적용합니다.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<MSSQL.x>

2단계: 다음 NTFS 폴더에 MSSQLServer 서비스 및 SQLServerAgent 서비스의 시작 계정(로컬 Windows NT 계정 또는 도메인 Windows NT 계정)에 대한 모든 권한을 설정합니다.

Drive:\Program Files\Microsoft SQL Server\<MSSQL.1>\MSSQL

SQL Server/에이전트의 서비스 계정을 변경하는 작업은 수동으로 수행하는 것보다는 SQL Server 구성 관리자를 사용하여 수행하는 것이 좋습니다.

기술적 자문에 응해 주신 Microsoft IT 전문가: Cary Gottesman, Saleem Hakani, Trayce Jordan, Peter Kalbach, Al Noel, Uttam Parui, Amber Sitko, Buck Woody

추가 리소스