If you are trying to connect to a SQL Server database which resides in Azure Windows Virtual Machine from SQL Server Management Studio and a .NET application .
We will explain those issues and share our experience on how you can solve these issues.
Issue 1 : When you tried to connect to Azure VM SQL Server from SQL Server Management Studio and If you get the following error and couldn’t connect to the database.
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, You will be able to connect to Azure VM Database from SQL Server Management Studio. You have to mark the Trust server certificate options then only it allows you to connect to the server.
Issue 2- You were not able to connect to the Database from the Application even though you provided proper server details and parameters in the connection string.
“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.)”
When you trying to connect to the Azure VM Database, you need to add the following part in the connection string.
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.xxx.cloudapp.azure.com;Initial Catalog=DatabaseName;Persist Security Info=False;User ID=UserId;Password=YourDBPassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=true;Connection Timeout=300000;” />