February 26, 2008 at 10:09 am
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_transportauth_scheme
----------------------------
TCP NTLM
results from server2 (the server I'm trying to link to):
net_transportauth_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.
February 28, 2008 at 9:05 am
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
February 29, 2008 at 10:48 am
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.
March 3, 2008 at 2:52 am
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
March 4, 2008 at 4:06 pm
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
March 5, 2008 at 1:47 am
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.
March 5, 2008 at 3:45 am
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.
March 5, 2008 at 6:37 am
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
March 5, 2008 at 3:43 pm
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
March 6, 2008 at 11:20 am
Many thanks Jacob. Slightly less of a mystery now!
August 7, 2008 at 5:38 am
hi,
i have read all the comments as well as suggestion but my problem is little bit different ,i have two servers .i am executing a query on linked server...its giving below wriiten error
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
i have checked sql server agent login on both servers .both have same login name with same rights.
still it is giving error
Can any one suggest any solution??
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 7, 2008 at 8:38 am
The user should give you a clue here - Anonymous login failed. So, there is no point checking your username/passwords; they are not being used. One server is trying to authenticate to the other user an anonymous login; in other words, it is not presenting any logon credentials.
You need to check your set up. Under what user should the logon process be running? Is it a service account or a specific user?
I would guess that a "Local System" or "Network Service" account is being used; these accounts are only recognised on the local system. Although in some cases the Network Service can appear as ServerName$.
Andy
December 8, 2008 at 9:52 am
I have the same error but when i check on both servers it gave me
net_transport auth_scheme
------------- ---------------
TCP NTLM
December 9, 2008 at 1:49 am
NTLM authentication will work fine, as long as neither server is configured to disallow this "lower" level of authentication (lower when compared to kerberos).
If you are in a AD domain, then as long as the Windows Services on both servers are running under the same credentials, you should be ok. Having said that, in this configuration, kerberos should be used by default.
If not in a domain, then it gets harder. The normal solution is to have exactly matched username/password accounts on both servers.
Andy
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply