I had checked the SQL Server logs (
exec sys.xp_readerrorlog) and the Windows logs (
Event viewer > Windows Logs > Secutiry) and I saw these messages
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed [CLIENT: xx.xx.xx.xxx]
and
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: xx.xx.xx.xxx]
and
The user has not been granted the requested logon type at this machine.
After a lot of reading I came across a few other's with this problem. These were most helpful
The trick was the the security policy Local Security Policy > Local Policies > User Rights Assignment > Access this Computer from Network did not include Users or Everyone thus when users tried to get into the DB, I guess SQL Server uses that user's account, not the acount under which the SQL Server service is running, to authenticate against the domain. Because that action was restricted they could not be authenticated and thus could not get access to the DB.
The solution is to add Users to the Access this Computer from Network policy. The default is
- Administrators
- Backup Operators
- Everyone
- Users
though so you'd have to have it changed by a GPO push or made a mistake somewhere down the line to have this not be present in the first place.