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, 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 the 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.
📝 One-Minute Brief
The SQL Server error “Target principal name is incorrect” often appears when validating connectivity and typically points to authentication or network configuration issues. This article explains why the error occurs, focusing on scenarios involving SQL Server, Service Principal Names (SPNs), DNS resolution, and Kerberos authentication. It walks through the root cause and outlines practical steps to correctly configure the environment and restore successful SQL connectivity.
Cause
When you establish a connection to Azure SQL Database, in order to encrypt the data, Microsoft gateway encrypts the data using the certificate for the domain *.database.windows.net. For this reason, if you try to connect to the database with the Trust Server Certificate parameter set to False, you will receive this error message.
Solution
A simple way to solve this problem is to set the 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!
