SQLServerExpress not allow remote connection

VictorVictor · October 19th, 2006, 03:56 PM

Help Please:

I'm writing a small web service and for testing am using the Northwind db. Testing fails due to the following message:

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

The (entire) code shown below uses the Northwind db, which I have just downloaded and installed.

I cannot find a way to change the default settings for the SQL Server to allow remote connections. Anyone suggest a way that I can do that? I am using SQL 2005 Express.

The error is not the conn string. Both conn definitions above (commented out) do not work, itâs the SQL Server default settings.

-------------------------
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Data.SqlClient

<WebService(Namespace:="http://www.FTA.net/customers", Description:="FTA web service")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicPr ofile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.Des ignerGenerated()> _
Public Class Customers
    Inherits System.Web.Services.WebService
    <WebMethod()> _
    Public Function GetCustomers() As DataSet
        Dim conn As SqlConnection
        Dim myDataAdapter As SqlDataAdapter
        Dim myDataSet As DataSet
        Dim cmdString As String = "SELECT * FROM Customers"
        'conn = New SqlConnection("Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI")
        'conn = New SqlConnection("Server=localhost; uid=sa;pwd=;database=Northwind")
        myDataAdapter = New SqlDataAdapter(cmdString, conn)
        myDataSet = New DataSet
        myDataAdapter.Fill(myDataSet, "Customers")
        Return myDataSet
    End Function
End Class
-------------------------

Thanks, VV

Imar · October 19th, 2006, 04:04 PM

Hi VictorVictor,

Take a look at the "Other Issues" section near the bottom of this article:

http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=395

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

VictorVictor · October 20th, 2006, 12:15 AM

Hi Imar:
Interesting. Thanks for the direction to your article.

I followed the procedure your article specified and enabled both named pipes and TCP/IP but still no joy. Same error msg.

Two other things I tried:

(1) I changed the name of the DataSource from local to my machineâs name and SQLServer: YOUR-D9E47C800B\SQLEXPRESS.
When I use that string in the following After: conn statement I get the same error as before but with a different number. See below.

Before: conn = New SqlConnection("Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI")
After: conn = New SqlConnection("Data Source=(D9E47C800B\SQLEXPRESS);Initial Catalog=Northwind;Integrated Security=SSPI")

Error msg:
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: 26 - Error Locating Server/Instance Specified)

--------------------------------------------------------------------------------------------------------------------------

(2) I changed the name of the Server from localhost to my machineâs name and SQLServer: YOUR-D9E47C800B\SQLEXPRESS.
When I use that string in the other, following After conn statement I get a different error. See below:

Before: conn = New SqlConnection("Server=localhost; uid=sa;pwd=;database=Northwind")
After: conn = New SqlConnection("Server=D9E47C800B\SQLEXPRESS; uid=sa;pwd=;database=Northwind")

Error msg:
System.Data.SqlClient.SqlException: 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.)

I am heartened by the remark that a connection was successful. So, it looks like it doesnât like any form of âlocalâ for a db name. I have no clue on what it means about no process on the other end.

VV

Imar · October 20th, 2006, 01:08 AM

You probably don't have an SA account with a blank password, as you don't get one by default. So, the last connection strings are probably not going to work.

But are you sure Northwind is connected to your SQL Express installation? By default it's not installed anymore. If it's not, you need a different connection string that attaches your database automatically

Do you have a copy of "sql server management studio express"? That makes managing SQL Server a lot easier.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

VictorVictor · October 20th, 2006, 04:25 PM

Imar:

Thanks for the quick reply. Sorry it's taking so long but ongoing business has high priority.

Yes, I have sql server management studio express and am using it to create a new db. BTW, Northwind was not connected but now it is. Still unable to connect to it tho...

I have the following string in my little webserver code and suspect that the url: www.FTA.net which does not actually exist may be what is preventing me from connecting. I am not sure tho because I believe the url only represents a unique string and is not
actually accessed. But I'm not sure about that. You might set me straight on that.

<WebService(Namespace:="http://www.FTA.net/customers", Description:="FTA web service")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicPr ofile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.Des ignerGenerated()> _
Public Class Customers ...
...class definition follows, and is unchanged from earlier code.

So, I have spend much of today creating a SQL express database using the sql server management studio express facility. And Wow, what an exercise in frustration. I have successfully created 5 tables as the dbo, and have attempted to enter a record in the first one. It will not accept the data. Keeps giving an error messages as follows:

No row was updated.
The data in row 1 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: String or binary data would be truncated.
The statement has been terminated.

There is nothing wrong with the data. The field calls for a varchar(10) and I am keying in (without quotes) "Active".

If I make that file one that will accept Null and then null it out, SQL objects to some other field, and on and on...

Almost ready to throw out SQL 2005 Express and re-install SQL 2000.

VV

Imar · October 21st, 2006, 03:11 AM

Hi VictorVictor,

I don't think the URL has anything to do with this. It looks like a configuration /security issue to me.

If you want to use SQL Server Express, why don't you just add a database to the App_Data folder and then use a connectionstring like this:

Code:

<connectionStrings>
  <add name="YourName" connectionString="Data Source=(local)\SqlExpress;
    AttachDbFilename=|DataDirectory|\YourDatabase.mdf;Integrated Security=True;
    User Instance=True" providerName="System.Data.SqlClient" />
</connectionStrings>

That should work much better than trying to set up SA accounts on the database.

Hope this helps,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

VictorVictor · October 23rd, 2006, 03:38 PM

Imar:

Took your advice as follows:

Made an ordinary simple aspx page and used the web.config manager to establish a connection to the Northwnd sql database which I attached to the project in the data folder.
Ran it, it worked fine.

Took the exact same web.config, database, connection string, etc, and used them in a simple web service.
Ran it, did not work.

Below appear the web service code, the error msg, and listing extracts showing the connection string.

This is my first web service and I'm trying to do it without spending hours going thru a book on web services. Seems like my shortcut approach is not going to cooperate, so I am going to turn to line 1, page 1 of the book and follow it. Hopefully, all will become clear as I go thru the book.

Thanks for your help.

VV

Imar · October 23rd, 2006, 03:41 PM

I don't see any code or error messages below.

But, did you create the web service in the exact asp.net project as the aspx page? That is, did you create a brand new website and then added a Web service to it?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

VictorVictor · October 23rd, 2006, 03:41 PM

Oops, forgot to attach the listings:

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Data.SqlClient

<WebService(Namespace:="http://www.FTA.net/customers", Description:="FTA web service")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicPr ofile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.Des ignerGenerated()> _
Public Class Customers
    Inherits System.Web.Services.WebService

    <WebMethod()> _
    Public Function GetCustomers() As DataSet
        Dim conn As New SqlConnection
        Dim myDataAdapter As SqlDataAdapter
        Dim myDataSet As DataSet
        Dim cmdString As String = "SELECT * FROM Customers"
        conn.ConnectionString = "<%$ ConnectionStrings:NORTHWNDConnectionString %>"
        myDataAdapter = New SqlDataAdapter(cmdString, conn)
        myDataSet = New DataSet
        myDataAdapter.Fill(myDataSet, "Customers")
        Return myDataSet
    End Function

End Class

System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.
   at System.Data.Common.DbConnectionOptions.GetKeyValue Pair(String connectionString, Int32 currentPosition, StringBuilder buffer, Boolean useOdbcRules, String& keyname, String& keyvalue)
   at System.Data.Common.DbConnectionOptions.ParseIntern al(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey)
   at System.Data.Common.DbConnectionOptions..ctor(Strin g connectionString, Hashtable synonyms, Boolean useOdbcRules)
   at System.Data.SqlClient.SqlConnectionString..ctor(St ring connectionString)
   at System.Data.SqlClient.SqlConnectionFactory.CreateC onnectionOptions(String connectionString, DbConnectionOptions previous)
   at System.Data.ProviderBase.DbConnectionFactory.GetCo nnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
   at System.Data.SqlClient.SqlConnection.ConnectionStri ng_Set(String value)
   at System.Data.SqlClient.SqlConnection.set_Connection String(String value)
   at Customers.GetCustomers() in C:\Choctaw\WebSite8\App_Code\Customers.vb:line 19

        <add name="NORTHWNDConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\SQL Server 2000 Sample Databases\NORTHWND.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True"
            providerName="System.Data.SqlClient" />

        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>"

    <connectionStrings>
        <add name="NORTHWNDConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\SQL Server 2000 Sample Databases\NORTHWND.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient"/>
    </connectionStrings>

VictorVictor · October 23rd, 2006, 03:42 PM

No, they were two separate projects.