Our website is released to live and running normally for days or even weeks.
And then without any change it suddenly starts to throw database connection errors:
Error
System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'DOMAIN\ServerName$'.
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Linq.SqlClient.SqlConnectionManager.UseConnection(IConnectionUser user)
at System.Data.Linq.SqlClient.SqlProvider.get_IsSqlCe()
at System.Data.Linq.SqlClient.SqlProvider.InitializeProviderMode()
at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
at System.Data.Linq.DataContext.ExecuteCommand(String command, Object[] parameters)
at MyClass.Data.Linq.MYDB.MYDBDataContext.OnCreated()
In the connection string we are using basic authentication which is working normally.
Connection string
<add name="MYDBConnectionString" connectionString="server=DBServer;database=MYDB;user=myeuser;pwd=mypassword;Persist Security Info=False;Trusted_Connection=False;Integrated Security=False" providerName="System.Data.SqlClient" />
My suspicion is if there is a network error and the website cannot access the database next time it tries to authenticate with the application pool user and from that point it ignores the user set in the connection string.
UPDATE: Also worth noting the website runs fine again after an iisreset or application pool recycle, but it cannot access the database until that as it is keep trying to connect with the wrong user.
How can I prevent this? I want my website to always use the username and password set in the connection string.
I have tried setting Persist Security Info, Trusted_Connection, Integrated Security in different ways, but I could not find the right combination.
Or is there anything else I could set or try to stop it from falling back to the app pool user?
I would appreciate any tips.
Finally after that many month, I managed to replicate it and fix it. I will add a solution here in case someone would have the same issue maybe saves some time for them.
The issue was that on our website we had a main web.config file with connection strings and we had a subfolder with another web.config file with other connection strings.
The connection strings in the subfolder were used by pages in the subfolder and the connections in the main one all over the website.
At some point, a new page was created outside of the subfolder that was using a data context which had the connection string in the web.config of the subfolder.
Problem
Two web.config files with separate connection strings. (main folder and subfolder)
Two pages at different locations using the same connection string. (in main folder and subfolder)
Use cases and the issue:
If the first page that was opened was one of the pages in the subfolder then it was initialised normally. After that visiting the page outside the subfolder was also working as the connection string had been initialised.
If the first page visited was the one outside the subfoder, then it tried to connect with the application pool user and failed. This is because it could not find the connection string. After this even those pages in the subfolder would fail as the connection string is not initialised again.
Solution
I have moved the connection strings to the main web.config and now it is working in all cases.
Side note:
The reason for the failure to happen only once a month was that, if the first page on the day that was visited after the application pool reset was the special one outside the subfolder then the whole website went down and stayed there until an iisreset.
The chances for visitin that special page first was small, so it happened rarely.
After iisreset and visiting the website it was fine again.