시스템 관리자가 잠긴 경우 SQL Server에 연결

적용 대상: SQL Server

이 문서에서는 잠긴 상태일 때 시스템 관리자로서 SQL Server 데이터베이스 엔진에 대한 액세스 권한을 다시 얻는 방법을 설명합니다. 시스템 관리자는 다음 이유 중 하나로 인해 SQL Server 인스턴스에 대한 액세스 권한을 잃을 수 있습니다.

• sysadmin 고정 서버 역할의 멤버인 모든 로그인이 실수로 제거되었습니다.

• sysadmin 고정 서버 역할의 멤버인 모든 Windows 그룹이 실수로 제거되었습니다.

• sysadmin 고정 서버 역할의 멤버에 해당하는 로그인은 퇴사했거나 부재 중인 개인을 위한 로그인입니다.

• sa 계정이 비활성화되어 있거나 암호를 아는 사람이 없습니다.

해결

액세스 문제를 해결하려면 단일 사용자 모드에서 SQL Server 인스턴스를 시작하는 것이 좋습니다. 이 모드는 액세스를 다시 시도하는 동안 다른 연결이 발생하지 않도록 합니다. 여기에서 SQL Server 인스턴스에 연결하고 sysadmin 서버 역할에 로그인을 추가할 수 있습니다. 이 솔루션에 대한 자세한 단계는 단계별 지침 섹션에 제공됩니다.

명령줄에서 -m 또는 -f 옵션을 사용하여 단일 사용자 모드로 SQL Server 인스턴스를 시작할 수 있습니다. 그러면 컴퓨터 로컬 관리자 그룹의 모든 구성원이 sysadmin 고정 서버 역할의 구성원으로 SQL Server 인스턴스에 연결할 수 있습니다.

단일 사용자 모드에서 인스턴스를 시작하면 SQL Server 에이전트 서비스를 중지합니다. 그렇지 않으면 SQL Server 에이전트가 먼저 연결하여 서버에 사용 가능한 유일한 연결을 가져와 로그인을 차단할 수 있습니다.

또한 로그인하기 전에 알 수 없는 클라이언트 응용 프로그램에서 사용 가능한 유일한 연결을 차지할 수도 있습니다. 이러한 일이 발생하지 않도록 하려면 애플리케이션 이름 뒤에 오는 -m 옵션을 사용하여 지정된 애플리케이션에서 단일 연결로의 연결을 제한할 수 있습니다. 예를 들어 -mSQLCMD을 사용하여 SQL Server를 시작하면 연결이 sqlcmd 클라이언트 프로그램으로 식별되는 단일 연결로 제한됩니다. Management Studio에서 쿼리 편집기 통해 연결하려면 -m"Microsoft SQL Server Management Studio - Query"을 사용합니다.

Important

-m을(를) 응용 프로그램 이름에 보안 기능으로 사용하지 않아야 합니다. 클라이언트 응용 프로그램은 연결 문자열 설정을 통해 응용 프로그램 이름을 지정하므로 가짜 이름을 이용하면 쉽게 스푸핑할 수 있습니다.

다음 표에는 명령줄에서 단일 사용자 모드로 인스턴스를 시작하는 다양한 방법이 요약되어 있습니다.

옵션	설명	사용하는 경우
-m	단일 연결로 연결 제한	인스턴스에 연결을 시도하는 다른 사용자가 없거나 인스턴스에 연결하는 데 사용하는 애플리케이션 이름을 잘 모르는 경우.
-mSQLCMD	자신을 sqlcmd 클라이언트 프로그램으로 식별해야 하는 단일 연결로 연결을 제한합니다.	sqlcmd로 인스턴스에 연결할 계획이며 다른 애플리케이션이 사용 가능한 유일한 연결을 사용하지 못하도록 하려는 경우.
-m"Microsoft SQL Server Management Studio - Query"	Microsoft SQL Server Management Studio - Query 애플리케이션으로 식별해야 하는 단일 연결로 연결을 제한합니다.	Management Studio에서 쿼리 편집기를 사용하여 인스턴스에 연결할 계획이며 다른 애플리케이션이 사용 가능한 유일한 연결을 사용하지 못하도록 하려는 경우.
-f	단일 연결에 대한 연결을 제한하고 최소한의 구성으로 인스턴스를 시작합니다.	다른 구성으로 인해 시작할 수 없는 경우.

단계별 지침

SQL Server를 단일 사용자 모드로 시작하는 방법에 대한 단계별 지침은 단일 사용자 모드로 SQL Server 시작을 참조하십시오.

PowerShell 사용

옵션 1: Azure Data Studio를 사용하여 실행 가능한 Notebook에서 직접 단계 실행

참고 항목

이 Notebook을 열기 전에 로컬 컴퓨터에 Azure Data Studio가 설치되어 있는지 검사. Azure Data Studio를 설치하려면 Azure Data Studio를 설치하는 방법에 대해 알아보기를 참조하세요.

Azure Data Studio에서 노트북 열기

옵션 2: 수동으로 단계 수행

1. 관리자 권한 Windows PowerShell 명령 프롬프트를 엽니다.

2. 서비스 이름과 SQL Server 인스턴스, 그리고 Windows 로그인 변수를 설정합니다. 이러한 값을 사용자 환경에 맞는 값으로 바꿉니다.

   기본 인스턴스가 있는 경우 인스턴스 이름 없이 MSSQLSERVER를 사용합니다.

   $service_name = "MSSQL`$instancename"
   $sql_server_instance = "machine_name\instance"
   $login_to_be_granted_access = "[CONTOSO\PatK]"
   

3. 다음 명령을 사용하여 단일 사용자 모드로 다시 시작할 수 있도록 SQL Server 서비스를 중지합니다.

   기본 인스턴스가 있는 경우 인스턴스 이름 없이 MSSQLSERVER를 사용합니다.

   net stop $service_name
   

4. 이제 단일 사용자 모드에서 SQL Server 인스턴스를 시작하고 SQLCMD.exe 연결만 허용합니다(/mSQLCMD).

   참고 항목

   대문자 SQLCMD를 사용해야 합니다

   기본 인스턴스가 있는 경우 인스턴스 이름 없이 MSSQLSERVER를 사용합니다.

   net start $service_name /f /mSQLCMD
   

5. sqlcmd를 사용하여 CREATE LOGIN 명령을 실행한 후 ALTER SERVER ROLE 명령을 실행합니다. 이 단계에서는 로컬 관리자 그룹의 멤버인 계정으로 Windows에 로그인한 것으로 가정합니다. 이 단계에서는 도메인 및 로그인 이름을 시스템 관리자 멤버십을 부여할 자격 증명으로 교체한 것으로 가정합니다.

   기본 인스턴스가 있는 경우 서버의 이름을 사용합니다.

   sqlcmd.exe -E -S $sql_server_instance -Q "CREATE LOGIN $login_to_be_granted_access FROM WINDOWS; ALTER SERVER ROLE sysadmin ADD MEMBER $login_to_be_granted_access; "
   

   다음 오류가 표시되면 다른 sqlcmd가 SQL Server에 연결되어 있지 않은지 확인해야 합니다.

   Sqlcmd: Error: Microsoft ODBC Driver X for SQL Server : Login failed for user 'CONTOSO\BobD'. Reason: Server is in single user mode. Only one administrator can connect at this time.

6. 혼합 모드(선택 사항): SQL Server 인스턴스가 혼합 인증 모드에서 실행 중인 경우 다음을 수행할 수도 있습니다.

   1. SQL Server 로그인에 sysadmin 역할 멤버 자격을 부여합니다. 다음과 같은 코드를 실행하여 sysadmin 고정 서버 역할의 멤버인 새 SQL Server 인증 로그인을 만듭니다. <strong_password>를 원하는 강력한 암호로 바꿉니다.

      기본 인스턴스가 있는 경우 서버의 이름을 사용합니다.

      $strong_password = "<strong_password>"
      sqlcmd.exe -E -S $sql_server_instance -Q "CREATE LOGIN TempLogin WITH PASSWORD = '$strong_password'; ALTER SERVER ROLE sysadmin ADD MEMBER TempLogin; "
      

   2. 또한 SQL Server 인스턴스가 혼합 인증 모드로 실행 중이고 사용하도록 설정된 sa 계정의 비밀번호를 초기화하려는 경우. 다음 구문을 사용하여 sa 계정의 암호를 변경합니다. <strong_password>를 새로운 강력한 암호로 바꿔야 합니다.

      기본 인스턴스가 있는 경우 서버의 이름을 사용합니다.

      $strong_password = "<strong_password>"
      sqlcmd.exe -E -S $sql_server_instance -Q "ALTER LOGIN sa WITH PASSWORD = $strong_password; "
      

7. 다중 사용자 모드에서 SQL Server 인스턴스 중지 및 다시 시작

   기본 인스턴스가 있는 경우 인스턴스 이름 없이 MSSQLSERVER를 사용합니다.

   net stop $service_name
   net start $service_name
   

SSMS(SQL Server Configuration Manager and Management Studio) 사용

이 지침은 다음 조건을 가정합니다.

• SQL Server가 Windows 8 이상에서 실행됩니다. 해당하는 경우 이전 버전의 SQL Server 또는 Windows에 대한 약간의 조정이 제공됩니다.

• SQL Server Management Studio가 컴퓨터에 설치되어 있습니다.

로컬 관리자 그룹의 멤버로 Windows에 로그인된 상태에서 이 지침을 수행합니다.

1. Windows 시작 메뉴 SQL Server 구성 관리자 아이콘을 마우스 오른쪽 단추로 클릭하고 관리자 권한으로 실행을 선택하여 관리자 자격 증명을 Configuration Manager에 전달합니다.

2. SQL Server 구성 관리자의 왼쪽 창에서 SQL Server 서비스를 선택합니다. 오른쪽 창에서 SQL Server 인스턴스를 찾습니다. (SQL Server의 기본 인스턴스에는 컴퓨터 이름 뒤에 (MSSQLSERVER)가 포함됩니다. 명명된 인스턴스는 등록된 서버에서와 같은 이름(대문자)으로 나타납니다. SQL Server 인스턴스를 마우스 오른쪽 단추로 클릭하고 속성을 선택합니다.

3. 시작 매개 변수 탭의 시작 매개 변수 지정 상자에 -m를 입력하고 추가를 선택합니다. 클릭합니다.

   몇몇 이전 버전의 SQL Server 에는 시작 매개 변수 탭이 없습니다. 이 탭이 없는 경우 고급 탭에서 시작 매개 변수를 두 번 클릭합니다. 매개 변수가 작은 창에서 열립니다. 기존 매개 변수를 변경하지 않도록 주의하세요. 맨 끝에 새 매개 변수 ;-m를 추가하고 확인을 선택합니다. 클릭합니다.

4. 확인을 선택하고 다시 시작 메시지가 나타나면 서버 이름을 마우스 오른쪽 단추로 클릭한 다음 다시 시작을 선택합니다.

5. SQL Server가 다시 시작되면 서버가 단일 사용자 모드로 전환됩니다. SQL Server 에이전트가 실행되고 있지 않은지 확인합니다. 시작하면 유일한 연결만 필요합니다.

6. Windows 시작 메뉴 Management Studio 아이콘을 마우스 오른쪽 단추로 클릭하고 관리자 권한으로 실행을 선택합니다. 그러면 관리자 자격 증명이 SSMS에 전달됩니다.

   이전 버전의 Windows에서는 관리자 권한으로 실행 옵션이 하위 메뉴로 나타납니다.

   일부 구성에서 SSMS는 여러 연결을 시도합니다. SQL Server가 단일 사용자 모드이기 때문에 여러 연결이 실패합니다. 시나리오에 따라 다음 작업 중 하나를 수행합니다.

   1. 관리자 자격 증명을 포함하는 Windows 인증을 사용하여 개체 탐색기를 연결합니다. 보안과 로그인을 차례로 확장하고 로그인을 두 번 클릭합니다. 서버 역할 페이지에서 sysadmin을 선택하고 확인을 선택합니다.

   2. 개체 탐색기 연결하는 대신 Windows 인증(관리자 자격 증명 포함)을 사용하여 쿼리 창에 연결합니다. 개체 탐색기에 연결할 수 없는 경우에만 이 방식으로 연결할 수 있습니다. 다음과 같은 코드를 실행하여 sysadmin 고정 서버 역할의 구성원인 새 Windows 인증 로그인을 추가합니다. 다음 예에서는 CONTOSO\PatK이라는 도메인 사용자를 추가합니다.

      CREATE LOGIN [CONTOSO\PatK] FROM WINDOWS;
      ALTER SERVER ROLE sysadmin ADD MEMBER [CONTOSO\PatK];
      

   3. SQL Server가 혼합 인증 모드에서 실행 중인 경우 Windows 인증(관리자 자격 증명 포함)을 사용하여 쿼리 창에 연결합니다. 다음과 같은 코드를 실행하여 sysadmin 고정 서버 역할의 멤버인 새 SQL Server 인증 로그인을 만듭니다.

      CREATE LOGIN TempLogin WITH PASSWORD = '<strong_password>';
      ALTER SERVER ROLE sysadmin ADD MEMBER TempLogin;
      

      Warning

      <strong_password>를 강력한 비밀번호로 바꿉니다.

   4. SQL Server가 혼합 인증 모드에서 실행 중이고 sa 계정의 비밀번호를 다시 설정하려는 경우 Windows 인증(관리자 자격 증명 포함)을 사용하여 쿼리 창에 연결합니다. 다음 구문을 사용하여 sa 계정의 비밀번호를 변경합니다.

      ALTER LOGIN sa WITH PASSWORD = '<strong_password>';
      

      Warning

      <strong_password>를 강력한 비밀번호로 바꿉니다.

7. Management Studio를 닫습니다.

8. 다음 몇 가지 단계는 SQL Server 다시 다중 사용자 모드로 전환합니다. SQL Server 구성 관리자의 왼쪽 창에서 SQL Server 서비스를 선택합니다.

9. 오른쪽 창에서 SQL Server인스턴스를 마우스 오른쪽 단추로 클릭한 다음 속성을 선택합니다.

10. 시작 매개 변수 탭의 기존 매개 변수 상자에서 -m 을 선택하고 제거를 선택합니다.

    몇몇 이전 버전의 SQL Server 에는 시작 매개 변수 탭이 없습니다. 이 탭이 없는 경우 고급 탭에서 시작 매개 변수를 두 번 클릭합니다. 매개 변수가 작은 창에서 열립니다. 이전에 추가한 ;-m 을 제거하고 확인을 선택합니다.

11. 서버를 마우스 오른쪽 단추로 클릭한 다음 다시 시작을 선택합니다. 단일 사용자 모드에서 SQL Server를 시작하기 전에 중지한 경우 SQL Server 에이전트 다시 시작해야 합니다.

이제 sysadmin 고정 서버 역할의 멤버인 계정 중 하나에 정상적으로 연결할 수 있습니다.

관련 콘텐츠

• 서버 시작 옵션 구성
• 데이터베이스 엔진 서비스 시작 옵션