MSSQLWIKI

Karthick P.K on SQL Server

We do not see the SQL server and SQL Agent status from management studio. When we right click the instance, start, stop and resume options is disabled.

Posted by Karthick P.K on June 20, 2010

From  management studio we do not see the SQL server and SQL Agent status. When we right click the instance, start, stop and resume options is disabled.

 

1. Check if are running the management studio with elevated permissions (Right click SSMS—>Run as administrator)

 

2. WMI Permission could be a cause. Enable below permission in WMI.

 

–Open Dcomcnfg

–Expand Component Service -> Computers -> My computer

–Right-click and go to the Properties of My Computer

–Select the COM Security Tab

–Click on "Edit Limits"

–Under launch and activation permissions : Add both Startup account of SQLServer service and Logged on windows account.

 

 

–On the Start menu on, click Run.

–Type wmimgmt.msc, and then click OK.

–In the Windows Management Infrastructure program, right-click WMI Control (Local), and then click Properties.

–In the WMI Control (Local) Properties dialog box, select Security tab, expand Root, and then click CIMV2.

–Click Security to open the Security for ROOT\CIMV2 dialog box. Add startup account of SQLServer service and Windows Logon account and select Remote Enable permission.

 

 

3. Use the following command to give rights to query service .

 

subinacl /SERVICE MSSQL$SQLINST /GRANT=everyone

 

NOTE: MSSQL$SQLINST > Replace With your SQLServer service name.

 

 

4. We can use below command to reset the Security policy to default.

 

secedit /configure /cfg C:\WINDOWS\inf\defltsv.inf /db defltsv.sdb /verbose

 

–>If you still have the same error even after running this.

 

5. We can ran the following WMI query to check if we get any errors:

Select * from Win32_service where name="SQLServiceName"

 

 

Steps to check SQL Service status from WMI:

–Open “WBEMTEST”

–Connect to    “root\cimv2”

–Click on notification Query

–Run the below query

–SELECT * FROM __InstanceModificationEvent WITHIN 10 WHERE TargetInstance isa ‘Win32_Service’

–Stop SQLAgent or SQLServer service

–check if you get notification query:

 

 

6. Run the below Port query and check  if any of Instance is misidentified as cluster

 

a. "IsClustered "    Should be no

b.Check browser properties to see if it Standalone

 

7.Compare the HOST name ,select serverproperty (‘servername’)  , and select @@Servername

All the above three has to match.

–> If query “select serverproperty (‘servername’)” returns wrong name

 

Check the environment variable’s and see if we have below variables defined. Below keys should be present only for clustered SQLServer and  Key  "_CLUSTER_NETWORK_NAME_" should have SQLVirtual server name in case of cluster and this Environment variable should not be present for Standalone instance.

 

_CLUSTER_NETWORK_DOMAIN_=XXXXXXXXXXXX

_CLUSTER_NETWORK_FQDN_=XXXXXXXXXXXX

_CLUSTER_NETWORK_HOSTNAME_=XXXXXXXXXXXX

_CLUSTER_NETWORK_NAME_=XXXXXXXXXXXX

 

Note: Environment variable will be defined for service under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\MSSQL$InstanceName\Environment

 

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer:

The views expressed on this website/blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and

2 Responses to “We do not see the SQL server and SQL Agent status from management studio. When we right click the instance, start, stop and resume options is disabled.”

  1. […] We do not see the SQL server and SQL Agent status from management studio. When we right click the in… […]

  2. After checking out a handful of the blog articles on your web page, I honestly like your technique of blogging.
    I book marked it to my bookmark site list and will be
    checking back soon. Please check out my website as well and tell me how you
    feel.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: