Nakul Vachhrajani (2/27/2012)
I believe the reason would be because OPEN queries require that Adhoc queries be enabled on the SQL Server, which, depending upon the environment and the situation, might be considered a security loop-hole.
Sorry, I don't think this is correct. Adhoc needs to be enabled for OPENROWSET and OPENDATASOURCE, but not for OPENQUERY.
When you use OPENROWSET or OPENDATASOURCE, you're basically creating a linked server on the fly, AFAICS, since the connection string is included in the arguments you pass the function. With OPENQUERY, the linked server already exists. That's what makes the first two "ad hoc" and OPENQUERY not "ad hoc", and should also be a clear indication of why the first two represent a different security risk than the third. See?
Yes, the Kerberos double-hop thing can be a bear and yes the security context has to be locked out. But, with a non-ad-hoc syntax, which OPENQUERY is, you have control over that.
For other people in this thread who feel that the fully-qualified syntax is better for developers than OPENQUERY, there are at least two reasons why this is not a great idea:
1 - even when the linked server is MS SQL, and if you decide you want to put the data someplace else, or if you just want to hit a test database rather than production, and if you have set up your linked server using MS OLE DB Provider for SQL Server or another provider (versus "Server type = SQL Server"), you can *name* the linked server to represent the data you're connecting to, rather than the server name itself, and the move is transparent to your developers because that name doesn't need to change. I realize that synonyms/aliases are another way to do it, but see point #2.
2 - you've limited the developer's thoughts about heterogenous queries to "queries between two MS SQL Servers". What happens when the HR system is Oracle or the CMS system is MySQL? Get them used to the right syntax now. As a bonus, they might not have to change much of anything in their code, when you migrate a system to a different back end.