Brigando com o erro 18456, Login failed for user ANONYMOUS LOGON (Além do SPN)
Pois é… este problema vive me perseguindo! No entando, como eu já disse em outros artigos, este é um problema comum em ambientes que utilizam “Windows Authentication“. Bom, como sempre, na busca da solução para mais este problema segui meu checklist básico e desta vez notei que o problema não estava associado com o registro dos SPNs dos servidores.
Como os SPNs estavam registrados corretamente e não existia duplicidade, verifiquei então se a porta que estava registrada no SPN(1433) era mesmo a porta que estava sendo utilizada pelo serviço do SQL Server (sp_readerrorlog)… Constatei então que era!
Bom, não sendo problema de registro de SPN, resolvi então utilizar o utilitário DTCPing (How to troubleshoot connectivity issues in MS DTC by using the DTCPing tool) para ver se o problema poderia estar no MSDTC.
Utilizando o DTCPing (veja o Readme.txt do utilitário) nas duas máquinas que estavam sendo usadas no uso do linked server, o mesmo me gerou um arquivo de log em cada máquina e verificando o arquivo em uma das máquinas obtive o seguinte output…
++++++++++++++++++++++++++++++++++++++++++++++
DTCping 1.9 Report for SQL01
++++++++++++++++++++++++++++++++++++++++++++++
RPC server is ready
++++++++++++Validating Remote Computer Name++++++++++++
01-12, 11:23:02.076–>Start DTC connection test Name Resolution:
SQL02–>192.168.10.25–>SQL02.contoso.com.br
01-12, 11:23:02.201–>Start RPC test (SQL01–>SQL02) RPC test is successful
Partner’s CID:39D453F2-0C4F-4179-887A-571C918F9E6E
++++++++++++RPC test completed+++++++++++++++
WARNING:the CID values for both test machines are the same while this problem won’t stop DTCping test, MSDTC will fail for this
01-12, 11:23:11.841–>RPC server:SQL01 received following information:
Network Name: SQL01
Source Port: 4726
Partner LOG: SQL023216.log
Partner CID: 39D453F2-0C4F-4179-887A-571C918F9E6E
++++++++++++Start Reverse Bind Test+++++++++++++
Received Bind call from SQL02
Network Name: SQL01
Source Port: 4726
Hosting Machine:SQL01
01-12, 11:23:12.028–>Trying to Reverse Bind to SQL02…
Test Guid:39D453F2-0C4F-4179-887A-571C918F9E6E
Name Resolution:
SQL02–>192.168.10.25–>SQL02.contoso.com.br
Reverse Binding success: SQL01–>SQL02
++++++++++++Reverse Bind Test ENDED++++++++++
01-12, 11:23:12.028–>Called POKE from Partner:SQL02
Network Name: SQL01
Source Port: 4726
Hosting Machine:SQL01
++++++++++++Start DTC Binding Test +++++++++++++
Trying Bind to SQL02
01-12, 11:23:12.028–>SQL01 Initiating DTC Binding Test….
Test Guid:39D453F2-0C4F-4179-887A-571C918F9E6E
Received reverse bind call from SQL02
Network Name: SQL01
Source Port: 4726
Hosting Machine:SQL01
Binding success: SQL01–>SQL02
++++++++++++DTC Binding Test END+++++++++++++
Hummm… acho que o WARNING já diz tudo certo? Anotei então o Partner CID retornado (em verde no output acima) e verificando o output do arquivo de log da segunda máquina (SQL023216.log), foi possível notar que o CID das duas máquinas realmente eram iguais…
++++++++++++++++++++++++++++++++++++++++++++++
DTCping 1.9 Report for SQL02
++++++++++++++++++++++++++++++++++++++++++++++
RPC server is ready
WARNING:the CID values for both test machines are the same while this problem won’t stop DTCping test, MSDTC will fail for this
01-12, 11:23:02.260–>RPC server:SQL02 received following information:
Network Name: SQL02
Source Port: 4044
Partner LOG: SQL013408.log
Partner CID: 39D453F2-0C4F-4179-887A-571C918F9E6E
++++++++++++Validating Remote Computer Name++++++++++++
01-12, 11:23:11.776–>Start DTC connection test
Name Resolution:
SQL01–>192.168.10.24–>SQL01.contoso.com.br
01-12, 11:23:11.854–>Start RPC test (SQL02–>SQL01)
RPC test is successful
Partner’s CID:39D453F2-0C4F-4179-887A-571C918F9E6E
++++++++++++RPC test completed+++++++++++++++
++++++++++++Start DTC Binding Test +++++++++++++
Trying Bind to SQL01
01-12, 11:23:12.057–>SQL02 Initiating DTC Binding Test….
Test Guid:39D453F2-0C4F-4179-887A-571C918F9E6E
Received reverse bind call from SQL01
Network Name: SQL02
Source Port: 4044
Hosting Machine:SQL02
Binding success: SQL02–>SQL01
++++++++++++DTC Binding Test END+++++++++++++
++++++++++++Start Reverse Bind Test+++++++++++++
Received Bind call from SQL01
Network Name: SQL02
Source Port: 4044
Hosting Machine:SQL02
01-12, 11:23:12.338–>Trying to Reverse Bind to SQL01…
Test Guid:39D453F2-0C4F-4179-887A-571C918F9E6E
Name Resolution:
SQL01–>192.168.10.24–>SQL01.contoso.com.br
Reverse Binding success: SQL02–>SQL01
++++++++++++Reverse Bind Test ENDED++++++++++
Bom, diante disso bastou alterar o CID para uma das máquinas e o LS passou a funcionar! Para aqueles que estão se perguntando como fazer para alterar o CID, dê uma olhada o arquivo HowtoAnalyze_Dtcping_Output.txt que acompanha o pacote de download do DTCPing. Nele você encontrará o seguinte…
If DTCPing returns an error message similar to the following:
Unexpected: My session guid is same as partner’s guid
check whether the current server has been duplicated or cloned from the other server. If so, locate the HKEY_CLASSES_ROOTCID key in the registry. Under this key, you may notice more than one GUID. Locate the GUID whose underlying Description key is MSDTC. Note that this GUID is also listed in the DTCPing output window. If the other server has a GUID that is exactly the same for MS DTC in its registry, you must create a new GUID for MS DTC in one of the registries. You can use GuidGen to do this.
After you add this new GUID, and also all of its underlying keys to HKEY_CLASSES_ROOTCID, make sure to delete the old GUID that it is replacing.
If this step resolves your problem, it is highly recommended that you read the following article to learn more about duplicating (or “ghosting”) computers: Do Not Disk Duplicate Installed Versions of Windows
Há… as máquinas em questão são máquinas virtuais, ou seja, fazer clone das VMs podem gerar este tipo de problema, portanto, se seu ambiente possui máquinas SQL virtuais, fique atendo à duplicidade dos CIDs.
Abraços
Nilton Pinheiro
|
|
Fonte: Nilton Pinheiro