Search code examples
sql-serverpowerbiopenquery

OpenQuery in Power BI giving me back an error

I am new to Stored procedures & OpenQuery in Power BI.

I am trying to connect to my SQL Server database by passing an SQL statement:

SELECT * FROM OPENQUERY ([DVT-FUSION],
'EXEC CRM_DVT.dbo.SP_RPT_Billing_Summary ''R1'', ''R2'', ''0'', ''0''  ')

But for some reason I get back an error stating:

Microsoft SQL: Server 'DVT-FUSION' is not configured for DATA ACCESS.

Solution

  • As the error suggested, you database is not configured for DATA ACCESS.

    You should run this once on your SQL Server instance:

    EXEC sp_serveroption 'DVT-FUSION', 'DATA ACCESS', 'true';