configuring SQL Server instance for Data Access using OPENROWSET, OPENQUERY

Question

I have SQL Server 2012 full version installed on my standalone non-networked development PC running Windows 7 x-64. Let's say FOO is the name of my PC, and my SQL Server named instance is FOO\SQL2012.

I am trying to follow the examples here, which show how to create a new temporary table using a stored procedure as the source of the data.

I would like to execute either of these queries, but am getting the errors shown.

   SELECT * into #temp FROM OPENQUERY("FOO\SQL2012", 'exec MySchema.MyStoredProc')

   **ERROR: Server 'FOO\SQL2012' is not configured for DATA ACCESS.**

or this query:

  SELECT * INTO #temp FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2012;Trusted_Connection=yes;',
 ' EXEC MySchema.MyStoredProc')

 **ERROR: Msg 11529, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1

The metadata could not be determined because every code path results in an error; see previous errors for some of these.**

How would I configure the SQL Server instance to allow the currently authenticated user, whoever it may happen to be, to use OPENROWSET, OPENQUERY against the local instance?

Are separate versions of these queries required for users who have authenticated via Windows and for those who have authenticated via SQL Server?

Answer 1

You should only need to do this once per linked server (and I recommend either always referencing FOO\ or always referencing (local)\ ... be consistent!).

EXEC sp_serveroption 'FOO\SQL2012', 'DATA ACCESS', 'true';