(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