Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connect

After an office move (accompanied by IP Address changes), I can no longer
use Windows authentication to connect to one of my SQL Server machines.
Nothing else seems to have changed. This is an example of what I am facing.
For simplicity, I conducted this with Query Analyzer, attempting to connect
via SQL Server authentication and Windows authentication:
SERVER1 --> SERVER2 : Both succeed
SERVER1 --> SERVER3 : Both succeed
SERVER2 --> SERVER1 : Both succeed
SERVER2 --> SERVER3 : Both succeed
SERVER3 --> SERVER1 : Both succeed
SERVER3 --> SERVER2 : SQL Server Authentication succeeds, but Windows
authentication returns: Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection.
Server Specs
============
SERVER1 : SQL2K Enterprise Edition, SP3 (8.00.760) on Windows 2000 Server
SERVER2 : SQL2K Developer Edition, SP3a (8.00.818) on Windows 2000 Server
SERVER3 : SQL2K Developer Edition, SP3a (8.00.818) on Windows Server 2003
Named pipes and TCP/IP are enabled on all three (client and network).
SERVER1 sits in our DMZ; its IP Address did not change. SERVERs 2 & 3 got
new addresses and new DNS entries. None is on a domain.
I have seen similar questions posed here, but have yet to see a suggestion
that resolves my problem. Any ideas?
Dave Anderson
Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.
If server2 and serv3 are not on a domain what account are you using to log
on with windows authentication? Are you using pass-thru authentication? If
the user being returned is NULL (as in your case) it means the problem is
outside of sql permissions, this means sql cannot even see what account is
trying to log on to it. One example of how this can happen is this:
Server2 is on Workgroup2 - there is a local account User2 - there is NO
local account User3 on this machine
Server3 is on Workgroup3 - there is a local account User3 - there is NO
local account User2 on this machine
So now if I log on to Server3 as user User3, and then use windows
authentication to connect to server2 this would fail since on the Server2
no account User3 exists, what you would usually see is a login entry in the
SECURITY log on server2 for an account: ANONYMOUS (which by default does
not have permissions into SQL Server).
To troubleshoot this issue I would recommend
1. If you are using PASS-THROUGH authentication (per my example above),
create an account User3 on your Server2 box which has the same name and
password as the user3 account on Server3
2. Enable security account auditing on your Server2 box to see what account
is seen on the Server2 box when you try your windows authentication
connection - I suspect you may see ANONYMOUS or something similar
3. Check these articles as well:
- BOL topic: Security Account Delegation
- You may not be able to connect to a SQL Server that is running on a - ID:
840219
http://support.microsoft.com/?id=840219
- How to troubleshoot connectivity issues in SQL Server 2000 - ID: 827422
http://support.microsoft.com/?id=827422
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Fany Vargas [MSFT] wrote:
> If server2 and serv3 are not on a domain what account are you
> using to log on with windows authentication?
I am using local accounts on both servers, in which the account name &
password are identical. Recall that SERV2 --> SERV3 is successful with
Windows Authentication, but SERV3 --> SERV2 is not.

> Are you using pass-thru authentication?
I'm not sure what you mean by this. If I select "Windows Authentication"
when connecting (whether in a SQL Server tool or with something like a UDL
file setup), the one direction fails while the other succeeds...

> 1. If you are using PASS-THROUGH authentication (per my example
> above), create an account User3 on your Server2 box which has the
> same name and password as the user3 account on Server3
Aready there...

> 2. Enable security account auditing on your Server2 box to see what
> account is seen on the Server2 box when you try your windows
> authentication connection - I suspect you may see ANONYMOUS or
> something similar
I had actually already tried this. It logs nothing whatsoever, which I
assume means the whole process is aborted before a connection is even
attempted.

> ...You may not be able to connect to a SQL Server that is running...
> http://support.microsoft.com/?id=840219
I was so sure this was going to solve my problem, as it describes it
precisely. Unfortunately, the resolution therein does not help me connect.
In hindsight, I suppose I should not have expected it to help, as my problem
arose after the physical (and network) move. In other words, since Local
Security Policy was not a hurdle before the problem began, why should it be
the cause of the problem?
Is it possible I am merely correlating the network changes with this?
Perhaps a recent Windows Update plays a part...

> - How to troubleshoot connectivity issues in SQL Server 2000 - ID:
> 827422 http://support.microsoft.com/?id=827422
This was actually my starting point. I have no idea where to go from here.
Dave Anderson
Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.
|||1. I can see you are indeed using Pass-through authentication (this means
you are using a local account with same name and pwd to authenticate) .
You should be seeing an entry in the security log. Can you ensure that the
security policy to enable auditing has been set? If there is a group policy
defined (there should not be if its in a workgroup) then this would
override your local security policy. Also, you can you enable security
auditing on the sql server itself (see BOL topic: SQL Server Properties
(Security Tab))
2. Are you able to connect from some OTHER client? Or do trusted
connections to SRV2 fail from ALL clients?
from your description it seems that trusted connections work from all other
clients except SRV3
3. What is the startup account of the SQL Service account is it the local
administrator account? If not, try setting it to local admin and test your
connection.
4. On SRV3 try creating an LMHOST entry for SRV2, are you now able to
connect with trusted auth?
5. Can you create a new account TESTACCOUNT on both SRV2 and SRV3 (with
same password) and see if you can use pass through authentication with
these new accounts (the issue may be account specific)
6. When connecting to SRV2 specify the IP address rather than the network
name - does it now work?
7. Check if the account has "impersonate client after authentication" right
on SRV2 - try for both sql service account and the account trying to log on
8. This error usually refers to security issues. Essentially SQL server is
unable to get information about the NT account that you are trying to use
to connect to it. The fact that you are not seeing an entry in the security
log hints to this as well. You may also want to try running a netmon trace
to see if we ever establish a connection to sql.
9. Does the account have "Access this computer from the network" rights? If
not try adding this right and retry your connection
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||First I'd like to thank you for all of your help - my problem is solved. My
comments (and a question) are inline:
Fany Vargas [MSFT] wrote:
> 1. I can see you are indeed using Pass-through authentication (this
> means you are using a local account with same name and pwd to
> authenticate) . You should be seeing an entry in the security log.
> Can you ensure that the security policy to enable auditing has been
> set? If there is a group policy defined (there should not be if its
> in a workgroup) then this would override your local security policy.
> Also, you can you enable security auditing on the sql server itself
> (see BOL topic: SQL Server Properties (Security Tab))
The security log mirrors my specific error message - once per connection
attempt. None of the policy settings in KB:840219 helped, though this was
not really a surprise, given that only one direction was failing (of the 6
possible between 3 servers), not to mention the fact that this connectivity
problem is new, and I am unaware of recent changes to the policies on either
machine.

> 2. Are you able to connect from some OTHER client? Or do trusted
> connections to SRV2 fail from ALL clients?
> from your description it seems that trusted connections work from all
> other clients except SRV3
Not only that, but trusted connections TO all other servers were fine from
that machine...

> 3. What is the startup account of the SQL Service account is it the
> local administrator account? If not, try setting it to local admin
> and test your connection.
Local System. Changing it had no effect.

> 4. On SRV3 try creating an LMHOST entry for SRV2, are you now able to
> connect with trusted auth?
No.

> 5. Can you create a new account TESTACCOUNT on both SRV2 and SRV3
> (with same password) and see if you can use pass through
> authentication with these new accounts (the issue may be account
> specific)
Different accounts did not matter.

> 6. When connecting to SRV2 specify the IP address rather than the
> network name - does it now work?
> 7. Check if the account has "impersonate client after authentication"
> right on SRV2 - try for both sql service account and the account
> trying to log on
> 8. This error usually refers to security issues. Essentially SQL
> server is unable to get information about the NT account that you are
> trying to use to connect to it. The fact that you are not seeing an
> entry in the security log hints to this as well. You may also want
> to try running a netmon trace to see if we ever establish a
> connection to sql.
> 9. Does the account have "Access this computer from the network"
> rights? If not try adding this right and retry your connection
Suffice it to say that I tried all of the above, without solution. Then I
came back the next day and demonstrated the problem to one of our network
guys, and as I walked him through all of the steps I took to show the
different error messages, I got it to work by setting up an alias to use
Named Pipes.
Now, this was one of the first steps I took, as it was the natural place to
start, and I revisited it several times through this ordeal. But it never
led to resolution until Friday. I also noticed on Friday that I could again
map network drives by name rather than IP Address -- I had been getting the
"duplicate name exists on the network" error**
In the end, I am left wondering if this was just some WINS artifact or
something. Is this possible? And even if it were, why would Windows
Authentication fail over IP, but succeed over named pipes?
**Recall that the IP Address changed on both machines. But this drive
mapping error was not preventing Windows Authentication from other machines.
Furthermore, success at drive mapping by IP Address was not paired with
success at SQL Server connection by IP Address.
Dave Anderson
Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.
|||If named pipes worked and TCP did not (assuming that SQL was indeed
listening on TCP) then it may be an Kerberos - SPN issue. You may have a
duplicate or invalid SPN. To see the list of SPN's you can use the SETSPN
utility
(http://www.microsoft.com/downloads/d...31fd-ab77-46a3
-9cfe-ff01d29e5c46&displaylang=en). However, SPN's should only apply in a
domain environment not a workgroup environment. Let us know if you get any
results when running setspn -L ...
See these references:
* BOL Topic: Security Account Delegation
* PRB: Setspn Fails if Domain Name Differs from NetBIOS Name Where SQL -
ID: 277658 - http://support.microsoft.com/?id=277658
* INF: SQL Server 2000 Kerberos support including SQL Server virtual - ID:
319723 (available at http://support.microsoft.com)
* You receive a "Cannot generate SSPI context" error message when you use -
ID: 824402 (available at http://support.microsoft.com)
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Dave Anderson wrote:
> After an office move...
> SERVER1 --> SERVER2 : Both succeed
> SERVER1 --> SERVER3 : Both succeed
> SERVER2 --> SERVER1 : Both succeed
> SERVER2 --> SERVER3 : Both succeed
> SERVER3 --> SERVER1 : Both succeed
> SERVER3 --> SERVER2 : SQL Server Authentication succeeds, but Windows
> authentication returns: Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection...
One final footnote: I believe I now know the cause of this problem. When we
moved offices, we also consolidated offices. That is to say that groups from
several facilities are now housed at the same place, with a single LAN. And
one of the groups from a different facility brought along a machine with the
same MACHINE NAME as SERVER2 (though ours had the DNS entry).
This appears to be the cause of the "duplicate name" problem with mapping
drives, and is most likely the cause of our SQL Server authentication
nightmares.
Dave Anderson
Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.