| SQL Studio Data Compare 
Version: 2.3
| Question:
Why sometimes I cannot perform compare when using Windows authentication? Answer:
In order to provide the best performance SQL Studio Data Compare performs the compare process on the server side. That way no data is transferred to the client and the full power of the server engine is utilized. When the compare involves two different servers SQL Studio Data Compare creates a linked server for the second SQL Server instance.
If Windows authentication is selected on the connection wizard then certain conditions have to exist:
- Security account delegation is available on the client and sending server.
- The provider supports Windows Authentication Mode; for example, SQL Server running on Windows.
Since the credentials of the host SQL Server service account are used for the linked server it might fail if the service does not meet the above conditions. Most commonly the user will see the following error:
Login failed for NT Authority\anonymous login
For more details on linked servers and their implementation in Microsoft SQL Server see the following MSDN article:
http://msdn2.microsoft.com/en-us/library/ms189811.aspx
To resolve the issue use SQL Server authentication or use the following articles to configure delegation:
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx
http://support.microsoft.com/kb/925001
http://www.sqlmag.com/Article/ArticleID/23471/sql_server_23471.html
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2gmm.asp
More information on Kerberos and NTLM authentication in SQL Server connections is available here:
http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx
|