Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

I encountered this problem today on a server running SCVMM 2012. I found a lot of things online suggesting a lot of different possible solutions, but none of them worked for me. Most of the possible solutions involved configuration errors with authentication, but this has worked fine for some time, and only today started giving this problem. Besides that, this server hosts the databases for a number of other apps, and none of those stopped working today, so after a quick check over the SQL Server settings I was left scratching my head.

AlwaysOn Availability Groups and User Accounts

I recently built a AlwaysOn Availablity Group for my companies database server on SQL Server 2012. With three databases for mission critical applications running on the database server, we needed a little more protection than a weekly backup that we can restore.  So I took two fresh servers, installed Windows Server 2012 Standard and SQL Server 2012 Enterprise on each and found this link, which is a great place to start when building an AlwaysOn Availability group.  There's a couple points that were either missing or noted so briefly that I missed them, but it pretty much covers everything from end to end.  There's also an ebook called "High Availability Solutions" downloadable from Microsoft with a lot more information and detail.

Excessive Kaseya Database Size

For years, we had set Kaseya to maintain 30 days of log files for workstations and servers that we manage.  However, a matter arose that made it really attractive to have access to a larger amount of historical data as what we wanted to double check was well beyond 30 days out.  There's nothing that could be done in that case, but can this headache be eliminated moving forward?  Kaseya support said that there would be no ill effects by upping this to 365 days or more, it would just cause the ksubscribers database to grow.  And since our database server was barely breaking a sweat, we made the change.

Fast forward about a year, and our previously 35GB ksubscribers database had ballooned up to well over 400GB, and everything involving Kaseya was dog slow.  The breaking point came when I could no longer get a reapply schema operation to complete.  It would fail at a different point each time, but not too far off from each other failure.  Since we were a couple revisions of Kaseya behind, and a new version was due to come out soon, I figured it was time to address this. Dropping the log retention back to 30 days did nothing, so I opened a ticket. 

The support tech found that the reapply schema failure was because the operation was timing out before completion due to the size of the database.  He was able to increase the timer so that it would finish, and then reset it back to it's original value.  He did not tell me what the value was or where I could find it and noted that leaving it too high would ultimately mask serious issues later on.  

Once we had a reapply schema completed, I was left with these steps to get the database size back under control.  We were told to only do this during a maintenance window, but since steps 2 and 3 took several days each, and everything ran fine while it was running, we couldn't do so.  Nobody can have a maintenance window on their main business app for a week or more.

1. Stop IIS and the Kaseya services.
2. In SQL Management Studio, run the following:
• DBCC SHRINKDATABASE('KSUBSCRIBERS')
3. Reindex / Rebuild the indexes.  We were provided with a stored procedure to do this, I'll have to dig it up but a Kaseya support tech should have it.
• exec [dbo].[sp_rebuildindexes]
4. Update the DB statistics.  Again, we were provided with a stored procedure to do this. 
• EXEC [dbo].[sp_update_stats]
• @dbs = N'ksubscribers'
5. Start IIS and Kaseya services.
6. Access Kaseya VSA web interface and double check the database size reported under System > Statistics. 
7. Run Reapply Schema.