Cannot connect to SQL Server

I've just restored my Windows and installed apps using a Norton Ghost image, which is as good as reinstalling Windows and the applications. I had to reinstall SQL Server Express and VB Express after the restore since I hadn't included them in the original image.

Now ever since I've done that, I cannot add my database to the data sources of any project in VB. When I click on 'add new data source', select 'database' and click next, I get to the screen 'Chose your data connection'. Clicking the 'New Connection' button brings us to the dialog 'Add connection, where we are asked to select the database file we would like to connect to. I select the file, and select Windows Security mode (which was selected during the install of the db engine) and click 'Ok'. The system seems to hang for a while after that, and later, I get this error message -

'An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this faliure 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)'

This problem did not exist before I restored my system. There is also one more thing - when installing SQL Express, I tried to select the SQL Server Service, as well as Reporting Services to run as 'Local Service' instead of Network Service and Local System. But the installation didn't accept it, and threw an error, and didn't install the db engine correctly. It also gave me an error message at the end of the installation that it couldn't start the SQL Server service, as well as the Reporting Server service.

I had to remove and reinstall SQL Server, and this time I had to select both services to run as Local System. The installation went fine after that. I am using Win XP Pro on my home system. What seems to be the issue with SQL Server?

Help would be greatly appreciated.

Please check my blog: http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

If this does not solve your issue, please post more info according to:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

Thanks.

|||

More info according to the MS guidelines page (second link you provided) -

Client Side

1) No connection string entered manually, the program is supposed to generate a connection string after I have selected the database file (data source). Never reach that point. (I have provided detailed information about this in my first post itself).

2) Again, exact error message reproduced above.

3) local. SQL Server Express installed on home computer on Win XP Pro, and (tried to be) accessed from VBExpress and SQL Management Studio Express also installed on local machine.

4) can ping -a localhost, reponse ok (no client server scenario here)

5) The system cannot find the file specified (I think I have telnet disabled in services)

6) Already specified

7) No domains scenario, no network exists. Everything is on the same workgroup

8) TCPIP

9) No alias exists. (Note, never configured this before either, when everything used to work fine)

10) Don't know.

[II] Server Side

1) SQL Server 2005

2) Express

3) TCP/IP

4) Do you mean the db engine service? If so, then yes, it does. I can access the database, and do pretty much everything with it from within Management Studio.

5) It is default instance - MSSQLServer. SQL Browser not enabled.

6) Local System. (Tried to install it as local service, but installation gave an error, sql server service failed to start, and had to remove completely and reinstall and this time selected local system, and installation went fine).

7) not applicable. no remote connections.

8) not applicable. no remote connections. no sql browser started.

[III]

1) Win XP Pro

2) Norton Internet Security 2005, Spybot Search and Destroy. (autorun feature is disabled in both, except norton personal firewall)

[IV]

1) not applicable.

Ok, on your blog it says you never see error message 26 on default instance, but I am seeing it on the very same default instance. I even started the SQL Browser service, which was disabled previously, but I still get the same error.

Alright, checked it seems to alternate between error 25 and error 26 arbitrarily.

|||

Are you using "localhost\mssqlserver" in your connection string? This IS NOT correct. For default instance, just use servername(i.e. localhost in your case) and never specify "mssqlserver" as instance name.

error 25 means your connection is not valid.

SQL Network Interfaces, error: 25 - Connection string is not valid) (Microsoft SQL Server, Error: 87)

|||

I cannot enter any connection string myself at all. It is generated automatically, based on the options that I select. Here,

let me show you.

First, I select 'add a new data source' for my solution in vbexpress. http://img507.imageshack.us/img507/2595/image1wl3.jpg

Then I select 'database' and click next. http://img507.imageshack.us/img507/4564/image2fb0.jpg

Now, note, in the box circled, though I can place a cursor, it won't allow me to manually enter any text at all. I select the 'new connection' button at the top, which brings me to the next screen. http://img168.imageshack.us/img168/1936/image3nn7.jpg

I select 'browse' to add the name of the database file I wish to connect to. http://img514.imageshack.us/img514/4107/image4ty1.jpg

http://img514.imageshack.us/img514/8822/image5xi4.jpg

Since I had selected 'windows security' mode when installing the db engine, I let it remain at that and hit ok. http://img525.imageshack.us/img525/4291/image6br0.jpg

After hitting Ok, the system seems to hang for a while. Check top left corner. http://img514.imageshack.us/img514/6544/image7vn0.jpg

Then I get this error message. http://img168.imageshack.us/img168/1359/image8md1.jpg

Then I realise that I have not installed this instance as 'SQLExpress', but as the default instance 'MSSQLServer'. So I go to the advanced settings to change a parameter. http://img168.imageshack.us/img168/5230/image9hy6.jpg

Now it has 'SQLExpress' selected by default, even though I haven't got that instance, so I change it to the proper instance name listed below. http://img514.imageshack.us/img514/812/image10rp7.jpg

When I hit ok now on the add connection page, I get the following error message. http://img168.imageshack.us/img168/3346/image11ar8.jpg

There is no way I can enter any connection string myself.

|||

Thanks.

When you first connect to .\sqlexpress, you got "error 26" beacause there is no such an instance installed. This is understood, right?

Then you changed to .\mssqlserver, this is an invalid connection string. As I said, you should not use "\mssqlserver" if it's a default instance. This is a bug in Visual Studio, I have reported it a while back and it should be fixed in next release. They should just use "."

There is a workaround for your case, before you add Data Source, do the following:

From Visual Studio, --> Tools menu --> Options --> Database Tools --> Data Connections -->

Change your "SQL Server Instance Name" from "sqlexpress" to blank.

Then go back to add your data source, in the "advanced" tab, you should be able to find an entry "."

|||

I tried everything you mentioned. Nothing worked. The solution in the last post solved the original issue, but the db engine began giving me other errors after that. (I don't recall what error or exception it gave me, I was really frustrated and irritated by that time).

So I ultimately went ahead and reinstalled the OS and reinstalled SQL Server, but this time, I chose to select all the default settings for SQL Server. Everything works now.

If Microsoft only ever intended the default settings to work in its products, then why does it include additional settings/features in the first place?

Thanks for your help anyway, Mr. Hong. You don't have anything to do with Microsoft's incompetence, so please be assured that the above rant isn't directed at you.

|||

I'm glad things are working for you now. Usually, if you don't know the impact of a configuration, the recommendation is to use the default settings. The default setting has been optimized for most common usage, but may not be the best for a specific case.

|||I got exactly the SAME problem

I've reinstalled SQL Server 2005 with Defaults Settings, but the problem is always present. It really suck! Why Microsoft products cannot communicate togheter? pffff

I remplace "SQLexpress" instance name in Visual Studio by a blank but nothing changed.... So what can i do? I think there is no solution and my project in vb.net will be not finished.

Thank you Microsoft
|||Can you post your connection string and also check my blog about this error message? What instance do you have and where? Thanks.|||Hi !

I've followed all the steps on your blog but nothing...

My instance name is the default name -> MSSQLSERVER

The Connection String is :

-> Data Source=.\SQLEXPRESS;AttachDbFilename="C:\DataWarehouse.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True

If I change the datasource value and set ".\MSSQLSERVER" it fails.

It's like Reachrishikh, the wizard doesn't allow me to put other values that ".\sqlexpress" and ".\mssqlserver". I wanna replace it by (local) or something else

Thank you for your help !!!
|||please read my post on " 04 Jun 2007, 5:28 PM UTC " of this thread for the workaround. ".\MSSQLSERVER" is invalid.|||Hi !

I think that the problem is serious.

Look at this (Sorry for french language)

But nothing change in the wizard

What do you think about that?

Thank you !!

|||Are you sure the box has nothing there? What about if you put other words (say myInst) there? If there is still no change, there is probably another bug in French version. Thanks.