Hello,

Am I going mad. I recently reinstalled Sql Server after installing XP SP3 and none of my connection strings referencing Sql Server as "localhost" work any more, "localhost" can't find my local database server installation. What's up?

Bob

Meant XP Service Pack 2

It’s starting to appear that the Windows Firewall under XP SP2 is the culprit.

The following worked fine under XP SP1:

value="server=localhost;Trusted_Connection=true;da tabase=Reports" />

Under XP SP2 using “localhost” and Windows Authentication like this generates:

Error: SQL Server does not exist or access denied.

So I tried changing “localhost” to “(local)” – have no idea what the difference is - with Windows Authentication:

value="server=(local);Trusted_Connection=true;data base=Reports" />

That generates:

Error: login failed for user 'MACHINENAME\ASPNET'.

Then I tried using Sql Server Authentication with “(local)”:

 value="server=(local);user id=sa;password=myPassword;database=Reports" />

THIS WORKS!!!

I also tried “localhost” with Sql Server Authentication:

value="server=localhost;user id=sa;password=myPasswor;database=Reports" />

and again got:

Error: SQL Server does not exist or access denied.

I found some info on google addressing this weird issue, such as:

http://www.justinvp.com/archives/000087.asp

I tried some of the solutions in the KB article at:

http://support.microsoft.com/default.aspx?kbid=841249

but nothing seems to work. I even granted Windows Firewall exceptions to Port 1433 (Sql Server’s Default TCP/IP port??), but no luck.

Does anyone:

a) Have any insight to offer.

b) Know what the difference between “localhost” and “(local)” is.

Perplexed,

Bob

Chk out:: http://dotnetjunkies.com/WebLog/pete...2/25/7897.aspx

Best Regards
Vadivel

MVP ASP/ASP.NET
http://vadivel.thinkingms.com

Thanks Vadivel. However, I read that page last night and didn't find it very helpful. Information like "Localhost is a term for the local IIS web site. The value of server for a local SQL Server is "local" is questionable, at best. "localhost" is, I believe, simply an alias for 127.0.0.1 (the local machine) when communicating over TCP/IP.

The .NET documentation since version 1 uses this standard connection string to connect to a local instance of SQL Server in an AASP.NET application:

myConnection = New SqlConnection("server=localhost;database=pubs;Trus ted_Connection=Yes")

I've been using this string since NET 1.0 on XP. My point is that all of a sudden, for no reason I can fathom, after working for 2 years, it no longer works under XP SP2, and the XP SP2 Firewall may be the problem, though I can't be sure.

If I use my Sql Server name with Windows Authentication, I get the following error:

Login failed for user 'ELIOT\ASPNET'.

and I've already have an ASPNET account in the database that used to work. I also tried impersonation. No luck. I just can't connect to Sql Server using Windows Authentication and ASP.NET under XP SP2.

Don't get it.

Bob

Hi Bob,

When you used (local) you got an error for the ASPNET account, right?

So it seems things do work, the server can be reached, and the database accessed. Did you try adding the ASPNET account to the database again?

ALso, are you using SQL Server for Sessions? You need to give the right permissions to the ASPNET account for the Sessions feature as well...

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: The Union Forever by The White Stripes (Track 7 from the album: White Blood Cells) What's This?

Hi Imar,

Thanks for the response. When I reinstalled SQL Server and attached my database, the db had a user account for ASPNET. I forgot to add a server login for ASPNET.

However, the problem still persists. I can now use Windows Authentication, but not with “localhost”.

I created a bare bones .aspx page to keep things simple that does nothing but connect to SQL Server and display some data in a datagrid. Here is your standard .NET documentation Windows Authentication connection string, that I used forever with XP:

SqlConnection myConnection = new SqlConnection("server=localhost;" +
         "database=pubs;Trusted_Connection=Yes");

With XP SP2, I get the error:

SQL Server does not exist or access denied.

This, however, works:

SqlConnection myConnection = new SqlConnection("server=(local);" +
         "database=pubs;Trusted_Connection=Yes");

“localhost” no longer works. “(local)” now does.

I’m glad I got Windows Authentication going (thanks for the recreate the account reminder), and I can go through my apps and change “localhost” to “(local)”, but I’d sure like to know why “localhost” no longer works.

My local instance of IIS used to be able to access my local instance of Sql Server using “localhost”; it now requires “(local)”. Some google clues seem to indicate that the Windows Firewall in XP SP2 might be blocking the old, tried- and-true, “localhost” communication route, though I don’t really know what that means, or if that’s even happening. Weird.

Thanks,

Bob

Of course, using my Sql Server instance name (which also happens to be my machine name) also works as a replacement for (local), which I guess is just an alias for my machine name. "localhost", I thought, is simply an alias for IP address 127.0.0.1, which directs TCP/IP communication to my local machine, or something like that. Maybe the Windows Firewall is bloking a port that IIS needs to communicate with Sql Server, as the KB article mentioned above seems to suggest. Who knows?

Bob

Hello all.

 I dont know if it is correct or not.But to my experience I believe localhost is meant only for your web server. ie. for testing your web pages locally.

 When you give a "sqlserver" connection string the sql server driver on your webserver will look for the machine name or the proper "IP address". Further if you are working locally the sqlserver notation for local system is "(local)".
 For 127.0.0.1 : It is a loop back address for your web server and never a unique ip address for your system.
 If you are using an IP address make sure that it is a unique one .

Thank you very much Shibu for your response. Nevertheless, I am stuck with the perplexing fact that a connection string like:


SqlConnection myConnection = new SqlConnection("server=localhost;" +
         "database=pubs;Trusted_Connection=Yes");

is a) taken directly from MSDN documentation explaining how to connect an .aspx page to SQL Server and b) is how I successfully connected .aspx pages running in IIS to SQL Server all the time in XP, but can no longer under XP SP2.

In the connection sting above, "localhost" does not refer to the instance of IIS on the local machine. MSDN documentation again indicates that the "sevrer=" parameter refers to "the name or network address of the instance of SQL Server to which to connect", i.e. the data source. In fact, the meaning if "localhost" seems to vary depending on the context in which it is used. When used in a HTTP request, such as http://localhost/mywebsite, "localhost" means the instance of IIS (or other web server) running on the local machine. When used in the context of a SQLConnection object's connection string property, however, "localhost" means the instance of SQL Server (or other database server) running on the local machine.

There is nothing wrong with using "localhost" to refer to a database server in the context of a connection string in XP or XP SP1. It simply doesn't work for me on XP SP2.

Other perplexed folks have posted regarding this problem at, for example:

http://www.justinvp.com/archives/000087.asp

and

http://todotnet.com/archive/2004/08/12/306.aspx

I simply want to know why "localhost" no longer works in a context in which it used to work fine.

- Bob