27

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.

Improve this question
15
  • 2
    How are the values of ServerName, DomainName and DBName set? Are they correct? Commented Mar 25, 2013 at 11:18
  • 1
    I want to note that the SQL server is located in another machine, if this have to do anything with it Commented Apr 28, 2013 at 5:44
  • 1
    Please don't use the sa account to access sql server. Make sure you have sql server authentication enabled (right-click your server in management studio and go to properties, then click 'security' in the left side to view current status). Next, verify that you have named pipes and/or other protocols enabled that you may use, via All Programs -> Microsoft SQL Server (insert version here) / Configuration tools / SQL Server Configuration Manager. Select the instance type of the DB and drill down to client protocols and enable named pipes and/or tcp/ip protocols. Commented May 29, 2013 at 4:57
  • 1
    The "Named Pipes" thing is usually a Red Herring. The connection manager tries the client-enabled protocols in order, and Named Pipes is just the last one on the list, so it's the one that gets named in the error message. Disable it on the client and you'll see "TCP/IP" show up in the message instead. Commented May 29, 2013 at 14:08
  • 1
    @C.B. You can encrypt the string in the registry using the aspnet_setreg tool. Commented May 29, 2013 at 18:42

8 Answers 8

Reset to default
7
+25

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.)
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

How do i check 1 and 2? For 3, in my case, using the IP address still raises the same error, but adding the 1433 port changes the error to "TCP Provider, error: 0 - No connection could be made because the target machine actively refused it" - despite this being the CORRECT tcp port number used by the SQL Server.
Updates: both my WebService and the WinForms App get the "actively refused it" error 0 if i add the ",1433". I've said above that the port is correct, since a check on the target machine with "netstat -ano| findstr 1433" shows sqlservr.exe's pid. As for #2, i've used the IIS Authentication feature, and it shows "ASP.NET Impersonation" as disabled.
Try the same connection string on the SQL server machine. Also make sure the firewall is allowing 1433 inbound on the SQL server machine.
@C.B. -- you check for 1 and 2 first by determining what the IDENTITY of your application pool is in IIS. iis.net/learn/manage/configuring-security/… -- what is it set to? You can usually rule out a permissions issue by setting the identity to something with lots of privileges e.g. LocalSystem (temporarily). Then try it. If you still get the error, then you can be pretty sure it isn't a permissions issue. FYI I just about always use the "Network Service" account.
1. is verified I am using impersonation in the web application, 2. also verified, 3 . I am sure I am using the right port I even used wireshark to ensure I am accessing the SQL server and i posted the network data in my post, I will try adding DBMSSOCN to my connection string (it might help)
4

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.

Improve this answer

3 Comments

That's a valid concern, but doesn't affect the core problem: The complete connection string will be identical in both the web app / web service and in the .NET (forms) app, and the former will raise an error for it, the latter will work just fine.
For anyone giving this "answer" a +1: do you really believe such a typo would generate "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. [...] (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)" instead of a syntax error?!?
It must be compile time error in C#. But some how if it works, providing wrong server name/password gives same error you have mentioned above. Probably wrong password formed is giving error. Anyway do you give it a try? or just reject case by your common-sense
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.

Improve this answer

2 Comments

In my case, it's enabled. Perhaps you missed the "a .Net application that uses the same connection string from the computer where the IIS resides [...] works fine." part?
the notorious "Named Pipes" part of the error message is almost always a Red Herring. See my comment above.
3

You mention that SQL Server is on another machine. You should make sure the SQL Server Browser service on that machine running.

You will find it in the Windows Services management console (as well as other locations).

In addition, you will have to make sure UDP port 1434 is open in the firewall.

Improve this answer

9 Comments

In my case, it's running. Perhaps you missed the "a .Net application that uses the same connection string from the computer where the IIS resides [...] works fine." part?
@C.B. - I don't see anything that says the working .NET app and SQL are on separate machines. In the case where the app and SQL are on the same machine, the local machine app may work. However, external apps may not. (See my update about opening the firewall.)
Well, the working .NET app is on the same machine with the IIS, and the IIS is on a different machine than the SQL Server... Is this not enough information?
@C.B. Just trying to help - remember that you are not the only one with the problem. This info may not help you, but could very well help the OP.
@C.B. -- what is the result if you open a command prompt and try to TELNET to the SQL Server over port 1433? e.g. telnet <sql_ip_address> 1433 --? Do this to rule out a FIREWALL or some other CONNECTIVITY issue. technet.microsoft.com/en-us/library/cc771275%28v=ws.10%29.aspx Fixing this problem is all about ruling out possible causes and narrowing the scope of your problem solving effort.
|
3

Check this first:

Make sure that you sql server accept remote connections

Sql Server Instance ->Properities->Connections->Allow remore connections.

following this step in second:

Try setting up a connection in VS with the Server Explorer pane:

1) Open Server Explorer.

2) Right click "Data connections" and select "Add connection"

3) In the dialog that follows, select your DataSource, and database, specify the security info, and press the "Test connection" button.

4) When the connection works, press "OK"

5) highlight your database in the Server Explorer pane, and look at the Properties pane. A working example of the connection string will be shown, which you can copy and paste into your app or config file.

Improve this answer

4 Comments

1: remote connections are allowed. 2: The only difference between the VS-generated connection string and the one i was using is an extra "Persist Security Info=True". Didn't help though, i still get "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)".
@C.B. -- this is unclear, when you TESTED the connection in Visual Studio -- did it work or no?
@mikey : it worked, but i was already clear that the connection string itself was okay, since it worked in the test app. By the way, why don't you add your own answer, to have all the useful links you provided in one place? ;-)
I stated in the question that I tried accessing the SQL server through a console application (.Net) and it works, I also added that I used wireshark and gave the connection data I found. I am sure you can rule out the connection issue.
2

Check the values of ServerName, DomainName, DBName & MyPassword. Are they correct ?? How they populate?

Improve this answer

Comments

2

Let us check into the steps to resolve this error.

1) SQL Server should be up and running. 2) Enable TCP/IP in SQL Server Configuration 3) Open Port in Windows Firewall 4) Enable Remote Connection 5) Enable SQL Server Browser Service 6) Create exception of sqlbrowser.exe in Firewall 7) Recreate Alias

Source here

Hope it helps

Improve this answer

Comments

0

may be the SQL server was not recognized by client system

because the error occurred due to sql server network not available and also it may be some error for network failure

Improve this answer

1 Comment

Umm... no. Perhaps you missed the "a .Net application that uses the same connection string from the computer where the IIS resides [...] works fine." part?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.