{"id":6255,"date":"2024-05-02T14:11:19","date_gmt":"2024-05-02T05:11:19","guid":{"rendered":"https:\/\/engineer-chanpu.blog\/?p=6255"},"modified":"2024-05-05T09:58:03","modified_gmt":"2024-05-05T00:58:03","slug":"sql-server-azure-%ec%8b%9c%ec%8a%a4%ed%85%9c-%ed%95%a0%eb%8b%b9-%ea%b4%80%eb%a6%ac-id-%ec%9c%a0%ec%a0%80-%ec%b6%94%ea%b0%80-%eb%b0%a9%eb%b2%95","status":"publish","type":"post","link":"https:\/\/engineer-chanpu.blog\/?p=6255","title":{"rendered":"SQL Server: Azure \uc2dc\uc2a4\ud15c \ud560\ub2f9 \uad00\ub9ac ID \uc720\uc800 \ucd94\uac00 \ubc29\ubc95"},"content":{"rendered":"\n

Agenda<\/h1>\n\n\n\n
    \n
  1. Microsoft Entra \uc778\uc99d\uc744 \uc774\uc6a9\ud55c SQL Server \uc5d0 \ub85c\uadf8\uc778 \ubc29\ubc95\uc744 \uc54c\uc544 \ubcf8\ub2e4.<\/li>\n\n\n\n
  2. \uc77c\ubc18\uc801\uc778 SQL User \ucd94\uac00 \ubc29\ubc95\uc744 \uc54c\uc544 \ubcf8\ub2e4.<\/li>\n\n\n\n
  3. \uc2dc\uc2a4\ud15c \ud560\ub2f9 \uad00\ub9ac ID(System assigned managed ID)<\/code> User \ucd94\uac00 \ubc29\ubc95\uc744 \uc54c\uc544 \ubcf8\ub2e4.<\/li>\n\n\n\n
  4. \uad00\ub9ac ID\uc5d0 SQL DB \uc77d\uae30\/\uc4f0\uae30 \uad8c\ud55c\uc744 \ud560\ub2f9\ud558\ub294 \ubc29\ubc95\uc744 \uc54c\uc544 \ubcf8\ub2e4.<\/li>\n<\/ol>\n\n\n\n
    <\/div>\n\n\n\n
    \"\"
    SQL Server \uc811\uc18d \ubc29\uc2dd\uc5d0 \ub530\ub77c \ucd94\uac00 \ud3ec\ud2b8\uac00 \ud544\uc694\ud55c \uacbd\uc6b0\ub3c4 \uc874\uc7ac\ud55c\ub2e4.<\/figcaption><\/figure>\n\n\n\n
    <\/div>\n\n\n\n

    SQL Server \uc5d0 \ub85c\uadf8\uc778<\/h2>\n\n\n\n

    Microsoft Entra \uc778\uc99d\ub9cc \ud5c8\uc6a9<\/h3>\n\n\n\n

    SQL Server \uc5d0 \uc811\uc18d\ud560 \ub54c Entra \uc778\uc99d\ub9cc\uc744 \ud5c8\uc6a9\ud55c\ub2e4. <\/p>\n\n\n\n

    \uc774 \uc124\uc815\uc744 \uc801\uc6a9\ud55c \uacbd\uc6b0, SQL Server admin<\/code> \uacc4\uc815\uc73c\ub85c\ub3c4 \uc811\uc18d\uc774 \ubd88\uac00\ub2a5\ud558\ub2e4. <\/p>\n\n\n\n

    \"\"
    Microsoft Entra \uc778\uc99d<\/figcaption><\/figure>\n\n\n\n
    <\/div>\n\n\n\n

    SQL Server \uc5d0 \ub85c\uadf8\uc778<\/h3>\n\n\n\n
      \n
    1. \uc774\ub984 \ud574\uacb0\uc5d0 \ubb38\uc81c\uac00 \uc5c6\ub294 \uac83\uc744 \ud655\uc778\ud55c\ub2e4.<\/li>\n\n\n\n
    2. SSMS (SQL Server Management Stuido)\uc758 \uc811\uc18d \ubc29\uc2dd\uc744 Microsoft Entra MFA<\/code> \ub85c \uc9c0\uc815\ud55c\ub2e4.<\/li>\n\n\n\n
    3. Entra \uc778\uc99d\uc744 \uc774\uc6a9\ud558\uc5ec SQL Server \uc5d0 \ub85c\uadf8\uc778\ud55c\ub2e4.<\/li>\n<\/ol>\n\n\n\n
      \"\"
      1. nslookup <xxxx.database.windows.net><\/figcaption><\/figure>\n\n\n\n
      <\/div>\n\n\n
      \n
      \"\"
      2. \uc11c\ubc84\uc5d0 Microsoft Entra MFA \uc778\uc99d\uc73c\ub85c \uc811\uc18d<\/figcaption><\/figure>\n<\/div>\n\n\n
      <\/div>\n\n\n\n

      \uc815\uc0c1\uc801\uc73c\ub85c SQL Server \uc5d0 \uc811\uc18d\ub418\ub294 \uac83\uc744 \ud655\uc778\ud55c\ub2e4.<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n
      <\/div>\n\n\n\n

      SQL Server User \uc0dd\uc131 \ubc0f \uc5ed\ud560 \ud560\ub2f9<\/h2>\n\n\n\n
        \n
      1. [\ubcf4\uc548] – [\ub85c\uadf8\uc778] – [\uc0c8 \ub85c\uadf8\uc778] \uc744 \uc120\ud0dd\ud55c\ub2e4.<\/li>\n\n\n\n
      2. User \uc0dd\uc131 \ucffc\ub9ac\ub97c \uc2e4\ud589\ud55c\ub2e4.<\/li>\n\n\n\n
      3. \ubcf4\uc548 \ub514\ub809\ud1a0\ub9ac \ud558\uc704\uc5d0 \uc0c8\ub85c\uc6b4 \uc720\uc800\uac00 \uc0dd\uc131\ub41c \uac83\uc744 \ud655\uc778\ud55c\ub2e4.<\/li>\n<\/ol>\n\n\n\n

        \uc0c8 \ub85c\uadf8\uc778(N)… \uc120\ud0dd<\/h3>\n\n\n\n
        \"\"<\/figure>\n\n\n\n
        <\/div>\n\n\n\n

        [\uc0c8 \ub85c\uadf8\uc778] \uc744 \ub204\ub974\uba74 \uc544\ub798\uc640 \uac19\uc740 \ud15c\ud50c\ub9bf \ucffc\ub9ac\uac00 \ud45c\uc2dc\ub41c\ub2e4.<\/p>\n\n\n\n

        <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
        -- =============================================================================================================================<\/span><\/span>\n-- Create SQL Login template for Azure SQL Database, Azure Synapse Analytics Database, and Azure Synapse SQL Analytics on-demand<\/span><\/span>\n-- =============================================================================================================================<\/span><\/span>\n<\/span>\nCREATE<\/span> <\/span>LOGIN<\/span> <SQL_login_name, <\/span>sysname<\/span>, login_name> <\/span><\/span>\n\t<\/span>WITH<\/span> <\/span>PASSWORD<\/span> = <\/span>'<password, sysname, Change_Password>'<\/span> <\/span><\/span>\nGO<\/span><\/span>\n<\/span>\n-- =============================================================================================================================<\/span><\/span>\n-- Create Microsoft Entra Login template for Azure SQL Database, Azure Synapse Analytics Database, and Azure Synapse SQL Analytics on-demand<\/span><\/span>\n-- =============================================================================================================================<\/span><\/span>\n<\/span>\n-- CREATE LOGIN <Microsoft Entra Principal, sysname, login_name> FROM EXTERNAL PROVIDER<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
        <\/div>\n\n\n\n
        User \uc0dd\uc131 \ucffc\ub9ac\ub97c \uc2e4\ud589<\/h3>\n\n\n\n
          \n
        1. \uc544\ub798\uc758 Sample query \ub97c \uc2e4\ud589\ud55c\ub2e4.<\/li>\n\n\n\n
        2. \uba85\ub839\uc774 \uc644\ub8cc\ub418\uc5c8\ub2e4\ub294 \uba54\uc2dc\uc9c0\ub97c \ud655\uc778\ud55c\ub2e4.<\/li>\n<\/ol>\n\n\n\n
          <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
          CREATE<\/span> <\/span>LOGIN<\/span> [Username]<\/span><\/span>\n    <\/span>WITH<\/span> <\/span>PASSWORD<\/span> = <\/span>N'Password'<\/span><\/span>\nGO<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
          <\/div>\n\n\n\n
          \"\"<\/figure>\n\n\n\n
          <\/div>\n\n\n\n
          [\ubcf4\uc548] \ub514\ub809\ud1a0\ub9ac \ud558\uc704\uc5d0 \uc0dd\uc131\ub41c \uc720\uc800\ub97c \ud655\uc778<\/h3>\n\n\n\n
          \"\"<\/figure>\n\n\n\n
          <\/div>\n\n\n\n
          App Service \uc2dc\uc2a4\ud15c \ud560\ub2f9 \uad00\ub9ac ID \uc720\ud6a8\ud654<\/h2>\n\n\n\n
            \n
          1. App service \uc758 identity<\/code> \ud398\uc774\uc9c0\ub85c \uc774\ub3d9\ud55c\ub2e4.<\/li>\n\n\n\n
          2. System assigned Status<\/code> \ub97c On \uc73c\ub85c \ubcc0\uacbd \ud6c4 \uc800\uc7a5\ud55c\ub2e4.<\/li>\n<\/ol>\n\n\n\n
            \"\"<\/figure>\n\n\n\n
            <\/div>\n\n\n\n
            SQL Server IAM \uc124\uc815<\/h2>\n\n\n\n
              \n
            1. SQL Server \uc758 Access control (IAM)<\/code> \ud398\uc774\uc9c0\uc5d0 \uc774\ub3d9\ud55c\ub2e4.<\/li>\n\n\n\n
            2. SQL DB Contributor<\/code> \uad8c\ud55c\uc744 \uc704\uc5d0\uc11c \uc0dd\uc131\ud55c App Servcie \uc758 \uad00\ub9ac ID\uc5d0 \ud560\ub2f9\ud55c\ub2e4.<\/li>\n<\/ol>\n\n\n\n
              \"\"<\/figure>\n\n\n\n
              <\/div>\n\n\n\n
              \uad00\ub9ac ID\ub97c \uc774\uc6a9\ud55c SQL DB User \uc0dd\uc131 \ubc0f \uad8c\ud55c \ud560\ub2f9<\/h2>\n\n\n\n
                \n
              1. [SQL DB] – [\ubcf4\uc548] – [\uc0ac\uc6a9\uc790] – [\uc0c8 \ub85c\uadf8\uc778] \ub97c \uc120\ud0dd\ud55c\ub2e4.<\/li>\n\n\n\n
              2. \uad00\ub9ac ID User \uc0dd\uc131 \ucffc\ub9ac\ub97c \uc2e4\ud589\ud55c\ub2e4.<\/li>\n\n\n\n
              3. User \uad8c\ud55c \ud560\ub2f9 \ucffc\ub9ac\ub97c \uc2e4\ud589\ud55c\ub2e4.<\/li>\n\n\n\n
              4. [\ubcf4\uc548] \ub514\ub809\ud1a0\ub9ac \ud558\uc704\uc5d0 \uc0dd\uc131\ub41c \uc720\uc800\ub97c \ud655\uc778\ud55c\ub2e4.<\/li>\n<\/ol>\n\n\n\n
                <\/div>\n\n\n\n
                \uc0c8 \ub85c\uadf8\uc778(N)… \uc120\ud0dd<\/h3>\n\n\n\n
                [\uc0c8 \ub85c\uadf8\uc778] \uc744 \ub204\ub974\uba74 \uc544\ub798\uc640 \uac19\uc740 \ucffc\ub9ac\uac00 \ud45c\uc2dc\ub41c\ub2e4.<\/p>\n\n\n\n
                <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
                -- =======================================================================================<\/span><\/span>\n-- Create User as DBO template for Azure SQL Database and Azure Synapse Analytics Database<\/span><\/span>\n-- =======================================================================================<\/span><\/span>\n-- For login login_name, create a user in the database<\/span><\/span>\nCREATE<\/span> USER <user_name, <\/span>sysname<\/span>, user_name><\/span><\/span>\n\t<\/span>FOR<\/span> <\/span>LOGIN<\/span> <login_name, <\/span>sysname<\/span>, login_name><\/span><\/span>\n\t<\/span>WITH<\/span> <\/span>DEFAULT_SCHEMA<\/span> = <<\/span>default_schema<\/span>, <\/span>sysname<\/span>, dbo><\/span><\/span>\nGO<\/span><\/span>\n<\/span>\n-- =======================================================================================<\/span><\/span>\n-- Create Microsoft Entra User for Azure SQL Database and Azure Synapse Analytics Database<\/span><\/span>\n-- =======================================================================================<\/span><\/span>\n-- For login <login_name, sysname, login_name>, create a user in the database<\/span><\/span>\n-- CREATE USER <Microsoft_Entra_User, sysname, user_name><\/span><\/span>\n--    [   { FOR | FROM } LOGIN <Microsoft_Entra_Principal_Login, sysname, login_name>  ]  <\/span><\/span>\n--    | FROM EXTERNAL PROVIDER<\/span><\/span>\n--    [ WITH DEFAULT_SCHEMA = <default_schema, sysname, dbo> ]<\/span><\/span>\n-- GO<\/span><\/span>\n<\/span>\n<\/span>\n-- Add user to the database owner role<\/span><\/span>\nEXEC<\/span> sp_addrolemember <\/span>N'db_owner'<\/span>, <\/span>N'<user_name, sysname, user_name>'<\/span><\/span>\nGO<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
                <\/div>\n\n\n\n
                User \uc0dd\uc131 \ucffc\ub9ac\ub97c \uc2e4\ud589<\/h3>\n\n\n\n
                <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
                CREATE<\/span> USER [Resource name] <\/span>FROM<\/span> <\/span>EXTERNAL<\/span> <\/span>PROVIDER<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
                <\/div>\n\n\n\n
                \"\"<\/figure>\n\n\n\n
                <\/div>\n\n\n\n
                User \uc5ed\ud560 \ud560\ub2f9 \ucffc\ub9ac\ub97c \uc2e4\ud589<\/h3>\n\n\n\n
                <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
                ALTER<\/span> <\/span>ROLE<\/span> db_datareader <\/span>ADD<\/span> MEMBER [User name]<\/span><\/span>\nALTER<\/span> <\/span>ROLE<\/span> db_datawriter <\/span>ADD<\/span> MEMBER [User name]<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
                <\/div>\n\n\n\n
                \"\"<\/figure>\n\n\n\n
                <\/div>\n\n\n\n
                DB \uc218\uc900\uc758 \uad8c\ud55c \uc870\ud68c<\/h3>\n\n\n\n
                <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
                -- \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc218\uc900\uc758 \uad8c\ud55c \uc870\ud68c<\/span><\/span>\nSELECT<\/span> <\/span><\/span>\n    dp.name <\/span>AS<\/span> UserName,<\/span><\/span>\n    dp.type_desc <\/span>AS<\/span> UserType,<\/span><\/span>\n    perm.permission_name <\/span>AS<\/span> PermissionType,<\/span><\/span>\n    perm.state_desc <\/span>AS<\/span> PermissionState<\/span><\/span>\nFROM<\/span> <\/span><\/span>\n    sys.database_permissions <\/span>AS<\/span> perm<\/span><\/span>\nINNER JOIN<\/span> <\/span><\/span>\n    sys.database_principals <\/span>AS<\/span> dp <\/span>ON<\/span> perm.grantee_principal_id = dp.principal_id<\/span><\/span>\nWHERE<\/span> <\/span><\/span>\n    dp.name = <\/span>'User name'<\/span>;  <\/span>-- \uc720\uc800 \uc774\ub984\uc73c\ub85c \ubcc0\uacbd<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
                <\/div>\n\n\n\n
                \"\"<\/figure>\n\n\n\n
                <\/div>\n\n\n\n
                DB \uc5ed\ud560 \uba64\ubc84\uc27d \uc870\ud68c<\/h3>\n\n\n\n
                <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
                -- \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc5ed\ud560 \uba64\ubc84\uc2ed \uc870\ud68c<\/span><\/span>\nSELECT<\/span> <\/span><\/span>\n    dp.name <\/span>AS<\/span> UserName,<\/span><\/span>\n    dp.type_desc <\/span>AS<\/span> UserType,<\/span><\/span>\n    dr.name <\/span>AS<\/span> RoleName<\/span><\/span>\nFROM<\/span> <\/span><\/span>\n    sys.database_role_members <\/span>AS<\/span> drm<\/span><\/span>\nINNER JOIN<\/span> <\/span><\/span>\n    sys.database_principals <\/span>AS<\/span> dr <\/span>ON<\/span> drm.role_principal_id = dr.principal_id<\/span><\/span>\nINNER JOIN<\/span> <\/span><\/span>\n    sys.database_principals <\/span>AS<\/span> dp <\/span>ON<\/span> drm.member_principal_id = dp.principal_id<\/span><\/span>\nWHERE<\/span> <\/span><\/span>\n    dp.name = <\/span>'User name'<\/span>;  <\/span>-- \uc720\uc800 \uc774\ub984\uc73c\ub85c \ubcc0\uacbd<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
                <\/div>\n\n\n\n
                \"\"<\/figure>\n","protected":false},"excerpt":{"rendered":"
                Agenda SQL Server \uc5d0 \ub85c\uadf8\uc778 Microsoft Entra \uc778\uc99d\ub9cc \ud5c8\uc6a9 SQL Server \uc5d0 \uc811\uc18d\ud560 \ub54c Entra \uc778\uc99d\ub9cc\uc744 \ud5c8\uc6a9\ud55c\ub2e4. \uc774 \uc124\uc815\uc744 \uc801\uc6a9\ud55c \uacbd\uc6b0, SQL Server admin \uacc4\uc815\uc73c\ub85c\ub3c4 \uc811\uc18d\uc774 \ubd88\uac00\ub2a5\ud558\ub2e4. SQL Server \uc5d0 …<\/p>\n","protected":false},"author":1,"featured_media":6318,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[205,107,200,33],"tags":[123,179],"class_list":["post-6255","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-access-security","category-azure","category-data-storage-solutions","category-tech-notes","tag-azure","tag-sql-database"],"_links":{"self":[{"href":"https:\/\/engineer-chanpu.blog\/index.php?rest_route=\/wp\/v2\/posts\/6255","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/engineer-chanpu.blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/engineer-chanpu.blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/engineer-chanpu.blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/engineer-chanpu.blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6255"}],"version-history":[{"count":16,"href":"https:\/\/engineer-chanpu.blog\/index.php?rest_route=\/wp\/v2\/posts\/6255\/revisions"}],"predecessor-version":[{"id":6306,"href":"https:\/\/engineer-chanpu.blog\/index.php?rest_route=\/wp\/v2\/posts\/6255\/revisions\/6306"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/engineer-chanpu.blog\/index.php?rest_route=\/wp\/v2\/media\/6318"}],"wp:attachment":[{"href":"https:\/\/engineer-chanpu.blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6255"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/engineer-chanpu.blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6255"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/engineer-chanpu.blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6255"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}