April 10, 2003 at 2:02 pm
I have 2 new SQL 2000 SP3 servers running mixed authentication. I am attempting to setup linked servers between the 2.
From ServerA I have defined a linked server to ServerB and vice versa. For this problem, I have setup an NT user account as sysadmin on ServerB using my NT account.
Using standard logic, I have checked the option to have the connection be made using the current connections security context.
However, this gives me an error: Login failed for user 'NT Authority\Anonymous Logon'. If I setup an impersonation to an SQL user with sysadmin (exact same credentials as the NT account) it works like a charm. However, if I use the impersonation of another NT account with sysadmin credentials, it fails again. The same problem occurs in the other direction as well.
Seems to me there is only a problem when attempting to pass NT credentials - for some reason they are passing anonymous as opposed to actual NT credentials.
Suggestions?
TIA,
Mike Kelly
April 11, 2003 at 9:18 am
Are you in an Active Directory Domain or an NT 4.0 domain?
Michelle
April 11, 2003 at 9:30 am
This is an NT 4.0 domain. I failed to mention that this problem occurs when I simply attempt to view the tables under the linked server. I figure if I can not do that, than it is unlikely that any other action will work.
April 11, 2003 at 9:34 am
I have seen the same thing.
If you use NT Authentication to connect to Enterprise Manager or Query Analyser, then it seems to propagate the user name as Anonymous to the linked server.
However, when I log in as sa, it works!
April 11, 2003 at 9:45 am
It doesn't have to be 'sa' as long as you login/register SQL Server with SQL Server authentication and with proper permissions.
Grant login "NT Authority\Anonymous Logon" to access your SQL Server with necessary permission may help you too.
April 11, 2003 at 9:59 am
In an NT domain you cannot pass the rights to another server. You must have an Active Directory domain to do it.
Your best bet is to allow all users to impersonate one account on the linked server with as minimum rights as you can get away with.
I found this at http://support.microsoft.com at one time.
Michelle
April 14, 2003 at 5:00 am
I agree with mimorr.
The best you can do in that environ is spoof 1 login if rights aren't defined.
OR, you could create SQL login(s) on both servers or Local NT users on both servers and give out that information.
What is the purpose of the linked server(s)? If reporting I would create a single login with read-only permissions and be done with it.
Good Luck
AJ Ahrens
SQL DBA
Custom Billing AT&T Labs
Good Hunting!
AJ Ahrens
webmaster@kritter.net
April 14, 2003 at 6:55 am
see BOL keyword "delegation of security account"
btw: if you extend permissions to [NT AUTHORITY\ANONYMOUS LOGON] then ANY domain user will have the permissions granted provided they connect using a linked server.
Bill
Bill Wunder
April 17, 2003 at 7:51 am
I went through a world of pain a long time ago to get linked servers to work. Here's a quote from BOL, from sp_addlinkedsrvlogin:
true for useself is invalid for a Windows NT authenticated login unless the Microsoft Windows NT® environment supports security account delegation and the provider supports Windows Authentication (in which case creating a mapping with a value of true is no longer required but still valid).
In essence, this is saying that the linked server definition will work across servers (that is, connecting two different physical boxes) only if Windows supports a certain kind of authentication/security. However, Windows does no such thing--leastways, the "conventional" NT 4.0 scheme doesn't; W2k Active Directory (and/or Kerberos) may, but I have no experience with this. I only learned the sordid truth after a tech support call with Microsoft. (I believe the true operative technical term describing the situation is "delegated authentication".)
Interestingly, NT authentication does work across multiple instances of SQL Server installed on a single box.
After fighting this, confusing documentations, and sp_ routines that simply didn't work [linked servers are not my friend], we ended up creating a scheme using NT authentication to connect to the "front" box, and it used SQL Authentication (using quite specific and deliberately designed logins) on our "targeted" boxes. It all ends up working like a charm, but the learning curve is fearsome.
Moral: If you must use linked servers in an environment that does not support delegated authentication, then you can never adopt the best practice of configuring your servers for NT Authentication only.
Philip
April 17, 2003 at 11:42 am
I agree with Allen_Cui, Linking servers using SQL authentication should not be a big of a deal as long as your machines are behind your fire wall. You need to have the user (any user) created in both SQL Servers (not NT user) witht the same password and then, set wich db this users will be allowed to see. After that go and link the server using the new user configuration. I hope this helps a little. I use this setting to link a bunch of servers and all of them work fine. Good luck!
April 21, 2003 at 9:51 am
Just wanted to add something we learned the hard way;You can not use NT domain groups when Linking Servers.
Perla Castro
Perla Castro
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply