인증으로부터 분리된 사용자 방지

인증으로부터 분리된 사용자 방지

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server 관리자가 겪는 문제 중 하나가 인증 관련문제로 '고아'가 발생하는 경우이다. SQL Server에서 SQL Server 로그인을 만들 때 문제가 발생 한다. 이 작업을 수행 할 때 SQL Server는 SQL Server 로그인에 대해 고유 SID를 생성한다. 로그인을 생성한 후 해당 서버 인스턴스의 사용자 데이터베이스에서 데이터베이스 사용자를 작성하고 해당 SQL Server 로그인을 사용하여 데이터베이스 사용자를 연결 한다.

 

다른 SQL Server 인스턴스에 해당 사용자 데이터베이스를 복원하려고 할 때 까지는 잘 작동하다가 서버에 이전과 동일한 사용자 ID를 가진 SQL Server 로그인을 만들 경우 해당 SQL Server 로그인의 SID(다른 데이터베이스 인스턴스)는 복원 한 사용자 데이터베이스에서 데이터베이스 사용자가 일치하지 않는다.

 

데이터베이스 미러링을 사용하는 경우 데이터베이스 사용자가 고아인 경우 하나의 인스턴스에서 다른 인스턴스로 페일 오버 되기 때문에 이것은 특히 큰 문제가 된다. 이 외에도 로그전달 등 기존 데이터베이스에서 새 데이터베이스 서버로 마이그레이션 할 때 문제가 발생 한다.

 

이 문제를 해결 하기 위한 방법은 여러 가지가 있지만 여기서는 원본 서버의 SID를 이용하여 같은 SID의 새 서버 로그인을 만드는 것이다.

 

다음 스크립트는 현재 서버의 모든 SQL Server 로그인에 대한 SID 정보를 확인 할 수 있다.

SELECT name, [sid] FROM sys.server_principals WHERE [type] = 's';

 

 

 

기존 계정을 삭제하고 새로운 계정 생성시 SID를 부여하여 생성한다.

-- Create new SQL Login on new server instance IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'SQLTest') DROP LOGIN SQLTest; GO   -- Use the sid from the old server instance CREATE LOGIN SQLTest WITH PASSWORD = N'YourStrongPassword#', sid = 0x05130637A7E8104FBB8E13106405138B, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; GO

 

 

 

[참고자료]

http://www.sqlskills.com/blogs/glenn/how-to-avoid-orphaned-database-users-with-sql-server-authentication/?utm_source=rss&utm_medium=rss&utm_campaign=how-to-avoid-orphaned-database-users-with-sql-server-authentication

 

 

2013-08-27 / 강성욱 / http://sqlmvp.kr