Connecting to master db??

matttrax

We are running Navision 3.7 on SQL Server 2000.

Today we experienced major blocking problems stemming from two users that somehow got logged onto the master database instead of the normal company production database. Ordinarily the only users who get logged on to master are myself (Navision admin) and the SQL Admin account.

When I tried to kill their sessions they ended up sitting in a rollback status for over 2 hours while the blocking continued to get worse. Ended up having to reboot the server in the middle of the day.

I'm not an experienced SQL Admin by any stretch. I only know really basic stuff. But I'm wondering how these users got connected to that database.

1. What handles which database you connect to? SQL or NAV? (Aside from the zup file and an icon pointing directly to the database)
2. Aren't there roles that a user must have to connect to Master?
3. What can we do to keep this from happening again?

kine

1) Do you know if the SPID of these sessions were under 50 or over?
2) Do you know which application was in the Application name for these sessions?

matttrax

They were both over 50.

And they were both under Navision for the application being used.

Very confusing as the master database is not a NAV database. After those two users logged on again in the afternoon they were correctly placed in the normal production database.

Our DBA (contractor so wasn't available when it was happeneing) said it had to do with SQL Mail hogging the resources on the server. Doesn't make sense to me, but like I said, I'm no SQL expert.

Thanks for the help.

Nobody

The only time NAV touches the Master DB is at log in as far as I know.

ara3n

Nobody wrote:

The only time NAV touches the Master DB is at log in as far as I know.

To get the license file for example

Nobody

And to hit the extended stored procs "xp_ndo_...." for security.

ara3n

Nobody wrote:

And to hit the extended stored procs "xp_ndo_...." for security.

If you are using windows authentication.

matttrax

So this happened again today and I realized we never got a resolution last time.

SPID 89 is connected to the Master database and is EXECUTING. A restart of the user's machine did not clear the process. It has happened for this same user two days in a row. When we killed it yesterday is sat in rollback at 0% for over 12 hours. Had to restart services. Unfortunately it is blocking inserts into our customer table.

Any ideas on how to get rid of this session? And stop it from happening.

neshdenial

Regarding ASP.Net i have one doubt,when i am trying to connect the webapplication with sql server database it showing the following error
Server Error in '/Sample' Application...

jsrark

Do these users still have SQL permissions for the master database?
Do you know what they were trying to execute in NAV when the error occurred?
Can you replicare the error on a nother user?
:?:

stryk

matttrax wrote:

SPID 89 is connected to the Master database and is EXECUTING.

Could you check the Input Buffer; e.g. DBCC INPUTBUFFER (89) ? This should show the statement which was executed. Or maybe the SQL Profiler gives a clue ... ?!

David_Singleton

When Navision filters out the list of available databases on the server (in open database dialog) what is it searching for? I have never traced this, but it would be easy to do.

My guess is the table or flag (probably in $ndo$dbconfig or $ndo$dbproperty) has been created in master and Navision thinks it is a NAV database. Thus allowing the log on.

ara3n

I just confirmed that it's $ndo$dbproperty table that it looks for in the db.

I created a sql db and created the table $ndo$dbproperty and it showed up in the list.

I got the following error, which makes sense.

The [DatabaseName] database on the 127.0.0.1 server does not contain Microsoft Dynamics NAV Classic system objects and cannot be opened.

David_Singleton

ara3n wrote:

I just confirmed that it's $ndo$dbproperty table that it looks for in the db.

I created a sql db and created the table $ndo$dbproperty and it showed up in the list.

I got the following error, which makes sense.

The [DatabaseName] database on the 127.0.0.1 server does not contain Microsoft Dynamics NAV Classic system objects and cannot be opened.

So someone would have had to move the system objects as well into master.

As a starting point I would go and look in master to see if any of this is in there.