x

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 :

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. Any ideas?
more ▼

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

vinman2 gravatar image

vinman2
217 2 2 4

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:\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:

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.
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 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 (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

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!!!!!!!!!!!!
more ▼

answered May 12, 2011 at 01:09 PM

vinman2 gravatar image

vinman2
217 2 2 4

Also changed ReportingServices.ps1

from $RSInstance = $RSInstallPath[3]

to

$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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.1k 19 21 74

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

DirkHondong gravatar image

DirkHondong
1.3k 15 17 19

(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

asaxton gravatar image

asaxton
41

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

lianvh gravatar image

lianvh
1

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x580
x61
x24

asked: May 04, 2011 at 06:16 AM

Seen: 5246 times

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