Not every company has Active Directory setup to give them a centralized way of managing authentication. For those who manage SQL Servers within a Workgroup getting authentication to work correctly with Windows logins can be tricky if you haven’t done it before.
In order to get Windows logins working correctly the trick is to create a local account on both the machine which you log into and the SQL Server with the same username and the same password. Once that is done you can grant permissions within the SQL Server instance to the local account.
So if your workstation is PC1 and your SQL Server is SQL1, then within the OS of SQL1 you create a login named dcherry. Then within SQL you create a login for SQL1dcherry and give it the rights which are needed. Now in PC1 create a login called dcherry. Now log into PC1 as dcherry, open SQL Server Management Studio, and you can connect to SQL1 using your Windows account.
Now if you need to change your password for any reason, you’ll need to log onto SQL1 and change your password there as well as the passwords on both machines must be the same.
If you’ve got even a few computers I’d recommend looking at getting Active Directory up and running as it’ll make your life easier in the long run as you don’t have to do all this to get Windows Authentication working.
Denny
