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
This is additional info to clarify the 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\sql\query ].
Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQL2005].
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
|||
SQL Server supports connections using shared memory (which can only work with applications on the same machine), named pipes (an inter-machine communication technology that is mostly used by older applications running on Windows machines - it lets the client communicate with SQL Server as if it were reading or writing to a file), or TCP/IP connections, which can work for any network client on any machine (including, say, Java clients on Unix machines).
In scenario #1 you've disabled remote connections, so only applications running on your server machine can access the server. In #1, you are only allowing connections via shared memory, and in #2 you are allowing shared memory or named pipes connections. It seems likely that in #1 and #2 SSMS is trying to create a TCP/IP connection to your server, but your server isn't accepting those connection types. You can configure how Management Studio connects to your server in the Connection Dialog by clicking the "Options >>" button. In the "Network protocol" dropdown, just pick one of the connection types your server is allowing. In #1, that would be "Shared Memory."
In scenario #3, the log is saying that SQL Server is accepting
TCP/IP connections from the TCP local loopback adapter (the reserved IP address 127.0.0.1, used by network applications to talk to the local machine) on port 4906. If you type "(local)" or "." as the name of the server, SSMS uses 127.0.0.1 as the address of the server.|||
Hi Steve,
Thanks for your post.
I'm not sure what do you mean by "in #2 you are allowing shared memory or named pipes from local clients". If I understand correctly, the #2 scenario is allowing shared memory from local clients and named pipes from remote clients.
If I don't click the Options button in the Connection Dialog, which protocol will be used by default or how can I find out? If I click the Options button in the Connection Dialog and then select <default>, is it same as not clicking the Options button?
Is there any webcast from technet/msdn/microsoft regarding Connection in SQL Server 2005 and/or SQL Server Browser Service?
Peter
|||You're right, #2 allows remote connections via named pipes. I wasn't reading your description correctly.
<default> means that the connection dialog doesn't specify a network protocol in the connection string used to connect to the server. Under ADO.net 2.0, I believe this results in a TCP/IP connection. If ADO.net changes this default in a future release, <default> would use it. The Options button just exposes additional connection options and doesn't change anything itself, so clicking <default> shouldn't have an effect if that was the setting initially.
I don't know the answer to your question about upcoming webcasts.
Hope this helps,
Steve
No comments:
Post a Comment