The subject line of this post is very interesting, right? Login failed for user ‘(null)’. How come, a login can fail for user “null”? Interesting because, login is failing, and we don’t even know for which user it is failing for.
Summary
We received an urgent mail from the client stating that they cannot connect to SQL 2000 instances from any of the servers (app, web, or DB). They initially thought the SQL 2000 instances were down. They also informed that, the issue was only with windows authentication and not with SQL authentication.
Analysis
We logged in and tried to connect from our end and voila! We were able to connect to the target instances. However, this did not resolve the issue and the client still faced the issue. We did some analysis and below were some of our observations:
- We were able to connect to the SQL 2000 instance (hosted on a Windows 2003 server) locally.
- We were able to connect to these instances from SQL 2019 (hosted on windows 2019 server) via SSMS.
- We were unable to connect to the instances from SQL 2012 (hosted on a Windows 2012 server).
- We checked the security logs (in the event viewer) on the SQL 2000 instance and found that the login failure was not being logged, which meant the login request was not reaching the server itself.
With these observations, we discovered that the problem was not with SQL connectivity but with the mode from which all authentications are done, i.e., Active Directory (AD). We also found that a security patch was applied on AD the previous day, which could have been the reason for this issue.
Workaround
Some detail investigation revealed that this issue may happen for older SQL versions like 2000. There is a concept of loopback check which causes trusted connections via the loopback adapter to fail.
A workaround to this problem is to disable loopback check at registry level on source server. In our case, it was SQL 2012 instance server. This is how, it can be done:
- Open registry editor using regedit in command prompt or RUN.
- Traverse to HKLM\System\CurrentControlSet\Control\LSA.
- Add a DWORD 32 value called “DisableLoopbackCheck”.
- Set value of this key to 1.
- Reboot the server.
Pros n Cons
This workaround works fine, and we were able to test it in our test environment. But we should highlight the few cons of this:
- This workaround has some security issues and client may not want to implement it.
- We need to reboot the server because the registry changes do not take effect until the server is rebooted for this workaround.
- The root cause of the issue was with active directory and hence, it is always better to resolve at root, by either apply a new security patch which resolves this or roll back previous security patch that caused the issue.
A DBA’s life is full of such situations where a decision has to be made, and based of situation, customer requirement, there can be pros and cons of any solution. This was once such situation.
Happy Learning!