{"id":1284,"date":"2022-05-09T11:56:49","date_gmt":"2022-05-09T18:56:49","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=1284"},"modified":"2022-05-09T12:03:27","modified_gmt":"2022-05-09T19:03:27","slug":"create-and-connect-to-an-azure-sql-db","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/create-and-connect-to-an-azure-sql-db\/","title":{"rendered":"Create and connect to an Azure SQL DB in 6 easy steps"},"content":{"rendered":"

\"Image<\/a><\/p>\n

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:<\/p>\n

    \n
  1. Create a resource group<\/li>\n
  2. Create an Azure SQL logical server<\/li>\n
  3. Create an Azure SQL database<\/li>\n
  4. Configure the firewall<\/li>\n
  5. Create a user<\/li>\n
  6. Get the connection string<\/li>\n<\/ol>\n

    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.<\/p>\n

    Open a browser and point to\u00a0https:\/\/shell.azure.com<\/a>\u00a0and let’s get started.<\/p>\n

    <\/a>Create a Resource Group<\/h2>\n

    A\u00a0resource group<\/a>\u00a0is 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\u00a0dev-demo<\/code>:<\/p>\n

    \n
    az group create -n<\/span> dev-demo -l<\/span> eastus\r\n<\/code><\/pre>\n
    \n
    <\/div>\n<\/div>\n<\/div>\n
    <\/a>Create an Azure SQL logical server<\/h2>\n
    An Azure SQL\u00a0logical server<\/a>\u00a0is 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\u00a0dev-demo-sql-srv<\/code>\u00a0and create an administrator. The administrator user should\u00a0NEVER<\/em>\u00a0be used to allow applications to connect to any database hosted in that server. It is for administrative tasks only.<\/p>\n
    \n
    az sql server create -g<\/span> dev-demo -n<\/span> dev-demo-sql-srv --admin-user<\/span> devdemoadmin --admin-password<\/span> SomeVery_STRONG_Passw0rd! \r\n<\/code><\/pre>\n
    \n
    <\/div>\n<\/div>\n<\/div>\n
    <\/a>Create an Azure SQL database<\/h2>\n
    Now that you have a server, you can create databases in it. The database will be named\u00a0db1<\/code>\u00a0and it will be using a\u00a0serverless<\/a>\u00a0(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):<\/p>\n
    \n
    az sql db create -g<\/span> dev-demo -n<\/span> db1 -s<\/span> dev-demo-sql-srv --service-objective<\/span> GP_S_Gen5_2\r\n<\/code><\/pre>\n
    \n
    <\/div>\n<\/div>\n<\/div>\n
    <\/a>Configure the firewall<\/h2>\n
    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:<\/p>\n
    \n
    az sql server firewall-rule create -g<\/span> dev-demo -s<\/span> dev-demo-sql-srv -n<\/span> AllAzureServices--start-ip-address 0.0.0.0 --end-ip-address<\/span> 0.0.0.0\r\n<\/code><\/pre>\n
    \n
    <\/div>\n<\/div>\n<\/div>\n
    Now you need to allow\u00a0your<\/em>\u00a0development 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\u00a0Azure Data Studio<\/a>\u00a0or\u00a0SQL Server Management Studio<\/a>\u00a0to 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\u00a0https:\/\/ipinfo.io\/<\/a>\u00a0and get your IP address, and then create a firewall rule using the reported IP for start and end address:<\/p>\n
    \n
    az sql server firewall-rule create -g<\/span> dev-demo -s<\/span> dev-demo-sql-srv -n<\/span> MyIP --start-ip-address<\/span> 12.34.56.78 --end-ip-address<\/span> 12.34.56.78\r\n<\/code><\/pre>\n
    \n
    <\/div>\n<\/div>\n<\/div>\n
    <\/a>Create a database user<\/h2>\n
    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\u00a0sqlcmd<\/code>\u00a0tool (as you notice you’ll be using the administrator login here):<\/p>\n
    \n
    sqlcmd -S<\/span> dev-demo-sql-srv.database.windows.net -d<\/span> db1 -U<\/span> devdemoadmin -P<\/span> SomeVery_STRONG_Passw0rd!\r\n<\/code><\/pre>\n
    \n
    <\/div>\n<\/div>\n<\/div>\n
    once you are logged in into the database – you’ll see a\u00a01><\/code>\u00a0prompt, you can create a user:<\/p>\n
    \n
    create<\/span> user<\/span> [<\/span>app<\/span>-<\/span>user<\/span>]<\/span> with<\/span> password<\/span> =<\/span> '4pplication_Passw0rd!'<\/span>;<\/span>\r\ngo<\/span>\r\n<\/code><\/pre>\n
    \n
    <\/div>\n<\/div>\n<\/div>\n
    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\u00a0db_reader<\/code>\u00a0and\/or\u00a0db_writer<\/code>\u00a0roles. If it also needs to create tables or objects, you may want to add it to the\u00a0db_owner<\/code>\u00a0role:<\/p>\n
    \n
    alter<\/span> role<\/span> [<\/span>db_owner<\/span>]<\/span> add<\/span> member<\/span> [<\/span>app<\/span>-<\/span>user<\/span>]<\/span>\r\ngo<\/span>\r\n<\/code><\/pre>\n
    \n
    <\/div>\n<\/div>\n<\/div>\n
    You can exit from the\u00a0sqlcmd<\/code>\u00a0prompt just by executing the\u00a0quit<\/code>\u00a0command.<\/p>\n
    Please note that users in the\u00a0db_owner group<\/a>\u00a0are 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:\u00a0An overview of Azure SQL Database and SQL Managed Instance security capabilities<\/a><\/p><\/blockquote>\n
    <\/a>Get the connection string<\/h2>\n
    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:<\/p>\n
    \n
    az sql db show-connection-string -s<\/span> dev-demo-sql-srv -c<\/span> ado.net\r\n<\/code><\/pre>\n
    \n
    <\/div>\n<\/div>\n<\/div>\n
    The command will print out the connection string. You just have to replace the values in the angular brackets, for example\u00a0<databasename><\/code> with your values. Make sure to use the application user you created in the step above, and you’ll be good to go!<\/p>\n
    <\/a>Want to learn more?<\/h2>\n
    If you want to learn what are the other ways (using the Portal, Powershell, or AZ CLI), here you can find a detailed article:\u00a0Quickstart: Create an Azure SQL Database single database<\/a><\/p>\n
    And if you are wondering why you should be looking at Azure SQL, this post is for you:\u00a010 reasons to use Azure SQL in your next project<\/a><\/p>\n
    \n
    Photo by Pixabay<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
    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: Create a resource group […]<\/p>\n","protected":false},"author":24720,"featured_media":1285,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[512,513],"class_list":["post-1284","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-sql","tag-connect","tag-tutorial"],"acf":[],"blog_post_summary":"
    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: Create a resource group […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/1284","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/24720"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=1284"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/1284\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/1285"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=1284"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=1284"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=1284"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}