Ongoing problem connecting to SQL Server
Hi,
I am getting the error:
System.Data.SqlClient.SqlException: An error has occurred while
establishing a connection to the server. When connecting to
SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could
not open a connection to SQL Server)whilst trying to connect to a database on SQL Server Express from an ASP .NET application. I'm trying to connect locally for testing purposes, so there seems to be little relevance in the "not allowing remote connections" error.
I was kindly helped out the other week by Jimmy Wu who gave me links to some resources to read. This allowed me to connect without an error via the Management Studio, but it did not solve my problem whilst connecting from my application.
I am able to tell you that in th SQL Server Configuartion Manager, the default pipe in the "named pipes" properties window is "sql\query". I have looked at the SQL Server error logs and found the following two lines:
2006-07-31 18:46:45.06 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].
2006-07-31 18:46:45.06 Server Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
I can tell you that the SQL Server Broswer is running, as it is listed in "net start" as:
SQL Server (SQLExpress)
SQL Server Browser
I must point out at this point that I do not understand this information, I just got it because the research I've done so far suggests it might help people who might try and solve this problem for me!
My connection string is as follows:
"server=(BENCHLAP/SQLEXPRESS);database=AlfordDocsIntranet"
although I have tried variations on this, including replacing "BENCHLAP" with "(local)" and "localhost"
I have already tried and failed to find a solution by studying http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx and http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx
Can anybody suggest where I am going wrong please? I'd very much appreciate it!
It is so frustrating wanting to develop my application but being unable to do so until I can sort out this problem with connecting to my database.
Many thanks
Ben
[2383 byte] By [
BenCh1] at [2007-12-22]
Hi Ben,
The information that you've pasted is nothing to be alarmed by. \\.\pipe\SQLLocal\SQLEXPRESS is the local-only named pipe used to fascilitate Shared Memory connections. It will be created whenever Shared Memory is enabled on the server. SQL Server Browser is the name resolution service used by SQL Server to transmit connectivity information to clients connecting to named instances.
Going back to your problem, I'm guessing that you've specified your server incorrectly. Try using a backslash, '\', instead of a forward slash '/'.
Il-Sung.
That's given me a slightly different error message now.
It's the same except for at the end it says:
(provider: SQL Network Interfaces, error: 26
- Error Locating Server/Instance Specified)"
Thanks for the help but I'm not sure which error message is best!
Any other ideas what it could be?
This is because your server is a named instance, we need some mechanism to locate it.
In short, you need run Sql Browser on the server. If you have firewall, please put UDP 1434 into exception and any TCP port your SQL server listens on.
Thank you for your continued help with this Xinwei Hong and Il Sung-Lee.
Here's what I've done so far on your advice:
I read those references you posted, and allowed port 1434 to go through my windows firewall (for the SQL Server Browser) and 1433 as well (for the named instance BENCHLAP\SQLExpress)
I also added an exception for the SQL Server program by adding new program exception and browsing to Sqlservr.exe.
Then I went into configuration manager and enabled TCP/IP protocols.
I then ran regedit to change the following registry key to value "1", but found that it was already 1 so i left it the same [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\SQLBrowser\Ssrplistener
Then I stopped the SQL Server instance and the SQL Browser and restarted them both.
Now all protocols (TCP/IP, named pipes, VIA and shared memory) are enabled for SQLEXPRESS and for SQL Native Client Configuration, even though all I'm trying to do is access the database from the same machine as SqlExpress is running on!
I've tested it with the following connection strings:
"server=(BENCHLAP\SQLEXPRESS);database=AlfordDocs"
"server=(localhost\SQLEXPRESS);database=AlfordDocs"
"server=((local)\SQLEXPRESS);database=AlfordDocs"
"server=localhost\SQLEXPRESS;database=AlfordDocs"
"server=BENCHLAP\SQLEXPRESS;database=AlfordDocs"
I'm at a loss. I can access my database with no problems from the management studio, but I still have the exact same connectivity error on my ASP .NET page, no matter what version of connection string I use.
Thanks so much for trying to help, but if you can think of any other mistake I've made, or any tests I can do to find my problem, I'd really appreciate it.
Ben
Wait, we may have made SOME progress....
Sorry I didn't notice this before. Now, I get a slightly different error. It has a connection but there's something wrong with it. Here's the error:
A connection was successfully established with the server,
but then an error occurred during the login process.
(provider: Shared Memory Provider, error: 0 - No process
is on the other end of the pipe.)
at System.Data.SqlClient.SqlInternalConnection.OnError
(SqlException exception, Boolean breakConnection)
And that error is sometimes replaced by this one:
Login failed for user ''.
The user is not associated with a trusted SQL Server connection.
I don't know what determines which error shows. It seems random at the moment. Is this better or worse than the last error we worked on? I'm nearly ready to crawl under a wet stone and sob(!) :-(
Ben
In most cases, this is because your IIS is running under machine local account such as "NT AUTHORITY\SYSTEM" or "NT AUTHORITY\NETWORK SERVICES". Such account cannot be used to authenticate connection to remote SQL Server. This is security feature defined by Windows.
To resolve this, http://support.microsoft.com/kb/307002/.
Basically, you need to run IIS under
(1) a domain account.
Or
(2) a machine account that is created on both machine.
or
(3) use SQL login and change your connection string accordingly.
Thank you all so much for your help with this. It has been the most tedious and least rewarding problem I've ever had to solve in my life, but it has been really good to know that there are chaps out there who will try and help. Thanks! To be honest I don't know which one of you finally cracked it, it was probably a combination of everyone's efforts. I really appreciate your time.
One of your recommended readings said that I should specify "Trusted_Connection=yes" in my connection string, and I think that this, combined with changing any number of properties and settings in IIS and SQL Server has finally resolved the issue. At least, it seems to have done.
Best wishes
Ben
(I find it so hard to believe that quite so much effort needed to be put into establishing a connection to an database that is on the same computer as the development PC. If anyone from Microsoft is reading this, I was really enjoying using SQL Server until it infuriated me so much. I do hope you take it into consideration when improving the product)
I'm glad that you've managed to get your connectivity to the server working. Thanks for your feedback -- your comment is appreciated and we'll definitely make a note of it.
Il-Sung.
