Connect to SQL Server on Azure VM with Microsoft Entra managed identity authentication failed

Question

I enabled Microsoft Entra authentication for SQL Server on Azure VM, and I can connect with this sql server using user assigned manage identity with SSMS successfully, but when I try to connect to this sql server in C#, I get below error "System.Data.OleDb.OleDbException: 'Communication link failure, TCP Provider: An existing connection was forcibly closed by the remote host.'" Does anyone know how to resolve this issue? Thanks in advance.

using System;
using System.Data.OleDb;

namespace ConnectSQLServer
{
    internal class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Provider=MSOLEDBSQL19;Data Source=****;Initial Catalog=SSISDB;Authentication=ActiveDirectoryMSI;User ID=[Object ID];Use Encryption for Data=Optional;";
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    Console.WriteLine("Connection successful!");
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Connection failed: " + ex.Message);
                }
            }
        }
    }
}

Answer 1

Hi @Huichao Gong (Shanghai Wicresoft Co Ltd),

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

We would like to inform you that, The error you are encountering "Communication link failure, TCP Provider: An existing connection was forcibly closed by the remote host" can happen due to a few possible issues related to the connection string, permissions, or specific settings for the managed identity and Microsoft Entra (Azure AD) authentication.

Here are some steps to troubleshoot and potentially resolve this issue:

1. Verify the Connection String: The OleDb provider and SQL Server might have specific requirements for Azure AD authentication with a managed identity. Ensure you have the correct provider (MSOLEDBSQL19 or MSOLEDBSQL). Also, you may need to specify Trusted_Connection=Yes and remove User ID if it conflicts with managed identity authentication. Try the following format string connectionString = "Provider=MSOLEDBSQL19;Data Source=****;Initial Catalog=SSISDB;Authentication=ActiveDirectoryMSI;Trusted_Connection=Yes;Encrypt=True;";
2. Network Security Configuration: Verify that the SQL Server firewall rules allow connections from the environment where this C# code runs.Ensure that no other network security groups (NSGs) or firewalls are blocking outgoing connections.
3. Update to the Latest Microsoft OLE DB Driver:If possible, ensure you’re using the latest OLE DB driver, as some older versions may not fully support Entra (Azure AD) authentication methods.Check if updating the driver version helps resolve the Communication link failure.
4. Use SqlClient Instead of OleDbConnection (Recommended for SQL Server): For better compatibility with managed identity authentication, use Microsoft.Data.SqlClient instead of System.Data.OleDb. This library provides native support for Azure AD authentication and managed identities. Example using SqlClient:

using System;
using Microsoft.Data.SqlClient;
namespace ConnectSQLServer
{
    internal class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Data Source=****;Initial Catalog=SSISDB;Authentication=Active Directory Managed Identity;Encrypt=True;";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    Console.WriteLine("Connection successful!");
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Connection failed: " + ex.Message);
                }
            }
        }
    }
}

5. Managed Identity Permissions:Double-check that the user-assigned managed identity has the required permissions on the SQL Server database.You may also need to verify that Microsoft Entra (Azure AD) authentication is enabled for the SQL Server.
6. SQL Server Configuration:On the SQL Server, check for any configurations that might impact TCP/IP connections (Example: packet size, encryption requirements, or idle timeouts).

Please refer to the below mentioned links:

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/tls-exist-connection-closed

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/resolve-connectivity-errors-checklist

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/tls-exist-connection-closed

https://learn.microsoft.com/en-us/azure/azure-sql/database/connect-query-dotnet-core?view=azuresql

I hope, This response will address your query and helped you to overcome on your challenges.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.