connect-appservice-to-sqldatabase

Connect Azure App Service to SQL Database

In my previous post I have described how to deploy an ASP.NET App to Azure App Service.

In this post I am going to show how to connect App Service to SQL Database in Azure. For  this we need do the following steps:

1. Create an ASP.NET  Web App
2. Create App Service in Azure
3. deploy Web App  to App Service
4. Create SQL Database in Azure
5. Connect App Service to SQL Database

Create ASP.NET Core Web App

Download  ProductMicorservice API, from Repository https://github.com/mehzan07/ProductMicroservice  or clone it using the Git command below:

git clone https://github.com/mehzan07/ProductMicorservice.git

cd ProductMicorservice

Open ProductMicorservice in Visual Studio 2022:

Now we have create our ASP.NET Core Web App.

Create App Service in Azure

In my previous post, I have described how to create App Service in Azure by publishing of Web App from Visual Studio (look to section: Create Azure App):

I have created an App Service with name: ProductMicroserviceAppService with Resource group: ProductMicroserviceRSG in location: North Europe from Visualo studio 2022. and it looks as following in Azure Portals:

After publishing with start of App Service with URL: https://productmicroserviceappservice.azurewebsites.net/ got error site not found.

After investigation I found that in  Startup.cs file and in the  Configure()  method, The Swagger UI configured only enabled in the development environment. and it was looked as following:

if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
app.UseSwagger();
app.UseSwaggerUI(c =>
{
 c.SwaggerEndpoint("/swagger/v1/swagger.json", "Product API V1");
c.RoutePrefix = string.Empty;
});
}

By this code the Swagger UI only starts in development environment (Visual Studio) but not in Production environment.

I have moved the Swagger configuration lines outside the if statement and that should allow it to load in Azure (i.e. outside of your IDE) and the code is  now as following:

if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
// Allow Swagger UI loads in production too (as Azure App Service)
app.UseSwagger();
app.UseSwaggerUI(c =>
{
c.SwaggerEndpoint("/swagger/v1/swagger.json", "Product API V1");
c.RoutePrefix = string.Empty;
});

After Redeploying the Web App to App Service the the browser is load Swagger UI  (URL: https://productmicroserviceappservice.azurewebsites.net/index.html)    as following:

Now we have deployed our Web App to App Server and we need to connect SQL Database to Get, Post, Delete products and that is because we need to create a SQL Database and connect our App Service to it.

Create the Database

We can create Database in two way: Via Visual Studio which shows under publish or via Azure Portal-

In this post I am going to create database via Portal

1. In Azure portal search bar at the top of the Azure portal, enter SQL. Select the item labeled SQL Servers from the search results.
2. On the SQL Servers page, select + Create.
3. fill out the form as follows.
   • Resource Group – choose the ProductMicroserviceRSG  group you created.
   • Server name – enter a globally unique name such as mehzanservice123
   • Location – select a region near you (North Europe) .
   • Authentication method – select Use SQL Authentication.
   • Server admin login – enter a username of your choice.
   • Password – enter a password you’ll remember
4. Press Review + Create button
5. Press to create button, takes a minute to  create press to the Go to Resources then you can see the SQLServer with name: mehzansqlserver and overview page is shown as follow:

6. 

7. After your SQL Server has been created, in the search bar at the top of the Azure portal, enter SQL. Select SQL Databases
8. On the SQL Databases page, select + Create.
9. On the Create Database page, fill out the form as follows.
   • Resource Group – choose the ProductMicroserviceRSG  group you created earlier.
   • Database name – enter a value of mehzandb.
   • Server – select the mehzansqlserver you created earlier.
   • Leave the rest of the settings at their default, and then select Review + create.
   • Select the Create button once Azure validates your settings. Provisioning the database may take a few minutes.

Press to Go to Resource then we have the following:

We have created SQL Server and SQL Database in this Server, and now it is time to connect this App Service to database.

Connect the App Service to Database

We will connect the our App Service (ProductMicroserviceAppService) to our database using a Connection String. You can use Service Connector to create the connection.

1. In the Azure portal, type the name of your app (ProductMicroserviceAppService) in the search box at the top of the screen.
2. In the search results, select this App Service to navigate to it.
3. In the left menu of App Service, select Service Connector.
4. Press to  +Create then:

On the Create connection page do the following steps:

1. select or enter the following settings:
   • Service Type: Select SQL Database.
   • SQL server: Enter your SQL Database server name (mehzansqlserver), you have created before.
   • SQL database: Select your databse name (mehzandb),  which you have created before.
2. Select Next: Authentication.
3. Under the Authentication tab:
   • Specify the username and password of your SQL database (you can take the SQL Server username and password you have  created as before)
   • Select Next: Networking, your see: Configure Firewals Rules, to Enable to Access  Target Service, then select Next: Review and wait the + Create be active then press to it.
4. Press to validation button to validate when validation is success then it is complete press to Refresh button you see the overview of Service connector as follow:

   

In the Service Connector page:

1. Expand the Service Connector >SQL Database, you can find ConnectionString.
2. Press to the Hidden value. Click to show value and copy the connection string value which you need in later step..

Now our App Service is connect to the SQL database in my case mehzandb. This Database name should be in the ConnectionString in appsettings.json.

This Database is empty, and in next step we should  generate schema for our data using Entity Framework Core.

Generate the Database Schema

To generate our database schema,  you need to set up a firewall rule on the SQL database server in Azure. This rule lets your local computer connect to Azure. For this step, you’ll need to know your local computer’s IP address. Azure will attempt to detect your IP automatically and presents the option to add it for you, as seen in the steps below.

1. In Azure portal, top search bar, search for your SQL Server( mehzansqlserver) which you have created earlier and select it from the results.
2. On the left navigation, select Networking.
3. On the Public access tab, select Add your client IPv4 address (xx.xx.xx.xx) to add a firewall rule that will allow your local computer to access the database.

4. Press to Save button at the down of the screen to persist your changes.

Next step is  to update the ConnectionString  value in appsettings.json file which was generated by the service connector (you have saved it in the previous step).   By this the localdb connection string value will be overridden by the connection string stored in Azure.

Open Visual Studio and in the appsettings.json. update the connection string as following code:

"ConnectionStrings": {
"ProductsDBConString": "Data Source=mehzansqlserver.database.windows.net,1433; Initial Catalog=mehzandb;User ID=MehzanAdmin;Password=*******"
}

As We see The ConnectionString name (“ProductsDBConString”) is the same for both local and Azure, only the value is changed. Because of this you needn’t update this in startup.cs file.

Updating Database in Azure via EF Core

In Visual Studio, start Package Manager console from the Tools menu and execute the following command:

PM> Add-Migration (give a param e.g InitialCreate)

This command Creates a Migration folder (if it is not created before) and two new files: datetime_InitialCreate and ProductContextModelSnapshot. The content of these files shows creating of your Database tables from the your Data modules, in my case: Category and Product.

The next command is:

PM> Update-Database 

These commands updates your database with the created tables in my case mehzandb with tables Category and Product

This update is based on ConnectionString value in appsettings.json file.

After the migration finishes, the correct schema is created.

Note: If you receive the error: Client with IP address xxx.xxx.xxx.xxx is not allowed to access the server, that means the IP address you entered into your Azure firewall rule is incorrect. To fix this issue, update the Azure firewall rule with the IP address provided in the error message.

Browse the Deployed Application

In the App Service overview page, select the Browse link at the top of page. If you refresh the page, you can your web app output.

In the browser you should access to the new database and execute: Get, Post, Put and Delete operation, as you have done in local Environment as shown in the bellow:

Clean up resources

In the preceding steps, you created Azure resources in a resource group. You can clean up by deleting of Resource group name as following steps:

1. From your App Service Overview page in the Azure portal, select Resouce Group(ProductMicroserviceRSG)  link.
2. Select Delete, type ProductMicroserviceRSG in the text box, and then select Delete.

Conclusion

In this post we have created a ASP.net Core Web App in Visual studio and then created an App Service in Azure, published our Web App to App Service, then after we Create SQL Database and connected our App Service to this Database. We have even generated Database Schema by updating of ConnectionString in appsetting.json and  generated Databse tables in our new Database in Azure.

My next post describes, Azure Functions

This post is part of Azure step by step 

Back to home page