SQL 2012 BPA error- 'Login does not exist on SQL Server OR Login is not a member of the Systems Administrator role'

  • Article

I was recently trying to use the diagnostic tool Microsoft SQL Server 2012 Best Practice Analyzer on my customer's machine that was getting ready for a migration.

·        Using this tool a DBA can determine whether the SQL Server is configured as per the best practices recommended by the SQL Server Products Team.

·        This tool validates your instance of SQL Server with certain built-in rules that can help you rectify common installation and configuration issues.

 

 

While this is a great tool that generates a ready report for us, we were not able to get it running on a default cluster instance.

We were able to successfully generate reports on a Standalone instance  and a named cluster instance without issues but the default cluster instance was throwing an error " Login does not exist on SQL Server OR Login is not a member of the Systems Administrator role"

 

We made sure we belonged to the local windows administrator's group on both nodes of the (active/passive) cluster – both as an individual login and as a member of a domain group.  The AD account was in the SQL sysadmin group too.

We have couple of online forums and blogs suggesting the modification of Engine.ps1 file but they didn't work for us and we ended up with the same error.

Sharing what finally worked for us and allowed us to generate a report

 

Workaround:

===========

  1. Open up the file Engine.ps1 to modify

Should be found in %Programdata%\Microsoft\Microsoft Baseline Configuration Analyzer 2\Modules\SQL2012BPA\Engine

2. We added this $SQLInstanceID =”MSSQL11.MSSQLServer” just below the line $SQLInstanceID = $SQLInstallPath[3] to make this work.

3. Run the Best Practice Analyzer GUI without providing the name of the Instance (leave it blank)

 

Hope this  simple solution saves some valuable time for someone attempting to run the BPA on a Default Clustered Instance of SQL 2012

 

Solution Credits to John Hatricks for parsing through the Powershell script to find this solution!