locked
Query Notifications Security Problem RRS feed

Answers

All replies

  • To start with, can you post the output of SELECT @@version? I note that the original poster in that other thread said SQL 2005 RTM. I hope you are not there...

    Else... I trust that Bob's blog post is accurate - Bob is very usually accurate. But you may have missed some small detail, but since we don't see your setup, we can't say. It would certainly help if you can post code that demonstrates the issue, like Ebucis did three years ago.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, June 23, 2012 8:53 PM
  • Hi Erland,

    Thank you for your response. Response to your SELECT:

    Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
     Feb 10 2012 19:13:17
     Copyright (c) Microsoft Corporation
     Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)

    I re-read many of the old blogs, tried all suggestions therein, and I still get that error.

    Dependency works fine if I am running out of administrator account. Do not seem to be able to get it. I must be missing something?

    Regards,

    Peter Battelino


    PMBT

    Saturday, June 23, 2012 9:40 PM
  • That's a slightly newer version that in the old thread. :-)

    As I said, it would help tremendously if you posted the full scripts to recreate your situation. Else, we can only guess what you are doing wrong.

    I will have to make the confession that I have not used SqlDependency myself, but if you had needed to be admin, I guess Bob would have said so in the old thread. Thus, I am not so inclined to set up an example myself, but if I get one to work with, I may take a closer look.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, June 23, 2012 10:38 PM
  • Query notifications changed between Beta and RTM of 2005. And all the docs were not always updated to reflect it. Including I whitepaper I wrote in against the beta release, I handed in an update but I get the impression some of the MSDN links still point to the beta one.
     
    Here’s the original blog posting when this changed in 2005 RTM: http://blogs.msdn.com/b/dataaccess/archive/2005/09/27/474447.aspx?CommentPosted=true. Have a look at the “required database permissions” section in this one.
     
    This is my last blog post about Query Notifications in 2005: http://www.sqlskills.com/BLOGS/BOBB/post/About-SCHEMAs-and-setting-up-Query-Notifications.aspx about an special consideration if your queue uses schemas.
     
     
    Hope this helps,
    Cheers,
    Bob
    • Marked as answer by PMBT Sunday, June 24, 2012 1:28 PM
    Sunday, June 24, 2012 2:36 AM
  • Dependency works fine if I am running out of administrator account. Do not seem to be able to get it. I must be missing something?

    Then it must be a permissions issue.  Below is script and code I tested under SQL Server 2012 using a minimally priviledged account.  I used a schema other than dbo as the user's default schema to avoid granting control on the dbo schema.

    CREATE DATABASE MyDatabase;
    GO
    ALTER DATABASE MyDatabase SET ENABLE_BROKER;
    GO
    USE MyDatabase;
    GO
    CREATE LOGIN MyLogin WITH PASSWORD = 'MyL0g1n@';
    GO
    CREATE USER MyLogin;
    GO
    CREATE SCHEMA MyLogin AUTHORIZATION MyLogin;
    GO
    ALTER USER MyLogin WITH DEFAULT_SCHEMA = MyLogin;
    GO
    --grant minimum service broker permissions
    GRANT CREATE SERVICE TO MyLogin;
    GRANT CREATE QUEUE TO MyLogin;
    GRANT CREATE PROCEDURE TO MyLogin;
    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO MyLogin;
    GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO MyLogin;
    GRANT RECEIVE ON dbo.QueryNotificationErrorsQueue TO MyLogin;
    GO
    
    --create objects to test dependency
    CREATE TABLE dbo.MyTable(
    	Col1 int PRIMARY KEY,
    	Col2 int
    	)
    GO
    CREATE PROC dbo.usp_select_data
    AS
    SELECT Col1, Col2
    FROM dbo.MyTable;
    GO
    GRANT EXECUTE ON dbo.usp_select_data TO MyLogin;
    GO
    
    using System;
    using System.Data;
    using System.Data.SqlClient;
    
    namespace QueryNotificationsDemo
    {
        class Program
        {
            static string connectionString = @"Data Source=MyServer;Initial Catalog=MyDatabase;User ID=MyLogin;Password=MyL0g1n@";
            static SqlConnection connection = new SqlConnection(connectionString);
    
            static void Main(string[] args)
            {
                SqlDependency.Start(connectionString);
                GetData();
                Console.WriteLine("Waiting for data changes");
                Console.WriteLine("Press enter to quit");
                Console.ReadLine();
                Cleanup();
            }
    
            static public DataTable GetData()
            {
                DataTable dt = new DataTable();
    
                SqlCommand cmd = new SqlCommand("dbo.usp_select_data", connection);
                cmd.CommandType = CommandType.StoredProcedure;
    
                // Create dependency for this command and add event handler
                SqlDependency dependency = new SqlDependency(cmd);
                dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);
    
                // Open the connection if necessary
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }
    
                // Get data
                dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
    
                return dt;
            }
    
            // Handler method
            static void OnDependencyChange(object sender,
               SqlNotificationEventArgs e)
            {
                SqlDependency dependency = (SqlDependency)sender;
                dependency.OnChange -= OnDependencyChange;
                SqlDependency.Start(connectionString);
                DataTable dt = GetData();
                Console.WriteLine("Data changed. {0} rows returned.", dt.Rows.Count.ToString());
            }
    
            static void Cleanup()
            {
                // Release the dependency and close connection.
                SqlDependency.Stop(connectionString);
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
    
        }
    }



    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Sunday, June 24, 2012 3:47 AM
  • Bob,

    Thank you so much for your reply. I have seen (and read many times) your blogs before, and I still could not get the my SQL dependencies to work. The only difference between your blog and what I was doing was that I was assigning all those permissions to a database role.

    I have re-checked everything - and it turns out I missed one item from your second blog: I created a schema owned by my non-privelaged user, made the user own the schema, but did NOT make that schema user's default schema. Missing this last step alone (with everything else in place) made the whole thing not work...

    Thanks again for straightening me out, much apreciated.

    Peter Battelino
    BTE Corporation


    PMBT

    Sunday, June 24, 2012 1:27 PM
  • Dan,

    Thank you so much for your reply. In the mean time I have figured out the problem (please see my reply to Bob).

    Best regards,

    Peter Battelino
    BTE Corporation


    PMBT

    Sunday, June 24, 2012 1:39 PM
  • I'm glad you figured it out.  There are many details to setup permissions properly and it's easy to overlook one.  That's why I posted a working example for comparison.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Sunday, June 24, 2012 1:46 PM
  • Glad it was useful.
     
    Cheers, Bob
     
    "PMBT" wrote in message news:fa11bb2d-ecfd-47af-affb-d0361abb6bb3...

    Bob,

    Thank you so much for your reply. I have seen (and read many times) your blogs before, and I still could not get the my SQL dependencies to work. The only difference between your blog and what I was doing was that I was assigning all those permissions to a database role.

    I have re-checked everything - and it turns out I missed one item from your second blog: I created a schema owned by my non-privelaged user, made the user own the schema, but did NOT make that schema user's default schema. Missing this last step alone (with everything else in place) made the whole thing not work...

    Thanks again for straightening me out, much apreciated.

    Peter Battelino
    BTE Corporation


    PMBT

    Sunday, June 24, 2012 2:18 PM