Connect to SQL Server using windows authentication from another PC without Active Directory

Question

I have SQL Server 2008 R2 Express installed on a PC.

I have some other PCs that are connected to this PC on a local network.

Currently, those PCs connect to SQL Server using SQL Authentication, however I want them to connect to SQL Server using Windows Authentication.

We don't have an Active Directory domain. All PCs are part of the "WORKGROUP" workgroup.

Answer 1

If none of the machines involved have Active Directory authentication enabled via Kerberos, which precludes using an online-based Microsoft Account (Windows 8 and higher), you should be able to connect to SQL Server using Windows Authentication assuming you follow these points:

1. Ensure SQL Server is configured to use TCP connections.
2. Ensure the Windows firewall is either disabled or has SQL Server connections allowed.
3. The machine where SQL Server is installed needs Windows user accounts for all the people who will be connecting via the network. For instance, if the SQL Server computer is named MyPC, and you have User1, User2, and User3 who will be using the SQL Server, MyPC must have accounts setup for User1, User2, and User3 with the same password those user accounts use on their own PC.

4. You create logins in SQL Server for those user accounts, i.e.:

   CREATE LOGIN [MyPC\User1] FROM WINDOWS;
   CREATE LOGIN [MyPC\User2] FROM WINDOWS;
   CREATE LOGIN [MyPC\User3] FROM WINDOWS;
   

5. The client machines may need to connect to the server using the server's IP address instead of it's name. If the server's IP address is 192.168.0.100, and it is listening on port 33333 then they would use this as the server name to connect to:

   tcp:192.168.0.100,33333
   

If you follow all the above advice, then receive the following error when connecting, then the client PC is using Kerberos authentication, and nothing will help you connect.

The target principal name is incorrect. Cannot generate SSPI context.