Publish a .Net Core Web API with Entity Framework Core to Azure (App Service) - Express Guide

Azure
- SQL Database (https://azure.microsoft.com/en-us/pricing/details/sql-database/single/) - ~$4.8971/month

1. Create SQL Server database instance
Azure provides different options for SQL Databases:
Databases offer the following deployment options:

As a single database
-With its own set of resources managed via a database server. A single database is similar to a contained database in SQL Server. This option is optimized for modern application development of new cloud-born applications. Hyperscale and serverless options are available.
An elastic pool
-Is a collection of databases with a shared set of resources managed via a database server. Single databases can be moved into and out of an elastic pool. This option is optimized for modern application development of new cloud-born applications using the multi-tenant SaaS application pattern. Elastic pools provide a cost-effective solution for managing the performance of multiple databases that have variable usage patterns.
A database server
-Is used to manage groups of single databases and elastic pools. Database servers act as a central administrative point for multiple single or pooled databases, logins, firewall rules, auditing rules, threat detection policies, and failover groups.

Read more (Source from): https://docs.microsoft.com/en-us/azure/sql-database/sql-database-paas-vs-sql-server-iaas

1. We are going to create a SQL Database. So search for SQL Databases and select 'SQL databases' from the drop down.
2. Click on Add
3. Select your subscription and Resource Group (Select the group that you create for the Web App, because this database will use by that web api)
4. Provide the database name which used in the connection string of your application as Database name.
5. Create a Server.
- Provide server name, server admin username and password
- Select the same region which you have select for the Web App as the SQL Server Location
6 Then, click 'Ok' to create new SQL Server
7. Select No for 'Want to use SQL elastic pool'.
8. Select the Compute + Storage
- Click on 'Configure database' (We are going to select a basic plan for our SQL Database instance)
- This will open up 'Configure' window.
- Click on 'Looking for base, standard, premium?' link
- Select Basic DTU (Database Transaction Units) and select the max size that you need for a month.
- Then click on Apply. This will redirect you to the Create SQL Database window that we were before.
9. Click on Review + create.
NOTE: Click on 'Download a template for automation' if you think it will be useful.
10. If everything looks ok click on Create.
- This will start creating SQL Database and this will take some time to complete.

2. Connect to Azure Database with SQL Server Management Studio
1. Go to your Azure portal
2. Go to your created SQL Database
3. Copy the Server name from the Overview section
4. Open SQL Server Management Studio (If you haven't intalled it yet download and install it from (https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15#download-ssms)
5. Click on Connect Object Explorer
6. Give Server name as your Azure Server Name
7. Select Authentication as SQL Server Authentication
- Give the SQL Server username that you gave for SQL Server in Azure as Login.
- Give the SQL Server password that you gave for SQL Server in Azure as Password.
8. Click on Connect
- This will ask you to create new firewall rule. So create that by selecting 'Add my client IP'.
9. Click Ok
- Then your Azure SQL Server will get connect to your SQL Server Management Studio

3. Run Migration scripts on Azure SQL Database
1. Open your solution in Visual Studio
2. Go to the appsettings.json (where your connection string is)
3. Comment out the local connection string and add another connection string with the same name for Azure
- Get the connection string from the Value field of the Connection string configuration in Azure (inside App Service)
- The godal is we are enabling one connection string at each time by commenting and uncommenting
- for example:
  "ConnectionString": {
    //Local
    //"MyDb": "{My-local-connection-string}"

    //Azure
    "MyDb": "Server={My-Azure-connection-string}"
  },
4. Enable the Azure connection string and save
5. Open NuGet package manager console
- Select the project which you have configured the Entity Framework as Default Project in Package Manager Console
6. Run Update-Database
7. Open SQL Server Management Studio and conenct to your Azure SQL Server
- Open your database to see the changes have been performed by the migration.
NOTE: When you done with the SQL migrations set back your local connection string for the developments.
NOTE: If you are using public repository to manage your source. Make sure to remove the Azure connection string because it will reveal your private credentials to public.

4. Setup Connection String in Azure
When working with Azure we are not keeping our production (Azure) connection strings in appsettings.json file.
We manage these production connection strings in Azure under your App Service.
To setup the connection string for the above created SQL Database in SQL Server in Azure , follow the bellow steps:
1. Open Azure portal
2. Go to the SQL Database you created
3. Within the Overview section, click on 'Show database connection strings'
- Copy it and close
4. Go to your App Service
5. Select Configuration under Settings section
6. Under Connection strings, click on New connection string
7. Fill the Add/Edit connection string window
- Give your connection string name in your .Net Core Web APIs' appsettings.json
- Give the coppied connection string from Azure SQL database as Value and change the Password value within the connection string
- Select SQLServer as Type
- Click Ok
8. Click on Save
- This will update web app settings

5. Test your API with Postman

Resolving 'Cannot open server '{Your-Server}' requested by the login.' error
When you try to test the app with Postman sometimes you might get the following error.

"Cannot open server '{Your-Server}' requested by the login. Client with IP address '{address}' is not allowed to access the server.  To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range.  It may take up to five minutes for this change to take effect."

NOTE: If you haven't return any errors which throw by exception blocks you will not be able to see this in Postman. So change your logic according to that.

The cause is SQL Sever that we setup in Azure have a firewall and it blocks the internal SQL Access from the hosted Web API in App Service.
To solve this issue follow the bellow steps:
1. Go Azure portal
2. Open the SQL Server
3. Open 'Firewalls and virtual networks' under Security
4. Add a firewall rule
- Add Rule name as you prefered
- Add Start IP
- Add End IP
NOTE: If you don't have an IP range you can use the same IP address for both Start and End IP
5. Click on Save
NOTE: These firewall rules will apply for your entire SQL Server not only for a specific database.

These IP addresses called Outbound IP Addresses
To view all the Outbound IP addresses which are used by your App Service;
1. Open your App Service (Which your API being hosted on)
2. Go to Properties

Learn more about Inbound and Outbound IP Addresses: https://docs.microsoft.com/en-us/azure/app-service/overview-inbound-outbound-ips#code-try-1