I’m going to start quoting Sandro: “Sometimes the life of a developer is a nightmare, hehe.“
But it is also fun once you find the solution.
So, on one of our BizTalk Server projects, we were trying to establish a connection to an Azure SQL database. To test this connectivity from the BizTalk Server machine with the Azure SQL database and to eliminate or avoid problems that could arise from BizTalk Server connectors, we decided to create a simple C# console application to verify if we could perform a SELECT on the SQL database. This way, we were sure that connectivities were set properly.
Our simple C# console application looks like this:
using System;
using System.Data.SqlClient;
namespace SQLConsoleApp
{
class SelectOperation
{
static void Main(string[] args)
{
// Connection string for your SQL Server
string connectionString = "Server=tcp:xxxxx;Initial Catalog=xxxxx;Persist Security Info=False;User ID=xxxxx;Password=xxxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
// SQL query to execute
string query = "SELECT TOP 10 * FROM dbo.Item";
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the connection
connection.Open();
// Create a command object
SqlCommand command = new SqlCommand(query, connection);
// Execute the command and retrieve data
using (SqlDataReader reader = command.ExecuteReader())
{
// Check if the reader has rows
if (reader.HasRows)
{
// Iterate through the rows and display the data
while (reader.Read())
{
// Example: assuming your table has columns named "Column1" and "Column2"
Console.WriteLine($"ItemID: {reader["ItemID"]}, ItemName: {reader["ItemName"]}");
}
}
else
{
Console.WriteLine("No rows found.");
}
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
}
Console.ReadLine();
}
}
}
Of course, if you want to use it, you have to fix the connection string details. Assuming that the connection string is properly set up, you can go ahead and test it.
However, in our case, it has raised the following error:
Error: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The target principal name is incorrect.)
You may ask yourself, from where did I get the connection string? Because something is wrong. Well, the answer is simple:
- If you access your Azure Portal and go to the database that you are trying to connect to,
- On the Overview page, click Show database connection strings option.
- From there, you get the possible Connection strings you can use. On the ADO.NET (SQL authentication), notice that TrustServerCertificate is set to False.
Cause
When you establish the connection to Azure SQL Database, in order to encrypt the data, Microsoft gateway encrypts it using the certificate they have for the domain *.database.windows.net. For this reason, if you try to connect to the database with the parameter “Trust Server Certificate” set to False, you will get this error message.
Solution
A simple way to solve this problem is to set Trust Server Certificate to True.
As the image above shows, this was our connection string:
string connectionString = "Server=tcp:xxxxx;Initial Catalog=xxxxx;Persist Security Info=False;User ID=xxxxx;Password=xxxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
The only change required for it to work was to change the TrustServerCertificate property to True instead of False.
So, the connection string would be like this:
string connectionString = "Server=tcp:xxxxx;Initial Catalog=xxxxx;Persist Security Info=False;User ID=xxxxx;Password=xxxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;"
If you enjoyed the content or found it useful and wish to support our efforts to create more, you can contribute towards purchasing a Star Wars Lego for Sandro’s son!
