SQL Server 2008 R2 Best Practice Analyzer : problems connecting to sql

Hi all, Has anyone been able to sucessfully deploy and connect to an instance with SQL Server 2008 R2 Best Practice Analyzer ?

I've installed it on the Cluster node directly (which is running windows server 2008) and also installed it on a remote stand-alone machine (running windows server 2003) and still continue to get the error "Login does not exist or is not a member of the Systems Administrator role". I tried creating my login explictly as mentioned here :


and tried to install BPA bypassing the powershell as mentioned here:


I continue to struggle to connect. SQL Server reports no login attempts or failures. Any ideas?

more ▼

asked May 04, 2011 at 06:16 AM in Default

avatar image

217 2 4 9

Adding to this problem. I tried to run BPA using the Powershell command line option mentioned in the BPA White Paper:


Invoke-MbcaModel -ModelId SQL2008R2BPA -SubModelId Engine -ComputerName {computername} -SqlServerInstance {servername} -CurrentLoginName ($Env:USERDOMAIN + "\" + $Env:USERNAME).ToString() -EngineLogFile c:\temp\engine.txt –RepositoryPath ("C:\TEMP\SQL2008" + (Get-Date).ToString("yyyyMMdd")).ToString()

--- To Create Report

model = get-MbcaModel –ModelId sql2008r2bpa $scanResult = get-MbcaResult –ModelId sql2008r2bpa $collectedConfig = get-MbcaResult –ModelId sql2008r2bpa –CollectedConfiguration

$model, $scanResult, $collectedConfig | export-CliXml c:\temp\as.xml

Get-MBCAResult -ModelId SQL2008R2BPA -SubModelId Engine | ConvertTo-Html | Add-Content -Path c:\test.html

To no avail. The error message in the engine.txt file was :

Engine Rules Execution Interrupted - Current User Name is unavailable or is disabled on (COMPUTERNAME)

May 09, 2011 at 11:05 AM vinman2

Found some other info on deploying BPA here: http://feleryan.wordpress.com/tag/sql2k8r2-bpa/

From here I tried to set the execution policy as such;

PS C:\Windows\system32> Set-ExecutionPolicy Unrestricted -f PS C:\Windows\system32> Get-Executionpolicy -list

Scope ExecutionPolicy

MachinePolicy Undefined UserPolicy Undefined Process Undefined CurrentUser Undefined LocalMachine Unrestricted

Still cannot run the scan.

One last thing to note is after running the scan, Under the "Report Type" option in BPA there is a radio button called "Colleted Data". when I clicked this there is an info tree that branches to the line "IsPSRemotingEnable = false"

Here's the screen shot:


I ensure that this is enabled: Enable-PSRemoting -force WinRM already is set up to receive requests on this machine. WinRM already is set up for remote management on this machine.

Any help would be much appreciated.

May 09, 2011 at 11:05 AM vinman2
(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

I managed to get SQL SERVER 2008 R2 BPA working!!!!

2 areas of trouble that I had to pin point by getting into the PowerShell files for BPA!

Those were found in %Programdata%\Microsoft\Microsoft Baseline Configuration Analyzer 2\Models\SQL2008R2BPA

I first attacked the message I was receiving

"Impact: Analysis cannot be performed"

and in the report Type :

" Collected Data" = "IsPSRemotingEnable = false"

Opened up PowerShell (as Administrator) and opened the file SQL2008R2BPA.ps1

Ran through most of the code and isolated to this area

 if ( $Alternate_Server_to_Scan -eq $Env:COMPUTERNAME )
     $Alternate_Server_to_Scan = "localhost"
 $RemoteHostName = icm $Alternate_Server_to_Scan { $env:computername } -ErrorAction SilentlyContinue
 #if ( $RemoteHostName -eq $null )
 #    Get-LogText $cError "SQL 2008 R2 BPA Execution Interrupted - Powershell Remoting is not Enabled on Remote Server '$Alternate_Server_to_Scan' '$RemoteHostName' nn " >> $ModelLogFile
 #    AddElementToDocument $XmlDoc $tns $false "SufficiencyCheck" "IsPSRemotingEnabled"
 #    $XmlDoc
 #    Exit

Running the following code manually

icm $Alternate_Server_to_Scan { $env:computername }

gave me the error:

"Connecting to remote server failed with the following error message : The WinRM client cannot process t he request. Default authentication may be used with an IP address under the following conditions: the transport is HTTPS or the destination is in the TrustedHosts list, and explicit credentials are provided. Use winrm.cmd to configure TrustedHosts. Note that computers in the TrustedHosts list might not be authenticated. For more information on how to set TrustedHosts run the following command: winrm help config. For moreinformation, see the about_Remote_Troubleshooting Help topic. + CategoryInfo : OpenError: (:) [], PSRemotingTransportException + FullyQualifiedErrorId : PSSessionStateBroken "

-----------NOTES ON POWERSHELL-------------

"By default, PowerShell requires Kerberos authentication to operate remotely, so you cannot use it in a simple peer-to-peer scenario. You can also not use it in a cross-domain scenario with untrusted domains. You will need to allow WSMan to use different authentication types to work remotely everywhere. All that is required is to add the IP addresses or computer names of computers you'd like to talk to. Note that this has to be done on both ends. The easiest (and most unsecure) way is to allow communication between any computer by specifying "*":

Set-Item WSMan:\localhost\client\trustedhosts * -force

A more selective approach would use an IP address or computer name instead of "*". Once done, you can use all remote cmdlets to work remotely. Just make sure you use the -credential parameter to enter a User Name and Password for authentication:

Invoke-Command { dir $env:windir } -computer -credential(Get-Credential)"

-----------END OF NOTES ON POWERSHELL-------------

I ran :

Set-Item WSMan:\localhost\client\trustedhosts * -force

Now the error in BPA was back to "Login does not exist or is not a member of the Systems Administrator role" and in the report Type : " Collected Data" -- "SufficiencyCheck" "CurrentUserLoginExistsOnSQL"= false"

(I was running Scan for the SQL Engine btw:)

Now I opened up the file Engine.ps1 found in found in %Programdata%\Microsoft\Microsoft Baseline Configuration Analyzer 2\Models\SQL2008R2BPA\Engine

Followed through the code and isolated this to the following areas of code:

 #Logic changed for Testing Cluster Environment
 if ( $SqlServerInstance -eq "MSSQLSERVER" )
     $EngineServiceName = $SqlServerInstance
     ## Finding Path To Executable
     $SQLExecutablePathName = GWMI win32_service | Where { $_.Name -eq $EngineServiceName } | Select PathName    
     $SQLExecutablePath = $SQLExecutablePathName.PathName.Trim('"')
     $EngineServiceName = "MSSQL$" + $SqlServerInstance 
     ## Finding Path To Executable
     $SQLExecutablePathName = GWMI win32_service | Where { $_.Name -eq $EngineServiceName } | Select PathName    
     $SQLExecutablePath = $SQLExecutablePathName.PathName.Trim('"')
 $StartIndex = $SQLExecutablePath.IndexOf("\Binn")
 $SQLInstallPath = $SQLExecutablePath.Remove($StartIndex).Split("\")
 $SQLInstanceID = $SQLInstallPath[3]
 $HostName = $Env:COMPUTERNAME
 $WOW64RegPath = "HKLM:\SOFTWARE\WoW6432Node\Microsoft\Microsoft SQL Server\" + $SQLInstanceID + "\Setup" 
 if ( Test-Path -Path $WOW64RegPath )
     $IsSQLInWOW64 = $true
 if ( $IsSQLInWOW64 )
     $SqlSetupRegKey = Get-ItemProperty -Path $WOW64RegPath
     $IsCluster = $SqlSetupRegkey.SQLCluster
     if($IsCluster -eq 1)
         # Cluster is not supported in WOW64. Raise the Pre-Req Check
         $IsSQLClusterInWOW64 = $true
     $RegPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\" + $SQLInstanceID + "\Setup"
     if ( Test-Path -Path $RegPath )
         $SqlSetupRegKey = Get-ItemProperty -Path $RegPath
         $IsCluster = $SqlSetupRegkey.SQLCluster
         if($IsCluster -eq 1)
             # This is a clustered Instance so get the virtual name
             $RegPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\" + $SQLInstanceID + "\Cluster"
             $SqlClusterRegKey = Get-ItemProperty -Path $RegPath
             $ClusterName = $SqlClusterRegKey.ClusterName
             # it's a clustered Instance. have to use ClusterName instead of HostName for SMO connection
             $HostName = $ClusterName        


 ## Code For SQL Login / SQL Login Status (Enable or Disabled) ##
 [int] $Count = 0
 [Boolean] $IsCurrentLoginAvailableOnSQL = $false
 $dbMaster = "master"
 $CurrentLoginName = EscapeLiteral $CurrentLoginName
 While($Count -lt $SqlServer.Logins.Count)
     if($SqlServer.Logins[$Count].Name.ToString() -eq $CurrentLoginName)
         $IsSysAdminQuery = "SELECT rol.name, mem.name
                             FROM sys.server_role_members AS srm
                             INNER JOIN sys.server_principals AS rol ON rol.principal_id = srm.role_principal_id
                             INNER JOIN sys.server_principals AS mem ON mem.principal_id = srm.member_principal_id
                             WHERE rol.name = 'sysadmin' AND upper(mem.name) = upper('$CurrentLoginName')"
         $CurrentDB = $SqlServer.Databases[$dbMaster]
         $Dataset = $CurrentDB.ExecuteWithResults($IsSysAdminQuery)
         if($SqlServer.Logins[$Count].IsDisabled -eq $false -and $Dataset.Tables[0].Rows.Count -gt 0)
             $IsCurrentLoginAvailableOnSQL = $true
      Get-LogText $cError "Engine Rules Execution Interrupted - Current User Name is unavailable or is disabled on '$HostName'" >> $EngineLogFile 
      AddElementToDocument $XmlDoc $tns $false "SufficiencyCheck" "CurrentUserLoginExistsOnSQL"

The trouble was not that my login didnt exist in sys.server_principals but that the way this code was setup it was trying to connect to the sql serve instance [node1][instancename] rather than the [sqlclustername][instancename]. So the script never conencts to SQL and fails on this statement "if(!$IsCurrentLoginAvailableOnSQL)"

the culprit was this code:

" $SQLInstanceID = $SQLInstallPath[3] "

Here's the breakdown (assuming my SQL cluster instance is called INSTANCE1:

 PS C:\Windows\system32> GWMI win32_service | Where { $_.Name -eq $EngineServiceName } | Select PathName
 PathName "D:\Program Files\Microsoft SQL Server\INSTANCE1\MSSQL10_50.INSTANCE1\MSSQL\Binn\sqlservr.exe" -sINSTANCE1

PS C:\Windows\system32> $SQLExecutablePath.Remove($StartIndex).Split("\")

D: Program Files Microsoft

SQL Server




PS C:\Windows\system32> $SQLInstallPath[3]


So when it checks to see if this is a cluster installation it looks in the following registry key

"HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\" + $SQLInstanceID + "\Cluster" in this case: HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance1\Cluster

Turns out that the "Cluster" key is not there thus returning a NULL value the "Cluster" key is in: HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.INSTANCE1\Cluster

so I modified Engine.ps1

" $SQLInstanceID = $SQLInstallPath[3] "


" $SQLInstanceID = $SQLInstallPath[4] "

I also modified AnalysisServices.ps1, IntegrationServices.ps1 and Replication.ps1

BPA R2 for sql 2008 is scanning and reporting properly!!!!!!!!!!!!

more ▼

answered May 12, 2011 at 01:09 PM

avatar image

217 2 4 9

Also changed ReportingServices.ps1

from $RSInstance = $RSInstallPath[3]


$RSInstance = $RSInstallPath[4]

May 13, 2011 at 12:37 PM vinman2
(comments are locked)
10|1200 characters needed characters left

It sounds like you're running the software in a security context that doesn't have access to the server. I've run the tool. It works fine. You can use SQL Logins, but you need to make sure they are in the sysadmin role on the server.

more ▼

answered May 04, 2011 at 06:57 AM

avatar image

Grant Fritchey ♦♦
137k 20 43 81

where can i plug in an sql login?

May 04, 2011 at 07:03 AM vinman2

Sorry, that was a mistake. Is remoting enabled?

May 04, 2011 at 07:25 AM Grant Fritchey ♦♦

Yes, did:

Once BPA was installed, opened a PowerShell prompt as "run as Administrator"

Executed the following commands Enable-PSRemoting -f winrm set winrm/config/winrs @{MaxShellsPerUser=`"10`"`}

May 04, 2011 at 07:33 AM vinman2

Did that complete successfully?, no errors?

You might be hitting the kerberos issue that Adam talks about here: http://blogs.msdn.com/b/psssql/archive/2010/06/21/known-issues-installing-sql-2008-r2-bpa-relating-to-remoting.aspx

I'll see if I can get Adam to look at this thread.

May 04, 2011 at 07:40 AM Grant Fritchey ♦♦

yes, it completed sucessfully and without errors.

I thought it was kerberos also but I cannot find any HTTP SPN defined on my Domain Account. not sure if there is a specfic SPN to check for.

I checked the spns by doing:

setspn -L [domain\account]

setspn -L [hostname]

and did not see anything with the text "http"

May 04, 2011 at 08:04 AM vinman2
(comments are locked)
10|1200 characters needed characters left

Hi Vin,

thx for mentioning my blog. I have changed my blog adress in the past but feleryan.wordpress.com was still available. Therefor I have copied your comments to the actual blog post: http://dirkhondong.wordpress.com/2010/09/18/sql2k8r2-bpa-again%E2%80%A6-because-i-was-a-little-bit-wrong/

Regards Dirk

more ▼

answered May 13, 2011 at 05:38 AM

avatar image

1.3k 15 19 24

(comments are locked)
10|1200 characters needed characters left

We've run into issues like that. When we hit that before, the account wasn't provisioned properly in SQL. Basically if it wasn't listed in the following DMV, it wouldn't work.

select * from sys.server_principals

Although if you added the account explicitly within SQL, it should be listed there.

The other thing would be how exactly are you trying to do this from BPA? Are you using Alternate_Server_To_Scan?

Also, if "Enable-PSRemoting -f" works successfully from an Admin PS Prompt, then i wouldn't worry about the whole HTTP SPN piece.

more ▼

answered May 04, 2011 at 08:26 AM

avatar image


Yes, I am in the sys.server_principals server principles table.

I attempt to connect and scan the DB engine by :

  1. Right click Microsoft Baseline Configuration Analyzer and "run as administrator"

  2. I choose "Connect to another Computer" and enter the Instance's Virtual server name defined in the Cluster Manager. Then Click "OK" and the servername now appears in the top, next to "Select a Product"

  3. IN the dropdown list under "Select a Product" I choose "SQL SERVER 2008 R2 BPA"

  4. Then Click "Start Scan"

  5. Next to "SQL_Server_Instance_Name" I enter the instance name

  6. I select the checkbox next to "Analyze_SQL Server_Engine"

  7. Then click "start scan"

  8. I will shortly thereafter get the message "

Impact: Analysis cannot be performed

Resolution: 1. Add user as a member of Administrators group, OR 2. Run Commands Enable-PSRemoting -f through PowerShell command prompt using elevated privileges, AND 3. Run Command winrm set winrm/config/winrs @{MaxShellsPerUser=`"10`"`} through PowerShell command prompt using elevated privileges"

May 04, 2011 at 10:15 AM vinman2
(comments are locked)
10|1200 characters needed characters left

Good Day. I installed Both the packages and I used all the tips I could lay may hands on , but still BPA does not work. It still displays the error "Login does not exist or was n ot added to the sysadmin role. " Why is such a simple programme giving so much trouble ?

more ▼

answered Nov 15, 2011 at 04:57 AM

avatar image


(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 04, 2011 at 06:16 AM

Seen: 6484 times

Last Updated: May 19, 2011 at 01:32 AM

Copyright 2016 Redgate Software. Privacy Policy