1

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.

DarrylGodden
  • 1,526
  • 5
  • 25
  • 44
  • 1
    Please post relevant errors from your SQL Server Error Log and Windoes Error Log(Application+System), as well as your connection string (you should change the names for your security). – Roi Gavish Apr 30 '13 at 08:26
  • Right now I'm back at my desk I'm ready to tackle this problem, head-on! Firstly here's the connection string: – DarrylGodden May 01 '13 at 08:50
  • Here's the entry from the Windows event sorry (sorry for the formatting) :Event code: 4006 Event message: Membership credential verification failed. Application domain: d5e9e855-1-130118720463570698 Trust level: Full Application Virtual Path: /GDServers Application Path: E:\Websites\GDServers\ Process name: WebDev.WebServer40.exe Account name: KC-362\darryl Request URL: http://localhost:51054/GDServers/default.aspx Request path: /GDServers/default.aspx User host address: 127.0.0.1 Is authenticated: False Authentication Type: Thread account name: KC-362\darryl – DarrylGodden May 01 '13 at 09:00
  • The other odd thing is, there are tons of entries in the SQL Server log that say "Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: 42.96.193.206]" approximately 2-3 per second. Related or not? – DarrylGodden May 01 '13 at 09:01
  • 1
    The `Account name: KC-362\darryl` bit looks like the connection is trying to use Windows Authentication and not your SQL login/password, but `Process name: WebDev.WebServer40.exe` also suggests that this is Visual Web Developer connecting, not IIS. – Gord Thompson May 01 '13 at 09:24
  • If this sheds any light, here's the config info from web.config: – DarrylGodden May 01 '13 at 09:27
  • Is it installed on a windows 2008 R2? On this OS there are issues with loopback network driver. – Jack May 02 '13 at 12:09
  • Can you try the following and see if you can access it. Local connections can connect to the instance by using the named pipes protocol. To connect to the default instance, use osql -E –S np:. as the connection string. To connect to a named instance, use osql -E -S \\.\pipe\MSSQL$\sql\query as the connection string. – Jack May 02 '13 at 12:10

7 Answers7

1

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).

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • @DarrylGodden Rats. Can you check the SQL Server logs to see if the web app is even talking to SQL Server when it tries to connect? – Gord Thompson Apr 29 '13 at 11:53
  • Strangely it says login failed for SA incorrect password, even though we're using a login not SA. – DarrylGodden May 01 '13 at 08:23
  • Hi Gord, unfortunately this is a very small application (probably why this is so frustrating, the class file references the same connection string in the web.config: protected string appConnString = ConfigurationManager.ConnectionStrings["GDServersConnString"].ConnectionString; – DarrylGodden May 01 '13 at 09:43
  • I wonder why this was not voted up , but Thanks to you @GordThompson , You saved my day , one can check this to make it clear , its not a vulnerable https://stackoverflow.com/questions/1229691/what-is-the-difference-between-integrated-security-true-and-integrated-securit – TAHA SULTAN TEMURI Jul 26 '21 at 07:34
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.

DarrylGodden
  • 1,526
  • 5
  • 25
  • 44
0

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.

Glen Best
  • 22,769
  • 3
  • 58
  • 74
  • I've tried numerous connection strings, to no avail, including trusted connections. Just to updated everyone in this question I'm using SQL Server 2008 R2 not SQL Express, cheers. – DarrylGodden May 01 '13 at 08:21
  • I was suggesting you assign a different port (or ensure that the default/unnamed instance is shutdown) – Glen Best May 02 '13 at 05:20
0

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?

Caleb Palmquist
  • 448
  • 2
  • 7
  • 15
0

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).

David Cummins
  • 972
  • 6
  • 15
0

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):

Add the app pool user

After that, your users list should look like

Database users list

and the servers login list like

Servers login list

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:

enter image description here

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

Linus Caldwell
  • 10,908
  • 12
  • 46
  • 58
0

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".

nathas
  • 949
  • 1
  • 9
  • 15