Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

powershell WMI error: "Could not obtain SQL Server Service information. An attempt to connect to WMI ... failed with the following error: access is denied." Expand / Collapse
Author
Message
Posted Thursday, May 22, 2014 9:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 23, 2014 4:06 AM
Points: 1, Visits: 13
Open Powershell IDE "As Administrator", worked for me !
Post #1573625
Posted Friday, September 25, 2015 3:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 16, 2016 11:53 AM
Points: 4, Visits: 78
The code I'm running on my SQL Central Management server is:

$logs = 0..6 | % { (get-item SQLSERVER:\sql\<servername>\default).ReadErrorLog($_) }

Originally, I got this to work by adding the service account that ran the PowerShell code to the local Administrators Windows group _AND_ creating a SQL login with sysadmin privileges on the remote SQL server host. Not sursprisingly, my security officer had an issue with this. My initial attempts to reduce access resulted i the following error:

WARNING: Could not obtain SQL Server Service information. An attempt to connect
to WMI on 'NEX-SRV1' failed with the following error: SQL Server WMI provider
is not available on NEX-SRV1. --> Invalid namespace

After quite a bit of fooling around, I have what I think is the minimum security needed to read the SQL error logs. Perform the following grants on the remote server running the SQL Server database:

Local Windows Group
- Add the service account to the "Distributed COM Users" group

WMIMgmt.msc
Add the service account to each of the following branches with all security options EXCEPT "Edit Security"
- Root > cimv2
- Root > cimv2 > ms_409
- Root > Microsoft > SQLServer > ComputerManagement

SQL Server
- Create a SQL login for the service account and add it to the "Security Admin" role.

After I had made these changes, I have the ability to monitor SQL Error logs from a central location without having to grant crazy levels of access to the service account.

Post #1723201
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse