Get started with Azure SQL Database servers, databases, and firewall rules by using the Azure portal and SQL Server Management Studio

7 min to read Contributors

In this getting-started tutorial, you learn how to use the Azure portal to:

  • Create a new Azure resource group
  • Create an Azure SQL logical server
  • View Azure SQL logical server properties
  • Create a server-level firewall rule
  • Create the Adventure Works LT sample database
  • View Adventure Works LT sample database properties in Azure

In this tutorial, you also use the most recent version of SQL Server Management Studio to:

  • Connect to the logical server and its master database
  • View master database properties
  • Connect to the sample database
  • View user database properties

When you finish this tutorial, you will have a sample database and a blank database running in an Azure resource group and attached to a logical server. You will also have a server-level firewall rule configured to enable the server-level principal to log in to the server from a specified IP address (or IP address range).

Time estimate: This tutorial will take you approximately 30 minutes (assuming you already meet the prerequisites).

Prerequisites

Tip

You can perform these same tasks in a getting started tutorial by using either C# or PowerShell.

Sign in by using your existing 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

Create a new logical SQL server in the Azure portal

  1. Click New, type sql server, and then click ENTER.

    logical sql server

  2. Click SQL server (logical server).

    create-logical sql server

  3. Click Create to open the new SQL Server (logical server) blade.

    new-logical sql server

  4. In the Server name text box, provide a valid name for the new logical server. A green check mark indicates that you have provided a valid name.

    new server name

    Important

    The fully qualified name for your new server will be .database.windows.net.

  5. In the Server admin login text box, provide a user name for the SQL authentication login for this server. This login is known as the server principal login. A green check mark indicates that you have provided a valid name.

    SQL admin login

  6. In the Password and Confirm password text boxes, provide a password for the server principal login account. A green check mark indicates that you have provided a valid password.

    SQL admin password

  7. Select a subscription in which you have permission to create objects.

    subscription

  8. In the Resource group text box, select Create new and then, in the resource group text box, provide a valid name for the new resource group (you can also use an existing resource group if you have already created one for yourself). A green check mark indicates that you have provided a valid name.

    new resource group

  9. In the Location text box, select a data center appropriate to your location - such as "Australia East".

    server location

    Tip

    The checkbox for Allow azure services to access server cannot be changed on this blade. You can change this setting on the server firewall blade. For more information, see Get started with security.

  10. Click Create.

    create button

View the logical SQL Server properties in the Azure portal

  1. In the Azure portal, click More services.

    more services

  2. In the Filter text box, type SQL and then click the star for SQL servers to specify SQL servers as a favorite within Azure.

    set favorite

  3. In the default blade, click SQL servers to open the list of SQL servers in your Azure subscription.

    new sql server

  4. Click your new SQL server to view its properties in the Azure portal. Subsequent tutorials help you understand the options available to you on this blade.

    sql server blade

  5. Under Settings, click Properties to view various properties of the logical SQL server.

    sql server properties

  6. Copy the fully qualified server name to your clipboard for use a bit later in this tutorial.

    sql server full name

Create a server-level firewall rule in the Azure portal

  1. On the SQL server blade, under Settings, click Firewall to open the Firewall blade for the SQL server.

    sql server firewall

  2. Review the client IP address displayed and validate that this is your IP address on the Internet using a browser of your choice (ask "what is my IP address). Occasionally they do not match for a various reasons.

    your IP address

  3. Assuming that the IP addresses match, click Add client IP on the toolbar.

    add client IP

    Note

    You can open the SQL Database firewall on the server to a single IP address or an entire range of addresses. Opening the firewall enables SQL administrators and users to login to any database on the server to which they have valid credentials.

  4. Click Save on the toolbar to save this server-level firewall rule and then click OK.

    add client IP

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, enter the necessary information to connect to your SQL server using SQL Server Authentication.

    connect to server

  4. Click Connect.

    connected to server

  5. In Object Explorer, expand Databases, expand System Databases, expand master to view objects in the master database.

    master database

  6. Right-click master and then click New Query.

    query master database

  7. In the query window, type the following query:

    select * from sys.objects

  8. On toolbar, click Execute to return a list of all system objects in the master database.

    query master database system objects

    Note

    To explore SQL security, see Get Started with SQL security

Create new database in the Azure portal using Adventure Works LT sample

  1. In Azure portal, click SQL databases in the default blade.

    sql databases

  2. On the SQL databases blade, click Add.

    add sql database

  3. On the SQL Database blade, review the information completed for you.

    sql database blade

  4. Provide a valid database name.

    sql database name

  5. Under Select source, click Sample and then underSelect sample, click AdventureWorksLT [V12].

    adventure works lt

  6. Under Server, provide the server admin login user name and password.

    server credentials

    Note

    When adding a database to a server, it can be added as a single database (this is the default) or to a SQL elastic pool. For more information on elastic pools, see Elastic pools.

  7. Under Pricing tier, change the pricing tier to Basic (you can increase the pricing tier later if desired, but for learning purposes, we recommend you use the lowest cost tier).

    pricing tier

  8. Click Create.

    create button

View database properties in the Azure portal

  1. On the SQL databases blade, click your new database to view its properties in the Azure portal. Subsequent tutorials help you understand the options available to you on this blade.

    new sample db blade

  2. Click Properties to view additional information about your database.

    new sample db properties

  3. Click Show database connection strings.

    new sample db connection strings

  4. Click Overview and then click your server name in the Essentials pane.

    new sample db essentials pane

  5. In the Essentials pane for your server, see your newly added database.

    new sample db in server essentials pane

Connect and query sample database using SQL Server Management Studio

  1. Switch to SQL Server Management Studio and, in Object Explorer, click Databases and then click Refresh on the toolbar to view the sample database.

    new sample db with ssms

  2. In Object Explorer, expand your new database to view its objects.

    new sample db objects with ssms

  3. Right-click your sample database and then click New Query.

    new sample db query with ssms

  4. In the query window, type the following query:

    select * from sys.objects

  5. On toolbar, click Execute to return a list of all system objects in the sample database.

    new sample db query system objects with ssms

Create a new blank database using SQL Server Management Studio

  1. In Object Explorer, right-click Databases and then click New database.

    new blank database with ssms

    Note

    You can also have SSMS create a create database script for you to create a new database using Transact-SQL.

  2. In the New Database dialog box, provide a database name in the Database name text box.

    new blank database name with ssms

  3. In the New Database dialog box, click Options and then change the Edition to Basic.

    new blank database options with ssms

    Tip

    Review the other options in this dialog box that you can modify for an Azure SQL Database. For more information on these options, see Create Database.

  4. Click OK to create the blank database.

  5. When complete, refresh the Database node in Object Explorer to view the newly created blank database.

    new blank database in object explorer

Tip

You can save some money while you are learning by deleting databases that you are not using. For Basic edition databases, you can restore them within seven days. However, do not delete a server. If you do so, you cannot recover the server or any of its deleted databases.

Next steps

Now that you've completed this tutorial, there are number of additional tutorials that you may wish to explore that build what you have learned in this tutorial.

Additional resources

What is SQL Database?