Named Pipe Provider: Could not open a connection to SQL server – Firewall Authentication
There is a pretty good blog about how to resolve this issue. Here is the link: http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/
From both my SQL Management Studio (2005 and 2008 version), and a SSIS package, I got this connection error. See the screenshot below.
According to the DBA of the target database:
After some digging, the problem turned out to a domain + firewall issue. My PC is in a different domain from the target SQL server. The firewall between the two domains are not allowing my account to pass through (the target SQL database uses a SQL account, not the Windows Authentication).
A phone call to the Helpdesk leads to a firewall site where I can use my SSO (Single Sign On) account to authenticate my account to the firewall. Problem Solved. Partly.
There are still issues about connecting to SQL servers in a SSIS package that resides in a domain that is different from the target SQL server. How would the SSIS package pass through the firewall to reach to the target SQL Server?