AlwaysOn and Error 41006 (and error 41158)

When setting up AlwaysOn Availability Groups you may receive Error 41158 which references error 41006 when you attempt to join the the replica to the Availability Group.  What these errors in a nut shell mean is that it ain’t going to work with your current configuration.

Assuming that you ran through your SQL Server installation and went next, next, next through the install this result is to be expected.  The reason for this is that your SQL services are all running under local accounts which don’t have the ability to log into each other.  There’s two solutions to this problem at this point.  1 is supported, the second isn’t.

Option #1 – aka. The Supported Option

Reconfigure the SQL Services which will be hosting the Availability Group Replicas to run under a single domain account.  Restart the services.  Give the SQL Account that the services are running under sysadmin rights.  The replicas should sync up automatically at this point.  If they don’t you can use the ALTER AVAILABILITY GROUP command to join the AG.

Option #2 – aka. Totally Unsupported, but works great for a demo

Add the domain computer account for each of the nodes of the cluster to each others SQL instance so that they can log in.  For example the four computers which I use for my demo are called ALWAYSON1, ALWAYSON2, ALWAYSON3, and ALWAYSON4.  So on machine ALWAYSON1 I added the domain accounts BACONALWAYSON2$, BACONALWAYSON3$, and BACONALWAYSON4$ as members of the sysadmin fixed server role (again this is for my demo lab so I’m going for working not secure).  On machine ALWAYSON2 I add BACONALWAYSON1$, BACONALWAYSON3$, and BACONALWAYSON4$ and so on for machines 3 and 4.  Once that was done the replication should being syncing up automatically.  If they don’t either use ALTER AVAILABILITY GROUP or use the UI to force retrying.



Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?