SQL Server 2014 – Adding a Local User Account

authenticationconnectivitysql serversql server 2014

I have a (developer) instance of SQL Server 2014 running in my machine. For quick background I'm on a small network like this:

  • Workgroup: HOME
  • Users
    • HOME\PCA
    • HOME\PCB
    • HOME\PCC

I'm HOME\PCA. Now I was trying to see if I can make the other machines connect to my instance using an application I'm working on. Port 1433 is working fine and if I go to PCB or PCC and test to create a new ODBC connection I successfully reach the instance.

When in SSMS trying to add HOME\PCB or HOME\PCC It will just deny and tell: Windows NT user or group 'HOME\PCB' not found. Check the name again. Checking in my Security/Logins I see that I am PCA\DevMachine (DevMachine) being my Windows login. If I try yo 'Search' in the 'New Login' dialog the only Location available is PCA.

I don't have a lot of experience in SQL Server permissions but my assumption is that it just wont let the other computers be added to my SQL Server instance because it takes that PCA is the server and so PCB and PCC should lay under PCA\PCB sort of scheme.

Is this anything close to the cause of this behavior? or, is there anything I'm missing that is out of the obvious (All network cables are plugged)?

Regards

Best Answer

This has nothing to do with SQL Server, is just basic Windows authentication. In a workgroup you can only authenticate using NTLM mirrored accounts. Mirrored accounts are local accounts that have identical name and and password on different machines.

So to use Windows authentication between a process running on machine A and a process running on machine B, in a workgroup, you must:

  • create a local user A\foo on A
  • create a mirrored user B\foo, on B, that has the same password as A\foo
  • run the client process as A\foo on A
  • grant permissions to B\foo on B

I strongly suggest you deploy a proper domain and use AD instead.

Related Question