Cannot Connect to SQL Server or Instance

Installed SQLServer with named instance and when trying to connect from remote host received following error

Cannot connect to “SQL Server\Instance”
Additional Information:
A network-related or instance-specific error occurred while establishing a connection to SQL Server.
The Server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28- Server doesn’t support requested protocol) (Microsoft SQL Server)

Error Screen shot: 

There are various reasons for this error, below are the main reasons

1. You might have entered an incorrect connection string – double check that the connection string is correct or not. If you are using the IP address to connect then make sure that you are able to ping the IP.

2. Instances /Server is down , not yet started- Check SQL Server , Agent, SQL Full-Text Filter Daemon Launcher, SQL Server Browser services are up and running. If not running start the services

3. Remote connection on the SQL Server Instance was not enabled

Right Click on SQServer Instance --> Properties -->Go to “Connections”--> “Allow remote connections to this server” should be checked

4. TCP/IP port is not enabled on the Instance

Open Microsoft SQL Server Configuration Manager--> SQL Server Network Configuration -->Protocols for “Instance”--> TCP/IP MUST be “Enabled”. If not ENABLE the same.

You should be able to connect now. If you are using the dynamic port 1433 make sure to change to Static port using below post/URL

http://www.sqlserver-expert.com/2012/07/configure-sqlserver-to-listen-on-static.html

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com 

OpenSQLServerInstanceRegKey:GetRegKeyAccessMask failed (reason: 2) in SQL Server

Installed SQLServer 2012 Express Edition with Named Instance. When trying to startup the SQL Server Agent to setup the backups, the agent failed with below error

The request failed or the service did not respond in a timely fashion.
Consult the event log or other applicable error logs for details 

When I looked into Event log I found below error message, its not much useful.

OpenSQLServerInstanceRegKey:GetRegKeyAccessMask failed (reason: 2). 

I looked into the SQLAGENT log and found below error messages

2014-01-28 09:33:07 - ? [100] Microsoft SQLServerAgent version 11.0.2100.60 (X64 unicode retail build) : Process ID 1848
2014-01-28 09:33:07 - ? [495] The SQL Server Agent startup service account is SQL-Domain1\SQL-SER1$.
2014-01-28 09:33:09 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (DisableAgentXPs)
2014-01-28 09:33:09 - ! [000] The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229)
2014-01-28 09:33:09 - ? [098] SQLServerAgent terminated (normally)

I have changed the SQL Server Agent logon account to administrator and ran below command to enable AgentXPs option and restarted the SQL Server Agent, but still no luck

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0;
GO

When I looked into SQLAGENT log and found out that SQLServer Express edition does not support SQL Server Agent. 

2014-01-28 10:00:31 - ? [100] Microsoft SQLServerAgent version 11.0.2100.60 (X64 unicode retail build) : Process ID 1684
2014-01-28 10:00:31 - ? [495] The SQL Server Agent startup service account is SQL-Domain1\Administrators.
2014-01-28 10:00:31 - ? [000]
2014-01-28 10:00:31 - ? [101] SQL Server SQL-SER1\INSTANCE1 version 11.00.2100 (0 connection limit)
2014-01-28 10:00:31 - ? [102] SQL Server ODBC driver version 11.00.2100
2014-01-28 10:00:31 - ? [103] NetLib being used by driver is DBNETLIB; Local host server is SQL-SER1\INSTANCE1
2014-01-28 10:00:31 - ? [310] 4 processor(s) and 8192 MB RAM detected
2014-01-28 10:00:31 - ? [339] Local computer is SQL-SER1running Windows NT 6.2 (9200)
2014-01-28 10:00:31 - ! [000] This installation of SQL Server Agent is disabled. The edition of SQL Server that installed this service does not support SQL Server Agent.
2014-01-28 10:00:31 - ? [000] Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457)
2014-01-28 10:00:31 - ? [098] SQLServerAgent terminated (normally)

I forgot that I was working on SQLServer Express Edition...:) I hope this post is useful for users, who face  similar situation.

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com