I think supplying username and password is not required, because SQL Server is using Windows Authentication Mode only (not mixed authentication) and if I supply the credentials then I would have to change the authentication mode? However to test what I did now is copied the client application to the SQL Server machine and changed the ConnectionString to as follows to test it locally:
And to my surprise it worked. I really don't understand what's the issue when connecting remotely through network? I even tried the Microsoft KB articles related to the error but none of them solve the problem? When the application is working locally, it indicates that there is nothing wrong with the code?