Connect to SQL Server with Windows Authentication in a different domain

questions tips
,
1

(Title lifted from the dba.stackexchange question!)

At my home/office I run Windows 2012R2 in a Virtual Machine which hosts various SQL servers and databases.
I do my Clarion and other development either on the Windows 10 desktop host or in a virtual machine.

Ever since MS introduced the Sign in with a Microsoft account feature it has been a real pain to work with my SQL Server using “Windows Authentication” to connect. (You cannot use “Trusted Connection” from the dev machine to connect to SQL since you are not connected on the same domain. SSPI errors, etc, blah, blah.)

I love the Sign in with a Microsoft account, it makes life a lot easier with multiple desktops VMs and other such things so I don’t want to give it up. Having to workaround the “Windows Authentication” thing is annoying.

The only solutions/workaround (other than running a full domain on the network, too much sys admin, etc) were either:

  • Use sql auth (user/pass)
  • Use runas.exe with the /netonly switch to fake it.

Both options are not awesome and obviously make it impossible to test “Windows Authentication” related connectivity scenarios unless you drop into an account that connects on the work group i.e. not Sign in with a Microsoft account!

I had heard about using the Credential Manager to connect but was never able to get it to work.

Like magic, I found this today:

Which explains it in a way that I paid attention to. Light and frikin day! I can now do dev and testing properly. Ohhhhhh, so good.

Hopefully this helps someone in a similar situation or is at least of interest to any of you people!

Oh… the only downside is that you need to know the port the SQL server is running on. This is not so bad.

In case you are using dynamic port you can use this little snippet to find out what is currently in use:

USE master
GO
xp_readerrorlog 0, 1, N'Server is listening on' 
GO
1 Like
2

thanks for sharing! :+1:

3

Brahn,
Not sure if this will help but will describe briefly what i do in my environment.

I have my clarion dev environments running on 2 different vm’s.

I keep sql installed once on the host o/s. Windows 7 64 bit. All 3 machines have the same windows username and password. To get this to work (i think) i also have to have a permanent file share setup on the vm’s, back to the host. I then connect via Windows authentication when testing. No good for production but works just fine for testing.

For real world situations Microsoft SQL has (in SQL Server Management Studio) Server Objects --> Linked Servers. I have a production example of this setup on a client site…

Let me know if you need assistance sorting this one out, happy to assist.

4

Yeah, this stops working when you are logging in to your windows machine (from Win8 and up I think it is) using a microsoft account.

5

prehaps no.

In my current clarion development environment, the host is a windows 7 64 bit hardware platform, the vm’s are windows 8. I also have same setup on a windows 10 64 bit box working nicely.

6

You are effectively all in the same domain on all the machines/logins so yeah, that works.

The scenario I am referring to in the original post is when you have networked logins on a domain that is different to the one the SQL Server is running on. It’s certainly only a development/testing situation. In production you would be dealing with proper domain integrations of course!