September 7, 2018 at 8:51 am
Hi, can someone please help me with this situation:
Setup is:
Two node Always On AG cluster (Node1 and Node2)
(Version: 2016)
Three named instances with one AG on each node:
Ins1:AG1, Ins2:AG2 and Ins3:AG3
Everything looks ok, and all synchronization too.
I use the same SQL service account for all instances.
On one of the instances I have created
I need to run a query on Ins1 that gets some information from a table on Ins2.
I have created a linked server on Ins1 with the name of AG2, when both AG1 and AG2 are on the same node, then this works ok.
When I fail over AG2 to node2, then I first got the error:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Then i created SPN for all the three instances on Node1, and also for the AGs:
setspn -S MSSQLSvc/Node1.energycorp.com:56927 ENERGYCORP\Node1_MSS_Srv
setspn -S MSSQLSvc/Node1.energycorp.com:57849 ENERGYCORP\Node1_MSS_Srv
setspn -S MSSQLSvc/Node1.energycorp.com:58051 ENERGYCORP\Node1_MSS_Srv
SetSPN -s "MSSQLSvc/AG1.energycorp.com:1433" ENERGYCORP\Node1_MSS_Srv
SetSPN -s "MSSQLSvc/AG2.energycorp.com:1433" ENERGYCORP\Node1_MSS_Srv
SetSPN -s "MSSQLSvc/AG2.energycorp.com:1433" ENERGYCORP\Node1_MSS_Srv
I also found the service account in AD, and added delegation access for that account.
Specified services only
Kerberos only
I clicked add, and searched for the service account .
I added all the SPN's that i created (three for the node1 and three for the AG)
Now when running the query I get another error message:
OLE DB provider "SQLNCLI11" for linked server "AG2" returned message "Invalid authorization specification".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI11" for linked server "AG2" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "AG2".
I hope someone can see this in a more clearer light that I can.
September 9, 2018 at 11:20 pm
niklasrene - Friday, September 7, 2018 8:51 AMHi, can someone please help me with this situation:
Setup is:Two node Always On AG cluster (Node1 and Node2)
(Version: 2016)
Three named instances with one AG on each node:
Ins1:AG1, Ins2:AG2 and Ins3:AG3Everything looks ok, and all synchronization too.
I use the same SQL service account for all instances.On one of the instances I have created
I need to run a query on Ins1 that gets some information from a table on Ins2.I have created a linked server on Ins1 with the name of AG2, when both AG1 and AG2 are on the same node, then this works ok.
When I fail over AG2 to node2, then I first got the error:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.Then i created SPN for all the three instances on Node1, and also for the AGs:
setspn -S MSSQLSvc/Node1.energycorp.com:56927 ENERGYCORP\Node1_MSS_Srv
setspn -S MSSQLSvc/Node1.energycorp.com:57849 ENERGYCORP\Node1_MSS_Srv
setspn -S MSSQLSvc/Node1.energycorp.com:58051 ENERGYCORP\Node1_MSS_Srv
SetSPN -s "MSSQLSvc/AG1.energycorp.com:1433" ENERGYCORP\Node1_MSS_Srv
SetSPN -s "MSSQLSvc/AG2.energycorp.com:1433" ENERGYCORP\Node1_MSS_Srv
SetSPN -s "MSSQLSvc/AG2.energycorp.com:1433" ENERGYCORP\Node1_MSS_SrvI also found the service account in AD, and added delegation access for that account.
Specified services only
Kerberos only
I clicked add, and searched for the service account .
I added all the SPN's that i created (three for the node1 and three for the AG)Now when running the query I get another error message:
OLE DB provider "SQLNCLI11" for linked server "AG2" returned message "Invalid authorization specification".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI11" for linked server "AG2" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "AG2".I hope someone can see this in a more clearer light that I can.
Please download this tool https://www.microsoft.com/en-us/download/details.aspx?id=39046 and make sure SPN's are all set. Your set up is little tricky, let us know if you are still running into issues.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply