Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2008 10:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 27, 2014 9:25 AM
Points: 18, Visits: 35
I've gone through literally hundreds of web pages and suggestions on how to remedy this solution but nothing works.

Background:
Trying to create a linked server between two 2005 sql servers. The servers are located in different states. They are both joined to the local domain. We had issues initially setting this up, but finally got it working about 4 or 5 months ago. About 1 week ago it stopped working (have no idea, never touch this server), re-booted the server and it worked for a day. After it stopped working, we updated both servers with all updates. This was about the middle of last week and since then it has never worked again. The users have the correct permissions to both servers. It doesn't even work for me from Server Management Studio, I am a sysadmin on both servers.

Here is the tsql I use to create the linked server:
EXEC sp_addlinkedserver @server='SERVERNAME', @srvproduct='', @provider='SQLNCLI', @datasrc='SERVERNAME', @provstr='Trusted_Connection=yes'

The tsql I use to create the linked server login:
EXEC sp_addlinkedsrvlogin 'SERVERNAME','true'

I've tried setting the following options:
exec sp_serveroption 'SERVERNAME','data access','true'
exec sp_serveroption 'SERVERNAME','rpc','true'
exec sp_serveroption 'SERVERNAME','rpc out','true'

I've ran the following sql on both servers and receive the following results:
select net_transport, auth_scheme from master.sys.dm_exec_connections where session_id=@@spid
results from server1 (the server I'm trying to create the linked server on):
net_transport auth_scheme
------------- ---------------
TCP NTLM

results from server2 (the server I'm trying to link to):
net_transport auth_scheme
------------- ---------------
TCP KERBEROS

I think that they both should be using KERBEROS, but we cannot figure out how to set server1 to use KERBEROS.

I've even tried (which we would prefer not to have to do) hard coding a Remote login and password. This doesn't even work.

Any help on this would be greatly appreciated. Thanks in advance.
Post #460366
Posted Thursday, February 28, 2008 9:05 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:05 AM
Points: 3,461, Visits: 353
THis is a HOP problem. You need to run your sql services in a domain account to access the network resources. Else this will fail as the local system account can access only the server resources and not the network resources. also check if the resources havee necessary permisssions.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Post #461783
Posted Friday, February 29, 2008 10:48 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 4:47 PM
Points: 499, Visits: 985
I've always run into problems trying to use Integrated Sign ons with Linked Servers. There's some extra setup that needs to be done in Active Directory (which I don't have access to), so I use SQL Signons for my linked servers.

Theres some information here regarding the AD setup needed.

Good Luck!

Steve G.



Post #462573
Posted Monday, March 3, 2008 2:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 8:29 AM
Points: 1,067, Visits: 1,148
If all computers authenticating with each other are Windows 2000 or greater, and in an Active Directory domain (Windows 2000 Native or higher), then Kerberos will be used by default.

You can set security options in Group Policy (under "Security Options"), for example "Network Security: LAN Manager authentication level", but I don't think these have any effect if all computers trying to authenticate can use Kerberos... I could be wrong, of course!

If you think it is an authentication issue, enable auditing on the remote computer: under "Audit Policy", enable Failure auditing for "Audit object access" and "audit account logon events" and "audit logon events". Then retry you query. You should get some helpful messages in the Security Log on the remote computer.

Andy
Post #463027
Posted Tuesday, March 4, 2008 4:06 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 6:13 PM
Points: 318, Visits: 1,183
The link that aureolin posted is exactly what you need. If you haven't already done so check it out. Key points are:

- your SQL Server service accounts should to be domain accounts, not LocalSystem.
- you MUST have your SPNs setup correctly for each server involved in the delegation chain.
- both the computer accounts and the SQL Server service accounts must be setup as Trusted for Delegation. This can either be constrained delegation if you're using 2003 native forest & domain, or full delegation for 2000 mode domain/forest.

In the past I've always found it much simpler to use fixed logins for linked servers. You shouldn't have very many logins you need to map from one server to the other so it's not that much of a management overhead - hopefully you don't have a company of users who all need access to the DB directly with their network logins?

Your LM authentication level should not affect this - you will either be using NTLM or Kerberos for Trusted Authentication, and the specific version of LM/NTLM doesn't matter. All of the above points are specific to only Kerberos anyway (unless you use protocol transition in a 2003 domain).

Regards,

Jacob
Post #464139
Posted Wednesday, March 5, 2008 1:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, June 3, 2013 9:53 PM
Points: 433, Visits: 619
As an addition to Jacobs comments Kerberos is also very sensitive to DNS issues - make sure that you have both regular and reverse DNS entries for both servers. Kerberos will do a reverse DNS lookup on the caller and tell the caller to piss off if a reverse DNS lookup fails, etc.



Post #464266
Posted Wednesday, March 5, 2008 3:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 8:29 AM
Points: 1,067, Visits: 1,148
Enterprise Manager for MSSQL Server 2000 used to have an option to register the instance in Active Directory - which I guess would create the SPN?

I cannot find the same option in Management Studio, and according to BOL you should use "setspn" at the command line.

Can anyone confirm that this is correct?

For reference, I found the document titled "How Service Publication and Service Principal Names Work" on Microsoft's TechNet useful.
Post #464315
Posted Wednesday, March 5, 2008 6:37 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 6:21 AM
Points: 680, Visits: 6,857
You might want to read this.

http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

If you have access to AD console.msc to view some of the setup, you should be able to start reviewing the computers and accounts involved.

Setspn ( a tool in the resource kit for W2000 server ) can be used to look at the endpoints that are setup.

Since Domain Admin is required for much of the setup, you might just want to get one involved early in the process. They should be helpful in resolving this.
Greg E
Post #464379
Posted Wednesday, March 5, 2008 3:43 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 6:13 PM
Points: 318, Visits: 1,183
AndyD - that's not the same thing. Registering the instance in AD does not create the SPN, it just publishes the "presence" of the SQL server so that people can browse and find it. There is only one situation where SQL Server automatically creates the SPN: when it's running as LocalSystem. The problem is that SPN is created on the computer account for the server, and LocalSystem doesn't have any network priviledges, so it's useless for Kerberos delegation. When you change the service account to a domain user you will see an error message in the SQL logs when SQL Server starts complaining that it can't register the SPN as the domain user it's running as (usually) doesn't have the necessary permissions to create SPNs (and it shouldn't). This error is normal - a domain admin should be manually creating the SPN on the domain user object that SQL Server is running as.

Regards,

Jacob
Post #464815
Posted Thursday, March 6, 2008 11:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 8:29 AM
Points: 1,067, Visits: 1,148
Many thanks Jacob. Slightly less of a mystery now!
Post #465015
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse