Bora.Choi

 SQL Server 에이전트 구성

 SQL Server 에이전트는 msdb 데이터베이스에 있는 테이블에 대부분의 구성 정보를 저장합니다. SQL Server 에이전트는 SQL Server 자격 증명 개체를 사용하여 프록시의 인증 정보를 저장합니다.

이 기능을 수행하려면 SQL Server에서 sysadmin 고정 서버 역할 멤버인 계정의 자격 증명을 사용하도록 SQL Server 에이전트를 구성해야 합니다.

작업

 작업은 SQL Server 에이전트에서 수행하도록 지정된 일련의 동작입니다.

 작업을 사용하면 한 번 이상 실행되고 성공과 실패에 대해 모니터링될 수 있는 관리 작업을 정의할 수 있습니다. 작업은 한 대의 로컬 서버나 다중 원격 서버에서 실행될 수 있습니다.

각 작업 단계는 특정 보안 컨텍스트에서 실행됩니다. Transact-SQL 을 사용하는 작업 단계의 경우 EXECUTE AS 문을 사용하여 해당 작업 단계에 대한 보안 컨텍스트를 설정합니다. 다른 작업 단계 유형의 경우에는 프록시 계정을 사용하여 해당 작업 단계에 대한 보안 컨텍스트를 설정합니다.

SQL Server 에이전트 관리 보안

SQL Server 에이전트는 msdb 데이터베이스에서 sysadmin 고정 서버 역할의 멤버가 아닌 사용자의 SQL Server 에이전트에 대한 액세스를 제어하는 SQLAgentUserRole, SQLAgentReaderRole 및 SQLAgentOperatorRole 고정 데이터베이스 역할을 제공합니다. 이러한 고정 데이터베이스 역할 외에도 데이터베이스 관리자는 하위 시스템과 프록시를 사용하여 각 작업 단계가 작업 수행에 필요한 최소 권한으로 실행되도록 합니다.

• 역활

  •  msdb의 SQLAgentUserRole, SQLAgentReaderRole 및 SQLAgentOperatorRole 고정 데이터베이스 역할 멤버와 sysadmin 고정 서버 역할 멤버는 SQL Server 에이전트에 액세스할 수 있습니다.

• 하위 시스템  = SQL Agent Subsystems

  •  하위 시스템은 작업 단계에서 사용할 수 있는 기능을 나타내는 미리 정의된 개체입니다.
  •  전체 작업이 실행할 수 있는 권한이 있다고 해도 하위 시스템의 설정된 프록시로 실행할 수 없으면 실행 불가능하다.

• Proxy

  • SQL Server 에이전트는 프록시를 사용하여 보안 컨텍스트를 관리할 수 있습니다. 프록시는 둘 이상의 작업 단계에서 사용할 수 있습니다. sysadmin 고정 서버 역할의 멤버는 프록시를 만들 수 있습니다.
  • 각 프록시는 보안 자격 증명에 해당됩니다.
  • 각 프록시는 하위 시스템 집합 및 로그인 집합과 연결할 수 있으며 프록시는 해당 프록시와 연결된 하위 시스템을 사용하는 작업 단계에서만 사용할 수 있습니다.

  • SQL Server 에이전트 구현에 대한 보안을 향상시키려면 다음 지침을 따르십시오.

    • 프록시에 대한 전용 사용자 계정을 만들고 이러한 프록시 사용자 계정만 사용하여 작업 단계를 실행합니다.
    • 프록시 사용자 계정에 필요한 사용 권한만 부여합니다. 특정 프록시 계정에 할당된 작업 단계를 실행하는 데 실제로 필요한 사용 권한만 부여합니다.
    • Windows Administrators 그룹의 멤버인 Microsoft Windows 계정을 사용하여 SQL Server 에이전트 서비스를 실행하지 않습니다.
      

Proxy 만들기

Microsoft Windows 사용자의 보안 자격 증명에 대한 액세스 권한을 SQL Server 에이전트에 제공합니다. 각 프록시는 하나 이상의 하위 시스템과 연결될 수 있습니다. 프록시를 사용하는 작업 단계에서는 Windows 사용자의 보안 컨텍스트를 사용하여 지정된 하위 시스템에 액세스할 수 있습니다.

 SQL Server 에이전트가 프록시를 사용하는 작업 단계를 실행하기 전에 SQL Server 에이전트는 프록시에 정의되어 있는 자격 증명을 가장한 다음 해당 보안 컨텍스트를 사용하여 작업 단계를 실행합니다.

QL Server 에이전트 프록시는 자격 증명을 사용하여 Windows 사용자 계정에 대한 정보를 저장합니다. 자격 증명에 지정된 사용자에게 SQL Server가 실행 중인 컴퓨터에 대한 "일괄 작업으로 로그온" 권한이 있어야 합니다.

작업 단계에서 프록시를 사용하려면 사용자에게 프록시에 액세스할 수 있는 권한이 있어야 합니다. 다음과 같은 3가지 유형의 보안 주체에 액세스 권한을 부여할 수 있습니다.

• SQL Server 로그인
• 서버 역할
• msdb 데이터베이스 내의 역할

1. USE MASTER

   go

    

2.  -- 생성하기 window 유저를 생성해야한다.

   CREATE CREDENTIAL sqlagent WITH IDENTITY = '장비명\sqlagent', SECRET = '암호';

    

   SELECT * FROM SYS.CREDENTIALS;

    

   USE [msdb]

   GO

   EXEC msdb.dbo.sp_add_proxy @proxy_name=N'SQL_Agent_Proxy',@credential_name=N'sqlagent',

                @enabled=1,

                @description=N'SQL Agent 를위한Proxy 설정TEST'

   GO

   EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'SQL_Agent_Proxy', @subsystem_id=2

   GO

   EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'SQL_Agent_Proxy', @subsystem_id=3

   GO

   -- 보안주체를설정해준다. 읽기밖에되지않은일반로그인계정

   EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'SQL_Agent_Proxy', @login_name=N'dev'

   GO

    

3.  

   [작업을 생성 해본다.]

4.  

5.  

   해당 내역을 실행할 수 없는 dev 계정으로 JOB을 실행해 보았을 경우  실패한다. 윈도우 계정의 권한이 없으며, 최고 권한이 sysadmin도 아니기 때문이다.  Proxy 를 기본으로 사용하고 있기에 실행할 수 없다.

   Non-SysAdmins have been denied permission to run CmdExec job steps without a proxy account.  The step failed.

6. 작업 명령은 del c:\test_proxy.txt 로 해야 한다. 운영체제 명령인데 잘 못 적었음. ^________^

1.  

   윈도우 자격증명을 매핑한 Proxy로  실행하게 변경 한다.

    

2.  결과)

   Unable to start execution of step 1 (reason: Error authenticating proxy 도메인\계정, system error: The referenced account is currently locked out and may not be logged on to.).  The step failed.

3.  

   발생한다. 이유인즉, job의 dev 소유자는 작업을 실행할 수 있는  권한이 없다. 즉, SQL 로그인 계정이 msdb에 접근해서 job을 실행할 수 있는 권한이 소유해야 하며, 이 소유자가 운영체제의 로그인 계정이 아니고 SQL 계정임에도 불구하고 윈도우 계정을 Proxy를 만들어서 자격증명을 만들었기 때문에  파일 삭제는 이루어 진다. SQL 계정을 윈도우 계정으로 연결해준 격이 된다.
   

이 글은 스프링노트에서 작성되었습니다.