none
Azure database, join betwen table with row level security and external table

    Question

  • Hi guys,

    we get this error message if we join a table with row level security to an external table.

    Msg 46806, Level 16, State 1, Line 3 An error occurred while executing GlobalQuery operation: 100001;Failed to generate query plan.

    Anyone any idea what could be wrong here?

    Thank you,

    Anca

    Wednesday, January 31, 2018 9:06 AM

All replies

  • Hello,

    Have you define a predicate function to join both tables? Has the policy been created with the SCHEMABINDING option set to ON?

    https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, January 31, 2018 10:55 AM
  • Azure Elastic Query and using external tables sometimes doesn't play well with some sql commands and or data types.

    For example you can't use query hints like:  

    OPTION (maxdop 1)

    If this is your case: you have to start removing all fancy hints/code in your query until you find the one that's causing it.

    If it's a DataType issue(which I think is your problem here):

    Then you might want to create a jump table without that row level security and than use that as your jump External Table.  Of course your row level data security might be compromised here.  So use wisely and/or as needed for your business needs.  

    Friday, May 24, 2019 11:09 PM
  • Good day Anca,

    You provided us stories about the behavior that the user see, but you did not provided any information about what you did in order to get to this state, and unfortunately we cannot read minds (This one can😃).

    Please provide the exact queries which you used in order to reproduce the issue.

    In the mean time I will provide you a full working procedure. In general there is not problem in using JOIN between external table and real table even in two different Azure Virtual Servers:

     
    -->> connect to the master database in instance02
    create database instance02db(
    	MAXSIZE = 100 MB, EDITION = 'basic'
    );
    GO
    
    -->> connect to the database instance02db
    
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My!Simple@Pass#for$Lecture'  
    GO
    
    -- CREATE 
    CREATE TABLE [dbo].[instance02tbl](   
    ID int identity(1,1) NOT NULL,
    txt nvarchar(20) NULL
    )
    
    INSERT [dbo].[instance02tbl](txt)
    values ('a'),('s'),('d')
    GO
    
    CREATE SCHEMA SecurityScheme;  
    GO
    
    CREATE OR ALTER FUNCTION SecurityScheme.fn_Predicate(@txt nvarchar(20))
    RETURNS TABLE WITH SCHEMABINDING AS RETURN 
    	SELECT 1 AS fn_Predicate_result
    	--WHERE @txt = SESSION_CONTEXT(N'txt')
    	where @txt = 's'
    GO
    
    DROP SECURITY POLICY if exists PolicyFilter
    GO
    CREATE SECURITY POLICY PolicyFilter
    	ADD FILTER PREDICATE SecurityScheme.fn_Predicate(txt) ON [dbo].[instance02tbl]  
    WITH (STATE = ON);
    GO
    
    --EXEC sp_set_session_context 'txt', 'a';  
    --SELECT SESSION_CONTEXT(N'txt'); 
    --GO
    
    select * from [dbo].[instance02tbl]
    GO-- filter works well 
    

     

    Now, we can move to the second virtual instance

    -->> connect to eh master in instance instance01
    
    create database instance01db(
    	MAXSIZE = 100 MB, EDITION = 'basic'
    );
    GO
    
    -->> connect to the database instance01dbin
    
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My!Simple@Pass#for$Lecture'  
    GO
    
    CREATE DATABASE SCOPED CREDENTIAL credential_name 
    	WITH IDENTITY = '<Your User name in the instance02>', SECRET = '<Password for instance02>';
    GO
    
    CREATE EXTERNAL DATA SOURCE RemoteData
    WITH( 
    	TYPE=RDBMS,
    	LOCATION='instance02.database.windows.net',
    	DATABASE_NAME='instance02db', 
    	CREDENTIAL= credential_name
    );
    
    CREATE EXTERNAL TABLE [dbo].[instance02tbl](   
    	ID int NOT NULL,
    	txt nvarchar(20) NULL
    )
    WITH (
    	DATA_SOURCE = RemoteData
    );
    GO
    
    SELECT * FROM [dbo].[instance02tbl]
    GO
    
    --EXEC sp_set_session_context 'txt', 'a';  
    --SELECT SESSION_CONTEXT(N'txt'); 
    --GO
    
    -- scope of the SESSION_CONTEXT does not moved to the external table
    --SELECT * FROM [dbo].[instance02tbl]
    --GO
    
    CREATE TABLE [dbo].[instance01tbl](   
    	ID int NOT NULL,
    	txt nvarchar(20) NULL
    )
    GO
    INSERT [dbo].[instance01tbl](id, txt)
    values (1,'q'),(2,'w'),(3,'e')
    GO
    
    select * from [dbo].[instance01tbl]
    GO
    
    select * 
    from [dbo].[instance01tbl] t1
    LEFT JOIN [dbo].[instance02tbl] t2 ON t1.ID = t2.id
    GO
    
    

    I hope this sample solve your needs👀


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Saturday, May 25, 2019 9:46 AM
    Moderator