openrowset and opendatasource issue

Discussion:

(too old to reply)

Lynn

2007-05-14 22:08:15 UTC

Hi,

I experienced a strange issue. The Ad Hoc Remote Query is enabled in a
server A. But, when I run openrowset and opendatasource in my client tool
with the following statement (SQL Server managment studio) after connect to
server A, I always get the error message -- "Msg 18456, Level 14, State 1,
Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."

SELECT top 10 *
FROM OPENDATASOURCE(
'SQLNCLI',
'Data Source=servername;Trusted_Connection=yes;'
).sgdrssd.dbo.cut_attributes

So, I went to server A machine and run the same query in Management Studio,
I got the result from remote server.

Right after I run this same query in server A machine, I run the same query
in my client machine, I didn't get any error anymore and the same result set
is returned.

However, about 10 minutes after the query was run in the SQL server A
machine, I run the same query in my client machine again, the error message
"Login failed ..." appears again.

What's the issue here? Please advise ASAP. Tks.

--
Best Regards,

Lynn

Stefan Delmarco

2007-05-14 23:48:46 UTC

Permalink

I think you're picking up a pooled connection if you access the same
OPENDATASOURCE within a couple of minutes of accessing the OPENDATASOURCE
from server A. It will work from the client when a connection succeeded on
the server and you picked up a pooled connection. Connections are pooled
based on their connection string which is why you are seeing the pooled
connection being reused. The OPENDATASOURCE will fail when a sufficient
amount of time has elapsed for the successful pooled connection to age and
be closed.

The reason you're getting "Login failed for user 'NT AUTHORITY\ANONYMOUS
LOGON'" is due to the double hop problem. When you run on server A it is
only a single hop (Server A -1-> remote) to the remote server. When you run
from your client you have two hops (client -1-> Server A -2-> remote), hence
the failure. Either switch to using linked servers (where you can use the
SQL Server's identity for authentication), enable Kerberos account
delegation or use SQL authenticated logins.

Some more information here:
http://msdn2.microsoft.com/en-us/library/aa905162(sql.80).aspx
http://msdn2.microsoft.com/en-us/library/ms189580.aspx
--
Cheers,
Stefan Delmarco

http://www.fotia.co.uk/

Post by Lynn
Hi,
I experienced a strange issue. The Ad Hoc Remote Query is enabled in a
server A. But, when I run openrowset and opendatasource in my client tool
with the following statement (SQL Server managment studio) after connect to
server A, I always get the error message -- "Msg 18456, Level 14, State 1,
Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."
SELECT top 10 *
FROM OPENDATASOURCE(
'SQLNCLI',
'Data Source=servername;Trusted_Connection=yes;'
).sgdrssd.dbo.cut_attributes
So, I went to server A machine and run the same query in Management Studio,
I got the result from remote server.
Right after I run this same query in server A machine, I run the same query
in my client machine, I didn't get any error anymore and the same result set
is returned.
However, about 10 minutes after the query was run in the SQL server A
machine, I run the same query in my client machine again, the error message
"Login failed ..." appears again.
What's the issue here? Please advise ASAP. Tks.
--
Best Regards,
Lynn

Lynn

2007-05-15 03:39:02 UTC

Permalink

Thank you. The information is very useful.
--
Best Regards,

Lynn

Post by Stefan Delmarco
I think you're picking up a pooled connection if you access the same
OPENDATASOURCE within a couple of minutes of accessing the OPENDATASOURCE
from server A. It will work from the client when a connection succeeded on
the server and you picked up a pooled connection. Connections are pooled
based on their connection string which is why you are seeing the pooled
connection being reused. The OPENDATASOURCE will fail when a sufficient
amount of time has elapsed for the successful pooled connection to age and
be closed.
The reason you're getting "Login failed for user 'NT AUTHORITY\ANONYMOUS
LOGON'" is due to the double hop problem. When you run on server A it is
only a single hop (Server A -1-> remote) to the remote server. When you run
from your client you have two hops (client -1-> Server A -2-> remote), hence
the failure. Either switch to using linked servers (where you can use the
SQL Server's identity for authentication), enable Kerberos account
delegation or use SQL authenticated logins.
http://msdn2.microsoft.com/en-us/library/aa905162(sql.80).aspx
http://msdn2.microsoft.com/en-us/library/ms189580.aspx
--
Cheers,
Stefan Delmarco
http://www.fotia.co.uk/