September 26, 2005 at 2:12 pm
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
September 29, 2005 at 8:00 am
This was removed by the editor as SPAM
September 29, 2005 at 6:58 pm
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
September 30, 2005 at 9:10 am
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