Impossible to connect to SQL Server 2005 Express

[Gabriel]

Hello,

I installed SQL server 2005 SP1 on a Windows XP SP2

From Visual Studio, I'm trygin to create a connection to a database, but I
receive this error but I'm creating the connection (I don't receive the
database list in the dropdown)

"An error has 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 Server does not allow remote connections
(provider : Named Pipes, error : 40 - Could not open a Connection to SQL
Server)

This time, I'm lost :
- disabled firewall
- Configration manager : TCP/IP and Named Pipes enabled
- SQL NAtive Client Configuration : TCP/IP and Named Pipes are enabled
- ServerManager : Security -> Login add MyComputerName\ASPNET

But impossible to connect from Visual Studio 2005, no database in the
dropdown.

Any idea ?

Regards,

 

[Juan T. Llibre]

Follow the instructions in this KB :

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

 

[Gabriel]

Follow the instructions in this KB :
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

Thanks, but no change still impossible to connect from Visual Studio.

Any other idea ?

Regards,

 

[James Irvine]

Thanks, but no change still impossible to connect from Visual Studio.

Any other idea ?

Regards,

I had the exact same error and fixed it by adding \SQLEXPRESS to the
connect string:


Data Source=myHost\SQLEXPRESS;Initial Catalog=pMyLocalexpress;Integrated
Security=True;Pooling=False

 

[Juan T. Llibre]

re:

Thanks, but no change still impossible to connect from Visual Studio.

Did you stop/restart SQL Server 2005 ? A stop/restart is needed.

Is SQL Server 2005 Express installed on the same box as Visual Studio ?
You don't need to setup *Remote Access* to connect to a local SQL Server 2005.

If it is installed on the same box, the connection should be quite easy :

<configuration>
<connectionStrings>
<add name="MySqlServer" connectionString="Data Source=machinename\SQLEXPRESS;
Initial Catalog=YourdbName;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>

You also, of course, need to have a matching <providers> entry for "MySqlServer" in web.config.

i.e., :
<providers>
<add connectionStringName="MySqlServer"
.... etc.
</providers>

 

[Guest]

Hi,

Are you using SQL Server 2005 or SQL Express?? If you are using SQL Express
edition you need to add Machinename\SQLESPRESS as the server name else the
machine name alone will do. Is this error getting only while connecting from
VS??

 

[Gabriel]

Are you using SQL Server 2005 or SQL Express?? If you are using SQL
Express
edition you need to add Machinename\SQLESPRESS as the server name else the
machine name alone will do. Is this error getting only while connecting
from
VS??

It's the express edition, before on this PC it was the standard edition

Listen ... I do this
- Launch VS2005
- File > New Web site .. I give a name
- Server explorer > Add connection :

With this parameter :
- server name : localhost (tried : localhost\SQLESPRESS too)
- SQL server authentification : username :sa and the password

Nothing in the dropdown to select the database

There is no web.config at this time. I add one, did the same operation, no
difference.

Regards,

 

[Gabriel]

I had the exact same error and fixed it by adding \SQLEXPRESS to the
connect string:

You are right, I had : mycomputer\SQLEXPRESS in the wizard and that's work
fine now

Thank everybody

 

[Guest]

I hope that you are able to establish the connection from Management studio.

If so follow these steps:

1) Enable sql server browsing services

1. Click Start, point to Programs, point to Microsoft SQL Server 2005,
point to Configuration Tools, and then click SQL Server Surface Area
Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click
Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page,
click SQL Server Browser, click Automatic for Startup type, and then click
Apply.

Note When you click the Automatic option, the SQL Server Browser service
starts automatically every time that you start Microsoft Windows.
4. Click Start, and then click OK.


2) Launch VS2005
- Server explorer > Add connection :

You will get a pop up asking for choose datasource:

select Data source as "Microsoft SQL server" and Data Provider as ".Net
Framework Data Provider for SQLServer".

Click continue.

Note:Check the data source and data provider from the Add connection popup
by clicking on the change button if you get the Add connection popup directly.

You will get the Add connection Popup:

In that server name dropdownlist click on the dropdown arrow to select
server. (Or) enter the "127.0.0.1\sqlexpress" as server name.

Select Windows authentication / sqlserver and click on the button "Test
Connection". If the connection succeeds try to select the database and click
ok. You should get the databse tree view in the server explorer.

Hope this would help!!!

Regards,

Pramod,
Web Programmer,
Technopark, Thiruvanathapuram,
India.

 

[Juan T. Llibre]

re:

- server name : localhost (tried : localhost\SQLESPRESS too)

That won't do.

You *need* to use machinename\SQLEXPRESS

re:

- SQL server authentification : username :sa and the password

Try using mixed authentication.

Windows Authentication mode is the default security mode
after a typical installation of SQL Server 2000 or of SQL Server 2005.

You cannot use a SQL Server Standard security login account after a Typical
installation of SQL Server 2000/2005 until you change the authentication mode to Mixed.

You can change the authentication mode manually by modifying
the following registry information to contain value 2 instead of 1:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\LoginMode

1 means Windows authentication, this is the value you should find set.
By changing this to 2, you'll set the mixed authentication mode.

Be sure to restart the server for this change to take effect.