Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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 (Piotr Rodak) is a Stack Overflow user :-)

share|improve this question

1 Answer 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.

share|improve this answer
    
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 '10 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 '10 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 '10 at 22:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.