4

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?

2

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.

  • +1 Thanks for plain and smooth explanation. I really understood the whole mechanism now. I was not aware of active directory for managing these authorization issues. – UfukSURMEN Jan 4 '17 at 15:33
2

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

Generally yes, but more specifically it depends on how "Windows" is defined in this context. In your scenario, the Windows account / login exists in one Domain, and hence will have access to the resources in that Domain. But having access to that Domain doesn't magically grant access to all other Domains. If it did, then you would be able to connect to all other Windows servers that are accessible from the one that you authenticated against, even across the interwebs.

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

You are correct: you must add a trust relationship between the Domains so that the "other" Domain knows that it can trust authenticated logins from the Domain that you are currently on.

The following resources will explain this trust relationship, the various options for trust relationships, and some provide details on how to establish a trust relationship between domains (some of the documentation is written for Windows 2000 but at least one states, at the top, that it is still relevant, at least through Windows Server 2012):

  • You are right. The confusing part was to distinguish Domain, and Machine difference actually. These terms are being used interchangebly , which makes it hard to get the context, as you mentioned. So all these Authorization issues are related with Active directory right? As far as I understand In order to understand authentications and authorization I must learn active directory first ? – UfukSURMEN Jan 4 '17 at 16:22
  • 1
    @UfukSURMEN "Active Directory" is Microsoft's directory service. Look at this comparison to Novell's directory service: Comparing Microsoft Active Directory to Novell's NDS. But "Domain" is a logical grouping of "Machines", better than the old Windows "Workgroups". A "Domain Controller" is a central authority for authentication and info. Also look at: Workgroup or Active Directory?. – Solomon Rutzky Jan 4 '17 at 17:10
  • I see.Thank you very much for you support. I get I have so much to learn about. – UfukSURMEN Jan 4 '17 at 17:15
0

There are things like Group policies in windows,so the user to be able to access your sql server or windows, needs to be added into a particular group of database users first for that particular domain maintained by the respective organization.

Apart from that most of the times a jump box is provided by the organization with limited access permissions to windows features which enables DBA's to run clients such as Sql server management studio for example and do the routine tasks as per his /her role ,mostly multiple instance nodes are registered in sql server and thus a user can connect to it via ssms by just providing the node or instance name. Hope this helps.

  • Hi Shivam, So The User who is gonna connect to my Sql Server must be added as a valid windows user or must be added into a group of users in group policies. But these users must be also added as a new database user in sql server security. But Mainly Jump servers are being used for this? Did I Understand right? thanks – UfukSURMEN Jan 1 '17 at 18:27
  • Yes you got the first part right,and a jump server is nothing but a system within a domain with minimum required programs installed & limited access permissions provided to the user to connect to other production servers easily it works as a medium between you & the client destination servers. For example: There are 10 servers of customer that you have to manage,so the customer will not allow direct RDP to those servers from you,they will provide a jump box & allow direct RDP from your company only to that jump box using a secure tunnel,to access all other nodes you have to use jump. – Shivam Kumar Jan 2 '17 at 12:43

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.