SQL SERVER – FIX: ERROR: (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error :)

Today I have faced SQL server Error and I have solved through below mentioned step:-

Dec 22, 2012

Regular readers of my blog are aware of the fact that I have written about this subject umpteen times earlier, and every time I have spoken about a new issue related to it. Few days ago, I had redone my local home network. I have LAN setup with wireless router connected with my four computers, two mobile devices, one printer and one VOIP solution. I had also formatted my primary computer and clean installed SQL Server 2008 into it. Yesterday, incidentally, I was sitting in my yard trying to connect SQL Server located in home office and suddenly I stumbled upon the following error. Finding the solution was the most infuriating part as it consumed my precious 10 minutes.

Let us look at few of the common errors received:

An error has occurred while establishing a connection to the server.

(provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)

I eventually found the resolution to this error on this blog itself; so I was saved from hunting for the solution. I am sure there are a number of developers who had previously fixed this error while installing SQL Server 2008 or SQL Server 2005 but in due course forgot the right solution to this error. This is just a quick refresher. Make sure to bookmark this as you never know when you would need this solution.

Let us check into the steps to resolve this error.

1) SQL Server should be up and running.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Services, and check if SQL Server service status is “Running”.

In addition, ensure that your remote server is in the same network. Run “sqlcmd -L” in your command prompt to ascertain if your server is included in your network list. You can even find tutorial for the same here SQL SERVER – Find All Servers From Local Network – Using sqlcmd – Detect Installed SQL Server on Network.

I have confronted numerous situations when these nerve-wracking errors crop up, and most of the time when I try to troubleshoot I notice that SQL Server services are neither running nor installed. If SQL Server is not installed as default instance SQL Server Browser should be running together with it; we will explore this further in Topic 5.

2) Enable TCP/IP in SQL Server Configuration

When two or more SQL Servers are connected across network they do all communication using TCP/IP. The default port of SQL Server installation is 1433. This port can be changed through SQL Server Configuration Manager. TCP/IP should be enabled for SQL Server to be connected.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> Select TCP/IP

Right Click on TCP/IP >> Click on Enable

You must restart SQL Server Services for all the changes to take effect. Right click and go to menu properties to select location where default port of SQL Server can be changed.

3) Open Port in Windows Firewall

Windows Firewall is very efficacious in protecting the Operating system from all sorts of malicious attacks. By default, many of the ports and services are refrained from running by firewall. Time and again, SQL Server ports are not open in firewall as well. All the ports on which SQL Server is running should be added to exception and firewall should filter all the traffic from those ports. As described, by default SQL Server runs on port 1433, but if default port is changed then the new port should be added to exception. If SQL Server has named instance (another instance besides default instance) is installed, SQL Server browser should also be added to the exception, as described in Step 7.

Go to Control Panel >> Windows Firewall >> Change Settings >> Exceptions  >> Add Port

Make the following entries in popup “Add a Port” and click OK.
Name : SQL
Port Number: 1433
Protocol: Select TCP

4) Enable Remote Connection

Enabling remote connection is another important, yet oft-neglected step that is frequently missed by database administrators while setting up SQL Server. If this feature is turned off SQL Server will function smoothly on local machine, but it will let another server connect to it remotely. By default this feature is ON in SQL Server 2008.

Right click on the server node and select Properties.

Go to Left Tab of Connections and check “Allow remote connections to this server”

5) Enable SQL Server Browser Service

If SQL Server is not installed as default instance but instead installed as named instance and also if there is no specific TCP/IP port configured, it will give rise to the error that is being discussed in this article. If SQL Server Browser service is enabled, it will allow the server to be connected through dynamic TCP/IP port. Enabling this service is a one-time process, as on enabling it once it will apply to all the instances installed on the same server.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Browser

Right Click on SQL Server Browser >> Click on Enable

6) Create exception of sqlbrowser.exe in Firewall

As elucidated in Step 6, sqlbrowser service needs to be enabled for named instance. Windows Firewall may prevent sqlbrowser.exe to execute. So, it is imperative to add exception for the same in windows firewall.

Search for sqlbrowser.exe on your local drive where SQL Server is installed. Copy the path of the sqlbrowser.exe like C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe and create the exception of the file in Firewall, as delineated in Step 3.

7) Recreate Alias

It is getting quite common to create alias of SQL Server and use it in application. This will ensure that in future if any physical SQL Server has to be moved, it will not be required to change any code or connection string. You can simply create alias with the same name pointing to different SQL Server and it will start working instantaneously. I have observed that a couple of times due to internal error while recreating alias this error was fixed.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Native Client 10.0 Configuration >> Aliases

Delete the alias that is giving problem and recreate it with identical parameters.

I have tried my best to include all the methods of fixing this error and if I have missed any, please leave a comment and I will be very glad to include them here. I have put in my effort to encompass this issue in one article that needs to be refereed when any connection error comes up.

.

Thanks

R.karthikeyan

9782829889

Types Of Backup in Windows

Explanation -1

Type	Definition	Benefits	Drawbacks
Full Back up:	A complete backup of everything you want to backup.	Restoration is fast, since you only need one set of backup data.	The backing up process is slow. High storage requirements.
Differential Backup:	The backup software looks at which files have changed since you last did a full backup. Then creates copies of all the files that are different from the ones in the full backup. If you do a differential backup more than once, it will copy all the files, or parts of files that have changed since the last full backup, even if you already have identical copies of those files in a previous differential backup. For restoring all the data, you will only need the last full back up, and the last differential backup.	Faster to create than a full backup. Restoration is faster than using incremental backup. Not as much storage needed as in a full backup.	Restoration is slower than using a full backup. Creating a differential backup is slower than creating an incremental backup.
Incremental Backup:	The backup software creates copies of all the files, or parts of files that have changed since previous backups of any type (full, differential or incremental). For example if you did a full back up on Sunday. An incremental backup made on Monday, would only contain files changed since Sunday, and an incremental backup on Tuesday, would only contain files changed since Monday, and so on.	This method is the fastest when creating a backup. The least storage space is needed.	Restoring from incremental backups is the slowest because it may require several sets of data to fully restore all the data. For example if you had a full backup and six incremental backups. To restore the data would require you to process the full backup and all six incremental backups.

Explanation -2

Differential backups:

It back up the changes that happened from last full backup.

sun mon Tue wed Thu
full diff diff diff restore requested
file1,file2,file3 file4 file4,5 file4,5,6 last full and last diff will restore(file1,2,3,4,5,6)

Sunday we have full backup and had 3 files(file1,2,3)
Monday we scheduled differential backup and the change is a new file4 is added and backup the change(file4).
Tuesday we have diff back up again and there is new file5 and changes since last full backup are file4,5 and those

would be backed up.
Wednesday we have diff back up again and there is new file6 and changes since last full backup are file4,5,6 and

those would be backed up.

Whenever there is a restore request on Thursday we can actually consider a recent full and differential backup

which recovers all the files as shown.

Advantages:

Easy while restoring.

Disadvantages:

Takes long time to backup.
Costing for the storage media as the data would be little bigger.


Incremental:

It back up the changes that happened from last backup (full or differential).

sun mon Tue wed Thu
full incr incr incr restore requested
file1,file2,file3 file4 file5 file6 last full and all incremental backups are needed for restore

(file1,2,3,4,5,6)


Sunday we have full backup and had 3 files(file1,2,3)
Monday we scheduled incremental backup and the change is a new file4 is added and backs up the change(file4).
Tuesday we have incremental back up again and there is new file5 and changes since last backup are file5 and those

would be backed up.
Wednesday we have incremental back up again and there is new file6 and changes since last backup are file6 and
those would be backed up.

Whenever there is a restore request on Thursday we can actually consider recent full and all incremental backups

Which recovers all the files as shown.

Advantages:

Backup time would be less as we backup little changes.
Low costing on tapes as there is little backup.

Disadvantages:

Restore is difficult as we need all tapes from last full backup and all incremental backups.

Related To :- Backup

Thanks

R.karthikeyan

9782829889

Windows Server Interview questions

1. What is DHCP? How we configure DHCP?
2. What are the ways to configure DNS & Zones?
3. What are the types of backup? Explain each?
4. What are Levels of RAID 0, 1, 5? Which one is better & why?
5. What are FMSO Roles? List them.
6. Describe the lease process of the DHCP server.
7. Disaster Recovery Plan?
8. What is scope & super scope?
9. Differences between Win 2000 Server & Advanced Server?
10. Logical Diagram of Active Directory? What is the difference between child domain & additional domain server?
11. FTP, NNTP, SMTP, KERBEROS, DNS, DHCP, POP3 port numbers?

What is Kerberos? Which version is currently used by Windows? How does Kerberos work?

Hi If you found this post useful then kindly share your view and feed back through comments.

Feature Comparison Windows Server 2008 R2 Hyper-V and Windows Server 2012 Hyper-V

Hi Friends..

Today I wanted to create multiple virtual NIC through one Physical NIC in Hyper-v. So I started to Searching regarding the same. I have find out that this can be done through Virtual Switch Manger.

And then I searched about Virtual Switch manger. This is the new features in windows 2012 server and Windows8.

Here with I want to share with you “Features comparison between Windows 2008 R2 and Windows 2012 on the base of Hyper-v”.

Please find the document in below mentioned URL. 

Features comparison between Hyper-V 2008 R2 and 2012

 

Thanks Guys…

R.karthikeyan

Senior Consultant- IT

+91-9782829889/9772655552