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:
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.
Best Answer
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.