Create and connect to an Azure SQL DB in 6 easy steps

There are many ways in which an Azure SQL database can be created, and if you are new to the development all those different options can be quite intimidating. Let me show you, in just a few steps, how easy it is instead. This is what we are going to do:

1. Create a resource group
2. Create an Azure SQL logical server
3. Create an Azure SQL database
4. Configure the firewall
5. Create a user
6. Get the connection string

I'll be using the Azure Shell portal, as it provides a full experience without the need to install anything on your machine. The only thing you must have ready, is an Azure subscription.

Open a browser and point to https://shell.azure.com and let's get started.

Create a Resource Group

A resource group is a container of Azure resources. It is needed as it simplifies quite a lot the management of those resources. Let's create one in the East US region, and name it dev-demo:

az group create -n dev-demo -l eastus

Create an Azure SQL logical server

An Azure SQL logical server is needed to make it easier to manage many databases. A logical server administrator can automatically access any database in the server. Let's create a server named dev-demo-sql-srv and create an administrator. The administrator user should NEVER be used to allow applications to connect to any database hosted in that server. It is for administrative tasks only.

az sql server create -g dev-demo -n dev-demo-sql-srv --admin-user devdemoadmin --admin-password SomeVery_STRONG_Passw0rd! 

Create an Azure SQL database

Now that you have a server, you can create databases in it. The database will be named db1 and it will be using a serverless (I know...it's fun. We had to create a server before...) model, so that you'll pay for it only when you use it (auto pause is set by default after 1 hour of no activity):

az sql db create -g dev-demo -n db1 -s dev-demo-sql-srv --service-objective GP_S_Gen5_2

Configure the firewall

If you plan to use the created database with some other Azure services (like, for example, Azure Functions or Web Apps or Containers) and you don't have extremely high security policies, you can allow Azure services to connect to your database by creating this firewall rule:

az sql server firewall-rule create -g dev-demo -s dev-demo-sql-srv -n AllAzureServices--start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0

Now you need to allow your development machine to connect to Azure SQL. Since you're using the cloud shell, this is really not needed for now, but I'm pretty sure at some point you'll want to use Azure Data Studio or SQL Server Management Studio to connect and manage the database from your machine, and so you need to make sure the firewall will allow such connection. Open a browser and go to https://ipinfo.io/ and get your IP address, and then create a firewall rule using the reported IP for start and end address:

az sql server firewall-rule create -g dev-demo -s dev-demo-sql-srv -n MyIP --start-ip-address 12.34.56.78 --end-ip-address 12.34.56.78

Create a database user

You learned before that the administrative account should never be used to allow applications we create or work with to connect to the database, so we need to create a dedicated user. It's easy. You need to connect to Azure SQL database using the sqlcmd tool (as you notice you'll be using the administrator login here):

sqlcmd -S dev-demo-sql-srv.database.windows.net -d db1 -U devdemoadmin -P SomeVery_STRONG_Passw0rd!

once you are logged in into the database - you'll see a 1> prompt, you can create a user:

create user [app-user] with password = '4pplication_Passw0rd!';
go

Once the user has been created, you need to give it enough permission to work with the data in the database. If it just needs to read and write from tables, you can assign it the db_reader and/or db_writer roles. If it also needs to create tables or objects, you may want to add it to the db_owner role:

alter role [db_owner] add member [app-user]
go

You can exit from the sqlcmd prompt just by executing the quit command.

Please note that users in the db_owner group are basically local administrator so they can do pretty much everything on the database. Security is a complex - but extremely important - topic, make sure you check the basics out here: An overview of Azure SQL Database and SQL Managed Instance security capabilities

Get the connection string

To get the connection string that you need to use in your application to connect to Azure SQL, you can just use the following AZ command, where you can also specify for which language or library you want to connection string:

az sql db show-connection-string -s dev-demo-sql-srv -c ado.net

The command will print out the connection string. You just have to replace the values in the angular brackets, for example <databasename> with your values. Make sure to use the application user you created in the step above, and you'll be good to go!

Want to learn more?

If you want to learn what are the other ways (using the Portal, Powershell, or AZ CLI), here you can find a detailed article: Quickstart: Create an Azure SQL Database single database

And if you are wondering why you should be looking at Azure SQL, this post is for you: 10 reasons to use Azure SQL in your next project

Photo by Pixabay