Anonymous Pull Subscription Security Question

  • Does anyone know where the following data is generated?

    Subscription Properties (It's an Anonymous Pull Subscription to a Transactional Publication)

    Security Tab

    There are two radio buttons: Impersonate the SQL Server Agent... and Use SQL Server Authentication.

    I create this Subscription many times as I am loading laptops for our agency. It's set up to run with a batch file using osql and a copied subscription database. The problem is that when I create the copy of the subscription DB, I make sure the 'Impersonate..' button is checked. However, when I attach the copied subscription, the 'Use SQL Server Authentication' is checked with 'sa' as a login.

    I need the 'Impersonate...' button checked. I can do it manually and everything works properly. I need it to work automatically though.

    Thanks in advance for any help.

    Art Sennholtz 

  • This was removed by the editor as SPAM

  • This is a cut and paste of what we do. Attach & Go subscriptions by default use SQL authentication regardless of the security setting from the original database. The key for BOL is "sp_change_subscription_properties".

    Here is a snippnet from a JScript file I have used in the past and it should point you in the correct direction (obviously we have a few more variables being passed in - but it should be clear):

    szCmd = "osql -n -b -d{yourDB} -oosql.out " + szConnectionString + " -Q\"Declare @pPublisher varchar(200) \n" +

    "Declare @pPublisher_db varchar(200) \n" +

    "Declare @pPublication varchar(200) \n" +

    "select @pPublisher=publisher, @pPublisher_db=publisher_db, @pPublication=publication from mssubscription_properties \n" +

    "exec sp_change_subscription_properties @Publisher=@pPublisher, @Publisher_db=@pPublisher_db, @Publication=@pPublication, @Property='Distributor_Security_Mode', @Value=1 \n" +

    "\"";

    Hope this helps,

    Gareth


    Kindest Regards,

    Gareth

  • Gareth,

    Thank you, thank you, thank you! I've been working with Microsoft for a week on this issue to no avail. Your suggestion was just what I needed.

    And, thank you SQLServerCentral for being such a good resource.

    Art

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply