November 16, 2002 at 7:00 am
I am still trying to deal with the last problem that was created when I locked down a database with App Roles. All of the applications that use the database are working just fine. However, I have one app that is doing replication and since replication and app roles don't go together (according to my tests and confirmation by Morris Lewis) I am trying to jiggle the security to get this working.
Here is my set up
- Win2K, SQL Server 7 (all latest service packs)
- an NT Group called "Domain Users". They have a login to the sql server and Public access to this database.
- approle on my database
So *I* am a fully priveleged admin on the network and on the database as well (regardless of the app roles).
I can run my replication (snapshot, push out to subscriber) with no problems. This is, by the way, a vb application. So it runs the "snapshot.exe" program with all of my parameters. When the snapshot is complete, it then creates a distribution object and publishes the snapshot to the subscriber.
There is ONE other user on the system that needs to be able to do this.
Prior to the approle, the DomainUsers group had read/write priveleges on the database and this person (who also btw has the sql server client apps on their machine) had no problems running it.
Now, of course, I get a "perm required to run this operation" when I try to do snapshot from that user's login.
I have fiddled with sql security. Here are a few of the tests.
I explicitly added this person to the logins and explicitly gave that login access to the database. I made the user have read perm on the database (haven't tried write yet - but this shouldn't be the solution). I think maybe write has something to do with it because we are writing to the distribution database, right? I have fiddled with the OWNER of the disribution agent. It is currently MY OWN login. I tried setting it to the other login - but this didn't work.
I have trolled MSDN library and forums etc. but I just can't figure this out.
I know it can be done, but ...."uncle!" I give in. I have an inkling who is the mr. replication guru around here (mr. warren) but really, I'll try anything!
Thanks much.
J Lerman
November 16, 2002 at 8:06 am
Dont know that I qualify as guru, just interested in replication! I dont think changing owner will matter, the app role seems to be the issue. Dont have the answer, but as usual, have some ideas for going around. One (oh, I can hear the screams now) is to just put the other user in sysadmins. Not great, but it would work. If I follow what you're doing you want to request a push operation from a client, correct? I do something similar at work and I do it by always running the request under a sysadmin account. I beat the permissions issue right now by having them email me a message where the subject is "replicate:dbname". I run some code in Outlook that runs the job. Right now moving this to something a little nicer, an aspx page - users will click the link/button/whatever, we'll stick a row in a table indicating which db to update. Then either have a trigger start the job, or just run the job every min to look to see if there are rows to process, then match the dbname to the appropriate job. Works for things besides replication.
Im guilty of just writing off app roles I suppose, good idea, just not good enough. See my couple articles from earlier this year about sql logins being the way to go.
Im trying to finish up a bunch of stuff before leaving for PASS next week, but I'll try to experiment some to see if there is a way to make the app roles work. In the mean time, maybe another reader already knows??
Andy
November 16, 2002 at 11:49 am
Guru? Well, I'd say it's all relative!
Hmmm - this was why I asked the question to Morris Lewis at SQL Connections (I was at ASP COnnex - but went to one of his sessions). He LOVES approles (made me feel better about having chosen them) but did say I will have a problem with replication. I know that your solution will do the trick but EEEK! These folks have to call me everynight when they finish their scheduling so that I can do the replication for them to post to the web. I'm trying to avoid that dependency. Anwyay - have a great time at PASS. A member of my user group (www.vtdotnet.org), Roman Rehak, will be presenting at a session! And he has already been given instrux to say hi to my buddies Fernando and Kimberly. Do say HI to Roman if you have a chance. It'll make him feel famous!! <g> And thanks for the feedback.
julie
November 16, 2002 at 3:57 pm
No reason for you to be there if you go the job route. Have your friend catch up with us, between Brian, Steve, and I we have five sessions.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply