3

We plan to change the default server options of an SQL2k5 server instance by enabling data access.

The reason is that we want to run SELECT * FROM OPENQUERY(LOCALSERVER, '...') - like statements on the server.

What are the possible disadvantages of enabling server option "data access" (alias sys.servers.is_data_access_enabled) for the local server (sys.servers.server_id = 0)?

(There must be a reason for MS setting this option to disabled by default...)

EDIT: it turns out that I'm not the first person to ask this question:

http://sqlblogcasts.com/blogs/piotr_rodak/archive/2009/11/22/data-access-setting-on-local-server.aspx

The DATA ACCESS server option is not very well documented in my opinion - the Books On Line say it is a property of linked servers. It doesn't mention at all that you actually can have it enabled on your local server to enable OPENQUERY calls. I noticed that when you disable DATA ACCESS on a linked server, you can't query any table located on it (I tested it on my loopback server) neither using OPENQUERY nor four-part naming convention. You can still call procedures (with four-part naming) that return rowsets. Well, the interesting question is why it is disabled by default on local server - I suppose to discourage users from using OPENQUERY against it.

It also seems that the author of the post is a Stack Overflow user :-)

UPDATE: Another tech blogger ran into the same issue in 2014. Also a Stackoverflow user.

Improve this question

1 Answer 1

Reset to default
1

There must be a reason for MS setting this option to disabled by default..

For a while now Microsoft products have taken the approach of trying to be 'secure out-of-the-box'. This means that features, such as turning on data access for a linked server, needs to be explicitly turned on. This prevents users (or admins) from inadvertently enabling an options which may be a security risk.

Improve this answer
3
  • This is technically correct if speaking of real linked servers. This case is specific, however: I want to enable OPENQUERY-like access to the local server. With the right credentials, one can already do a lot of malicious business without this setting enabled. Well, except some exotic stuff, like fetching the result set of nested stored procedures. Not really the thing that a potential attacker would wish for, I guess.
    – Attila Csipak
    Jun 2, 2010 at 6:09
  • Oh right. it's the local server your enabling this on? One question is Why? this is not ideal, your better off using four part naming if you want to access tables between databases & turning db ownership chaining on
    – Nick Kavadias
    Jun 2, 2010 at 13:43
  • "Well, except some exotic stuff, like fetching the result set of nested stored procedures." Which can prove to be a nontrivial problem when simply using four part naming and INSERT-EXEC, try googling "An INSERT EXEC statement cannot be nested". In my case, I want to query the execution status of jobs from a T-SQL script, for which the adequate facility seems to be sp_help_job (see microsoft.com/technet/abouttn/flash/tips/tips_060804.mspx). I want to use sp_help_job in an automated script, and fetch its result set with INSERT-EXEC, in which case I get the mentioned error message.
    – Attila Csipak
    Jun 2, 2010 at 22:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .