DSN Default Database Changes

[WayneRyan]

We have a bunch on "dumb" engineers who link to SQL Server with a
User DSN. For "some" reason (over the weekend) the Default Database
changed from DatabaseA to DatabaseB in all of their DSNs!

The only abnormal activity was that one of the IT folks detached DatabaseA.

Could this have cause it.

And what kind of process would chase down all of those user DSNs and
change the Default DB?

Easily enough corrected, but ... Why???

Thanks,
Wayne

 

[Banana]

Was DatabaseB named same as DatabaseA or considered as a synonym or back up of DatabaseA?

Did the users report any pop up at the startup prompting for more information? I suppose that if the SQL Server had only two databases or at least the A and B was adjcaent, and when the users tried to connect to the DatabaseA, DSN would fail because it can't find DatabaseA and a little popup shows up prompting the user to fill in the missing credentials. Maybe it had also selected DatabaseB by default?

Were there any automated scheduled task upon the user's .mdb files?

And forgive me for this 'is it plugged in?' question but could there be a piece of code somewhere in the .mdb file that does the linking automatically?

 

[WayneRyan]

Hi Banana,

No, the database names were very different (EWDS070 & Delta_Destination).

Users saw no popups or anything.

There are many, many databases on this server and the "names" are not adjacent.

No scheduled tasks.

No DSN-Less connections, the engineers use "User DSNs" to let their Access
front-ends see the server data.

I use DSN-Less connections and was totally unaware of this. I even had a user
DSN on my machine that was changed, but it was unreferenced.

The issue is resolved, it just mystifies me as to why all the DSNs would suddenly
point to a different database (AND to the SAME different database).

Either way it's time to resume migrating from Server 2000 to Server 2008.

Thanks again,
Wayne

 

[Banana]

Gee whiz, you certainly have a stumper here, Wayne.

I would love to know how that could happen.

The only other straws I could grasp at here is that DSNs were overwritten by some kind of company updates.

If you ever find out the cause, I'd love to hear about it.

Good luck with the migration!

 

[WayneRyan]

Banana,

I know ... it's just weird that a piece of software will agressively "attack"
client machines. And since when does software "choose" such a thing
as a Default Database (other than Master)?

Oh well, I'll let you know but I'll probably never know why.

Thanks,
Wayne

 

[WayneRyan]

Banana,

Well, the plot thickens.

I noticed that a large number of users who had their default database
as the one that was detached had their default switched to DatabaseB.

Easily enough fixed.

I set my default database:

exec sp_defaultdb 'wayne.ryan', 'QaDb'

QaDb has DbID = 70

Master..SysXLogins has me as a 70.
Master..SysDatabases has QaDb as a 70.

Detached QaDb.

Master..SysXLogins has me as a 70. (Even though now there is no 70).
Master..SysDatabases has no entry for QaDb.

I can't start Query Analyzer (No default DB)!

Reattached QaDb ... It reattached as a 70.

My default db worked when I started Query Analyzer.

I thought this process was going to change my default DB to 1 (Master)
but it didn't.

I didn't use the "remove all history" option when detaching. That might
be a critical point as maybe that's what made it "remember" that it was
database 70.

Anyway, just thought you might enjoy an update.

I'll try again in the future using the "remove all history" and see if it
changes my default DB.

Thanks,
Wayne

 

[Banana]

Hmm. All of those are actually new stuff for me. Sounds like a new self-study lesson for me.

Thank you for sharing, Wayne.

 

[WayneRyan]

Banana,

I'll post back when I experiment next.

See ya,
Wayne

 

[WayneRyan]

Banana,

I've tried everything to make the darned thing change the default database.

No matter how I detach a database, any user's that have that as their
default database still point to the detached database. Even if it doesn't
exist.

Just one of those things that we'll never know the answer to.

Thanks again,
Wayne

 

[SQL_Hell]

Maybe the default database was databaseA in the DSNs, but the default was set to databaseB for each login... so when DatabaseA was detached it reverted to the default databases on each login rather than the DSN.

 

[Banana]

Hmm. Interesting idea.

FWIW, the way ODBC works, a database must be selected. To use other databases objects, we must use fully qualified name ([database].[table name].[column name]), or we end up either referring to wrong table (the default database's table rather than the other database) or code blows up because it can't find the table. If the DSN was set to a different database, Wayne's application would have to have had fully qualified names in all queries, which seems unlikely to me.

But I could be wrong or missing a point.

 

[SQL_Hell]

Yeah you are right, I just tested this by creating a test database, test login and test DSN. It wouldn't change by itself, it would give an error such as "invalid default db".

Curious issue you had here Wayne

 

[WayneRyan]

Well, I don't think that I'll ever find out what really happened. I guess that
I'll just stay with DSN-Less techniques.

We are into our migration to SQL Server 2008, so I'm on to my next issues.

Thanks again all,
Wayne