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

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%MicrosoftMicrosoft Baseline Configuration Analyzer 2ModelsSQL2008R2BPA

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:localhostclienttrustedhosts * -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 10.10.10.10 -credential(Get-Credential)"

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

I ran :

Set-Item WSMan:localhostclienttrustedhosts * -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%MicrosoftMicrosoft Baseline Configuration Analyzer 2ModelsSQL2008R2BPAEngine

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('"')
}
else
{
    $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
    }
}
else
{
    $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
         break
       }     
    }
$Count++
}

if(!$IsCurrentLoginAvailableOnSQL)
{
    Get-LogText $cError "Engine Rules Execution Interrupted - Current User Name is unavailable or is disabled on '$HostName'" >> $EngineLogFile 
    AddElementToDocument $XmlDoc $tns $false "SufficiencyCheck" "CurrentUserLoginExistsOnSQL"
    $XmlDoc
    Exit
}

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:Windowssystem32> $SQLExecutablePath.Remove($StartIndex).Split("")

D: Program Files Microsoft

SQL Server

INSTANCE1

MSSQL10_50.INSTANCE1

MSSQL

PS C:Windowssystem32> $SQLInstallPath[3]

INSTANCE1

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

"HKLM:SOFTWAREMicrosoftMicrosoft SQL Server" + $SQLInstanceID + "Cluster" in this case: HKLM:SOFTWAREMicrosoftMicrosoft SQL ServerInstance1Cluster

Turns out that the "Cluster" key is not there thus returning a NULL value the "Cluster" key is in: HKLM:SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.INSTANCE1Cluster

so I modified Engine.ps1

" $SQLInstanceID = $SQLInstallPath[3] "

to

" $SQLInstanceID = $SQLInstallPath[4] "

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

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

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.

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

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.

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 ?