FIX : A network-related or instance-specific error occurred while establishing a connection to SQL

In this article i explained you to Fix SQL error occurred while establishing a connection to remote computer, so we can say we can resolve error "Network-related or instance-specific error occurred while establishing a connection to SQL Server [2005/2008/2012]"

Background

Couple of years back when i was not much familier with SQL, i try to connect to my SQL Server from my client. I put servername and try to login with SQL Server authentication with user name and password and i got error "A network-related or instance-specific error occurred while establishing a connection to SQL Server...", after spending couple of hours on it i was able to get rid of it, this article is for those who got same error and still they are put their head in SQL to resolve the issue.

Introduction

Many times we are trying to connect to SQL server with SQL management studio either by express version or by enterprise version and we got following error
"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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) "
Above error is really headache for sql users.
Here is the snap of error

Fix it

To fix the issue we might need to do some configuration changes in SQL management studio (Here i use SQL 2008 for it)

Allow remote connections 
The very first thing you want to check is, your Remote Connections are enabled on your SQL Server database, to check it go through below steps
1. Select SQL server on object explorer -> Right click and click on properties -> select 'connections' -> under Remote server connections sections -> check 'Allow Remote connections to this server'
see below snap

This is the basic and simple check it may resolve your error, if not then continue with this article

Enable TCP/IP protocol
Next step is the enable the TCP/IP, just navigate to 'the SQL Server Configuration Manager' -> in left pane Open 'SQL server network configuration' -> select protocol for SQLEXPRESS in right pane select TCP/IP and make it 'enable'
see below snap

This is one of the helpful step and resolve many users issue, if the problem still continues then continue with this article

Handle Firewall port
Many times your firewall security settings does not allow SQL to pass through, you need to handle that firewall port to work with SQL stuff. To allow in firewall there need to configure couple of settings 
1. set 1433 port in TCP/IP proerpties
2. set Inbound and Outbound rules in windows firewall settings (Applicable for window 7 and above operating system)

set TCP port 1433 in configuration window as below snap shows

Now to set Inbound and Outbound rule we need to navigate to windows firewall, Just Open the Control Panel and navigate to Windows Firewall.
Click on Advanced Settings on the left hand side and you should see the Windows Firewall with Advanced Security. Select the Inboud Rules on the left hand side and click on New Rule… on the right hand side.
go through the following snap sequence it will help you to enable and set the windows firewall settings
Open 'windows firewall with advanced security' screen, select 'Inbound Rule' and click on 'New Rule', see below snap

New Inbound wizard screen' will open, Now in left hand side of the window select 'Rule Types', in right hand side select 'Port' now click 'Next'

In protocol and ports section select 'specific local ports' and give port number as '1433' (default sql port), now click 'Next'

In action section, select 'Allow the connection', and click on Next

In Name section give name and description of the rule, which help us to identify the rule in firewall, now click on Finish

Yes, we are almost done with the settings now close SQL server and restart it again and try to connect, it will resolve your issue.

Summing up

Above error is very basic Many time occur due to different conditions, if you have database installed on SQL 2008 and if you try to connect it with SQL 2005 management console then also same error occurred, when you have install a new instance of SQL server on machine then also this error arise, above are some settings that help you to get rid of the error

Suggestion and Queries most welcome

Thanks

Prasad

Tricky SQL Server Interview Question and answers

Are you preparing for a SQL server interview? want some tricky questions and answers that will give you break through ? Then you are at the correct place. Here i have some bunch of tricky SQL Server interview questions and answers that will really help you to go through the interview

1.With is the Sixth normal form in SQL server? - Ohhh...You may think that, i have heard about first to forth only but what is this SIXTH? Friends, do not try to impress the interviewer just skip this question.
Sixth normal form does exist but use it when you want a relational system in conjunction with time. At this moment SQL Server does not support it directly.

2. In which Files does SQL Server Actually Store Data? - SQL server has 2 data files associated with it
1. MDF : Which is actual data file storage
2. LDF : (Log data files) which stores transaction log

3. Do you know How many locks are exist in SQL Server ? - We have these many locks exist here, see below
1. Intent
2. Shared
3. Update
4. Exclusive
5. Schema
6. Bulk Update

4.What is SQL Profiler?- It is a tool that allows administrator to monitor different events and transaction of SQL server instance, You can capture and save data about each event to a file or SQL Server table to analyze later
it is not available with EXPRESS edition

5. Can i insert into a table having just one IDENTITY column?- Yes you can, Use following Query

INSERT INTO TABLE1 DEFAULT VALUES;

6. How to drop primary key from a column using Query
- See below query to drop primary key constraint

ALTER TABLE Table1 DROP CONSTRAINT PK_Table1_Col1

7. What is the difference between a DDL trigger and a DML trigger?- DDL commands are always deal with table structures, these command can update/modify table structure (CREATE, ALTER, DROP)
DMS commands are always deal with data/records not with table structure (INSERT, UPDATE, DELETE)

8. What the difference between UNION and UNIONALL?
- Union will remove the duplicate rows from the result set while UNIONALL does not remove them. So to avoid data redundancy you need to use UNION

9. What is the clustered and a non-clustered index?
- Clustered index is the physical index that reorders the way records in the table are physically stored, In non-clustered index logical order of the index does not match the physical stored order of the rows on disk

10. What's the difference between a primary key and a unique key?
- Both keys are not allow duplicate entries but primary key create a cluster index where as unique key create a non clustered index. Secondly primary key doesn't allow NULLs, but unique key allows one NULL only.
11. How to change the data type of a column- The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
Here is the query to change column datataype
ALTER TABLE table_name ALTER COLUMN column_name data type

12. How to check the version of SQL server and operating system?
- With the help of the following query we can get it
SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition').

13. What is CHECK Constraint?
- CHECK Constraint used to limit the values that can be placed in a column

14. How can you delete duplicate records in a table where there is no primary key?
- You can take Use of the SET ROWCOUNT command. Here if you had 2 duplicate rows you would issue SET ROWCOUNT 1, then your DELETE command then SET ROWCOUNT 0.
15. What is NOT NULL Constraint?- This constraint force the column to not accept NULL values

16. How to copy data from one table to another table?
- With the help of 'INSERT INTO SELECT' or 'SELECT INTO' queries you can copy data from a datatable to another

17. What is PIVOT
- To automatically sort, count, and total the data stored in one table we use PIVOT. It will also rotate table as rows to columns and vice versa

18. What are DBCC commands?
- These are the Database Consistency Checker commands; They will Check disk allocation consistency, Display information about recent transactions.
19. Find the 3rd MAX salary in the emp table

Select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);

20. What is the difference between Trigger and Stored Procedure?- Triggers cannot be called directly they need to associated with different quires

Finisher
Suggestion/Queries always welcome

Happy Interview
- Prasad