IIS / SQL Server connection error

Question

I am doing a sample code and I got stucked in a really strange behavior, I am trying to connect to a SQL Server using this connection string

"Data Source=" + ServerName + "." + DomainName  + ";Initial Catalog="+DBName+";Integrated Security = false;Persist Security Info=false;User ID=sa;Password=" & Mypassword & ";Connection Timeout=90"

Apparently I am not using Windows Integrated Authentication or (SSPI), when I use this on the web application I get this error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Just to note I created a .Net application that uses the same connection string and is running from the computer where the IIS resides and it works fine.

Here are the steps I have done:

1. I checked the SQL client configuration I opened it and enabled both of the protocol tcp and Named pipes.

2. I did impersonation on the IIS and used windows authentication and made sure that the IIS is working with the same credentials as mine.

3. I used Wireshark to see wut is the difference between the two connections and I couldn't understand the output however it seems that from the IIS it always tries to use windows authentication although I have clearly made the string such that it doesn't use windows authentication basically wut happens is that some negotiation in the beginning of the connection that seems the same from both application "not_defined_in_RFC4178@please_ignore" and then the IIS goes always to NTLMSSP noting the my user doesn't have access to the database except through user name and password so the negotiation fails with IIS while it succeeds normally from the application.

My question is how may I fix this, I kind of assume that there is some configuration that mandates that any SQL client from IIS uses windows authentication but I can't know where or how to disable it

I note I am not using web.config configuration for the SQL Server connection string and this issue happens with IIS7.

Answer 1

Here are the first things I would check:

1. Verify the Application pool user has rights to access network resources

   • Since you stated it works when you are logged into the machine, try changing the application pool user to the user with which you log into the machine.

2. Check if you have impersonation enabled in your web.config. If you do, make sure that user can access network resources.

3. Try changing the connection string to use an IP address (as a test). If that fixes the issue, maybe it is some sort of DNS problem.

   • Make sure you include a ,1433 at the end of the IP Address
   • Set Network Library=DBMSSOCN in your connection string. (See this article on ConnectionStrings.com for more info.)

Answer 2

If you have typed exact connection string as above, please check changing & around Password in connection string to +.

The connection string written above is not valid string at all since string can be concatenate another string using + not using & in C# as I know.

Answer 3

Aside from what the others have indicated, the only other time I recall the error message specifically with "Named Pipes" reference was based on how SQL-Server is setup.

To confirm, I went into my version of SQL Server Configuration Manager. Then, looked at the "SQL Native Client 11.0 Configuration" (just in case, did for both 32-bit and 64-bit) and opened to show "Client Protocols". Within that it shows options for Shared Memory, TCP/IP and Named Pipes. Make sure your "Named Pipes" protocol is enabled.