December 29, 2008 at 12:56 am
Hi All,
I have two cluesters (cluster1 and cluster2), which contains sqlserver2005 databases.
Cluster1 containts one SQLServer1 database and Cluster2 contains one SQLServer2 2005 database. Both of the SQLServer 2005 databases are connected with Linked Servers.
While accessing data from SQLServer1 database to SQLServer2 database I am getting the following error:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
If anyone knows about this error please help me.
Thanks in Advance
Sarat
December 29, 2008 at 6:52 am
You are facing Delegation or commonly termed "double hop" issue, to troubleshoot the issue you can follow the below steps:
Verify your DNS settings
The name resolution process in Domain Name System (DNS) is used to resolve the IP address to the name of the instance of SQL Server
To verify that the name resolution process is resolving the correct server, you can ping the server by using the server name and the IP address of the server. To do so, follow these steps:
1. Click Start , and then click Run .
2. In the Run dialog box, type cmd in the Open box, and then click OK .
3. At the command prompt, run the following command:
ping
Note the IP address that is returned.
4. At the command prompt, run the following command (where IP address is the IP address that you noted in step 3):
ping –a
It should resolve to the FQDN
If either of the specified commands are not successful, time out, or do not return the correct values, the DNS lookup is not working correctly or the problem occurs because of other networking or routing issues. To see your current DNS settings, run the following command at a command prompt:
ipconfig /all
Verify the domain controller:
On a domain controller, in Active Directory Users and Computers:
1.Right-click the SQL Server that you want to set up for delegation, and then click to select Trust this computer for delegation. If it is not the last computer in the chain, all the computers that are intermediaries must be trusted for delegation.
2.Grant delegation permission to the SQL Server service account domain user account. You must have a domain user account for clustered SQL Server installations (this step is not required for computers that are running SQL Server that are using a local system account):
a.In the Users folder, right-click the user account, and then click Properties.
b.In the user account properties dialog box, click the Account tab.
c.Under Account Options, click to select the Account is Trusted for Delegation check box. Make sure that the Account is sensitive and cannot be delegated check box is cleared for this account.
Verify Service Prinicpal Name:
1.Use the following command to verify if the SPNs exist.
ldifde -f c:\spn.txt -d "DC=was,DC=int,DC=imf,DC=org" -l serviceprincipalname -r "(serviceprincipalname=MSSQLSvc/*)" -p subtree -t 3268
2.If you do not see the SQL Server Computer you may have to add the SPN manually.
3.You can use the setspn utility to set the SPNs, run the following SETSPN command to register an SPN for the port that the computer that is running SQL Server is using:
setspn -A MSSQLSvc/
If a computer is using Dynamic port allocation following the below steps are required to register the SPN on SQL Server Service startup.
To configure the SQL Server service to create SPNs dynamically when the SQL Server service starts, follow these steps:
1. Click Start , click Run , type Adsiedit.msc , and then click OK .
Note The ADSIEdit tool is included in the Windows Support Tools. To obtain the Windows Support Tools, visit the following Microsoft Web site:
2. In the ADSI Edit snap-in, expand Domain [ DomainName ] , expand DC= RootDomainName , expand CN=Users , right-click CN= AccountName , and then click Properties .
Notes
•DomainName is a placeholder for the name of the domain.
•RootDomainName is a placeholder for the name of the root domain.
•AccountName is a placeholder for the account that you specify to start the SQL Server service.
•If you specify the Local System account to start the SQL Server service, AccountName is a placeholder for the account that you use to log on to Microsoft Windows.
•If you specify a domain user account to start the SQL Server service, AccountName is a placeholder for the domain user account.
3. In the CN= AccountName Properties dialog box, click the Security tab.
4. On the Security tab, click Advanced .
5. In the Advanced Security Settings dialog box, make sure that SELF is listed under Permission entries .
If SELF is not listed, click Add , and then add SELF .
6. Under Permission entries , click SELF , and then click Edit .
7. In the Permission Entry dialog box, click the Properties tab.
8. On the Properties tab, click This object only in the Apply onto list, and then click to select the check boxes for the following permissions under Permissions :
•Read servicePrincipalName
•Write servicePrincipalName
9. Click OK two times.
Note For help with this process, contact Active Directory product support, and mention this Microsoft Knowledge Base article.
10. In the CN= AccountName Properties dialog box, click Attribute Editor .
11. Under Attributes , click servicePrincipalName in the Attribute column, and then click Edit .
12. In the Multi-valued String Editor dialog box, remove the service principle names (SPNs) for the instances of SQL Server that use this SQL Server service account.
Warning You should only delete the SPNs for the instances of SQL Server that you are currently working on. The other instances of SQL Server that use this service account will be able to remove the SPNs that are related to these instances the next time that you start these instances.
13. Exit the ADSI Edit snap-in
r, manish
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply