Remote Connections/Remote Access

Question

Can someone clear up the difference between Remote Connections and Remote Access?

Environment:  Web application on IIS servers connects to remote SQL Server database servers

OS Windows Server 2003/2008 R2

DB SQL Server 2005 / 2008 R2 Enterprise we do have linked servers for right now, but are phasing them out in place of web services.

We manage the SQL Server database servers via SQL Server Management Studio.

Thanks.

Don Jones

Answer 1

Can someone clear up the difference between Remote Connections and Remote Access?

Hello,

In witch context do you see this? A remote connection is always a remote access, so there is no difference in fact.

---

Olaf Helper

Blog Xing

Answer 2

Thanks for the reply,

From what I have read/researched it made it sound like you disabled remote access, you can't access the database across the network.  Other items I found made it only needed enabled if you were connecting to a linked server.

I disabled remote access on one server, but I was still able to access it from across the network using SQL Server Management Studio; so I am finding contradictions.

The contradictions is what is confusing.

With remote access disabled, what should I be able to do and not do?  Is there a known bug in SQL Server 2005 that I am htting and that is why there is a contradiction?

Don Jones

Answer 3

A remote connection is connecting by using TCP or named pipes from a second computer (not the computer running SQL Server).

Perhaps the Remote Access you are referring to is the "the execution of stored procedures from local or remote servers." See Configure the remote access Server Configuration Option http://technet.microsoft.com/en-us/library/ms191464.aspx

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 4

I disabled remote access on one server, but I was still able to access it from across the network using SQL Server Management Studio; so I am finding contradictions.

Did you restart SQL Server after you changed the setting? The change does not take effect until you do.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

Thanks for the reply.

For remote connection, how does SQL Server Management Studio fall into this? 

We should have remote access disabled for all servers with the exception on those that have linked servers.  Correct?

Thanks.

Don Jones

Answer 6

Thanks for the reply.  I did restart SQL Server, and I went as far as rebooting the server.

I still was able to connect even after disabling remote access.

Don Jones

Answer 7

It appears that this setting does not affect linked server connections.  See the deprecation comments, such as this one in 2012 BOL:   "This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use sp_addlinkedserver instead."

Since a linked server is the replacement, this helps to understand that the remote access is a shortcut that does not require a linked server.  It simply depends on (1) the local server  having in sys.servers a remote server name and (2) on the remote server the setting of "Allow remote connections to this server" = 1 (or checked on in the Server Properties / Connections form) .

Also, the change takes effect immediately without a need to restart.  (But whether that is acknowledged by another server immediately or not is all unknown to me.)

Whatever the setting, the linked server should work.  (Or so I believe.  It has been many years since I used something other than a linked server.)

RLF

Answer 8

So the configuration setting "Remote access" (which I had forgotten about), is legacy setting, which you can set to 0 if you like, but it is unlikely to have any practical effect. It relates to a feature "remote servers" which predates "linked servers", and will have to confess that I'm largely ignorant about it. But this setting has nothing to do with SSMS, nor with linked servers.

There is no configuration setting "Remote connections", but there is such a section in the Surface Area Configuraiton tool that comes with SQL Server. This is less esoteric. This setting, controls whether SQL Server listens to TCP ports from other machines. If you disable this one, and restart SQL Server, you will not be able to connect from SSMS running on a different machine from SQL Server.

---

Erland Sommarskog, SQL Server MVP, [e-mail]