SQL Server – Connect with Windows Authentication Across Different Domains

authenticationsql server

I get we have two options while connecting to remote SQL Servers or other domains with Windows authentication:

  1. runas /netonly method
  2. adding windows credentials

But doesn't Windows authentication mean that the user who logged in to Windows has authentication to use SQL Server?

So since I am not an authorized login or user in remote servers, I mean I am not a Windows user on their machine, how could I connect to their servers?

So everyone who knows my domain name and instance name can connect to my machine.

What about security? Do we have to add other Windows users or machines to connect to our SQL Server?

I believe we must add other machines to our domain or we must add other domains as trusted to our domain.

Please help; what is the point I missed?

Best Answer

When you login to a computer you can do so in the following manner:

  • login to the computer with a user that is stored on the computer (e.g. COMPUTER_1\USER_3)
  • login to the computer with a user that is stored in the active directory of the domain you are using (e.g. DOMAIN_1\USER_1)

When the login is complete you have verified the authenticity of your account (USER_n) against either the computer (COMPUTER_1) or against the Active Directory domain (DOMAIN_1).

Environment

Now let's say we have the following information based on your questions:

  • first domain user account USER_1
  • second domain user account USER_2
  • first computer user account USER_3
  • second computer user account USER_4
  • first computer COMPUTER_1
  • second computer COMPUTER_2
  • first server SERVER_1
  • second server SERVER_2
  • first domain DOMAIN_1
  • second domain DOMAIN_2

Next I am assuming the following:

  • USER_1, COMPUTER_1 and SERVER_1 are members of the DOMAIN_1
  • USER_2, COMPUTER_2 and SERVER_2 are members of the DOMAIN_2
  • USER_3 is a user account on COMPUTER_1
  • USER_4 is a user account on COMPUTER_2

Scenario 1:

You login to COMPUTER_1 using the account USER_3 (computer user account). When you try to connect to the SERVER_1 using Windows Authentication you are denied access.

Why?

The COMPUTER_1\USER_3 local Windows account cannot be added to the SQL Server as a login on SERVER_1. Only SERVER_1\USER_n accounts could be added as SQL Server Logins to SERVER_1 or DOMAIN_1\USER_n accounts. SQL Server is unable to find COMPUTER_1\USER_n accounts when creating Windows Authenticated SQL Serer Logins.

Scenario 2

You login to COMPUTER_1 using the account DOMAIN_1\USER_1 (Domain user account). When you try to connect to the SQL Server SERVER_1 using Windows Authentication you are granted access.

Why?

DOMAIN_1\USER_n accounts can be added as Windows Authenticated SQL Server Logins to the SQL Server instance running on SERVER_1 and then granted access to the SQL Server. SQL Server will determine that you (DOMAIN_1\USER_1) are a valid Domain user and will allow you to connect to the SQL Server (assuming you have previously assigned these permissions to the account on your SQL Server).

Scenario 3

You login to COMPUTER_1 using the account DOMAIN_1\USER_1 (Domain user account). When you try to connect to the SERVER_2 (remember server is in DOMAIN_2) using Windows Authentication you are most likely denied access.

Why?

DOMAIN_1\USER_n accounts cannot be added as SQL Server Logins to the SQL Server instance running on SERVER_2. Unless you have a trust between the domain DOMAIN_1 and DOMAIN_2. The SQL Server instance on SERVER_2 will be unable to verify that you (DOMAIN_1\USER_1) is a valid Domain login and will deny you access SQL Server.

Summary

When you login to a computer you have only verified your authenticity towards either the computer or to the domain the computer belongs to. Any other permissions (SQL Server access, Windows Server access) depend on the rights assigned to your account either in the domain or on the target system. If a system is out of scope of the DOMAIN_1 or SERVER_1, then you will be unable to access these servers with Windows Authenticated SQL Server logins.

If a user is trying to connect to a SQL Server in a non-domain environment then you will possibly be better off with dedicated Native SQL Server Logins instead of Windows Authenticated SQL Server Logins. When you want to connect to the SQL Server you will have access via the SQL Server login.

This is a very short basic summary of how authentication works.

Related Question