SQL does not allow remote connections-Error 40 Problem

Question

Hello,

   In the MSDN site (http://msdn2.microsoft.com/en-us/library/2f8abfew(VS.80).aspx) there is a sample code to teach Stored Procedures in SQL Server. But it doesn't run. When one clicks on the Create Sprocs button one gets the following error message:

"An error occured 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 does not allow remote connections. (provider:Named Pipes Provider, error: 40- Could not open a connection to SQL server)" (I wish MS allowed one to copy error messages from msgboxes!)

   On this forum, there are several such complains. But there is no solution. Is there anybody out there who knows solution to this problem: Is it possible to get rid of error 40?  How can be change default settings under SQL 2005 Express? Also why remote connection is required? I am accesing a local database  .\SSQLEXPRESS. Please help.

Athena

Answer 1

SQL Express is designed to not allow remote connections.   In order to use remote connections you'll need a version of SQL higher than express.   SQL Express is designed as a free local database solution - which ultimately can be ported up to full blown SQL server, if needed.

Alternatively you could use MSDE - a performance throttled version of SQL 2000 which does allow remote connections.

To do work on a local database - from you previous postings you didn't include the connection string details, which may be your problem as you previous posting about creating/dropping tables on SQL Express worked just fine when used with an appropriate connection string.

 

 

Answer 2

Take a look at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/emsqlexcustapp.asp  Specifically the Networking Support section.

With only shared memory available by default, connections from a remote machine to SQL Server Express will fail unless the networking is turned on. To turn networking on, there are the following options:

• Use the Surface Area Configuration tool to enable networking and enable and start the SQLBROWSER service.
• Use SQL Server Configuration Manager to enable relevant protocols and start SQL Browser. Figure 2 shows the usage of this tool to enable the networking protocols.
• Use DISABLENETWORKPROTOCOLS=0 in the setup command line, if you know in advance that networking support is needed.
• Use SMO-based scripting to enable the protocols.

I haven't spent a lot of time with SQL Express, so I'm not certain this will resolve your problem.  Let us know how it turns out.

Answer 3

I've developed a commercial application for retail, SQL Server 2005 Express has been working wonderfully... but locally. And my app does not need any remote conections, just installing it in another computer other mine presents an error text quoting "under the default settings sql server does not allow remote connections".
I tried creating a small console application in Visual Basic 2005 Standard Edition, which uses a silent install of SQL Server Express 2005, and sending the right paramters to allow networking and to allow mixed (sql and windows) authentication:
"setup.exe /qn addlocal=all InstanceName=SQLEXPRESS  DisableNetworkProtocols=0 Secutity Mode=SQL SAPWD=passwd COMPANYNAME=TRSoftware"
 All this aimed at installing SQL Server 2005 Express to the user in an easy way.
Finally I added this console application to my Setup Project, in 'Custom Actions' in the 'Commit' section.
All my application files are correctly installed, but when installation is finished and I the console application runs it shows me an error, it says: "Could not find file c:\Program Files\MyApp\Dep\Install SQL Server.Install State"

I don't know what I've done wrong, 'cause I've checked the SQL Server Express documentation and the command line parameters I sent are correct, this is very confusing and posted this on the SQL Server forum and they sent me here. I believe many developers are facing this same problem, because we cannot start shipping our applications if we don't overcome the problem of automatically install SQL Server Express alongside our software.

Answer 4

Have you considered using ClickOnce deployment to publish your application? It will automatically install SQL Express (if needed) and put your local database in the data directory so your application can find it.

Answer 5

Hello,

 

I am absolutely new to this but I had the same problem trying to access a local database. After several days of reading a lot of posts, I did the following and it was solved. Not sure which step was the key or if I am adding problems to it, but I am writing it because I haven't seen these steps in other posts, so I hope it helps someone.

 

1. In the SQL Server 2005 Surface Area Connection, I navigated to SQLEXPRESS/Database Engine/Remote Connections and selected Local Connections Only.

 

2. In the main page of SQL Server 2005 Surface Area Connection I clicked in Add New Administrator and added my computer user (selecting it and clicking on the arrow).

 

3. I restarted the computer.

 

4. In Visual Basic Studio, in the Create New SQL Server Database, I now had the option of selecting GAMEROOM\SQLEXPRESS when before I only had the option of GAMEROOM (my computer's name).  I wrote a name for a database, clicked Ok and it worked!!!

 

After that I was also able to add connections, always using GAMEROOM\SQLEXPRESS.

 

Thanks,

 

Rafael

Answer 6

Is it TRUE that SQL EXpress does not allow "remote connections", and does it consider Visual Basic installed on the SAME computer, on the SAME hard drive, in an adjacent directory, to be a "remote connection"?

Answer 7

Anonymousandmad wrote:

Is it TRUE that SQL EXpress does not allow "remote connections", and does it consider Visual Basic installed on the SAME computer, on the SAME hard drive, in an adjacent directory, to be a "remote connection"?

 

To answer my own question, no, SQL server does not view Visual Basic 5.0 as a remote connection, and it is possible to connect Visual Basic to SQL server without accepting remote connections.

 

But now another problem is created, which is "the project file cannot be loaded" when double clicking on the .sln file, for any project.  It worked before, but now does not, making this doubly frustrating.  Should this question be raised in a different forum, or is this the correct forum, as it appears to be related? 

Answer 8

I had the same problem. My solution: SQL Server Configuration Manager -> In SQL Services: right clic on SQL Server -> properties -> Build In account set on Local System

Answer 9

Rosomak_7 wrote:

I had the same problem. My solution: SQL Server Configuration Manager -> In SQL Services: right clic on SQL Server -> properties -> Build In account set on Local System

 

Perhaps my system is configured differently than yours, or I have a different version.

 

In "sql server configuration manager", there is no "SQL services".

 

There is an "SQL Server 2005 Services", and "SQL Server 2005 Network Configuration", and "Sql Native Client Configuration", but right clicking them doesen't bring up "properties".

 

Right clicking on "Sql Server 2005 Services" and clicking "open" does open a dialog box with five different services in them.

 

There is no "build-in account" on any of them, and the "built-in account" on "SQL Server (MSSMLBIZ) gives the message "a new member could not be added to a local group because the member has the wrong account type" when attempting to change it from "Network service" to "Local service".

 

With all that said, I don't think the problem is here anyway, because SQL does work on some directories but not in others.

Answer 10

IsraeliteKnight,

I was having the same problem where I was getting the message "SQL DOES NOT ALLOW REMOTE CONNECTIONS ERROR 40" when I was trying to attach a SQL Server Express database in Visual Web Developer 2008. I did the following to get past this error:

1. Call up SQL Server Surface Area which is in Configuration Tools in the START menu

2. Once in there go to Remote Connections. The radio button that says Local Connections Only is probably clicked. You should click the one underneath that says "Local and remote connections and then Using TCP/IC and named pipes.

3. In the Window on the left go to SQL SERVER BROWSER and change startup type to Automatic then Apply. After that the START button should become enabled.  Click the START button.

Once you do this restart Visual Web Developer 2008 and when you go into the Database Explorer, all your data connections in SQL Server will show up.

Rick Clauser LQ/Iraq

Answer 11

I've been experiencing similar problems and can't seem to get the issue corrected. Admittedly, I'm very much a novice when it comes to SQL however I've read right the way through this thread to no avail. I may well be doing something wrong, I probably am in fact - is there anywhere I could find a similar solution explained in laymens terms?

Thanks in advance.

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

U.B.A.
UK Blinds and more!

Answer 12

 

 After days and days, hours and hours I have this problem resolved.

 

I contacted someone with a little more resources(Microsoft Directly) then myself, and here is what they found(they just found the resolution and cause of this bug 2 days ago as of 7/17/2008:

 

This error is caused by a bug when you uninstall and reinstall SQL server. So if you recently re-installed, uninstalled, upgraded or other wise here is how you fix this error. It is simple:

 

Locate the following folder(This is the location in vista, windows xp will be different):

C:\Users\*YourUserName\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

 

Delete the SQLEXPRESS Folder.

 

Start the SQL Server Configuration Manager

 

SQL Server will repopulate that folder.

---

Integrity Technology Specialists