Web App to SQL Server Not Connecting ( Error Locating Server/Instance Specified)

Question

I know this is an often asked question, but I'm still receiving this error and I have tried:

• Using localhost, the IP address and the instance name in the connection string
• Changing the SQL Server Configuration Manager so Named Pipes and TCP are both enabled Ensured the database is set to allow remote connections
• Opened 1433 & 1434 for UDP & TCP in Windows Firewall, in fact I tried turning off the firewall for a moment to see if it would connect
• Ensured the browser & the MSSQLSERVER service are both up and running using NETWORK SERVICE

Now for some oddness. The SQL Server instance and the IIS running the website are on the same machine.

If I open a browser and browse to the website I get the error. If I open a browser on my local machine and browse to the website I get the error. If I change the connection string in my development PC to the IP address of the remote server running SQL server and debug the application it connects and runs without error!

I am totally at a loss now as to what could be the error.

This is SQL Server 2008 RC2.

I'm on the verge of giving up - I very much appreciate the efforts you guys have made, I have tried all the suggestions here and I am coming to the conclusion that something must have gone awry in the set-up of the server, the website or the database. Just to reiterate if you remote desktop onto the server and connect to the website using http://gdservers you can login and it all works fine, if you use http://gdservers.<domain>.co.uk the login fails. The error in the event log is a membership credentials failure, not blocked by firewall or anything like that. If there's any other suggestions I'd love to hear them, thanks again.

Answer 1

I don't think anyone spotted it and I only spotted it on Wednesday and then realised I'd made another error. If someone has mentioned it above let me know and I'll change the award and give them the points.

The applicationName was missing from the membership provider. I spotted it on Wednesday and added one in, what I didn't realise I'd done is made a spelling mistake when adding the applicationName in, so when I did this, it didn't fix it, so I thought it wasn't the problem.

Yesterday whilst reviewing the code I noticed I spelt the applicationName 'GSServers' instead of 'GDServers' when I changed it, it all started working.

Not a very satisfactory conclusion and I apologise.

Thank you to everyone who tried to solve this problem and sticking with it.

As I said if someone did mention this in an answer or comment somewhere let me know and they can have the points.

Answer 2

Comment: Using SSPI with a windows user account is preferable from a security perspective.

Answer:

Port 1433 is used for SSL by the default (unnamed) instance. Ensure that your named instance has an SSL port properly assigned:
http://blogs.msdn.com/b/sqlexpress/archive/2005/05/05/415084.aspx

Try these connect strings (note that they don't use 'localhost' - which is case sensitive, I believe):

• 'Provider=SQLOLEDB;Data Source=COMPUTERNAME\SQLEXPRESS;Initial Catalog=Test;User ID=COMPUTERNAME\USERNAME; Password=PASSWORD;Integrated Security=False'

• 'Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=Test;User ID=COMPUTERNAME\USERNAME; Password=PASSWORD;Integrated Security=False'

• 'Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=Test;User ID=COMPUTERNAME\USERNAME; Password=PASSWORD;Integrated Security=SSPI'

---

ADDITIONAL EDIT:

Whenever client is run on same machine as SQL Server, the error occurs. When client is run from dev machine, no error. You may be failing to resolve the machine name because DNS does not support any of the names.

Suggestion: Connect using localhost. Ensure you can ping localhost and/or telnet localhost 1433. Ensure your hosts file (e.g. C:\Windows\System32\drivers\etc\hosts) has entry:

  127.0.0.1       localhost

Alternatively, use: (local)\INSTANCENAME

Run SQLCMD –L to list installations (confirming correct browser operation).

Ensure you don't have older versions of the management tools ensured on the same server. E.g. 2008 & 2005 tools cannot coexist - but 2008 tools are backwards compatible, so you can delete both and just cleanly install 2008.

Answer 3

The fact that it's trying to login as sa locally suggests you're not using the connection string in your code; I don't think it's a SQL server problem, but an app problem.

In your app, make sure you're actually using the value of the connection string. Don't use the default constructor. E.g.

using(SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["myconnectionstring"].ConnectionString)){ /* .. code */ }

not

using(SqlConnection conn = new SqlConnection()){ /* .. code .. */ }

If I change the connection string in my development PC to the IP address of the remote server running SQL server and debug the application it connects and runs without error!

It's possible you're dev box has been added an authenticated login, and it's automagically "working".

Answer 4

If your web app intends to use SQL Server Authentication with a SQL login/password then make sure that your connection string explicitly states Integrated Security=False. Otherwise your connection may default to Windows Authentication and not behave as expected.

Edit

Also, be sure that the connection string you're tweaking is the one that is actually being used to make the connection. Sometimes things can get confusing when an application has multiple components that may have their own configuration settings. For example, the problem in the thread here turned out to be:

I was updating the connection string on the web.config but, it's a multitier application, the data access component is in a separate .dll and has its own connection string.

Edit

I'm beginning to wonder if the SQL Membership Provider is using Windows Authentication to connect to the SQL Server even if your data connections aren't. Check out the video here and perhaps review your settings (especially the authentication setting in the provider setup steps).

Answer 5

Usually the easiest and most secure way is using integrated security:

<connectionStrings>
    <add name="HitCounterContext"
         connectionString="Data Source=(local)\SQLEXPRESS;Initial Catalog=db;Integrated Security=True"
         providerName="System.Data.SqlClient" />
</connectionStrings>

Make sure you use the correct instance name, which per default is "SQLExpress". I don't know how IDEs work with that, but using this connection string with IIS should at least connect successfully (which is not equal to authentication).

Maybe the authentication fails because your database must have a user for the app pool account. If you disable custom errors in your web.config so you get .net error messages, the error message should tell you that the authentication failed for the app pool user (the message actually tells you which user is used). For instance: If you create a new website called "yoursite.com" using IIS 7, per default it creates a new app pool with the name "yoursite.com". With that you'll have to add a user to the database for IIS APPPOOL/yoursite.com (might not be in the users list) and let it be the db_owner for testing.

Add the user for the app pool using "SQL Server Management Studio" like that (sorry it's german. This actually is the form after the user was added, but it should look the same while adding):

After that, your users list should look like

and the servers login list like

where each app pool has its own login for the server and user for the respective database.

By the way: Connecting from the Management Studio works just the same like from your asp.net app, except that you normally use your current user with SMS:

So, if this works, the connection from your app should do as well. The rest relies on the user for the app pool.

Answer 6

How are you indicating the Data Source/server name in your connection string? I've encountered situations where there was a hosts file on the server redirecting elsewhere, or the server was indicated by a domain name which resolved to the external IP address of the server, or other similar oddness.

Also, if you have SQL Serve installed on the server, do you also have Management Studio installed there? It's the quickest way to rule out code or permission errors, just trying variations through Management Studio until you find login details that work (or know that none do).

Answer 7

Stupid question but, have you checked the credentials of the login? I'm still very much a noob in this area but I found out thru backing up and restoring a database (at least in MS SQL Server) that the restored user login doesn't work.

I've had to delete the user from the database and add it back in, then everything works fine. Could it be you are accessing a different version of the database on the non-working machine?