Case of timeout while Cognos tries to connect to SQL using windows authentication and JSQLConnect

We faced a very weird situation that on one customer we had installation failure while trying to install Cognos (cannot start service).
While looking at the logs / event viewer we saw that its failing wen trying to connect to SQL (although all other parts of our install are working and successfully connect to the DB)

We engaged Cognos support…. long story short … didn’t give any advantage

 

The solution came from reviewing the Cognos Configuration Utility under ProcMon!!!

While the Configuration application is trying to connect (and times out at the end) we see in procmon that somehow the system is trying to connect to \\127.0.0.1,1534\pipe\sql\query  and get BAD NETWORK PATH

Important to say that the address that we used is ourservername:1435 (yeap –> non default port)

I have validated that all the SQL client are configured correctly with the correct order using SQL configuration manager…. still noting changes.

Connecting to SQL using SQL Management works perfect

I decided to continue review the procmon trying to understand why we get connection to name pipe instead of our TCP….

I searched up and saw that the process is querying registry: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\ProtocolOrder and getting value of NP, TCP (meaning name pipe and tcp)

so I decided to backup this registry portion and clear it, amazing doing this fixed the problem.

Issue resolved by digging again using procmon

While I don’t have root cause why this registry contain this wrong order or where is the bad port arrived, I decided to go ahead and publish this blog post – coz we wasted 3 days of Vendor-Customer ping pong

 

Few interesting point now that we are all smarter:

1. Similar case – suggested that SQLOleDB Is effected by this registry
2. Feature of protocol fall back

Screen shot (if it helps)