Do I need to create user accounts on SQL Server before I can use Windows authentication?

[PaulHuffman]

Still working on getting Server 10.3.1 working with SQL Server 2012 R2 on Windows Server 2012 R2.  I wanted to use Windows authentication for user connections,  DB authentication for SA and SDE.  Do I need to first add users and their passwords to SQL with SSMS or the Create User tool?  What happens then when a user changes his Windows password.  No domain at this location. https://technet.microsoft.com/en-us/library/ms144284.aspx

[JakeSkinner]

Hi Paul,

Yes, the user will need to exist in the SQL Server instance and assigned to the correct database with 'Connect' permissions.  I haven't tested a local user account, but a domain user account is not affected when the password is updated.

[PaulHuffman]

Thanks.

But when I got started with this in SSMS, I got "huf******* is not a valid Windows NT name.  Give a complete name :<domain\username>. Microsoft SQL Server, Error 15407)  "   I'm not running a Windows Domain at this location.  I've tried adding my Workgroup e.g. workgroup.local\huf*****, but haven't guessed at the correct syntax.  Is it possible to have Windows authentication without a domain?

I tried an idea to create a login with the same password on the SQL machine as suggested at  http://dba.stackexchange.com/questions/8151/windows-authentication-for-a-non-local-non-domain-login    But got the same error message.

[PaulKroseman]

I think what the stackexchange post is asking is to create the user as a local Windows user on the server where SQL resides. Add this user (servername\username) to the SQL instance security. Open the Credential Manager on the client computer and add a Windows Credential for the server username.

I only use domain authentication and have no idea if what I said is correct. Having a domain makes this infinitely easier.

[MichaelHewitt]

I have had the same issue since I converted my computer from Windows 7 to Windows 10.  My microsoft account took over.  I found a way to convert my microsoft account back to a local account. 

From the start menu choose Settings>Accounts>Your Account.  Choose the "sign in with a local account instead" link.  Window will convert the account to a local account then associate your microsoft account with it.  The "new" account has a normal username that you can use in Active Directory and SQL.