Contents hide

1 SQL Server 에 로그인

1.1 Microsoft Entra 인증만 허용

1.2 SQL Server 에 로그인

2 SQL Server User 생성 및 역할 할당

2.1 새 로그인(N)… 선택

2.2 User 생성 쿼리를 실행

2.3 [보안] 디렉토리 하위에 생성된 유저를 확인

3 App Service 시스템 할당 관리 ID 유효화

4 SQL Server IAM 설정

5 관리 ID를 이용한 SQL DB User 생성 및 권한 할당

5.1 새 로그인(N)… 선택

5.2 User 생성 쿼리를 실행

5.3 User 역할 할당 쿼리를 실행

5.4 DB 수준의 권한 조회

5.5 DB 역할 멤버쉽 조회

Agenda

Microsoft Entra 인증을 이용한 SQL Server 에 로그인 방법을 알아 본다.
일반적인 SQL User 추가 방법을 알아 본다.
시스템 할당 관리 ID(System assigned managed ID) User 추가 방법을 알아 본다.
관리 ID에 SQL DB 읽기/쓰기 권한을 할당하는 방법을 알아 본다.

SQL Server 접속 방식에 따라 추가 포트가 필요한 경우도 존재한다.

SQL Server 에 로그인

Microsoft Entra 인증만 허용

SQL Server 에 접속할 때 Entra 인증만을 허용한다.

이 설정을 적용한 경우, SQL Server admin 계정으로도 접속이 불가능하다.

SQL Server 에 로그인

이름 해결에 문제가 없는 것을 확인한다.
SSMS (SQL Server Management Stuido)의 접속 방식을 Microsoft Entra MFA 로 지정한다.
Entra 인증을 이용하여 SQL Server 에 로그인한다.

정상적으로 SQL Server 에 접속되는 것을 확인한다.

SQL Server User 생성 및 역할 할당

[보안] – [로그인] – [새 로그인] 을 선택한다.
User 생성 쿼리를 실행한다.
보안 디렉토리 하위에 새로운 유저가 생성된 것을 확인한다.

새 로그인(N)… 선택

[새 로그인] 을 누르면 아래와 같은 템플릿 쿼리가 표시된다.

-- =============================================================================================================================
-- Create SQL Login template for Azure SQL Database, Azure Synapse Analytics Database, and Azure Synapse SQL Analytics on-demand
-- =============================================================================================================================

CREATE LOGIN <SQL_login_name, sysname, login_name> 
	WITH PASSWORD = '<password, sysname, Change_Password>' 
GO

-- =============================================================================================================================
-- Create Microsoft Entra Login template for Azure SQL Database, Azure Synapse Analytics Database, and Azure Synapse SQL Analytics on-demand
-- =============================================================================================================================

-- CREATE LOGIN <Microsoft Entra Principal, sysname, login_name> FROM EXTERNAL PROVIDER

User 생성 쿼리를 실행

아래의 Sample query 를 실행한다.
명령이 완료되었다는 메시지를 확인한다.

CREATE LOGIN [Username]
    WITH PASSWORD = N'Password'
GO

[보안] 디렉토리 하위에 생성된 유저를 확인

App Service 시스템 할당 관리 ID 유효화

App service 의 identity 페이지로 이동한다.
System assigned Status 를 On 으로 변경 후 저장한다.

SQL Server IAM 설정

SQL Server 의 Access control (IAM) 페이지에 이동한다.
SQL DB Contributor 권한을 위에서 생성한 App Servcie 의 관리 ID에 할당한다.

관리 ID를 이용한 SQL DB User 생성 및 권한 할당

[SQL DB] – [보안] – [사용자] – [새 로그인] 를 선택한다.
관리 ID User 생성 쿼리를 실행한다.
User 권한 할당 쿼리를 실행한다.
[보안] 디렉토리 하위에 생성된 유저를 확인한다.

새 로그인(N)… 선택

[새 로그인] 을 누르면 아래와 같은 쿼리가 표시된다.

-- =======================================================================================
-- Create User as DBO template for Azure SQL Database and Azure Synapse Analytics Database
-- =======================================================================================
-- For login login_name, create a user in the database
CREATE USER <user_name, sysname, user_name>
	FOR LOGIN <login_name, sysname, login_name>
	WITH DEFAULT_SCHEMA = <default_schema, sysname, dbo>
GO

-- =======================================================================================
-- Create Microsoft Entra User for Azure SQL Database and Azure Synapse Analytics Database
-- =======================================================================================
-- For login <login_name, sysname, login_name>, create a user in the database
-- CREATE USER <Microsoft_Entra_User, sysname, user_name>
--    [   { FOR | FROM } LOGIN <Microsoft_Entra_Principal_Login, sysname, login_name>  ]  
--    | FROM EXTERNAL PROVIDER
--    [ WITH DEFAULT_SCHEMA = <default_schema, sysname, dbo> ]
-- GO


-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'<user_name, sysname, user_name>'
GO

User 생성 쿼리를 실행

CREATE USER [Resource name] FROM EXTERNAL PROVIDER

User 역할 할당 쿼리를 실행

ALTER ROLE db_datareader ADD MEMBER [User name]
ALTER ROLE db_datawriter ADD MEMBER [User name]

DB 수준의 권한 조회

-- 데이터베이스 수준의 권한 조회
SELECT 
    dp.name AS UserName,
    dp.type_desc AS UserType,
    perm.permission_name AS PermissionType,
    perm.state_desc AS PermissionState
FROM 
    sys.database_permissions AS perm
INNER JOIN 
    sys.database_principals AS dp ON perm.grantee_principal_id = dp.principal_id
WHERE 
    dp.name = 'User name';  -- 유저 이름으로 변경

DB 역할 멤버쉽 조회

-- 데이터베이스 역할 멤버십 조회
SELECT 
    dp.name AS UserName,
    dp.type_desc AS UserType,
    dr.name AS RoleName
FROM 
    sys.database_role_members AS drm
INNER JOIN 
    sys.database_principals AS dr ON drm.role_principal_id = dr.principal_id
INNER JOIN 
    sys.database_principals AS dp ON drm.member_principal_id = dp.principal_id
WHERE 
    dp.name = 'User name';  -- 유저 이름으로 변경