SQL Server Express 2005 and VB 2005: I Can't Connect to Database.?

Question

All protocols for SQL Express are enabled but i can't connect to DB using VB 2005 Express DataGridView! I can see the Server name in the Add Connection dialog box. But when I click on the Database button, no databases are displayed. I get an error.

I can see the database from SQL Server Management Studio. SQL Server is on my desktop, from which I am also running VB.

I have enabled the following three protocols:

- Shared Memory
- Named Pipes
- TCP/IP

but still, when i browse for the database,i am getting the following error on pressing the Test Connection button:

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)

Please help me sort out this problem

Answer 1

I work tech support for a company whose software needs SQL. Whenever that message is displayed in our case it has been because the server name is incorrect or a firewall is blocking the connection. You can try temporarily disabling windows firewall and making sure the server and instance name are correct. If it's a local install, the server name should be (local)\instancename. You can try the osql commands from the command prompt to see if you have connectivity.



type at the dos prompt: osql -L

This will give a list of all SQL instances on all computers.



To test connectivity: osql -S -U -P (where S is server\instance, U is user ID, and P is password.)

Answer 2

SQL 2005 server by default only allows connections to the database from the computer where it is installed. If you are connecting from a different computer you will need to enable it for remote connections (http://support.microsoft.com/kb/914277).



If that is not the problem, how are you trying to authenticate? Are you using windows authentication or mixed mode authentication on the server?

Answer 3

I would suggest changing from Windows authentication, and using SQL authentication. I have never seen anyone successfully utilize the Windows authentication, and it always seems to create problems. To do so, you will have to set up a user account on the SQL Server, and make sure you use the same user name and password for it, and the the connection. Good luck, and please let me know if that doesn't work - I am emailable through YA. Good luck : )

Answer 4

I searched the Suppport Forum at SQL Server 2005 Express Edition and found a thread that helped but I can't find it now. Here is a link to the SQL Server 2005 Express Edition Support forum: https://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=385&SiteID=1



In essense the thread I found earlier said that you must use the instance name in the Data Source. Meaning Data Souce= (local)\SQLEXPRESS.



Hope this helps.