locked
SQL dependency is not working when dynamic data masking is not enabled RRS feed

  • Question

  • Iam using sql dependency together with dynamic data masking. When the dynamic data masking is enabled, sql dependency onchange is not firing. Here is my code.


    private bool ConfigureSQLDependency()
    {
        try
        {
          string SQLQuery = "select Active from [dbo].[user_mater] where Active=0";
          SqlDependency dependency = ConfigureSQlDependency(SQLQuery);
          if (dependency != null)
          {
            dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
          }
       }
       catch (Exception ex){ throw; }
    }

    private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
      try
      {
         // do some thing on data change 
         SqlDependency dependency = sender as SqlDependency;
         string DynamicConnectionString = string.Empty;
         if (dependency != null)
         {
              dependency.OnChange -= dependency_OnChange;
              dependency = null;
         }
         if (e.Type == SqlNotificationType.Change)
         {
             ConfigureSQLDependency();                    
         }
        }
        catch (Exception ex)
                {

                }
    }

    and here is the table 

    CREATE TABLE [dbo].[user_mater](
    [id] [int] MASKED WITH (FUNCTION = 'default()') NULL,
    [active] [bit] NULL
    ) ON [PRIMARY]

    Everything is working fine if masking is not enabled. Once data masking is enabled , the dependency_OnChange event is not firing. Can someone help me out, what is going wrong with data masking.


    • Moved by Tom Phillips Wednesday, March 18, 2020 12:43 PM .Net framework question
    • Edited by Sapthagiri Meesa Monday, March 23, 2020 5:28 AM
    Wednesday, March 18, 2020 12:18 PM

All replies

  • Hi Sapthagiri Meesa,

    I have a question to confirm with you based on your description.

    Is dependency_OnChange event fired after disabling the data masking?

    In order to figure out the problem, you can recreate a new database and make a test again.

    We are waiting for your update.

    Best Regards,

    Xingyu Zhao 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 19, 2020 7:16 AM
  • Hi Xingyu,

    dependency_OnChange event is fired only after disabling the data masking.

    note: pls make sure service broker is enabled before replicating the scenario.

    Regards,
    Sapthagiri

    Friday, March 20, 2020 10:09 AM
  • Hi Sapthagiri Meesa,

    I make a test on my side.

    Table in my database:

    If I  select 'productname' from the table, dependency_OnChange will be fired, but the type of SqlNotificationEventArgs is 'Subscribe'.

    If I select 'productprice' from my table, everything is fine and dependency_OnChange will be fired after changing any data in 'productprice'.

    Since 'Active' in your table is not masked, dependency_OnChange should be fired.

    Here's the code in my test:

            static void Main(string[] args)
            {
                SqlDependency.Start(connectionString);
                getDataWithSqlDependency();
                Console.WriteLine("Waiting for data changes");
                Console.WriteLine("Press enter to quit");
                Console.ReadLine();
                SqlDependency.Stop(connectionString);
                
            }
    
            static DataTable getDataWithSqlDependency()
            {
                using (var connection = new SqlConnection(connectionString))
                using (var cmd = new SqlCommand("SELECT productname FROM dbo.product;", connection))
                {
                    var dt = new DataTable();
                    var dependency = new SqlDependency(cmd);
                    dependency.OnChange += new OnChangeEventHandler(onDependencyChange);
                    connection.Open();
                    dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
                    return dt;
                }
            }
            static void onDependencyChange(object sender,SqlNotificationEventArgs e)
            {
                Console.WriteLine($"OnChange Event fired. SqlNotificationEventArgs: Info={e.Info}, Source={e.Source}, Type={e.Type}.");
    
                if ((e.Info != SqlNotificationInfo.Invalid)
                    && (e.Type != SqlNotificationType.Subscribe))
                {
                    var dt = getDataWithSqlDependency();
                    Console.WriteLine($"Data changed. {dt.Rows.Count} rows returned.");
                }
                else
                {
                    Console.WriteLine("SqlDependency not restarted");
                }
                Console.ReadLine();
            }
     

    Besides, you can refer to the following reference to mask id.

    Character mask output data on select

    Hope it can help you.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 24, 2020 2:23 AM
  • Hi Sapthagiri Meesa,

    How is the question going? Please let me know if you need further assistance.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 26, 2020 5:39 AM