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****@sommarsk og.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 "SuperSocke t 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