SQLTeam.com | Weblogs | Forums

Ad Hoc Distributed Queries

Is there a security concern to turn this on for a Read-Only database:

USE [Master]

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

The reason I am asking is because instead of using Link Server, OpenRowset and OpenDataSource are alternatives to Link Server.

best for you to read the documentation from MS

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-configure-transact-sql?view=sql-server-ver15

I'm not a huge fan of linked servers. They cause me all kinds of performance and security issues. My suggestion would be to avoid that. IIRC, that will force calling stored procedures across linked servers. That's nearly always a better situation to be in.

1 Like

Hi Graz,

What exactly is IIRC?

https://en.wikipedia.org/wiki/IIRC :grinning:

1 Like

That is what I thought but I thought Graz is talking about a technology.