Skip to content

Latest commit

 

History

History
313 lines (218 loc) · 20.3 KB

sql-database-control-access-aad-authentication-get-started.md

File metadata and controls

313 lines (218 loc) · 20.3 KB
title description keywords services documentationcenter author manager editor ms.assetid ms.service ms.custom ms.workload ms.tgt_pltfrm ms.devlang ms.topic ms.date ms.author
AAD auth: Azure SQL Database firewalls, authentication, access | Microsoft Docs
In this getting-started tutorial, you learn how to use SQL Server Management Studio and Transact-SQL to work with server-level and database-level firewall rules, Azure Active Directory authentication, logins, users, and roles to grant access and control to Azure SQL Database servers and databases.
sql-database
CarlRabeler
jhubbard
67797b09-f5c3-4ec2-8494-fe18883edf7f
sql-database
authentication and authorization
data-management
na
na
hero-article
01/17/2017
carlrab

SQL Database tutorial: Azure AD authentication access, and database-level firewall rules

In this getting-started tutorial, you learn how to use SQL Server Management Studio to work with Azure Active Directory authentication, logins, users, and database roles that grant access and permissions to Azure SQL Database servers and databases. You learn to:

  • View user permissions in the master database and in user databases
  • Create logins and users based on Azure Active Directory authentication
  • Grant server-wide and database-specific permissions to users
  • Log in to a user database as a non-admin user
  • Create database-level firewall rules for database users
  • Create server-level firewall rules for server admins

Time estimate: This tutorial takes approximately 45 minutes to complete (assuming you have already met the prerequisites).

Prerequisites

Note

This tutorial helps you to learn the content of these learn topics: SQL Database access and control, Logins, users, and database roles, Principals, Database roles, SQL Database firewall rules, and Azure Active Directory authentication.

Sign in to the Azure portal using your Azure account

Using your existing subscription, follow these steps to connect to the Azure portal.

  1. Open your browser of choice and connect to the Azure portal.

  2. Sign in to the Azure portal.

  3. On the Sign in page, provide the credentials for your subscription.

    Sign in

Provision an Azure Active Directory admin for your SQL logical server

In this section of the tutorial, you view information about the security configuration for your logical server in the Azure portal.

  1. Open the SQL Server blade for your logical server and view the information in the Overview page. Notice that an Azure Active Directory admin has not been configured.

    Server admin account in the Azure portal

  2. Click Not configured in the Essentials pane to open the Active Directory admin blade.

    AAD blade

  3. Click Set admin to open the Add admin blade and then select an Active Directory user or group account as the Active Directory admin for your server.

    Select AAD admin account

  4. Click Select and then click Save.

    Save selected AAD admin account

Note

To review connection information for this server, go to View or update server settings. For this tutorial series, the fully qualified server name is 'sqldbtutorialserver.database.windows.net'.

Connect to SQL server using SQL Server Management Studio (SSMS)

  1. If you have not already done so, download and install the latest version of SSMS at Download SQL Server Management Studio. To stay up-to-date, the latest version of SSMS prompts you when there is a new version available to download.

  2. After installing, type Microsoft SQL Server Management Studio in the Windows search box and click Enter to open SSMS.

    SQL Server Management Studio

  3. In the Connect to Server dialog box, select one of the Active Directory authentication methods and then provide the appropriate authentication information. For information on choosing a method, see Azure Active Directory authentication and SSMS support for Azure AD MFA.

    connect to server with aad

  4. Enter the necessary information to connect to your SQL server using SQL Server Authentication and the Server admin account.

  5. Click Connect.

    connected to server with aad

View the Server admin account and its permissions

In this section of the tutorial, you view information about the server admin account and its permissions in the master database and in user databases.

  1. In Object Explorer, expand Databases, expand System databases, expand master, expand Security, and then expand Users. Notice that a user account has been created in the master database for the Active Directory admin. Notice also that a login was not created for Active Directory admin user account.

    master database user account for AAD admin

    [!NOTE] For information about the other user accounts that appear, see Principals.

  2. In Object Explorer, right-click master and then click New Query to open a query window connected to the master database.

  3. In the query window, execute the following query to return information about the user executing the query. Notice that [email protected] is returned for the user account executing this query (we see a different result when we query a user database later in this procedure).

    SELECT USER;

    select user query in the master database

  4. In the query window, execute the following query to return information about the permissions of the Active Directory admin user. Notice that the Active Directory admin user has permissions to connect to the master database, create logins and users, select information from the sys.sql_logins table, and add users to the dbmanager and dbcreator database roles. These permissions are in addition to permissions granted to the public role from which all users inherit permissions (such as permissions to select information from certain tables). See Permissions for more information.

    SELECT prm.permission_name
   , prm.class_desc
   , prm.state_desc
   , p2.name as 'Database role'
   , p3.name as 'Additional database role' 
FROM sys.database_principals p
JOIN sys.database_permissions prm
   ON p.principal_id = prm.grantee_principal_id
   LEFT JOIN sys.database_principals p2
   ON prm.major_id = p2.principal_id
   LEFT JOIN sys.database_role_members r
   ON p.principal_id = r.member_principal_id
   LEFT JOIN sys.database_principals p3
   ON r.role_principal_id = p3.principal_id
WHERE p.name = '[email protected]';

    aad admin permissions in the master database

  5. In Object Explorer, expand blankdb, expand Security, and then expand Users. Notice that there is no user account called [email protected] in this database.

    user accounts in blankdb

  6. In Object Explorer, right-click blankdb and then click New Query.

  7. In the query window, execute the following query to return information about the user executing the query. Notice that dbo is returned for the user account executing this query (by default, the Server admin login is mapped to the dbo user account in each user database).

    SELECT USER;

    select user query in the blankdb database

  8. In the query window, execute the following query to return information about the permissions of the dbo user. Notice that dbo is a member of the public role and also a member of the db_owner fixed database role. See Database-Level Roles for more information.

    SELECT prm.permission_name
   , prm.class_desc
   , prm.state_desc
   , p2.name as 'Database role'
   , p3.name as 'Additional database role' 
FROM sys.database_principals AS p
JOIN sys.database_permissions AS prm
   ON p.principal_id = prm.grantee_principal_id
   LEFT JOIN sys.database_principals AS p2
   ON prm.major_id = p2.principal_id
   LEFT JOIN sys.database_role_members r
   ON p.principal_id = r.member_principal_id
   LEFT JOIN sys.database_principals AS p3
   ON r.role_principal_id = p3.principal_id
WHERE p.name = 'dbo';

    server admin permissions in the blankdb database

  9. Optionally, repeat the previous three steps for the AdventureWorksLT user database.

Create a new user in the AdventureWorksLT database with SELECT permissions

In this section of the tutorial, you create a user account in the AdventureWorksLT database based on a user's principal name of an Azure AD user or display name for an Azure AD group, test this user's permissions as member of the public role, grant this user SELECT permissions, and then test this user's permissions again.

Note

Database-level users (contained users) increase the portability of your database, a capability that we explore in later tutorials.

  1. In Object Explorer, right-click AdventureWorksLT and then click New Query to open a query window connected to the AdventureWorksLT database.

  2. Execute the following statement to create a user account in the AdventureWorksLT database for a user in the Microsoft domain called aaduser1.

    CREATE USER [[email protected]]
FROM EXTERNAL PROVIDER;

    new user aaduser1@microsoft.com AdventureWorksLT

  3. In the query window, execute the following query to return information about the permissions of user1. Notice that the only permissions that user1 has are the permissions inherited from the public role.

    SELECT prm.permission_name
   , prm.class_desc
   , prm.state_desc
   , p2.name as 'Database role'
   , p3.name as 'Additional database role' 
FROM sys.database_principals AS p
JOIN sys.database_permissions AS prm
   ON p.principal_id = prm.grantee_principal_id
   LEFT JOIN sys.database_principals AS p2
   ON prm.major_id = p2.principal_id
   LEFT JOIN sys.database_role_members r
   ON p.principal_id = r.member_principal_id
   LEFT JOIN sys.database_principals AS p3
   ON r.role_principal_id = p3.principal_id
WHERE p.name = '[email protected]';

    new user permissions in a user database

  4. Execute the following queries to attempt to query a table in the AdventureWorksLT database as user1.

    EXECUTE AS USER = '[email protected]';  
SELECT * FROM [SalesLT].[ProductCategory];
REVERT;

    no select permissions

  5. Execute the following statement to grant SELECT permissions on the ProductCategory table in the SalesLT schema to user1.

    GRANT SELECT ON OBJECT::[SalesLT].[ProductCategory] to [[email protected]];

    grant select permissions

  6. Execute the following queries to attempt to query a table in the AdventureWorksLT database as user1.

    EXECUTE AS USER = '[email protected]';  
SELECT * FROM [SalesLT].[ProductCategory];
REVERT;

    select permissions

Create a database-level firewall rule for AdventureWorksLT database users

Note

You do not need to complete this procedure if you completed the equivalent procedure in the related tutorial for SQL Server authtentication, SQL Database tutorial: SQL authentication, logins and user accounts, database roles, permissions, server-level firewall rules, and database-level firewall rules and are learning using the same computer with the same IP address.

In this section of the tutorial, you attempt to log in using the new user account from a computer with a different IP address, create a database-level firewall rule as the Server admin, and then successfully log in using this new database-level firewall rule.

Note

Database-level firewall rules increase the portability of your database, a capability that we explore in later tutorials.

  1. On another computer for which you have not already created a server-level firewall rule, open SQL Server Management Studio.

    [!IMPORTANT] Always use the latest version of SSMS at Download SQL Server Management Studio.

  2. In the Connect to Server window, enter the server name and authentication information to connect using SQL Server authentication with the [email protected] account.

    Connect as aaduser1@microsoft.com without firewall rule1

  3. Click Options to specify the database to which you want to connect and then type AdventureWorksLT in the Connect to Database drop-down box on the Connection Properties tab.

    Connect as aaduser1 without firewall rule2

  4. Click Connect. A dialog box appears informing you that the computer from which you are attempting to connect to SQL Database does not have a firewall rule enabling access to the database. The dialog box that you receive has two variations depending upon steps you have previously taken with firewalls, but you usually get the first dialog box shown.

    Connect as user1 without firewall rule3

    Connect as user1 without firewall rule4

    [!NOTE] The newest versions of SSMS include the functionality to allow subscription owners and contributors to sign in to Microsoft Azure and create a server-level firewall rule.

  5. Copy the client IP address from this dialog box for use in step 7.

  6. Click Cancel but do not close the Connect to Server dialog box.

  7. Switch back to a computer for which you have already created a server-level firewall rule and connect to your server using the Server admin account.

  8. In a new query window connected to the AdventureWorksLT database as Server admin, execute the following statement to create a database-level firewall by executing sp_set_database_firewall_rule using the IP address from step 4:

    EXEC sp_set_database_firewall_rule @name = N'AdventureWorksLTFirewallRule', 
  @start_ip_address = 'x.x.x.x', @end_ip_address = 'x.x.x.x';

    add database level firewall rule4

  9. Switch computers again and click Connect in the Connect to Server dialog box to connect to AdventureWorksLT as aaduser1.

  10. In Object Explorer, expand Databases, expand AdventureWorksLT, and then expand Tables. Notice that user1 only has permission to view a single table, the SalesLT.ProductCategory table.

  11. In Object Explorer, right-click SalesLT.ProductCategory and click Select Top 1000 Rows.

Next steps