삭제된 AD 그룹 계정으로 SQL Server 로그인 사례 (로그인 그룹 삭제 조치해야할 사항)

 

·       Version : SQL Server

 

도메인 [DOMAIN\group] 멤버 [DOMAIN\user] 있고 [DOMAIN\group] SQL Server 인스턴스에 대한 로그인 권한이 있는 그룹이 있다고 가정한다. “Drop Login [DOMAIN\group]” 실행하면 [DOMAIN\user] 사용자는 이상 SQL Server 액세스 없을 것으로 예상했다. 그러나 아래와 같은 결과가 나왔다.

·       [DOMAIN\user] 새로운 연결은 허용되지 않음

·       기존 연결된 사용자는 계속 작동함 (기존의 [DOMAIN\user] 연결에는 그룹 구성원 권한이 캐시되므로 해당 값을 새로 고칠때 까지 연결을 통해 쿼리를 계속 실행할 있다.)

 

“DROP LOGIN [DOMAIN\user]” 실행될 [DOMAIN\user] 로그인으로 추가되어있고 SQL Server 연결이 되어 있는 경우 다음과 같은 오류 메시지와 함께 명령이 실패 한다.

DROP LOGIN” command fails with error “Could not drop login 'DOMAIN\user' as the user is currently logged in.

 

 [DOMAIN\user] 연결이 해제되면  “DROP LOGIN [DOMAIN\user]” 성공하고 사용자는 이상 SQL Server 액세스할 없다.

[DOMAINO\group] 로그인으로 추가되고 “DROP LOGIN [DOMAINO\group]” 명령을 실행하면 [DOMAINO\user] 로그인 경우에도 “DROP LOGIN”명령이 성공한다. 그러나 [DOMAIN\ user] 여전히 로그인되어 있는지 확인하기 위해해서는 아래 스크립트를 실행하여 확인할 있다.

select login_name from sys.dm_exec_sessions where login_name = 'DOMAIN\user'

 

"DROP LOGIN <loginName>" 실행하면 SQL Server <loginName> 계정에 대한 활성 연결이 있는지 확인한다. 로그인에 대해 활성화 연결이 없으면 로그인이 제거되고 명령이 성공한다. 그러나 <loginName> AD 그룹 경우 SQL Server 그룹의 구성원 활성 로그인이 있는지 확인하지 않는다. [DOMAIN\ group] 삭제하면 [DOMAIN\user] 로그인되어 있고 [DOMAIN\ group] 구성원 경우에도 [DOMAIN\ group] 대한 활성 연결이 없기 때문에 명령이 성공한다. 실제로 [DOMAIN\ user] 권한있는 작업 ( : 로그인 생성) 수행하거나 연결을 재설정 ( : sp_reset_connection 로그 아웃 다시 로그인) 때까지 연결 상태를 유지한다. 이때 SQL Server [DOMAIN\ user] 권한을 확인하고 이상 유효한 로그인이 없는지 확인하고 연결을 끊는다. 이러한 연결을 강제로 재설정 있다. 그룹 로그인을 삭제 아래 스크립트를 실행한다.

SELECT session_id FROM sys.dm_exec_sessions WHERE login_name = 'DOMAIN\user'

 

스크립트를 실행하면 [DOMAIN\ user] 연결의 모든 세션 ID 보여준다. 이제 KILL <session_id>”를 실행하여 강제로 사용자 연결을 종료한다. [DOMAIN\user] 세션은 강제 종료된 상태이기 때문에 쿼리를 실행할 이상 유효한 로그인이 없는 상태여서 다시 연결을 해야한다. 이때 그룹이 삭제되어 있기 때문에 연결할 없게 된다.

연결 자체는 sp_reset_connection 호출하여 스레드가 로그 아웃했다가 SQL Server 다시 로그인하도록하여 권한 업데이트를 강제 수도 있다. 연결이 영구적이며 지속적으로 로그 아웃했다가 다시 로그인하여 권한 검사를 수행하지 않기 때문에 연결 풀링에 유용하다. 풀링 연결을 검색 sp_reset_connection 호출하면 로그인 권한이 취소 경우 연결이 실패된다.

 

그룹에 대한 작업 권한이 취소되면 구성원 사용자의 권한이 즉시 취소된다. 예를 들어 [DOMAIN\group] "foo"테이블에 대한 선택 권한이 있고 "REVOKE SELECT ON foo TO [DOMAIN\ group]" 실행 경우 [DOMAIN\user] 즉시 "foo" 대한 선택 권한을 잃게된다. 아래 스크립트를 사용하여  실습 가능하다.

# Login as an admin user to SQL Server and add [DOMAIN\group] as a login

#

CREATE LOGIN [DOMAIN\group] FROM WINDOWS

GO

 

# In another terminal, login to SQL Server as [DOMAIN\user] and run a query to show you have access

#

SELECT @@VERSION

GO

 

# Switch back to your admin terminal and drop the group login. It will succeed even though [DOMAIN\user] is connected.

#

DROP LOGIN [DOMAIN\group]

GO

 

# Switch back to your [DOMAIN\user] window and check you still have access

#

SELECT @@VERSION

GO

 

# Switch to the admin terminal and kill the connection

#

SELECT session_id FROM sys.dm_exec_sessions WHERE login_name = DOMAIN\user

GO

 

KILL <session_id from query above>

GO

 

# Switch to your [DOMAIN\user] window to see they no longer have access. This command should not return the version information.

#

SELECT @@VERSION

GO

 

 

[참고자료]

https://blogs.msdn.microsoft.com/bobsql/2018/08/03/sql-server-mysteries-the-case-of-the-dropped-ad-group-login/

 

 

2019-09-11 / Sungwook Kang / http://sungwookkang.com

 

 

 

SQL Server Login, SQL Server Account, Active Directory, AD, DROP LOGIN, CREATE LOGIN

  1. 침입자 다시보기 2020.07.21 12:32

    잘 보고 갑니다~~

QPI(Query Performance Insights) 라이브러리를 사용하여 Azure SQL Managed Instance 로그쓰기 사용량 확인

 

·       Version : Azure SQL

 

Azure SQL Managed Instance Azure 클라우드에서 호스팅되는 완전히 관리되는 SQL Server 인스턴스이다. 관리형 인스턴스에는 최대 로그쓰기 처리량 성능에 일부 제한이 있어 워크로드 속도가 느려질 있다. 이번 포스트에서는 관리형 인스턴스에서 쓰기 로그 처리량 문제를 식별하는 방법에 대해서 살펴본다.

 

Azure SQL Managed Instance에는 최대 로그 쓰기 속도와 같은 가지 기본 제공 리소스 제한이 있다. 로그 쓰기 제한을 도입한 이유는 로그 백업이 들어오는 데이터를 포착할 있어야 하기 때문이다.

·       Overview Azure SQL Database managed instance resource limits : https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-resource-limits#service-tier-characteristics

 

이번 포스트에서는 QPI 라이브러리를 사용하여 관리형 인스턴의 대기 통계를 쉽게 분석하고 있다.  QPI 라이브러리를 설치하려면 아래 링크로 이동하여 SQL Server 버전에 대한 SQL 스크립트를 다운로드 한다. QPI 라이브러리는 Query Store 보기에 의존하기 때문에 SQL Server 2016이상, Azure SQL 에서 사용이 가능하다.

·       Download QPI(Query Performance Insights) : https://github.com/JocaPC/qpi#installation

Disclaimer: QPI library is open source library provided as-is and not maintained by Microsoft. There are not guarantees that the results are correct and that there are not bugs in calculations. This is a helper library that can help you to more easily analyze performance of your Managed Instance, but you can do the same job by looking directly at DMVs.

 

라이브러리를 사용하면 대기 통계의 스냅샷을 쉽게 작성하고 잠시 기다렸다가 대기 통계 값을 읽을 있다.

exec qpi.snapshot_wait_stats;

 

waitfor delay '00:00:03';

 

select *

from qpi.wait_stats

order by wait_time_s desc;

 

 

결과를 살펴보면, 관리 인스턴스의 태스크가 INSTANCE_LOG_RATE_GOVERNOR 대기 유형을 기다리고 있음을 있다. 스크립트를 주기적으로 실행하여 이러한 문제쿼리를 식별할 있다.  또한 qpi에서 제공하는 다양한 명령어를 사용하여 관리형 인스턴스의 IO 성능을 분석하여 병목 현상을 식별할 있다.

·       qpi.snapshot_file_stats 프로시저는 sys.dm_io_virtual_file_stats DMV 함수에서 I/O 통계 스냅샷을 작성한다. sys.dm_io_virtual_stats에는 누적 정보가 포함되어 있으며 최근 시간 간격으로 샘플을 계산해야 하므로 스냅샷을 작성해야 한다.

·       qpi.file_stats보기는 마지막 스냅샷 이후 파일 통계를 가져온다. qpi sys.dm_io_virtual_file_stats 데이터를 기반으로 하는 IOPS 처리량등 여러 계산이 포함된다.

 

아래 스크립트는 파일 유형(LOG/DATA)별로 분류된 인스턴스의 쓰기 처리량(MB/s) IOPS 요약한다.

exec qpi.snapshot_file_stats;

 

waitfor delay '00:00:01';

 

select

       type,

       write_mbps = sum(write_mbps),

       iops = sum(iops)

from qpi.file_stats

group by rollup (type);

 

 

결과를 살펴보면 현재 인스턴스에서는 47.5MB/s 로그파일 쓰기와 1226 IOPS 사용하고 있음을 있다. Azure 설명서에서 리소스 제한에 대한 설명을 보면  ~48MB/s 현재 사용되는 인스턴스의 리밋이며, 이러한 리밋으로 인해서 INSTNACE_LOG_RATE_GOVERNOR 대기 통계가 발생하는 것이다. 인스턴스의 최대 사용량 리밋으로 인해서 성능 저하가 발생하지 않도록QPI 스크립트를 주기적으로 실행하여, 인스턴스의 리밋과 QPI결과값을 비교하여,모니터링 있도록 한다.

 

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2019/03/11/identify-log-write-limits-on-azure-sql-managed-instance-using-qpi-library/

 

2019-09-10 / Sungwook Kang / http://sungwookkang.com

 

Azure SQL, QPI, Query Performance Insights, SQL Server 2016, Query Store

ODBC 드라이버를 사용하여 SQL Server에서 Azure CosmosDB 쿼리 실행

 

·       Version : SQL Server, Azure CosmosDB

 

Azure CosmosDB 기존의  SQL Server 같은 클래식 데이터베이스에서 CosmosDB 컬렉션을 조회할 있는 ODBC 드라이버를 제공한다. 이번 포스트에서는 Transaction-SQL 사용하여 SQL Server에서 CosmosDB 컬렉션을 쿼리하는 방법을 설명한다.

 

SQL Server에서 CosmosDB 쿼리하는 이유는 무엇일까? CosmosDB 문서 기타 비관계형 데이터 유형을 저장 검색하고, 필터링하고 정렬할 있는 SQL / API 제공한다. 그러나 경우에 따라 GROUP BY, HAVING, 분석 기능이 있는 복잡한 쿼리를 실행하거나 CosmosDB 비관계형 데이터를  SQL Server 테이블에 저장하는 데이터와 조인해야 하는 경우가 있다. 경우 CosmosDB 데이터를 쿼리하기 위해 Transaction-SQL 모든 기능을 활용할 있다.

 

[CosmosDB 설정]

먼저 CosmosDB계정을 생성하고 컬렉션에 실습할 데이터를 추가 한다. 데이터베이스 WWI Orders 라는 콜렉션을 생성하고, 개의 문서를 추가한다.

 

실습 데이터 등록이 완료 되었으면, SQL API 사용하여 컬렉션에 액세스한다.

 

[드라이버 설정]

SQL Server 설치된 컴퓨터에 CosmosDB ODBC 드라이버를 설치해야한다. 드라이버를 설치하면 DSN에서 ODBC 소스를 설정하고 연결을 테스트해야한다.

·       ODBC 드라이버 다운로드 : https://aka.ms/cosmos-odbc-64x64

 

 

[CosmosDB 쿼리]

모든 설정 후에 기존 SQL Server에서 OPENROWSET 함수를 사용하여 CosmosDB DSN 지정하여 CosmosDB 데이터를 쿼리 있다.

 

실습 데이터의 결과를 보면 CosmosDB에서 3개의 문서를 나타내는 3개의 행을 얻게 된다. 누락된 필드는 NULL 반한된다. 또한 아래 스크립트처럼 필터를 사용할 수도 있다.

SELECT a.*

FROM OPENROWSET('MSDASQL',

'DSN=cosmosdb1',

'select * from Orders where billto_Name = ''John Smith''') as a

 

실습데이터의 JSON 형식을 보면 shipTo 또는 billTo 같이 복잡한 JSON 객체가 모두 동일한 ROW 표시되고 있는 것을 있다. , 모든 필드가 <object name>_ <field name> 형식으로 반환된다는 것이다. 하위 오프젝트가 있는 것으로 예상되는 경우 사용자는 이를 알고 있어야 한다. 또한 필자의 경우 태그와 같은 배열 속성이 매핑되거나 반환되지 않는다.

 

[결론]

CosmosDB ODBC 드라이버를 사용하면 CosmosDB 데이터에 대해 Transact-SQL 쿼리를 실행할 있다. 이는 CosmosDB 저장된 원격 데이터에 대해서 데이터 분석이 필요한 경우 유용햘 있다. 경우 결과를 필터링 하고 필요한 필드만 선택한 다음 전체 Transact-SQL 언어를 사용하여 SQL Server에서 다양한 분석을 수행하는 조건자를 사용하여 CosmosDB 보낼 있다.

( 글을 쓰는 시점에는) CosmosDB드라이버는 Azure SQL 설치되어 있지 않으며 자체 드라이버를 추가할 없기 때문에 이는 Azure SQL 데이터베이스가 아닌 SQL Server에서만 가능하다.

 

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2019/03/11/running-azure-cosmosdb-queries-from-sql-server-using-odbc-driver/

 

2019-09-09 / Sungwook Kang / http://sungwookkang.com

 

Azure CosmosDB, SQL Server에서 Azure CosmosDB 연결하기, CosmosDB ODBC

Azure SQL Managed Instance에서 로컬 스토리지 사용량 모니터링

 

·       Version : SQL Server, Azure SQL Managed Instance

 

Azure SQL Managed Instance 사용할때, 선택한 인스턴스의 vCore 예약 스토리지에 따라 사전에 정의된 스토리지 공간이 있다. 이번 포스트에서는 Managed Instance 스토리지 공간을 모니터링하는 방법에 대해서 알아본다.

 

Managed Instance에서는 아래와 같은 제약이 있다.

·       인스턴스의 스토리지 제한은 Azure Portal에서 선택할 있다. 범용은 8TB, 비즈니스 크리티컬은 4TB 보다 없다.

·       (범용 전용) 로컬 SSD 디스크 스토리지의 제한 – Managed Instance 경우  tempdb 24GB X vCore 공간만큼 로컬 SSD 배치된다. 배치된 로컬 SSD 한계에 도달하면 tempdb에서 임시 객체를 만들수 없다.

 

스토리지 리밋에 도달하면 스토리지 공간/ vCore 수를 늘리거나 일부 리소스를 비워야 한다. 스토리지 변경은 오래 걸릴수 있기 때문에 스토리지 한계에 도달하기 전에 스토리지를 추가하는 것이 중요하다. sys.dm_os_volume_stats Managed Instance 스토리지를 포함하여 볼륨에 대한 사용 정보를 제공한다. 아래 스크립트를 사용하여 스토리지의 사용량 정보를 확인 있다.

SELECT

       volume_mount_point,

       used_gb = CAST(MIN(total_bytes / 1024. / 1024 / 1024) AS NUMERIC(8,1)),

       available_gb = CAST(MIN(available_bytes / 1024. / 1024 / 1024) AS NUMERIC(8,1)),

       total_gb = CAST(MIN((total_bytes+available_bytes) / 1024. / 1024 / 1024) AS NUMERIC(8,1))

FROM sys.master_files AS f

       CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)

GROUP BY volume_mount_point;

 

 

 

 

아래 그림에서 http:// 경우 원격 Azure Premium Disk 저장소를 사용하고 있는지 보여준다.

 

 

스크립트를 주기적으로 실행하여 available_gb 줄어드는 것을 확인하여 스토리지 공간이 부족할 경우 알림을 받을 있도록 한다.

 

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2019/03/08/monitor-storage-local-storage-usage-on-general-purpose-managed-instance/

 

 

2019-09-06 / Sungwook Kang / http://sungwookkang.com

 

Azure SQL Managed Instance, Azure Local Storage, SQL Azure, sys.dm_os_volume_stats, 로컬 스토리지

+ Recent posts