Skip to main content

Configure Azure SQL Database User Access

Hands-On Lab

 

Photo of James Lee

James Lee

Training Architect

Length

01:30:00

Difficulty

Intermediate

Azure SQL allows for Azure Active Directory integration. What does that mean for us? It means having a centralized identity provider for SQL database and server access. No more localized SQL logins! In this hands-on lab, we integrate a pre-deployed SQL server with Azure AD. We use the Azure Portal to assign an Active Directory admin to the server. We then use SQL Server Data Tools in Visual Studio to assign a test user to the database. Finally, we log into the database using the test user and run a sample query.

What are Hands-On Labs?

Hands-On Labs are scenario-based learning environments where learners can practice without consequences. Don't compromise a system or waste money on expensive downloads. Practice real-world skills without the real-world risk, no assembly required.

Configure Azure SQL Database User Access

Introduction

Azure SQL allows for Azure Active Directory integration. What does that mean for us? It means having a centralized identity provider for SQL database and server access. No more localized SQL logins!

In this hands-on lab, we integrate a pre-deployed SQL server with Azure AD. We use the Azure Portal to assign an Active Directory admin to the server. We then use SQL Server Data Tools in Visual Studio to assign a test user to the database. Finally, we log into the database using the test user and run a sample query.

Connecting to the Lab

  1. Log in to the Azure Portal using the credentials provided on the lab instructions page.

Examine Resources in the Azure Portal

  1. Click All resource from the left-hand menu.

  2. Identify the lab extension. It is the five-character addition added to serveral of the resources. This will be referenced as LAB_EXTENSION throughout this lab. For example, the virtual machine should be vm-LAB_EXTENSION.

  3. Click pip-LAB_EXTENSION.

  4. Copy the IP address. This will be referenced as PUBLIC_IP_ADDRESS throughout this lab.

  5. Click All resource on the top menu bar.

  6. Click vm-LAB_EXTENSION.

  7. Click Connect.

  8. Click Download RDP File.

Log On to the Virtual Machine and Prepare Visual Studio

  1. Open a remote desktop program.

  2. Enter azureadmin for the Username and LA!2019!Lab1 for the Password. This step may vary depending on the remote desktop program being used.

  3. Click Continue. This step may vary depending on the remote desktop program being used.

  4. Once connecter to the machine, click the Visual Studio 2019 shortcut on the desktop.

  5. Click Sign in.

  6. Enter azureadmin for the account name.

  7. Click Next.

  8. Enter LA!2019!Lab1 for the password.

  9. Click Sign in.

  1. Click Start Visual Studio.

  2. If a dialog window appears indicating the license is stale, click Check for an updated license.

  3. Click Close.

  4. Click Continue without code.

Configure Firewall and Active Directory Admin for the SQL Server

  1. Return to the Azure Portal.

  2. Click All resources.

  3. Click sqls-LAB_EXTENSION, the SQL server.

  4. Click Firewalls and virtual networks under Security.

  5. Under RULE NAME enter "ClientIP".

  6. Under START IP, enter PUBLIC_IP_ADDRESS found in the first task.

  7. Under END IP, enter PUBLIC_IP_ADDRESS found in the first task.

  8. Click Save at the top of the window.

  9. Click OK in the pop-up window.

  1. Click Active Directory admin under Settings.

  2. Click Set admin at the top of the window.

  3. Paste the user account from the lab page into the text box.

  4. Click the user account that appears.

  5. Click Select.

  6. Click Save.

  7. Verify that the appropriate user account is listed as an Active Directory admin.

Grant SQL Database Access to a Test User

  1. Return to the remote desktop session into the virtual machine.

  2. Click View from the menu bar and select SQL Server Object Explorer.

  3. Click the button with a + to add a SQL Server in the SQL Server Object Explorer.

  4. Expand the Azure entry.

  5. Click master.

  6. Click the combo box next to Authentication and select Active Directory Interactive Authentication.

  7. In the User Name field, enter the user name from the lab page.

  8. Click Connect.

  9. Enter the password from the lab page.

  1. Click Sign in.

  2. Expand the following entries in the SQL Server Object Explorer: sqls-LAB_EXTENSION.database... > Databases > sqls-LAB_EXTENSION > Tables

  3. Click SalesLT.Customer.

  4. Click the New Query icon in the top bar of the SQL Server Object Explorer.

  5. Paste the following code into the SQL query window.

    CREATE USER [sql_test_user@azurelabs.linuxacademy.com] FROM EXTERNAL PROVIDER;
    EXEC sp_addrolemember [db_datareader], [sql_test_user@azurelabs.linuxacademy.com];
    1. Click the Execute Query button (arrow).

    2. In the SQL Server Object Explorer, expand the following entries: Security > Users and verify the sql_test_user entry exists.

Log On to SQL as the Test User and Execute a Query

  1. Right-click the root entry for sqls-LAB_EXTENSION.database... and select Disconnect.

  2. Click the button with a + to add a SQL Server in the SQL Server Object Explorer.

  3. Expand the Azure entry.

  4. Click sqldb-LAB_EXTENSION.

  5. Click the combo box next to Authentication and select Active Directory Interactive Authentication.

  6. In the User Name field, enter sql_test_user@azurelabs.linuxacademy.com

  7. Click Connect.

  8. Enter the LA!2019!Lab1 as the password.

  9. Click Sign in.

  1. Expand the following entries in the SQL Server Object Explorer: sqls-LAB_EXTENSION.database... > Databases > sqls-LAB_EXTENSION > Tables

  2. Click SalesLT.Customer.

  3. Click the New Query icon in the top bar of the SQL Server Object Explorer.

  4. Paste the following code into the SQL query window.

    SELECT * FROM SalesLT.Customer
    1. Click the Execute Query button (arrow).

Conclusion

Congratulations, you've completed this hands-on lab!