SQL authentication and strict computer policy

(Magyarul itt)

Many people faced the upleasant message below while trying to connect to SQL Server with SQL authentication: “Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server connection” The most typical reason for this is that the server is configured to allow only Windows logins, not SQL logins. The cure is pretty simple, open up server properties and change the authentication option from Windows only to SQL and Windows – and there you go.

Recently, I met the error message in a different situation. A few guys couldn’t connect to a server with Windows integrated authentication. They got the error message above. Others could connect without any issue. I tried to figure out what was going on: my first candidate was MTU size but as the authentication token size was the same size (actually, it was bigger for those who had no problem), I ruled this out. Then I checked if they could connect to a file share on the machine. This action failed and I found this in the security eventlog:

Event Type: Failure Audit
Event Source: Security
Event Category: Logon/Logoff
Event ID: 534
Date: 8/3/2008
Time: 3:04:30 PM
Computer: SQL10
Logon Failure:
Reason: The user has not been granted the requested
logon type at this machine
User Name: erik
Logon Type: 3
Logon Process: NtLmSsp
Authentication Package: NTLM
Workstation Name: ERIKPC

So what the heck does this mean? I asked my old pal Google, who said he read about it once, and people say it’s nothing else than network logon failure. So I decided to check the local security policy. And I found that in the User Rights Assignment node the Access this computer from the network privilege was granted only the local Administrators group. At that point I banged my head into the wall why I hadn’t found the pattern sooner and also extended this privilege to the users group as well. After a policy refresh, it worked.

Lookink back, it was pretty simple. As the peons users were not authorized to access the computer via the network, the OS tore and dropped their token so the SQL Server didn’t get anything from their identity. Once again I learnt something new.


  1. Z:

    Thanks, saved me from hours into looking to the wrong direction… This helped me in getting closer to the solution, although I had to jump a few more hoops in my case.

  2. Jeff:


    Thanks very much for translating this entry. I got over here from http://decipherinfosys.wordpress.com. For those, like me, who are not bona fide DBAs, yet who must administer a Developer’s Edition installation, little tidbits like this are most welcome. Good work.


Leave a comment