Certificate Issue While Connecting Azure Virtual Machine Database

This post discusses a certificate issue while connecting to Azure Virtual Machine Database. It looks at the problems and how to resolve them.

Introduction 

 

I was trying to connect to a SQL Server database, which resides in Azure Windows Virtual Machine from SQL Server Management Studio and a .NET application. I got an error and couldn’t connect to the database. Later on, I found a solution for it and was able to connect from both the application and SQL Server Management Studio. So, here in this blog I will explain those issues and share my experience on how we can solve these issues. I hope it will help others if they face a similar problem.

 

Issue 1 - When I tried to connect to Azure VM SQL Server from SQL Server Management Studio, I got the following error and couldn’t connect to the database.

 

 

Solution

 

Open SSMS and Click on the database connect option and provide server credentials. Then click on Options, as depicted below.

 

 

The below screen will appear. Select Trust Certificate and then Click on Connect option.

 

 

 

After that, I was able to connect to Azure VM Database from SQL Server Management Studio. We have to mark the Trust server certificate options then only it allows us to connect to the server.

 

Issue 2 - I was not able to connect to the Database from the Application even though I provided proper server details and parameters in the connection string. I got the following error.

 

“A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)”

 

Solution

 

We must change the connection string to connect to SQL Server Database, which is created in Azure Virtual Machine

 

When we connect to the Azure VM Database, we need to add the following part in the connection string.

 

“TrustServerCertificate=True”

 

Check whether or not there is a TrustServerCertificate available in your Webconfig. If not, then add this and make it True. If it is available and the value is False, then make it True.

 

The WebConfig file connection string should be as shown below:

<add key="ConnectionString" value="Server=serverName.southeastasia.cloudapp.azure.com;Initial Catalog=DatabaseName;Persist Security Info=False;User ID=UserId;Password=YourDBPassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=true;Connection Timeout=300000;" />

I hope this will help you resolve your problem if you face exactly the same issue as mine.