SQL Server with Mr. Denny

Sep 17 2014   4:00PM GMT

Getting Windows Authentication to Work Without a Domain

Denny Cherry Denny Cherry Profile: Denny Cherry

Active Directory
SQL Server
Windows authentication
Windows Workgroups and Networking
Workgroup Servers

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 SQL1\dcherry 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.


 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: