Change connection to Azure SQL Managed instance, failing to save
Azure SQL Managed Instance connection, using Private endpoint.
Connection setup works, we select Trust Certificate [YES], Test Database connection gets OK.
But when trying to click OK on the Data Sources setup page, we get the below error and connection does not save.
Solution is to either create entry in local hosts file, or create forward lookup zone on DNS server, but is this a requirement or can you fix it?
Microsoft.Data.SqlClient.SqlException (0x80131904): 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.) ---> System.ComponentModel.Win32Exception
(0x80004005): The target principal name is incorrect
at
Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,
Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at
Microsoft.Data.SqlClient.TdsParserStateObject.SNIWritePacket(SNIHandle handle,
SNIPacket packet, UInt32& sniError, Boolean canAccumulate, Boolean
callerHasConnectionLock)
at
Microsoft.Data.SqlClient.TdsParserStateObject.WriteSni(Boolean canAccumulate)
at
Microsoft.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode,
Boolean canAccumulate)
at Microsoft.Data.SqlClient.TdsParser.TdsLogin(SqlLogin
rec, FeatureExtension requestedFeatures, SessionData recoverySessionData,
FederatedAuthenticationFeatureExtensionData fedAuthFeatureExtensionData,
SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.Login(ServerInfo
server, TimeoutTimer timeout, String newPassword, SecureString
newSecurePassword)
at
Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
serverInfo, String newPassword, SecureString newSecurePassword, Boolean
ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean
isFirstTransparentAttempt, Boolean disableTnir)
at
Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo
serverInfo, String newPassword, SecureString newSecurePassword, Boolean
redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential
credential, TimeoutTimer timeout)
at
Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer
timeout, SqlConnectionString connectionOptions, SqlCredential credential,
String newPassword, SecureString newSecurePassword, Boolean
redirectedUserInstance)
at
Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, SqlCredential credential,
Object providerInfo, String newPassword, SecureString newSecurePassword,
Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions,
SessionData reconnectSessionData, ServerCertificateValidationCallback
serverCallback, ClientCertificateRetrievalCallback clientCallback,
DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo
originalNetworkAddressInfo, Boolean applyTransientFaultHandling)
at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo,
DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions
userOptions)
at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool
pool, DbConnection owningObject, DbConnectionOptions options,
DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at
Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
owningObject, DbConnectionOptions userOptions, DbConnectionInternal
oldConnection)
at
Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
owningObject, DbConnectionOptions userOptions, DbConnectionInternal
oldConnection)
at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection
owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate,
Boolean onlyOneCheckConnection, DbConnectionOptions userOptions,
DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection
owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions,
DbConnectionInternal& connection)
at
Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection
owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions
userOptions, DbConnectionInternal oldConnection, DbConnectionInternal&
connection)
at
Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection
outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1
retry, DbConnectionOptions userOptions)
at
Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1
retry)
at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1
retry, SqlConnectionOverrides overrides)
at
Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
at
Devolutions.RemoteDesktopManager.Business.DataSources.SQLServerConnectionDataSource.CreateDbConnection(String
connectionString, Boolean openConnection)
at
Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseConnectionDataSource.CreateDbConnection(Boolean
master, Nullable`1 connectTimeout)
at
Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseConnectionDataSource.ExecuteScalar[T](String
sql, IDbDataParameter[] parameters)
at
Devolutions.RemoteDesktopManager.Business.DataSources.SQLServerConnectionDataSource.ExecuteScalar[T](String
sql, IDbDataParameter[] parameters)
ClientConnectionId:8f42865c-b5ad-438d-88ab-553bfa9d4f6b
Error Number:-2146893022,State:0,Class:20
Hello,
Thank you for contacting us regarding this matter.
From what I have found online ( https://techcommunity.microsoft.com/t5/azure-database-support-blog/azure-sql-db-private-link-private-endpoint-connectivity/ba-p/1235573), the two solutions you mentioned would be the solution (point 4 and 5 in the page above). You are receiving the same error mentioned in the article.
Could you confirm that you can connect using SSMS without applying either of those changes? If you can, then we could troubleshoot the issue in RDM.
Best regards,
Richard Boisvert
Yes, we can use SSMS without the errors before we add the manuel hosts record.
Hello,
We will replicate your private endpoint internally and perform some tests; it is most likely a setting in RDM to change or implement.
Best regards,
Richard Boisvert
Hello,
We setup an SQL Azure Server/Database with Private Endpoint following steps outlined by Microsoft.
https://learn.microsoft.com/en-us/azure/azure-sql/database/private-endpoint-overview?view=azuresql
After successfully testing the connectivity via Telnet (telnet & Test-NetConnection) and SSMS RDM was able to connect without any further changes or setting modifications.
Here is the configuration (all other tabs are at the default values). Also works if we connect using the server admin account.
SSMS test
We even ran the query in RDM using a DataReport entry type, configured using the server admin, here are the result.
Stéfane Lavergne