I was trying to connect with SQL Server database which is residing in Azure Windows Virtual Machine from SQL Server Management Studio and .NET application, I got error and couldn’t connect to database. Later on, I found solution for it and able to connect from both application and SQL Server Management Studio. So, here in this blog I will explain those issues and share my experience how we can solve these issues. I hope, it will help for others if they face similar problem.
Issue 1: When I tried to connect to Azure VM SQL Server from SQL server Management studio I got following error and couldn’t connect to database.
Solution: Open SSMS and Click on database connect option and provide server credentials. Then click on Options as depicted below.
Below screen will be appeared. 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 to connect to the server.
Issue 2: I was not able to connect to Database from Application even though I provided proper server details and parameters in connection string. I got 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 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 following part in the connection string.
Check whether there is TrustServerCertificate available or not in your Webconfig. If not add this and make it True. If it is available and value is False then make it True.
WebConfig file connection string should be like such as:
<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 to resolve problem, if you face exactly same issue as mine.