Microsoft SQL 2008 - SQL agent log bugging with ODBC error - Could not open connection to SQL server.


I have received one of my customer problem and trouble shoot it till closure hence good to share here with step by step I performed.


Issue:
Microsoft SQL 2008 being used in Clustered with Mixed mode authentication and did modifications like, 
Order of connection, TCP id first than Named pipes
Tried by putting Alias name as a Cluster name and then restart agent service but didn't help.

Troubleshoot Step by step:

http://msdn.microsoft.com/en-us/library/ms175176.aspx   - By default, the SQL Server Agent service connects to an instance of SQL Server over named pipes by using dynamic server names that require no additional client configuration.

Please check if it connects the database with SQL Server ODBC drivers. You can start ODBC connection wizard from control panel, select new & then SQL server. At the end, it will show option of ‘Validate Test’ where you can check if it connects to SQL with ODBC drivers by looking test get success or failed.

However in my case, validation test was successful

So proceed further with next step.

There are a couple of things which we can check, that might be going on this case… (All of the following configurations are made on the computer running your SQL Server 2008 instance)

Allow remote connections to this server
The first thing you want to check is if Remote Connections are enabled on your SQL Server database.
To do this, open SQL Server 2008 Management Studio, connect to the server in question, right click the server…and open the Server Properties.

Navigate to Connections and ensure that Allow remote connections to this server is checked. Check if this solves the problem. If it does, here you go, continue with whatever you were doing and have a nice day.
Protocols for MSSQLServer
If you’re still running in issues let’s dig a bit deeper. The next good thing to check is the SQL Server Network Configuration. Open the SQL Server Configuration Manager, unfold the node SQL Server Network Configuration and select Protocols for MSSQLServer (or whatever the name of your SQL Server instance is).

Make sure that TCP/IP is enabled and try again. Even though I hope that this resolved your problems there might still be an issue with…
The Firewall
If there is still no communication happening between your computer and the remote SQL Server you most likely need to configure your firewall settings. A good first step is to figure out which port is being used by TCP/IP (and which you need to open in your firewall). You can do this by right clicking TCP/IP and selecting Properties.

Click on the tab IP Addresses – Port 1433 it is :-) That was easy enough and all there is left to do is to allow inbound TCP/IP traffic on Port 1433 in your firewall. In Windows 7 this works something like this. 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 Inbound Rules on the left hand side and click on New Rule… on the right hand side.

This opens the New Inbound Rule Wizard which you can use to allow inbound traffic on Port 1433 for TCP/IP (and which is exactly how you configured your SQL Server in the steps above). Just follow the steps outlined below.)

By performing above steps, it lead to resolved my issue.