Getting Windows Authentication to Work Without a Domain

Published On: 2014-09-17By:

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

Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via