Hi everyone, I hope someone can help me here!
A friend of mine set up a Windows 2003 Server for me in my home with Sql Server 2005. I'm developing a website for prospective employers to see the kinds of things I can program since I have no field-related work experience. I have a cool site up, but I can't showcase the any features that use sql server (which obviously, are the kinds of things employers want to see) because the login and password my friend remembers produces this error:
Cannot connect to server.
Additional information:
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 doesn't allow remote connections,(provider: Named Pipes Provider, error: 40-could not open a connection to sql server)(Microsoft sql server, Error:2)
My friend doesn't know how to fix this. Is there a way to create another login with admin rights in the command prompt so that I can login & get my database functionality going?
Thanks in advance,
Monster
Have you enabled remote connections for the SQL2005 instance with SQL Surface Area Configuration? You can also try to remove User Instance="true" attribute from the connection string if you don't need to generate user instnace.|||
No, because I don't know how to do those things. I'm used to just developing on my laptop, where everything always works great! Could you tell me how to do the first suggestion? And regarding the second suggestion, I think there's something there I need to understand when you transfer an application to a server. Does that mean that somehow the database is included in the app & doesn't need to interact with sql server at all? Obviously, I'm very new to this.
Thank you so much for your suggestions! I'll await your reply!
|||OK, for suggestion 1 you can click start->All Programs->Microsoft SQL Server2005->Configuration Tools-> SQL Server Surface Area Configuration->click?"Surface Area Configuration for Services and Connections"->expand the SQL instance in the tree on the left->Database Engine->Remote Connections->Using both TCP/IP and Named Pipes. For details, you can refer to:
How to configure SQL Server 2005 to allow remote connections
For the User Instance attribute,?if?it?is?true?it?indicates to redirect the connection from the default SQL Server Express instance to a runtime-initiated instance running under the account of the caller. you can refer to:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(VS.80).aspx
You can use database file included in your application, but that also requires an accessible SQL2005/Express instance to host the database file: we accomplish this by using the AttachDBFileName attribute in the connection string.
I recommend you take a look at this article which may help to understand the way VS2005 using .mdf file:
http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx
|||
Iori_Jay,
THANK YOU FOR YOUR RESPONSE...I haven't been able to check out what you've said, but I will do that tomorrow & get back to you.
Thank you for sticking with me through this!
Monster
|||It's my pleasure
What's going on now?|||
Well, I'm finally into mgmt studio and my databases are there. It turned out that in order for sql server to work on the test server it needed more ram, which was why I was locked out (it had nothing to do with the login, apparently).
But now, of course, there are other issues
|||
Whoops...must have pressed something and it posted before I was done...
I have 2 db's there, 1 for a fictitious bookstore, which works absolutely fine. The other is for a fictitious event booking site, which is accessed both traditionally via the db & then the app is rewritten to extract the data access to a web service. Neither of these work. I also have another simple web service that loads Northwind customer info, which doesn't work either.
Here are the connect strings (remember I'm a newbie):
Hey...just had a brainstorm...I'm going to try it & get back...
|||
Ok, brainstorm didn't work.
Here are the relevant sections of web.config:
<
appSettings> //
the brainstorm had to do with changing the localhost below to the name of my website, but that didn't help<
addkey="NorthwindCustomers.CustomerService"value="http://localhost:2285/KSMCADNET/WebServicesRemoting/CustomerService.asmx"/>
<
addkey="EventBookingWebService.EventBookingService"value="http://localhost:2285/KSMCADNET/EventBookingWebService/EventBookingClient/EventBookingService.asmx"/>
</
appSettings>
<
connectionStrings>
<
addname="pubsConnectionString1"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\pubs.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient"/>
<
addname="KimsBookstoreConnectionString"connectionString="Data Source=notebook\SQL2K5DEV;Initial Catalog=KimsBookstore;Integrated Security=True"providerName="System.Data.SqlClient"/>
<
addname="KimsBookstoreConnectionString2"connectionString="Data Source=notebook\SQL2K5DEV;Initial Catalog=KimsBookstore;Integrated Security=True"providerName="System.Data.SqlClient"/>
<
addname="EventBookingConnectionString"connectionString="Data Source=notebook\SQL2K5DEV;Integrated Security=True;Initial Catalog=EventBooking"providerName="System.Data.SqlClient"/>
<
addname="EventBookingConnectionStringServer"connectionString="Data Source=sqlsvr;Initial Catalog=EventBooking;Integrated Security=True"providerName="System.Data.SqlClient"/>
<
addname="KimsBookstoreConnectionStringServer"connectionString="Data Source=sqlsvr;Initial Catalog=KimsBookstore;Integrated Security=True"providerName="System.Data.SqlClient"/>
<
addname="KimsBookstoreConnectionString2Server"connectionString="Data Source=sqlsvr;Initial Catalog=KimsBookstore;Integrated Security=True"providerName="System.Data.SqlClient"/>
<
addname="NorthwindConnectionString"connectionString="Data Source=notebook\SQL2K5DEV;Initial Catalog=Northwind;Integrated Security=True"providerName="System.Data.SqlClient" />
<
addname="NorthwindConnectionStringServer"connectionString="Data Source=sqlsvr;Initial Catalog=Northwind;Integrated Security=True"providerName="System.Data.SqlClient" />
</
connectionStrings>
//sqlsvr is my test server, that my friend built, the notebook source is my dev laptop where everything works fineThe error I get when I try to connect to the data driven EventBooking site is this (so odd, because the bookstore connects fine, although 2 images consistently err, where the others work fine):
Server Error in '/' Application.
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: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)
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: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)
Source Error:
Line 37: da = new SqlDataAdapter();Line 38: da.SelectCommand = cmd;Line 39: da.Fill(ds, "Rooms");Line 40: Line 41: ddlRooms.DataSource = ds; |
Source File:e:\1ocmcad\EventBookingWebService\EventBookingClient\EventBookingClient.aspx.cs
Line:39
Stack Trace:
[SqlException (0x80131904): 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: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734867 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 EventBookingWebService_EventBookingClient_EventBookingClient.Page_Load(Object sender, EventArgs e) in e:\1ocmcad\EventBookingWebService\EventBookingClient\EventBookingClient.aspx.cs:39 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061 |
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
The error I get when I try to run the same app written with the web service is this:
Server Error in '/' Application.
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: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)
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: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)
Source Error:
Line 29: Calendar1.TodaysDate = DateTime.Now;Line 30: Calendar1.TodayDayStyle.BackColor = Color.Tan;Line 31: DataBind();Line 32: Line 33: |
Source File:e:\1ocmcad\EventBookingWebService\EventBookingClient\EventBookingWebServiceClient.aspx.cs
Line:31
Stack Trace:
[SqlException (0x80131904): 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: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734867 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770 System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +92 System.Web.UI.WebControls.ListControl.PerformSelect() +31 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70 System.Web.UI.Control.DataBindChildren() +216 System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding) +216 System.Web.UI.Control.DataBind() +12 System.Web.UI.Control.DataBindChildren() +216 System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding) +216 System.Web.UI.Control.DataBind() +12 EventBookingWebService_EventBookingClient_EventBookingWebServiceClient.Page_Load(Object sender, EventArgs e) in e:\1ocmcad\EventBookingWebService\EventBookingClient\EventBookingWebServiceClient.aspx.cs:31 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061 |
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
And the error I get when trying to run the Northwind web service is this:
Server Error in '/' Application.
No connection could be made because the target machine actively refused it
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.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it
Source Error:
Line 45: [System.Web.Services.Protocols.SoapDocumentMethodAttribute("http://www.1ocmcad.net/GetCustomers", RequestNamespace="http://www.1ocmcad.net", ResponseNamespace="http://www.1ocmcad.net", Use=System.Web.Services.Description.SoapBindingUse.Literal, ParameterStyle=System.Web.Services.Protocols.SoapParameterStyle.Wrapped)]Line 46: public System.Data.DataSet GetCustomers() {Line 47: object[] results = this.Invoke("GetCustomers", new object[0]);Line 48: return ((System.Data.DataSet)(results[0]));Line 49: } |
Source File:c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\fdd4322a\74322047\App_WebReferences.vmho5c06.1.cs
Line:47
Stack Trace:
[SocketException (0x274d): No connection could be made because the target machine actively refused it] System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress) +1002146 System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP) +33 System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception) +431[WebException: Unable to connect to the remote server] System.Net.HttpWebRequest.GetRequestStream() +1504525 System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) +103 NorthwindCustomers.CustomerService.GetCustomers() in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\fdd4322a\74322047\App_WebReferences.vmho5c06.1.cs:47 WebServicesRemoting_ConsumeService.btnGetCustomers_Click(Object sender, EventArgs e) in e:\1ocmcad\WebServicesRemoting\ConsumeService.aspx.cs:22 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102 |
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
I've enabled remote connections via named pipes & tcp/ip for the dev version of sql server 2005 as you suggested & also enabled sql server browser like the article you referred me to suggested.
I'm going to work on this all weekend, so there won't be any gaps in communication...I hope to hear from you soon!
Kim
|||
Ok, this is for all you guys who are looking for answers. I spent days on this problem (I won't say how many!) and it was all because of my lack of Sql Server knowledge. The initial problem "locked out of sql server mgmt studio" was an issue of my server not having sufficient memory. After that, I kept getting a myriad of login errors, which changed every time I tried another way to connect to the databases.
What the issue was, was that I didn't have permissions & users & logins set up right in management studio. I ended up creating a new user for each database under each databases' Security > Users folder of "BUILTIN\Administrators" and set role memberships so that that account could access the db's.
I hope this helps someone who is as frustrated as I was!
Thanks for all your help, Iori_Jay.
Monster
|||I'm glad to see you've solved the issue, really great job for a person not familiar with SQL
|||
Thank you for the compliment! Hopefully one day in the future I will be answering other people's questions rather than asking a ton of them!!!
And on that issue, could you please take a look at my other question?http://forums.asp.net/thread/1471646.aspx
Thanks so much!
Monster
nyaung wrote:
