My computer is not part of a domain., but rather a member of a workgroup. You can still use Windows Authentication for things like file sharing and accessing Microsoft SQL Server instances. All you have to do is use ‘NT Account Mirroring’. Basically, if you have two computers, the server “SQLSERVER” and the laptop “MyPC” you create Local User accounts on both machines. The accounts would have the exact same user name and password but the ‘domain’ would be each computers computer name. For example:
- SQLSERVER\Brad
- MyPC\Brad
When you connect using Windows Authentication even though the ‘domain’ (I hate how it is still referred to as domain even though it is NOT a domain) doesn’t match since the username and password do match Windows authenticates you through.
I recently installed Windows 8 on my laptop and provisioned a Hyper-V Windows Server 2012 (SQL Server 2012) VM. I was trying to connect to my server via SQL Server Management Studio and was getting this strange error: “Login failed. The login is from an untrusted domain and cannot be used for Windows Authentication”
So, I opened up my Local Accounts on both my Windows 8 machine and the Windows Server 2012 machine. Looking at the Local User accounts I can see that there is in fact a mirrored account “Brad” on both machines.
I went ahead and made sure that both accounts were using the exact same password. Still no luck, getting the same error.
I decided to dig more into my Windows 8 account. From the Metro interface swipe the right side of the screen and go to Settings –> Click Change PC Settings at the bottom of the screen –> Select Users
Right away I noticed a big button “Switch to a Local Account”
Wait a minute, I’m not on a domain, what other kind of account is there? It turns out that when you setup Windows 8 it creates a Microsoft Account which allows you to sync your photos, bookmarks, and other settings, to SkyDrive. In doing so, it makes some kind of unique Windows 8 account type. What is weird is, if you look at the first screenshot of the error in SQL Server Management Studio the account name that was being populated as “Windows Authentication” was showing as Brad-XPS14Z\Brad which is what my Local User account name is (or would have been).
I clicked the button to switch to a local account. It warns you to save your work as you’ll have to logout / login.
After the logout / login I tried launching SQL Server Management Studio again. This time when I clicked Connect (still using Windows Authentication) it logged me right in!
It turns out the problem was with that new Windows 8 “Microsoft Account”. Even though it looked like I had and was using the same Local Account it wasn’t. By the way, when I switched from Microsoft Account to Local Account I didn’t lose anything. My profile is exactly the same so my favorites, Start tiles, etc all remained exactly as they were.