What are the requirements to use Management Studio/Management Studio Express
for local instances?  Are the requirements same for both studios and same for
all OS?


Based on my own testings:
Remote connection is required for Management Studio in Windows XP SP2.
But it is not required in Management Studio Express in Windows XP SP2. 

For Windows 2003 and 2000, remote connection is not required for Management
Studio.  Have not tried Management Studio Express yet.



Peter

Peter (Pe***@discussions.microsoft.com) writes:
I'm not sure that I understand what you mean with "remote connection is
required". Could you clarify?

Hi Erland,

I hope the following will clarify my issues:

I'm testing the following in a Windows XP SP2 with SQL Server 2005 Developer
Edition SP1:

Scenario #1
1.  Checked Local connections only in SSSAC for a SQL Server 2005 local
instance
2.  Restarted that instance in SSCM (Note: only Shared Memory enabled for
Network Configuration)
3.  Tried to connect to that instance in SSMS and get this error:

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: 28 - Server doesn't support
requested protocol) (Microsoft SQL Server, Error: -1)

Scenario #2
1.  Checked Local and remote connections and Using named pipes only in SSSAC
for a SQL Server 2005 local instance
2.  Restarted that instance in SSCM (Note: Shared Memory and Named Pipes
enabled for Network Configuration)
3.  Tried to connect to that instance in SSMS and get this error:

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: 28 - Server doesn't support
requested protocol) (.Net SqlClient Data Provider)

Scenario #3
1.  Checked Local and remote connections and Using TCP/IP only in SSSAC for
a SQL Server 2005 local instance
2.  Restarted that instance in SSCM (Note: Shared Memory and TCP/IP enabled
for Network Configuration)
3.  Tried to connect to that instance in SSMS and was able to connect
4.  The following is fromt the SQL Server Logs:

    Server is listening on [ 127.0.0.1 <ipv4> 4906].
    Server local connection provider is ready to accept connection on [
\\.\pipe\MSSQL$SQL2005_AM7\sql\query ].
    Server local connection provider is ready to accept connection on [
\\.\pipe\SQLLocal\SQL2005_AM7 ].
    Server is listening on [ 'any' <ipv4> 2464].

What do the log mean?

Why I cannot connect to the local instance in Scenario #1 and #2?


Thanks for any help,

Peter

Show quoteHide quote

Peter (Pe***@discussions.microsoft.com) writes:
Show quoteHide quoteAnd exactly what did you use to connect? I tried to connect to a local
instance of SQL Express that listens on shared memory, and all these three
worked: 

    .\SQLEXPRESS
    (local)\SQLEXPRESS
    KESÄMETSÄ\SQLEXPRESS     -- KESÄMETSÄ is the machine name.,

This did not:

    127.0.0.1\SQLEXPRESS

Erland,

Are you using SSMS or SSSME?

I have only tried SSMSE to connect to local Express instance in Windows XP
SP2 and it works fine just like the way you mentioned (Note: When I
right-click SQL Server Logs, I don't see the option to view the log).  

The scenarios that I stated in my last reply are using SSMS (not SSMSE) to
connect to local SQL Server 2005 instance (not local Express instance) by
clicking the Connect and using ComputerName\InstanceName.


Thanks,

Peter


Show quoteHide quote

Peter (Pe***@discussions.microsoft.com) writes:
SSMS, the non-Express version.

Do you have both SSMS and SSMSE on your machine? I don't think this is
supported.

Hi Erland,

I don't have SSMS and SSMSE on the same machine.  I have different machines
with different OS (Windows 2000, Windows 2003, and Windows XP) to test SSMS
and SSMSE.

By the way, can I email you if I have some other SQL/Express questions?

Peter

Show quoteHide quote

Peter (Pe***@discussions.microsoft.com) writes:
I should add that I mainly tested with SQLCMD, as this is a command-
line tool, and quicker for the task. SQLCMD uses OLE DB to connect,
in difference to SSMS(E) that uses SqlClient.

It's always better to post questions to a public forum, than targetting
an individual. That increases your chances to a quick response from someone
who knows what he is talking about.

On Fri, 21 Jul 2006 17:20:01 -0700, Peter
<Pe***@discussions.microsoft.com> wrote:

Show quoteHide quoteHi,

I tried both your scenarios and they worked without error on the one
SP1 machine that I have relevant to your scenario.

Andrew Watt MVP

Stored Procedures in Integration Services
Cannot filter members in Management Studio Browsing a Cube
Sql 2005 can't update maintenance plans?
backup remote database to local hard drive or local database?
Error 0x80041010 WinMgmt when launching SQL SERVER MANAGEMENT STUD