DSN madness

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:
> 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?
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?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9798CEF77432EYazorman@.127.0.0.1...
> Paul Pedersen (nospam@.no.spam) writes:
> And the error message is?
Something on the order of "Database does not exist or access denied".
Like I said, this used to work fine. After a few ws, 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.

> 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?
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 ws, the database will be moved to a server.|||Paul Pedersen (nospam@.no.spam) writes:
> Something on the order of "Database does not exist or access denied".
> Like I said, this used to work fine. After a few ws, it began to get
> slow. Then it began to have occasional errors signing in. Now it won't
> sign in at all. Bizarre.
Since it goes slower and slower, it sounds like a networking problem.
What is the contents of the DSN?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9799687B2B9DYazorman@.127.0.0.1...
> Paul Pedersen (nospam@.no.spam) writes:
> Since it goes slower and slower, it sounds like a networking problem.
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.

> What is the contents of the DSN?
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|||Paul Pedersen (nospam@.no.spam) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9799687B2B9DYazorman@.127.0.0.1...
> 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.
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.)

> 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).
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.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1143942800.875293.89510@.j33g2000cwa.googlegroups.com...
> 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.
No, it was a system DSN. But thanks for trying.

> 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
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.|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97998597D1CA0Yazorman@.127.0.0.1...
> 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.)
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 ws (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.

>
> I had hoper that you have posted it as is.
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.

> What I wanted to know is whether you specify any network library.
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.

> 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.
Thanks. I would never have found it without that tip.
OK, I'll check that.
Thanks for all your help.sql