I have an app that accesses a SQL db via a DSN (and MSDE). The user name and
password are contained in the app, and passed to the data source at run
time.

Everything works fine if the user is an Adminstrator (on Windows), but can't
connect if the user is a User or Power User. If I promote the user to
Administrator, it works; if I demote him back again, it doesn't.

The really strange thing is, this used to work just fine!

This is driving me nuts. Anyone have a solution?

Paul Pedersen (nospam@no.spam) writes:
And the error message is?

From what you say it sounds like a permission problem on the DSN.
(Disclaimed: I never liked or understood DSN. I prefer to live in a
DSN-less world.)

By the way... I don't know what sort of app this is, but embedding
username/password into the app, does not sound like something I would
do. Would it not be better to use Windows authentication?

Show quote Hide quote Something on the order of "Database does not exist or access denied".

Like I said, this used to work fine. After a few weeks, it began to get
slow. Then it began to have occasional errors signing in. Now it won't sign
in at all. Bizarre.

I have reinstalled and re-configured MSDE several times. Recreated the DSN
too. Works fine for Adminstrators, doesn't work at all for others. Perhaps I
made a mistake in configuration, but I can't think what it might be.



The user name that is passed is assigned a specific role in the database.
Although there's only one instance of the app at present, eventually it
could be run by a number of users from a number of different machines. I
prefer not to have to track all those users, who have no other business in
the database anyway. This way, I can just track the app. Wherever it's
running from, it can sign in and get its data.

At present, the database is local to the machine the app is running on.
Within a couple weeks, the database will be moved to a server.

Paul Pedersen (nospam@no.spam) writes:
Since it goes slower and slower, it sounds like a networking problem.
What is the contents of the DSN?

I don't see what that might be. 1, It's not currently running on a network.
It's all local. 2, It works fine for Adminstrators, or even the account in
question if I promote it to Administrator (which I have done, just to get it
running - it's mission critical - but of course I don't want to leave it
that way). 3, Not everything was getting slow, just first getting access to
the data. Queries ran well enough.


Nothing special. Created in the ODBC Control Panel as a System DSN. Set as
SQL Server authentication (user name & password) rather than Windows login.
User name and password are not saved (they are provided by the app). Default
database setting is ignored, because that's specified in the db for the app
user. Basically it has nothing but a name, which the app looks for, a
specification of the SQL Server driver, and the instance name of MSDE (I use
a named instance).

At present, there is no other access to that MSDE instance, which is the
only SQL Server instance on the machine.

It works fine, for Administrators. And it used to work for Limited Users,
too.

Hey Paul,

It could be something obvious, like you set up a User DSN instead of a
System DSN; in that case, only the original user and/or the
Administrator group would have access to that DSN entry.  Or, it could
be that the accounts don't have access to the Windows registry entry
required to load the System DSN information.

Take a look at :http://support.microsoft.com/kb/306345/EN-US

and see if it helps.

Stu

No, it was a system DSN. But thanks for trying.



Hmm, that sounds interesting. I don't understand how that could have
happened, because it used to work.

I'll take a look at it.

Paul Pedersen (nospam@no.spam) writes:
I've seen issues like this on a local machine. It that case I was logged
as admin, the problem is that shared memory freaked out.

I guess that this machine is in a domain, and not a solitary workstation?
Then there may be contacts with the domain controller. (Windows networking
is nothing I know well, so exactly what problems that may be, I don't know.)

I had hoper that you have posted it as is.

What I wanted to know is whether you specify any network library.

In any case, verify in the Client Network Utiility that shared memory
is enabled. Although I mentioned that I've had problems with shared
memory, shared memory is what works best on a local computer.

Oh, since this is an MSDE machine, the Client Network Utility is probably
not available from the start menu, but running "CLICONFG" from command-
line should bring you to the version that comes with the MDAC.

Show quote Hide quote It was in a domain, but the server's motherboard died, so I moved the
database from MSDE on the server to the local machine, and set up MSDE on
it, and have been running it all locally. That machine no longer has any
network connection; it's completely standalone now. Like I said, it's been
working fine for a while.

I should have a new server up within a couple weeks (it's a volunteer job,
so it gets lower priority), this time with actual SQL Server instead of
MSDE, so maybe I can get it working again since I'll have the Enterprise
Manager to play with.



How would I do that? As far as I know, the ODBC control panel has control
over that. I don't even know where the control panel stores its DSNs - I
just assumed in the registry somewhere. It's a system DSN, not a file DSN.


There's nothing unusual in the way I set it up. I just chose SQL Server
driver, gave it the expected name, typed in the instance name of the server
(for some reason, it doesn't show up in the list), set it for SQL Server
authentication, and left everything else at the default.



Thanks. I would never have found it without that tip.

OK, I'll check that.

Thanks for all your help.

Change FK error: "Unable to delete relationship..."
find changed columns in a new version of a row
Querying for nearest values...
Wish List: String Concatenation Aggregate Operator
How to count rows ?