I'm new to using VS2005 and this is my first project connecting to our MS SQL 2000. App worked fine on my dev PC, connection to the same MS SQL Server. Published the web site to the web server (same server running MS SQL Server) and getting the below error. Is this a permission setup problem on SQL Server or does it really think it's connecting with a MS SQL 2005 database?
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)
Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: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)
Where is your published website hosted? I would suggest you check the connection strings in your web.config file. It might be the case that when you were developing, the server was (local) but now since you published the website to another server, it is still trying to connect to the (local) server where it should connect to the remote server.
|||Thanks for the reply. I attached my connection string info from the web.config file and you can see it's pointing to Server8.
The web site is published on Server8 also. Server8 is our Windows 2003 server running IIS and SQL Server 2000. So while in dev mode on my local PC, I was hitting server8 it appears.
<connectionStrings><addname="PP_PlusDevConnectionString"connectionString="Data Source=server8;Initial Catalog=PP_PlusDev;Integrated Security=True"
providerName="System.Data.SqlClient" /><addname="PP_PlusDevConnectionString2"connectionString="Data Source=server8;Initial Catalog=PP_PlusDev;Integrated Security=True"
providerName="System.Data.SqlClient" /></connectionStrings>
|||Whoa, it just occurred to me what the problem could be, but I still need help on resolving it. I recently installed the SharePoint Service 3.0 on Server8, and found out the hard way I didn't install it correctly. I was hoping it would use the existing MS SQL 2000, but instead it installed the MS SQL 2005 express. I also had to change the Port for my IIS Intranet site to use port 8081, as Sharepoint comandeered port 80.
So, I have to wonder, how does the connection string on my Dev PC know to hit the MS SQL 2000 database, but the same web site published on server8 appears to be hitting the MS SQL 2005 express? I guess I will be researching this with Sharepoint in mind. Any ideas?
|||Since SQL Server 2000, you can have multiple instances of SQL Server installed on a single machine. You can have up to onedefault instance,and then multiplenamed instances. The limit on the number of named instances depends on the edition of SQL Server you have installed.
I'd guess that the SQL Server 2000 is the default instance. By default, SQL Express is installed to the named instance .\SQLEXPRESS, but you can change that at installation. (The . just means local machine; you can substitute the machine name or localhost).
You can specify the instance as part of the connection string, such as Server8 or Server8\SQLEXPRESS.
But I'm betting this is not your problem. By default, when you install any version of SQL Server 2005, it does not allow remote connections, just as the error message said. You have to manually enable this, either through the Surface Area Configuration Tool or with T-SQL.
But the question remains: Is Server8 also your Web server? If it is, then you are not connecting remotely. In that case the problem lies elsewhere.
I hope this helps you find the problem, rather than making it more confusing!
Don
|||I think it's the other way around. I think it's defaulting to SQL Server Express and I want it to default to SQL Server 2000. Is there something I can do to insure this?
But to answer your question, Server8 is indeed the Web server and running SQL Server 2000 and SQL Server Express.
|||Okay, then try this. In your connection string, make the Data Source parameter localhost instead of server8. ADO.NET may be getting confused thinking that it is accessing a remote server. If that solves the problem, great.
But it still confuses me that you seem to be defaulting to SQL Express. That suggests that Express was installed as the default instance and that SQL Server 2000 was installed as a named instance. Is there any way to can find out what instances of SQL SErver are installed and their names? Maybe with Server Explorer in Visual Studio? Or you may have to ask the hosting company.
But it still feels like we're missing something...
Don
|||I think you are on the right path. I changed Server8 to localhost in the web.config and did get a differnt error which led me to check out the ASP.net configuration for the Intranet web site. I wanted to confirm that it was indeed using ASP.net v2, as v1 appears to be getting set by default. It was set to v2, but I saw the option to edit the web.config. Clicked on that option and found the following setting:
LocalSQLServer - data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true
I think this qualifies as the AH-ha moment. So now, what should I set this too to look at the MS SQL 2000? I think you have already answered that, so let me confirmed the named instance of it...right?
|||I did confirm the named instance is: MSSQLSERVER
So replaced SQLEXPRESS with MSSQLSERVER.
Now getting the follower error:Login failed for user 'NT AUTHORITY\NETWORK SERVICE'
1. I suspect I didn't update the web config correctly, as it should be using integrated security.
2. When I get back to VS and edit the remote project, it tells me the web.config has been edited by another source, do I want to set it back to default. I would rather set this correctly in VS, but I don't see in the web config where the server named instance is set.
|||I think your first connection string was fine to connect to sql server 2000. Because to connect to SQL Server 2005 you have to provide "data source=.\SQLEXPRESS" if you have used the default instanse name while installing SQL Server 2005.
Now I would suggest you to use the first connection string and also do some configurations on you server. Go to stat-->programs--> Sql Server 2000-->client Network Utility. Select General Tab and make sure that "TCP/IP" and "Named Pipes" protocols are enabled. Hope it will solve your problem.
|||Thanks for all the replies...but now I'm getting confused. TCPIP and Named Pipes have been enabled all along, no change has been made there.
Keep in mind I installed MS SQL Server 2000 first. Just recently I installed the SharePoint Service 3.0 that installed SQLExpress, which is only used by Sharepoint. I want my ASP.net applications to use MS SQL Server. I do NOT want to connect to SQL Server 2005 Express.
The named instance for SQL Server 2000 is MSSQLSERVER
But just changing the named instance in the web.config was not enough. I suspect I need the correct connection string which I do not have. Here's what I currently have it set to via IIS ASP.net edit web config: data source=.\MSSQLSERVER;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true
But to add to my confusion, I do not see this reflected in the Web.config when viewed via VS 2005.
I'll be happy to use the orignal connection string, but maybe I'm missing your point as I don't see how.
|||
thirt:
Here's what I currently have it set to via IIS ASP.net edit web config: data source=.\MSSQLSERVER;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true
Your connection string needs changing, since it is set up for SQL Express. It should look something like this:
data source=.\MSSQLSERVER;Integrated Security=SSPI;Initial Catalog=PP_PlusDev
That is more like what you started with, but now you have the data source set correctly.
Don
|||Thanks! I used that string but back to getting this error:Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
I looked to add this into the SQL's security but couldn't find that name or service.
|||Okay, cool. It's always progress when the error message changes!
This means that the NETWORK SERVICE, the default process identity for ASP.NET, doesn't have permissions to access SQL Server. You can resolve this in two ways. One way is to give NETWORK SERVICE the permissions it needs in the database, such as to access specific stored procedures or tables used by your application. The other is to instead use a SQL Server login, which you have to specify in your connection string. That login would have the permissions necessary for your application.
Does this make sense?
Don
>>One way is to give NETWORK SERVICE the permissions it needs in the database
I'm having problems finding this service. I go to EM's Security/logins/add new user/select the local server/and can't find the NT or Network Service to add.
No comments:
Post a Comment