The underlying provider failed on Open. - Login failed for user Domain\MachinName$

Question

I have a application which worked fine for many years, but after updating windows server it stopped working. The application (visual studio , SQL Server Database)is formed from two parts, 1) DataService located on DataServiceServer 2)Database located on DatabaseServer 3)application UI and Logic which located on UserPC in DataServiceServer I have two connection string to two different Servers

<add name="Server1Context" connectionString="metadata=res://*/Db1Model.csdl|res://*/Db1Model.ssdl|res://*/Db1Model.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=server1;initial catalog=DB1;integrated security=True;multipleactiveresultsets=True;&quot;" providerName="System.Data.EntityClient" />
<add name="Server2Context" connectionString="metadata=res://*/Db2Model.csdl|res://*/Db2Model.ssdl|res://*/Db2Model.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=server2;initial catalog=DB2;integrated security=True;multipleactiveresultsets=True;&quot;" providerName="System.Data.EntityClient" /> 

when I run the applcation I can connect to server1 with Server1Context , but I receive the error "login failed for user Domian\DataServiceServer$".

  System.ServiceModel.FaultException`1[System.ServiceModel.ExceptionDetail]: The underlying provider failed on Open. (Fault Detail is equal to An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is:
System.Data.EntityException: The underlying provider failed on Open. ----> System.Data.SqlClient.SqlException: Login failed for user 'Domain\DataServiceServer$'.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUser...).

I checked the permission for the Domian\DataServiceServer$ on both server for DB1 and DB2. both are equal and permissions are granted for Domian\DataServiceServer$.

I checked the request with SQL Profiler. I don't get any request on Server2 from Domian\DataServiceServer$, but I recieve request on Server1 from Domian\DataServiceServer$. how can I check that how this MachinAccount has be blocked from accessing the server2.

but I mention everything was fine for serveral years untill the update of windows server

and Idea is appreciated

Answer 1

Your reply verified my conjecture. After the Windows update, the default login method of SQLServer has been changed. You can repair this issue through the following steps:

1. First enter the database local, right -click the database

2. Select the attribute, then open the security settings

3. Select SQL Server and Windows identity verification mode. Finally, click OK and close the SQLServer to log in to complete the settings.

If this method does not work, try using Windows ID to verify whether it can be successfully logged in through SSMS