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?
Best Answer
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.