Wednesday, October 28, 2009

Native OLE DB\SQL Server Native Client 10.0 does not list out the available databases

This held our work for few hours. When we tried to make a connection to SQL Server 2008 server with “Native OLE DB\SQL Server Native Client 10.0” provider from a developer’s machine through SSIS, it did not list out available databases, and did not allow to connect to a database even the name of the database is just typed in the “Select or enter a database name” input box. We doubt that the problem was related to some network problem or to some installed components but, since it allowed to connect with “Native OLE DB\Microsoft OLE DB Provider for SQL Server”, thought that it is related to “SQL Server Native Client 10.0”. We spent hours to find the reason but could not find any issue related to SQL Server components. Though the connection can be established with “Microsoft OLE DB Provider for SQL Server”, since it does not allow to run some of the new TSQLs and specially it does not recognize “Date” data type properly, we had to dig deeper…. Finally, my colleague Buddhika found the issue. Windows Firewall Settings was blocking the Native Client 10.0 requests. Once it is turned off, connection could be established. Again, turning off the settings is not a good practice, we tried to find a way to make a connection while Firewall Settings is on, and we found. Simply, the TCP port 1433 added as an exception for the Firewall Settings. It worked. So, in shorter form, if you cannot make a connection to SQL Server 2008 with Native OLE DB\SQL Server Native Client 10.0” provider, make sure that the SQL Server listening port is added as an exception with Windows Firewall Settings. I am sure that we will be getting the same with Analysis Services too, and adding TCP port 2383 as an exception might solve the issue.

4 comments:

Gogula G. Aryalingam said...

Well! Who would have thought it could've been the Firewall! Great work... Quite interesting..

Daniel said...

A bit puzzled. You said "SQL Server Native Client 10.0" worked, but not “Microsoft OLE DB Provider for SQL Server”. Are the clients and servers on the same box. Also, do both Data providers use different network ports. If so, which port does "SQL Server Native Client use --- Is is using Named pipes or Netbios\NetBuei" And, is the server configured to allow Named Pipes, as well.

Dinesh Priyankara said...

Hi Daniel,

Actually, "Microsoft OLE DB Provider for SQL Server" worked, not "Native OLE DB\SQL Server Native Client 10.0". Once we added the port to the firewall, "Native OLE DB\SQL Server Native Client 10.0" started working too.

We faced this issue at one of client sites, so I cannot remember the whole thing now. SQL Server was installed in a separate box and the issue occurred with one of developers machines.

Any idea?

revin said...

We r facing the exact issue. Does anybody kknow which port SQL native client use? We added 1433 to firewall but that does t sole the issue