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.

[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_addlinksrvlo gin, to configure a login to use when logging into the
linked server.
--
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

[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.n et 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****@sommarsk og.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