Login failed for user null occurs after a period of time

[cerilocke]

I have an identical SQL database on two machines (my machine and a web
server) that links to a database on a third server (S3). When I execute
a stored procedure on my machine that accesses a database on S3, it
always runs without a problem. However, when I run the same stored
procedure on the webserver (via Query Analyzer on my machine, connected
to the webserver), the stored procedure runs without a problem for
about ten minutes, and then I suddenly receive the error "Login failed
for user '(null)'. Reason: Not associated with a trusted SQL Server
connection.". If I then connect remotely to the webserver via Remote
Desktop Connection and run the stored procedure there it works, and
then I can run it again on the webserver via Query Analyzer on my
machine, but the same problem will occur again after about ten minutes.
The only difference between my machine and the webserver is that the
webserver is running Windows Server 2003, whereas my machine runs
Windows XP. All settings in SQL on the two machines are identical, so I
would love to know if anyone has a possible solution to this seemingly
random problem.

[Stu]

It sounds like and Active Directory problem. Check the time on your
web server to see if it's close to the server time; if not you may want
to adjust it manually to match (or read up on w32tm to keep them in
sync). If the time is out of kilt, then the web server may start
dropping tokens.

You may also want to make sure that the web server doesn't try to power
down the NIC cards periodically.

[cerilocke]

Thanks for replying Stu. The time seems to be matching on the two
servers and my machine, so I can only assume that this is not the
problem. I cannot understand why I can always run the query without
fail directly on the web server, but if I run it on the web server via
query analyzer on my machine, it works for about five minutes, and then
I get the "Not associated with a trusted connection error". As I said,
if I then run it again directly on the server, I can then run it
successfully in Query Analyzer on my machine for about five minutes.
The only stored procedure with which I have the problem is the one that
calls data from the third machine, but it always works correctly on the
webserver. I would be most grateful if anyone can help me on this, as I
have wasted most of the day trying to solve the problem. Prior to using
the Windows 2003 server, I never had a problem on the previous web
server, which ran Windows 2000.

[xAvailx]

As Stu pointed out, it looks like an Active Directory problem. Are you
using Windows or SQL Server authentication? Sounds like Windows
authentication.

Check that the machine you are having issues with is authenticating do
the domain (look in Event Viewer for suspicious entries).
Alternatively, you may be able to use SQL Server authentication (if the
Server is configured to allow so), however, that is more of a short
term workaround.

HTH,

BZ

[cerilocke]

I am using Windows authentication. I have also ensured that the three
machines have identical time by using the net time DOS command, as Stu
suggested, but unfortunately the problem still exists. Thank you both
for your help. Any further suggestions from anyone would be greatly
appreciated, as I have totally exhausted my list of potential causes of
the problem.

[Stu]

Can you run the stored procedure as a SQL Server authenticated user?
This would confirm what we already suspect (it's a window
authentication issue) and not a hardware issue; if you get the same
results, it's a hardware issue; if not, then it's Active Directory.

If it's a hardware issue, you need to make sure that the NIC cards on
the web box are not powering down after a few minutes of inactivity;
running the stored procedure remotely may allow the connection to sleep
after a few minutes, whereas Remote Desktop is continually sending
packets back and forth.

Just thinking aloud.
Stu

[Erland Sommarskog]

(ce*******@ev1.net) writes:

I have an identical SQL database on two machines (my machine and a web
server) that links to a database on a third server (S3). When I execute
a stored procedure on my machine that accesses a database on S3, it
always runs without a problem. However, when I run the same stored
procedure on the webserver (via Query Analyzer on my machine, connected
to the webserver), the stored procedure runs without a problem for
about ten minutes, and then I suddenly receive the error "Login failed
for user '(null)'. Reason: Not associated with a trusted SQL Server
connection.". If I then connect remotely to the webserver via Remote
Desktop Connection and run the stored procedure there it works, and
then I can run it again on the webserver via Query Analyzer on my
machine, but the same problem will occur again after about ten minutes.
The only difference between my machine and the webserver is that the
webserver is running Windows Server 2003, whereas my machine runs
Windows XP. All settings in SQL on the two machines are identical, so I
would love to know if anyone has a possible solution to this seemingly
random problem.

When it dies after ten minutes, does it hit a new code path, or is
that access in a loop, and thus the same statement executed again
and again?

In the latter case, maybe you should consider to replace the loop,
so the procedure does have to run for ten minutes...
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[xAvailx]

Did you check Event Viewer for login issues to Active Directory?

Thx, BZ

[cerilocke]

I did check the Event Viewer for login issues. Under Application, one
warning has appeared twice today, which was "SuperSocket info:
(SpnRegister) : Error 8344.". Under Security, all events shown as
Success Audit are policy changes, and all showing as Failure Audit are
in the Logon/Logoff category, and have the following reasons listed.
Logon Failure:
Reason: Unknown user name or bad password
User Name:
Domain:
Logon Type: 3
Logon Process: Kerberos
Authentication Package: Kerberos
Workstation Name: -
Caller User Name: -
Caller Domain: -
Caller Logon ID: -
Caller Process ID: -
Transited Services: -

Thanks again to everyone for your help so far. It is greatly
appreciated.

[xAvailx]

>> I am using Windows authentication. <<

Can you try SQL Server authentication and check if the same issue comes
up?

Thx,

BZ

[cerilocke]

I tried using SQL authentication, and received the identical error,
"Login failed for user '???'. Reason: Not associated with a trusted SQL
Server connection."

[cerilocke]

I tried using SQL authentication, and received the identical error,
"Login failed for user '???'. Reason: Not associated with a trusted SQL
Server connection."

[cerilocke]

I tried using SQL authentication, and received the identical error,
"Login failed for user '???'. Reason: Not associated with a trusted SQL
Server connection."

[cerilocke]

It would appear that Active Directory is not actually installed on the
server (I apologize for not realizing this sooner, but knowing less
than nothing about network administration meant that I was looking in
Computer Management for things such as the Event Viewer). Would
installation be a likely solution to the problem?

[Erland Sommarskog]

(ce*******@ev1.net) writes:

I tried using SQL authentication, and received the identical error,
"Login failed for user '???'. Reason: Not associated with a trusted SQL
Server connection."

So the target server, does either
1) permit SQL authentication
2) does not trust the source SQL Server.

You never said whether you have a domain, or just a workgroup. Trusts
in workgroups is a tricky business.

If the target server permits SQL authentication, you could use
sp_addlinksrvlogin, to configure a login to use when logging into the
linked server.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[cerilocke]

Thanks for replying Erland. The three machines all belong to the same
domain, and I have all three set up to only use Windows Authentication,
with named pipes and tcp/ip (I have tried all permutations of removing
those without any success). I have spent all day trying every possible
suggestion that I have been able to find, including using SQL
authorization, but I still have the same error. It seems that my
identity does not get to the target server (S3) if I go from my machine
through the web server using either Query Analyzer or Enterprise
Manager, but I can connect to each of the two servers successfully from
my machine, and connect to each of the servers from the other server. I
hope that makes sense.

[Damien]

ce*******@ev1.net wrote:

Thanks for replying Erland. The three machines all belong to the same
domain, and I have all three set up to only use Windows Authentication,
with named pipes and tcp/ip (I have tried all permutations of removing
those without any success). I have spent all day trying every possible
suggestion that I have been able to find, including using SQL
authorization, but I still have the same error. It seems that my
identity does not get to the target server (S3) if I go from my machine
through the web server using either Query Analyzer or Enterprise
Manager, but I can connect to each of the two servers successfully from
my machine, and connect to each of the servers from the other server. I
hope that makes sense.

Well, from what you've said, it sounds like some kind of impersonation
problem - you said it previously worked when the webserver was running
2000?

I'd guess it's a delegation issue (you're authenticated to the
webserver, but it cannot impersonate you to the third machine). That
speculation led me to this article, which describes what is necessary
for delegation to work:

http://www.databasejournal.com/featu...le.php/3341651

Let me know if that helps?

Damien

[cerilocke]

Damien,

Having just arrived at the office, and briefly skimmed through the
article, it sounds to be exactly what my problem is, so thank you very
much for that. I will let you know if I eventually solve the problem.

[Erland Sommarskog]

(ce*******@ev1.net) writes:

Thanks for replying Erland. The three machines all belong to the same
domain, and I have all three set up to only use Windows Authentication,
with named pipes and tcp/ip (I have tried all permutations of removing
those without any success). I have spent all day trying every possible
suggestion that I have been able to find, including using SQL
authorization, but I still have the same error. It seems that my
identity does not get to the target server (S3) if I go from my machine
through the web server using either Query Analyzer or Enterprise
Manager, but I can connect to each of the two servers successfully from
my machine, and connect to each of the servers from the other server. I
hope that makes sense.

Apparently there is some fine-print about Windows networking, Active
directory and all that, of which I will have to admit ignorance.

But it seems to me that this is not really an intermittent error, but
a permanent, it just takes ten minutues for your store procedure to
get there. This can easilly be examined by connecting to the web server
from your XP machine, and then directly run a command that accesses
the linked server.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Damien]

Erland Sommarskog wrote:

(ce*******@ev1.net) writes:

Thanks for replying Erland. The three machines all belong to the same
domain, and I have all three set up to only use Windows Authentication,
with named pipes and tcp/ip (I have tried all permutations of removing
those without any success). I have spent all day trying every possible
suggestion that I have been able to find, including using SQL
authorization, but I still have the same error. It seems that my
identity does not get to the target server (S3) if I go from my machine
through the web server using either Query Analyzer or Enterprise
Manager, but I can connect to each of the two servers successfully from
my machine, and connect to each of the servers from the other server. I
hope that makes sense.

Apparently there is some fine-print about Windows networking, Active
directory and all that, of which I will have to admit ignorance.

But it seems to me that this is not really an intermittent error, but
a permanent, it just takes ten minutues for your store procedure to
get there. This can easilly be examined by connecting to the web server
from your XP machine, and then directly run a command that accesses
the linked server.

Hi Erland,

The reason it appears intermittent (works for five minutes after having
been done from the local system), I believe, is that the webserver has
cached the credentials from when he logged onto the webserver - those
credentials are sufficient to authenticate to the third machine. After
some time, those credentials expire, and he's back to only being
authenticated with the webserver.

That is all, of course, assuming that the problem is due to delegation,
which is my expectation.

Windows login/network security always gets complicated/awkward when
three machines are involved - the client, the first server and the
second server.

Damien

[cerilocke]

You are right Damien, in that my ID does get cached. The amount of time
for which it is remembered is actually only about two to three minutes
for some reason. Anyway, the team responsible for domain and network
management here are due to speak to me today, so hopefully I will be
able to let you know what happens. Thanks again to everyone who has
offered help in this matter. I always like to feel I have explored all
avenues before getting the techies involved here.

[cerilocke]

It has taken until now for my helpdesk people to enable delegation on
Active Directory, but having done so, the problem remains. If I enable
SQL authentication on the two servers, I can connect to Server 3 from
my machine via Server 2. The person on the helpdesk claims that it must
be a problem with the SQL configuration, but I cannot see how this can
be if SQL authentication works.

[Damien]

ce*******@ev1.net wrote:

It has taken until now for my helpdesk people to enable delegation on
Active Directory, but having done so, the problem remains. If I enable
SQL authentication on the two servers, I can connect to Server 3 from
my machine via Server 2. The person on the helpdesk claims that it must
be a problem with the SQL configuration, but I cannot see how this can
be if SQL authentication works.

Sorry, I've got nothing more to suggest, other than trying to find some
other program/service (completely unrelated to SQL Server) which will
have the same authorization/authentication needs. Bung it on both
servers and demonstrate that the "same problem" is caused.
Unfortunately, I'm quite tired at the moment and cannot think of
anything off the top of my head. Anyone else?

Damien

[cerilocke]

My frustration has now increased further, as I am able to successfully
connect to Server 2 via Server 3 from my machine using Windows
authentication, but I cannot connect to Server 3 via Server 2 from my
machine, which is the process that I really need to have working.
Everything is set up identically on the two servers (I have checked
everything). I read that having the two servers connected could cause a
problem, so I have now removed Server 2 as a linked server from Server
3, but this did not solve the problem. I am just intrigued as to why I
now get the error "SQL Server does not exist or access denied." rather
than the "Login failed for user '(null)'. Reason: Not associated with a
trusted SQL Server connection." error that I originally experienced.