2

We have an SQL server instance running on a standalone server (not part of the organizations domain network). Is there any way that we can use windows authentication to logon to sql server without having to add the remote server to the domain. I hope my question is clear, what is want is to be able to logon to sql server using windows authentication from my client, but I am not able to add windows users in sql server because the server is not part of the domain and so cannot identify the domain users that I am trying to add. Is there any way to get around this?

Thanks in advance.

Improve this question
1
  • You can setup an account with the same name as your domain user and it can use Windows authentication. However it will not be as safe as having a Kerberos ticket used. You will actually leak your domain password (hash) to that server. It’s better to use sqlauthentication with a totally unrelated username and password.
    – eckes
    Feb 19, 2019 at 3:13

3 Answers 3

Reset to default
1

Plan answer: No.

Let me explain:

Windows Authentication is just that, authenticating with a Window's login. So if you are dealing with a workgroup computer where you are on a domain it will not work, because that server can only authenticate a local account.

If you are talking about connecting with SSMS, you can try the runas coomand through command prompt and use the "netonly" parameter. I use this for connecting from workgroup to domain.

Improve this answer
4
  • Thanks for the reply. Is it possible to add the server to the domain but prevent users from being able to logon to the server using say RDP with their domain credentials. If we manage to add the server to the domain, we still want the users to only logon to sql server using ssms clients but not to the server directly.
    – user38729
    Oct 27, 2014 at 12:00
  • @user38729 You should be able to manage RDP credentials separately from SQL Server. Every time we bring a new SQL Server online, I can always connect to SQL Server, but I need to ask for remote desktop privileges. (I am not sure if that's how it works by default, or if we have group policy that prevents RDP, but you certainly can prevent users from RDPing.)
    – Aaron Bertrand
    Oct 27, 2014 at 13:12
  • @user38729 Having installed several machines, yes it is very easy to manage. Simply allow for tcp connections to port 1433, enable SQL Server Browser service, and add a user in the SQL Server instance that is allowed for remote logon to SQL server. You don't need to touch the RDP settings as it should be denied by default, however you could specifically deny users to remotely logon to the machine to be sure.
    – Reaces
    Oct 27, 2014 at 13:23
  • @user38729 That is something to speak with your AD administrators about. Terminal Services access (RDP) is something that is controlled by AD Group Policies. The default domain policy will be applied to the server when it is first added to the domain. Generally most AD administrators are going to place a server into an OU (e.g. like folder) that will have additional group policies to modify access. You will need to speak with them on what policy they have and what you need.
    – user507
    Oct 27, 2014 at 16:02
1

In short, no it won't work.

There are two types of windows authentication, local and domain. Seeing as how you're asking about your domain logon, that's clearly the second kind.

When you try to authenticate to a domain logon, the local machine will go up the chain in its domain group, and ask for a domain controller to authenticate your credentials. Seeing as how the SQL server is in a workgroup, it doesn't know your domain controllers exist, let alone how to validate your credentials.

However, if your only concern is using windows users to connect to the SQL server, not necessarily using your own credentials (IE you don't want to use SQL users). This can be achieved using runas and a local user on the SQL machine. MSSQLTips post about this.

Improve this answer
0

Set the 'server name' to be the name of the computer and select Windows Authentication then this should log you on locally.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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