Wednesday, March 7, 2012

Error connecting to local SQL Server Express with SQL Server management Studio Express

I have SQL Server 2005 Express Edition installed on a Windows Server 2003 SP1 machine. I am trying to connect to the server on the local machine via shared memory with SQL Server Management Studio Express but I get the following error message:

TITLE: Connect to Server

Cannot connect to SERVER\SQLEXPRESS.


ADDITIONAL INFORMATION:

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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)

I have already enabled remote connections, TCP Ports, etc., and disabled the firewall, but since I am connecting from a local machine, that should not matter. I have uninstalled and reinstalled SQL Server Express with same issue occuring. SQLServer service Browser service is running.

Can anyone share some ideas on how to troubleshoot this connection issue? Could this be caused by some scurity settings or services not running in Windows Server 2003? Any help would be greatly appreciated.

Seems to be a strange issue.

Please check whether the Engine Services are running by using the Configuration Manager. Also check whether the account with which the service is running has a valid login in the Server.

Regards,

Rajesh

|||

After a good night sleep, I tried to connect to the database via Management Studio Express with the following Connection: .\SQLEXPRESS. This allowed me to connect to the database. Previously I had always connected with Server\SQLEXPRESS which produced the above error. After connecting to the datagase, the Registered Database window of Management Studio Express showed the database engine as server\sqlexpress, even though I could not connect to it using Server\SQLEXPRESS.

I felt happier I could connect, but I still couldn't understand why I can't connect using SERVER\SQLEXPRESS but have to use .\SQLEXPRESS. My real purpose of using SQL Server Express is to upgrade the back-end of an existing networked MS Access database application. When I went to SQL Server Configuration Manager to set up remote connections so I could connect from a client using a form of .\SQLEXPRESS or maybe IP ADDRESS/SQLEXPRESS, I noticed under SQL Native Client Configuration => Aliases, there was an alias configured for "SERVER\SQLEXPRESS". I don't know why this was there since I removed (via add/remove programs deleting the remaining Program Files => SQL SERVER directories) and reinstalled the application. I deleted this alias. After doing this I was able to connect to the Sever from Server Management Studio Express using SERVER\SQLEXPRESS. Finally I am able to progress to the task at hand of connecting to the database via Access Client, which leads to other problem I hope someone can help me with below.

In upsizing our Access application, we have decided only to upgrade the back-end but keep the front-end using Access and Jet connection to SQL Server via ODBC. We are currently using the "SQL Sever" ODBC driver that comes standard on Windows XP machine, configured at Administrative Tools => Data Sources (ODBC). I know this is not the best, but budget dictates (minimal to no changes in front-end Access) and it is a small application (~100MB size with one year of data). I am able to connect to the SQLEXPRESS database from ACCESS front-end at the client workstations, so I know the SQL SERVER Firewall settings are O.K. The problem I am having is that the connection drops out after 15 -30 minutes. I then can not reconnect to the database for some time. However, after additional amount of time, I am able to connect without changing anything in the configuration. We are using Windows Server 2003 R2 SP1 with Windows XP Pro clients. I don't think it is a network cabling problem because at the times when I can not connect to SQL SERVER, file and print sharing still work.

Does anybody have any hints on the easiest way to troubleshoot and fix this intermittant issue? Should I try to use the "SQL Native Client" ODBC Driver? Any suggestions are greatly appreciated.

|||Check any error or warning from SQL error logs & event viewer logs for the intermittent disconnection, also you can run a netmon trace to find where it is having problem. I guess as the clients can connect to SQL server now after your setup, its only on the network you should chekc.

No comments:

Post a Comment