October 22, 2002 at 11:35 am
I think I have shot myself in the foot.
I am using : SQL Server 7 and MDAC 2.7
All latest service packs
I set up an application role in my sql database.
In my VB6 application where I use ADO for all of my data connections, I
modified my data access layer so that it access the database using the
application role.
There is a known bug related to resource pooling that says "the only
workaround"(!!) is to turn oledb services to -2 (no resource pooling) when
you make your connection.
Q229564: SQL Application Rol erroros with OLE DB resource pooling
"The only available workaround is to disable OLE DB Resource Pooling, which
ADO uses by default. This can be done by adding "OLE DB Services = -2" to
the ADO Connection string, as shown here: "
This is fine and dandy when I am connecting with the SQLOLEDB provider, but
not an option with the MSDataShape.1 provider.
I am totally cornered by this problem and don't see how to get around it. I
am , of course, experiencing the resource pooling problem with the
msdatashape connections.
I read in further documentatino about resource pooling and oledb that I can change the default timeout (60 seconds) in the client registry. I did (the best I could according to the step=by-step) with no affect.
Is there any OTHER known work around for this??
THanks much.
Julia Lerman
October 22, 2002 at 3:39 pm
Not fun. How about just dumping the app role and using a sql login? Not much of a change in your code, you can assign the same permissions to it in the db.
Andy
October 22, 2002 at 8:14 pm
Actually, I was trying really hard to figure out why a unique sql login used just in that database is much different than an app role. There's not really a lot written about app roles.
I have noticed that the error seems to happen only in debug mode, not when I run the exe (phew!) - but I can't truly count on that. And I haven't had one complaint from the users who are using this app all day.
julie
October 23, 2002 at 4:36 am
I've got a couple recent articles about sql logins you might want to look at, give you ideas anyway. Biggest benefit of app roles is that they override any other permissions granted - typically you'd use in conjuntion with a trusted connection. That way you're relying on NT to authenticate, then using the app role so that they can't use an authorized tool to access your data. App roles add some complexity since you (or your controls) have to remember to execute them each time you open a connection.
Andy
October 23, 2002 at 7:49 am
Andy thanks for the article ref's. I am definitely the "reluctant dba" - a programmer, but I have to do all of this with sql so MY programs work! I have always hated hard-coding things like security into apps because of the possiblity ofrenaming the server, etc. but in this case it is a controlled environment and I love that I don't have to worry about some access power user getting in to the database. I also have a db replication/publishing tool that I wrote that I still have to figure out how to give it access to the db now. Luckily, I can use it still, but not the users. It's a bummer because I'm using sql server 7, so I have to hand code all of the connections to the SQLDistribution class but I know I'll get that figured out. I certainly know a lot more about SQL Server than I ever thought I would. Hard to keep up with all of the new programming tools AND this, too. Well, as a friend says: sleep is for amateurs!
Thanks again.
And thanks for the site.
Julie
October 23, 2002 at 4:42 pm
Hard coding isnt always a bad solution - the trick is to make it easy to find, change, and deploy. Im part way through building a solution that addresses all three, maybe be able to write it up in a month or two!
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply