Home Dashboard Directory Help
Search

SQL Server 2008 R2 Best Practice Analyzer (BPA) : problems connecting to sql by VinnyP22


Status: 

Resolved
 as By Design Help for as By Design


2
1
Sign in
to vote
Type: Bug
ID: 667466
Opened: 5/9/2011 11:45:10 AM
Access Restriction: Public
0
Workaround(s)
view
1
User(s) can reproduce this bug

Description

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 :

http://blogs.msdn.com/b/psssql/archive/2010/06/20/introducing-the-sql-server-2008-r2-best-practices-analyzer-bpa.aspx

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

http://sqlblog.com/blogs/davide_mauri/archive/2010/06/21/installing-sql-server-2008-r2-best-practices-analyzer-on-a-stand-alone-machine.aspx

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



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 [domainaccount]

setspn -L [hostname]

and did not see anything with the text "http"



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"





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

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=3874409e-f19d-4d80-a5a2-0427d76b6d17&displayLang=en

Invoke-MbcaModel -ModelId SQL2008R2BPA -SubModelId Engine -ComputerName {computername} -SqlServerInstance {servername} -CurrentLoginName ($Env:USERDOMAIN + "" + $Env:USERNAME).ToString() -EngineLogFile c:tempengine.txt –RepositoryPath ("C:TEMPSQL2008" + (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:tempas.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)



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:Windowssystem32> Set-ExecutionPolicy Unrestricted -f PS C:Windowssystem32> 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:

http://imageshack.us/photo/my-images/3/59201120500pm.png/

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.
Details
Sign in to post a comment.
Posted by VinnyP22 on 10/18/2011 at 9:17 AM
sure, what is the problem?
Posted by animalkrack3r on 10/8/2011 at 11:54 AM
Vinny, are you available to help me with this same issue?
Posted by VinnyP22 on 5/13/2011 at 12:54 PM
Also changed ReportingServices.ps1

from $RSInstance = $RSInstallPath[3]

to

$RSInstance = $RSInstallPath[4]
Posted by VinnyP22 on 5/12/2011 at 1:29 PM
I managed to get SQL SERVER 2008 R2 BPA working!!!!

NOTES:

The following directories are used by MBCA:
Report output directory         %localappdata%\Microsoft\MicrosoftBaselineConfigurationAnalyzer 2\Reports\SQL2008R2BPAResults
Model configuration path    %Programdata%\Microsoft\Microsoft Baseline Configuration Analyzer 2\Models\SQL2008R2BPA
Temp and log files directory    %temp%\SQL2008R2BPA\SQL2008\<date>_<time>




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 HTTP
S or the destination is in the TrustedHosts list, and explicit credentials
are provided. *Use winrm.cmd to configure Tru
stedHosts*. 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 more
information, see the about_Remote_Troubleshooting H
elp 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 10.10.10.10 -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('"')
}
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 (assumign 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
INSTANCE1
MSSQL10_50.INSTANCE1
MSSQL
PS C:\Windows\system32> $SQLInstallPath[3]
INSTANCE1




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] "

to

" $SQLInstanceID = $SQLInstallPath[4] "


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



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





Sign in to post a workaround.