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.

  • 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 at 3:13
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.

  • 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 '14 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 '14 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 '14 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. – Shawn Melton Oct 27 '14 at 16:02
  • Thanks a lot for all your replies, your answers will help me decide on how to go about doing this. – user38729 Oct 28 '14 at 11:47
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.

0

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

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.