4

My database client and database server are on different machines and are not connected to the same domain (this is not supported with my web host). I'm not keen to send username/password details via a connection string and want to use windows authentication to connect to the database.

In this article's overview, it mentions this (emphasis mine):

When you use Windows authentication to connect to SQL Server, you use either Kerberos or NTLM authentication depending on the configuration of your servers and domain. You might not be able to use Kerberos authentication if:

  • Your database client and database server are separated by a firewall which prevents Kerberos authentication.

  • Your application server and database server are in separate domains with no trust.

In these situations, you can use either mirrored local accounts or SQL authentication. With mirrored local accounts, you configure two accounts on each server with identical user names and passwords. You must ensure that the passwords remain the same.

I'm assuming this just means creating a windows user with the same username and password on both machines, however I'm skeptical this would work. When I'm adding permissions for this user on the database client, it prefixes the username with the servername, and the same thing happens when creating a database login on the database server.

Is this what it means to create a mirrored local account? If so, how does this work considering the users are in different domains?

5

You've understood the process correctly. You need to set up two user accounts. One would be created on the SQL Server host, the other one would be created on the client you want to connect from. Their usernames and passwords should be identical.

SQL Server prefixes the username with hostname or domain name, however that is not a problem, Windows Authentication mechanism correctly resolves the username part and authenticates the other user if the password of the other user is identical.

2
  • What if the client's account is a domain account rather than a local account, but with the same username and password as the local account on the SQL Server host?
    – Ronnie
    Jun 9 '16 at 15:30
  • Afair, this schema works for that scenario too. But currently I do not have a setup to verify. Jun 9 '16 at 18:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.