Get SQL Server Service Account using T-SQL

Identify the SQL Server Service Account using T-SQL 

You can find the service account information in Services 

Go to Start --> Run --> Services.msc 

Right click on SQL Server Services and go to Properties. 

The account information is available under Log On Tab

And also service account information stored in Windows Registry, using xp_instance_regread extended stored procedure we can read windows registry.  Registry entries are like below 

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLServerAgent

Using below script also we can fetch the information   

DECLARE       @DBEngineLogin       VARCHAR(100)

DECLARE       @AgentLogin          VARCHAR(100)

EXECUTE       master.dbo.xp_instance_regread

              @rootkey      = N'KEY_LOCAL_MACHINE',

              @key          = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',

              @value_name   = N'ObjectName',

              @value        = @DBEngineLogin OUTPUT

EXECUTE       master.dbo.xp_instance_regread

              @rootkey      = N'HKEY_LOCAL_MACHINE',

              @key          = N'SYSTEM\CurrentControlSet\Services\SQLServerAgent',

              @value_name   = N'ObjectName',

              @value        = @AgentLogin OUTPUT

SELECT        [DBEngineLogin] = @DBEngineLogin, [AgentLogin] = @AgentLogin

GO

Output 

in above scenario both Agent and SQL Services are running as Local System 

Using DMVS also we can fetch the information   

SELECT

      servicename

      , service_account

      , startup_type_desc

      , status_desc

      , is_clustered

FROM   sys.dm_server_services

Output :

in above scenario SQL Services is running as NT Service and Agent is running with Domain Account. 

TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.

2015-02-17 12:27:38.19 spid15s     Error: 26014, Severity: 16, State: 1.

2015-02-17 12:27:38.19 spid15s     Unable to load user-specified certificate [Cert Hash(sha1) "5B4FF9FFF4E6752A3AD51489E1A9C455E580BCC3"]. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.

2015-02-17 12:27:38.19 spid15s     Error: 17182, Severity: 16, State: 1.

2015-02-17 12:27:38.19 spid15s     TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property. 

2015-02-17 12:27:38.19 spid15s     Error: 17182, Severity: 16, State: 1.

2015-02-17 12:27:38.19 spid15s     TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property. 

2015-02-17 12:27:38.19 spid15s     Error: 17826, Severity: 18, State: 3.

2015-02-17 12:27:38.19 spid15s     Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

2015-02-17 12:27:38.19 spid15s     Error: 17120, Severity: 16, State: 1.

2015-02-17 12:27:38.19 spid15s     SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Event ID 17182: TDSSNIClient initialization failed with error 0x7e, status code 0x3a.

Event ID 17826: Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

Event ID 17120: SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Solution :

1. Check 'Forced Encryption' (Turned False) and 'Certificate' value is cleared under SQL Server Configuration Manager.
2> Check the following registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer\SuperSocketNetLib\Certificate
and clear the Certificate values.