Cannot connect to SQL Server 2005 Express with Management Studio Express

PussyFoot

On my home machine without permanent network connections enabled, I cannot get the Management Studio connect to the database server. Always get this error:

Cannot connect ot MPLIAM\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: SQL Network Interfaces, erro: 26 - Error Locating Server/Instance Specified)(Microsoft SQL Server)

I have used the SQL Surface Area Configuration Tool to reset the defaults to allow remote connections, stopped and restarted the server, but still get the same message.

Please help.

Mike Wachal - MSFT

Hi Michael,

I think this is a duplicate of another post from you, but I'll answer here too.

You don't say if you're actually trying to connect remotely or not. If you're connecting remotely, the issue is likely your firewall is blocking the connection. Check out the FAQ at the top of this forum for a link to the article that discuss the complete steps to allow remote connections.

Mike

Arnie Rowland

Can you ping \\MPLIAM

Try usingthe IP address rather than the server name.

PussyFoot

Sorry. I lost the other post.

I'm just trying to connect on my own local machine to the server which is installed on my own local machine.

I have McAffee anti-virus software installed. There's no telling what that monstrosity is doing to shut down things -- all in the line of 'enhanced security', of course.

I am just about at my wits end trying to get this thing to work. I neglected to mention that when I first installed the server and the management studio, at least the latter connected to the former. Something changed somewhere along the way so that now it doesnt work. I've checked the Service in the Control Panel and it seems to be running. I even stopped and restarted it several times with no success. I used the config manager to configure the 'surface area' (love that one) so that remote connections are enabled. Still no luck.

What to do next

PussyFoot

ping mpliam

Pinging MPLIAM [169.254.101.152] with 32 bytes of data:

Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128

Ping statistics for 169.254.101.152:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms

Using Server Name: 169.254.101.152 in the Managment Studio

'cannot connect to database'

Any other ideas

Madhu K Nair

(a) check whether the service started or not from Configuration Manager Window

(b) Check whether the protocols are enabled or not from SQL Configuration Manager window

(c) Check which all are the client protocol enabled

Madhu

Mike Wachal - MSFT

In the other thread I had suggest trying a connection via SQLCmd, just to rule out any Management Studio weirdness, are you able to do that To reiterate, open a command prompt and type the following:

SQLCmd -S .\SQLEXPRESS -E -Q "SELECT @@Version"

This should return version information about your server if it works. If it fails, please post back the complete error message. As a second step should you encounter failure, please check the error log after the attempted connection to see if there is additional informaton there. The error log should be at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG.

It's interesting that you say this worked initially but connectons started failing later. This would indeed suggest that "something else" is impacting this, but I've no idea what. In general I wouldn't expect any AV program to prevent connections to SQL, but we should probably test this. As a temprorary troubleshooting step, disable McAffe and then try your connection again. Regardless of the result, turn McAffe back on once you've completed the test, but let us know if that impacts anything.

Finally, could you copy the test from the "Server" text box in the Management Studio Connection dialog and paste it into your reply

Mike

General E Pist

Thanks for your response, Mike.

I apologize for taking so long to respond. In any case, when I attempted at the Command Prompt:

SQLCmd -S .\SQLEXPRESS -E -Q "SELECT @@Version"

I received the following message (preceeded by a bunch of dashed lines):

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2) (1 rows affected)

The Error Log was quite extensive and, since I have been fooling with SQL Server quite a bit, I am uncertain which section pertains. But I interpret the above to indicate that the installation is working.

Here's a little bit of the most recent part of the Error Log:

2007-03-18 09:00:44.95 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2) 2007-03-18 09:00:44.95 Server (c) 2005 Microsoft Corporation. 2007-03-18 09:00:44.95 Server All rights reserved. 2007-03-18 09:00:44.96 Server Server process ID is 552. 2007-03-18 09:00:44.96 Server Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'. 2007-03-18 09:00:44.96 Server This instance of SQL Server last reported using a process ID of 236 at 3/17/2007 10:54:30 PM (local) 3/18/2007 5:54:30 AM (UTC). This is an informational message only; no user action is required. 2007-03-18 09:00:44.98 Server Registry startup parameters: 2007-03-18 09:00:44.98 Server -d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf 2007-03-18 09:00:44.98 Server -e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG 2007-03-18 09:00:44.98 Server -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf 2007-03-18 09:00:45.06 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required. 2007-03-18 09:00:45.06 Server Detected 1 CPUs. This is an informational message; no user action is required. 2007-03-18 09:00:45.67 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required. 2007-03-18 09:00:45.70 Server Database Mirroring Transport is disabled in the endpoint configuration. 2007-03-18 09:00:45.71 spid5s Starting up database 'master'. 2007-03-18 09:00:46.04 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required. // .....

The Server Text Box from the Management Studio (if I understand your request) looks as follows:

Connect to Server Server type: Database Engine (disabled) Server name: MPLIAM\SQLEXPRESS Authentication: Windows Atuhentication User name: MBPLIAM\Michael B Pliam (disabled) Password: (blank and disabled)

Attempting to connect results in:

Connect to Server Cannot connect to MPLIAM\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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

I will try to disable McAffee Virus Protection and get back to you if it makes any difference (Assuming that I can figure out how to disable it - it's sort of like getting rid of a bad cold.)

Thanks again for your help. Again, I wish to apologize for having lost this thread.

Mike Wachal - MSFT

You are correct that the output of SQLCmd indicates that SQL Express is working fine and can be accessed locally with no problems. This would suggest a specific problem with Management Studio. I don't see any problem with the information you supplied, but the error message indicates that the specific instance name you've supplied is incorrect.

Replace the value in the 'Server name' text box with .\SQLEXPRESS and see if that makes any difference. If this works, it would mean that your machine name is not actually MPLIAM. If it doesn't work, we may to punt this over to the tools guys to see if they have any ideas.

Mike

General E Pist

Problem solved ! 

Using .\SQLEXPRESS instead of MPLIAM\SQLEXPRESS  allowed the Management Studio to connect to the server.

I still do not understand what the problem was. 

My 'host' machine is definitely 'MPLIAM'. 

If I 'ping MPLIAM' from a Command Line prompt (as I believe I posted before), I receive the response:

Pinging MPLIAM [169.254.101.152] with 32 bytes of data: Reply from 169.254.101.152: bytes=32 time<1ms TTL=128 Reply from 169.254.101.152: bytes=32 time<1ms TTL=128 Reply from 169.254.101.152: bytes=32 time<1ms TTL=128 Reply from 169.254.101.152: bytes=32 time<1ms TTL=128 Ping statistics for 169.254.101.152:     Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds:     Minimum = 0ms, Maximum = 0ms, Average = 0ms

Further, MSDN provides C++ source code to determine the host machine name and volume.  For those interested, see:

http://windowssdk.msdn.microsoft.com/en-us/library/ms738527.aspx 

Running that program reveals

MPLIAM
3758647270

the host name and volume number respectively.

When Management Studio did connect (as noted above), the connection text box header has the following information:

Summary .\SQLEXPRESS (SQL Server 9.0.1399 - MPLIAM\Michael B Pliam) MPLIAM\SQLEXPRESS                             5 Items(s)

Mike, I want to thank you for your patience in helping me to solve this problem.  If my tone at times appeared intemperant, it was simply my frustration at being unable to get this program to work.  As is usually the case, it was probably my fault that things were awry.  I must have changed something without being aware of it.  Once things go bad, I have a tendency to start changing everything in sight instead of cautiously exploring the nature of the problem.

Regards

 

 

 

Mike Wachal - MSFT

I'm glad that it's working. It's odd that the machine name is not being recognized, but if you're happy, I'm happy.

Mike

Anthony Stanley

Hi,

I've been struggling with a similar problem. Wasn't able to connect remotely and everything was setup correctly on the Surface Area Configuration. I could connect locally though, but not remotely. Used the exact same Windows User each time, and no firewall in between client and server. The eventual resolution was something really simple and stupid on my part.

The SQL Server browser service wasn't running, all that I did was start it and suddenly I could connect remotely... which is strange because I've never had this issue with any other SQL server I've worked with...

anyway hope it helps...