SQL Server 2005 Management Studio Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MattP - 27 Sep 2007 17:33 GMT

Hi folks,

I have just recently experienced a very strange problem when trying to
use the SQL Server Management Studio on SQL Server 2005.

I had installed (some months ago) a copy of SQL Server 2005 Developer
Edition on a Windows Vista Home Premium OS, and also installed the
Service Pack 2 for SQL Server 2005.

All was working well. I could go into SQL Server Management Studio and
log into my local SQL server, and do all the things you would do in
Management Studio.

Just the other day, I attempted to get into Management Studio, and
although it gave me the normal login screen (Server name, Windows
Authentication etc), it would not allow me entry to the SQL Server and
its databases.

It simply comes up with the following message:-

"An error has occurred whilst 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)."

This message doesn't mean much to me I'm afraid, and in a way it
doesn't make sense, as I am not even trying to make a remote
connection, as the Server is local.

I tried a few things in Administrator Tools|Services (such as chaning
the "Log On As" to local Machine), and also in Surface Area
configuration tool, but to no avail.
Oddly enough, at this point, I could still access the data in the
databases on the server, via MS Access and VB.Net, I just couldn't go
into SQL Server Management Studio.

I then even tried un-installing SQL Server and re-installing, but it
still won't let me in. And now, because I have re-installed, my
databases are sitting outside of the SQL Server (I took copies of
the .mdf and .ldf files), but I can't attach them as databases in SQL
Server, as I can't go into SQL Server Management Studio.

Do any of the SQL Server experts out there have any idea how I can get
back into SQL Server Management Studio, and why this error may have
occurred (seemingly, out of the blue)?

Any help would be really appreciated, as I am completely stuck now
(short of purchasing a new machine to install on!!!).

Cheers,

Matt

Reply to this Message

Shailesh Khanal - 27 Sep 2007 21:02 GMT

Are you running a default instance or named instance?. Are you running the
sql server in non default port?

Make sure the SQL Browser service is running.

> Hi folks,
>
[quoted text clipped - 50 lines]
>
> Matt

Reply to this Message

MattP - 28 Sep 2007 07:17 GMT

On 27 Sep, 21:02, Shailesh Khanal
<ShaileshKha...@discussions.microsoft.com> wrote:

> Are you running a default instance or named instance?. Are you running the
> sql server in non default port?
[quoted text clipped - 57 lines]
>
> - Show quoted text -

Hi Shailesh,

Many thanks for the reply.

I have just this second figured out what the problem was. For some
reason, TCP/IP was disabled in "Protocols for SQL Server" (I was
previously only looking in "SQL Native Client Configuration" for TCP/
IP being enabled, which it was - my lack of experience in maintaining
SQL Server!!)).
Once enabled, it then allowed me entry via the Management Studio
(although strangely, McAfee AntiVirus / Firewall asked me if I should
grant Internet access to SQL Server - I have said yes, full access -
maybe this is wrong, and I shouldn't have granted any access to the
internet for SQL Server?).

I'm not sure how or why TCP/IP suddenly became disabled. I am going to
run a full scan on my machine to ensure it is virus free.

Anyway, thanks again for replying. Much appreciated.

By the way, in answer to your questions (in case it's of help to
others), it is a default instance install, and the port it is using is
the default port - 1433 I think?).

Cheers,

Matt

Reply to this Message

Ekrem Önsoy - 28 Sep 2007 10:56 GMT

Answes inline.

Signature

Ekrem Önsoy

> On 27 Sep, 21:02, Shailesh Khanal
> <ShaileshKha...@discussions.microsoft.com> wrote:
[quoted text clipped - 70 lines]
> IP being enabled, which it was - my lack of experience in maintaining
> SQL Server!!)).

Because you reinstalled your instance. In Developer Edition TCP\IP is
disabled by default.

> Once enabled, it then allowed me entry via the Management Studio
> (although strangely, McAfee AntiVirus / Firewall asked me if I should
> grant Internet access to SQL Server - I have said yes, full access -
> maybe this is wrong, and I shouldn't have granted any access to the
> internet for SQL Server?).

It may be because of 1433\1434 ports for SQL Server.

> I'm not sure how or why TCP/IP suddenly became disabled. I am going to
> run a full scan on my machine to ensure it is virus free.

Told you above it's because of Developer Edition's default settings. The
same default setting applies to Express Edition as well.

> Anyway, thanks again for replying. Much appreciated.
>
> By the way, in answer to your questions (in case it's of help to
> others), it is a default instance install, and the port it is using is
> the default port - 1433 I think?).

This is the default port number of SQL Server.

> Cheers,
>
> Matt

Conclusion: Your initial problem may be because of the Antivirus program or
your SQL Server service may not be running.

Reply to this Message

MattP - 29 Sep 2007 08:59 GMT

On 28 Sep, 10:56, Ekrem ?nsoy <ek...@btegitim.com> wrote:

> Answes inline.
>
[quoted text clipped - 109 lines]
>
> - Show quoted text -

Hi Ekrem,

Many thanks for the info. It's very useful to find out these things in
case of future problems.

Cheers,

Matt

Reply to this Message