Im getting this error on the production server:
A network-related or instance-specific error occurred while establishing a > connection to SQL Server.
It's on IIS, and connecting to sql server with Windows authentication.
Any idea? or solutions?
The error message listed in the question typically occurs to me when I'm having connection issues, not related to credentials. Is your connection string setup properly? Have you tested it out on a different machine?
Additionally though, with windows authentiation in place, updated the web.config to set:
solves the issue since the web application impersonates the person using the application, and their credentials have already been verified and are passed on to SQL Server.
Are you sure the production server has access to the relevant SQL Server? You can easily use the ODBC Data Source Administrator to test that this is so.
if it is a web application and you do not use impersonation, the application will try to connect with the IIS user, most likely NETWORK SERVICE, b/c that is the user that is running the app. My suggestion is to either use impersonation (see Dillie's post) or create an application user name and password and use that. I personally prefer application user names for most cases.