I have a ASP.NET MVC website in IIS 7 running against a SQL Server 2008 R2 database, in which I have a linked server set up. The linked server is set up so that a SQL account from my database is linked to a SQL account on the linked server.
All works fine until I recompile my website, and then it can't connect to the linked server anymore and I get this error:
Named Pipes Provider: Could not open a connection to SQL Server [5].
To fix it, I have to open the linked server properties in SQL Server and re-enter the password for the SQL account on the linked server in the Properties dialog and then everything will work again.
Any idea why I have to do this each time and what I can do to fix it?