MDX CA 2100 Security Issue

问题:

I have written MDX query and assigned it to Adomd Connection object. When i Run Code Analysis, it gives me CA2100 Review SQL queries for security vulnerabilities error we can't directly supply query to connection objects. It Says either we should embed it in Stored Procedure or use Parametrized query. But in my case, there are no parameters for this query. So kindly help, how can I remove this CA 2100 Security error. PFB the code. Thanks in advance.

            conn.Open();
        // Adomd Connection Object
        var adomdCommand = new AdomdCommand()
        {
            Connection = conn,
            CommandType = CommandType.Text,
            CommandText = mdxQuery
        };
        //Execute command to return cell set..
        CellSet csResult = adomdCommand.ExecuteCellSet();

        conn.Close();

回答1:

You can create stored procedure in SQL Server that performs execution of AS query. Assuming sp's are pre-compiled, that's secure. You should do the following:

• Add your AS server as linked server to SQL Database server (via Server Objects in SSMS)

• Create stored procedure. The general scheme is (sp body):

  declare @tsqlquery varchar(1000)
  declare @mdxquery varchar(2000)
  
  set @tsqlquery = 'SELECT
                  "[DimA].[A].[A name].[MEMBER_CAPTION]" as dimensionName, 
                  convert(float, "[Measures].[X]") AS measureValue
                FROM OPENQUERY(<**YOUR LINKED SERVER**>,'
  
   set @mdxquery = '''**YOUR MDX QUERY**'  + '''' + ')'
  
   EXEC(@tsqlquery + @mdxquery)
  

You can also create parametized store procedure that modifies MDX query text based on parameters. As long as you are using Stored Procedures, you are safe. For example, our report server queries only use SP's, not direct AS queries.

PS. If your AS server has role security defined, you should enable ImpersonateCurrentUser for the database, then your role will work.