sqlsql-serverdotnetnuke2sxcdnn9

Can't Save SQL Query in 2sxc SQL DataSource


I'm trying to use the DNN SQL DataSource in 2sxc's VisualQuery Designer to retrieve and display information from SQL in a 2sxc App. However, when I try to input my SQL query and click 'Save', a 403 error is displayed. I was unable to find the 2sxc error log. Is there something wrong with my SQL query? How can I get the datasource to accept my query?

SQL Query:

Note: some values have been modified for privacy.

SELECT Description, CanonicalURL,
(CASE WHEN CanonicalURL LIKE '/abc/%' THEN CanonicalURL ELSE (SELECT TOP 1 Replace(TabPath, '//', '/') FROM Tabs WHERE TabName = PageName AND TabPath LIKE '//abc//%') END) AS 'abc',
(CASE WHEN CanonicalURL LIKE '/def/%' THEN CanonicalURL ELSE (SELECT TOP 1 Replace(TabPath, '//', '/') FROM Tabs WHERE TabName = PageName AND TabPath LIKE '//def//%') END) AS 'def',
(CASE WHEN CanonicalURL LIKE '/ghi/%' THEN CanonicalURL ELSE (SELECT TOP 1 Replace(TabPath, '//', '/') from Tabs WHERE TabName = PageName AND TabPath LIKE '//ghi//%') END) as 'ghi'
FROM 
    (SELECT DISTINCT s.Description, Replace(TabPath, '//', '/') + '/' + PageName as 'CanonicalURL', PageName
    FROM [dbo].[My_Series_Tabs] cst
    JOIN My_SeriesContent s ON cst.SerieID = s.SeriesID
    JOIN Tabs t ON t.TabID = cst.tabID
    WHERE PageName IS NOT NULL AND TargetCanonicalURL = 1 AND TabPath NOT LIKE '%translated%' AND CultureCode = 'en-US') allseries

403 Error

Had an error talking to the server (status 403)


Solution

  • This error was caused by my organization's web application firewall (WAF), which was configured to block requests containing potentially malicious code/scripts. Working with the team that manages our WAF, we were able to make certain exceptions for 2sxc that allowed the system to work properly.