SQL Data Compare 11

Using Windows authentication logons between domains

SQL Compare and Data Compare make use of the built-in authentication mechanisms provided by Windows and are therefore limited to the Active Directory domain security model. If you wanted to compare schema or data across domains, for example, you could use SQL authentication accounts like SA. When choosing Windows Integrated authentication, the current desktop user's credentials are used to authenticate to the SQL Server so you never get the opportunity to enter a Windows username and password. If the SQL Server's Windows domain is different than the user logged into the desktop, then the SQL Server's Active Directory server would normally be unable to verify the identity of the logged-in user on behalf of the SQL Server.

If it's absolutely necessary to use Windows accounts to log into the SQL Server, for instance if the SQL Server is not configured to allow SQL logins and passwords, there are two possible workarounds to allow login credentials from one domain to be used in another. The first option could be to create a trust between the two domains. It's unlikely a network administrator would do this for you unless you're really, really nice to them, then maybe not even then, because of the security considerations and management overhead of a domain trust. The second option is called matching accounts - if you create a local Windows user on the SQL Server computer and on the local workstation, making sure to grant this account access to the SQL Server, and these two accounts share the same username and password, logging into the local workstation and connecting SQL Compare or Data Compare using Windows authentication will work. You can also try using the Runas command from the Windows command line.

As long as the SQL Server allows Windows accounts to authenticate using NTLM authentication, all that is necessary to allow access to the SQL Server is a token constructed using the Windows user name and password. If the usernames and passwords of two local Windows accounts share these attributes, then the hash will be accepted by both computers and successful authentication will occur.

Didn't find what you were looking for?