Connecting to master db??

matttrax
matttrax Member Posts: 2,309
edited 2010-03-19 in SQL General
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?
Matt Traxinger - ArcherPoint

Comments

  • kine
    kine Member Posts: 12,562
    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?
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • matttrax
    matttrax Member Posts: 2,309
    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.
    Matt Traxinger - ArcherPoint
  • Nobody
    Nobody Member Posts: 93
    The only time NAV touches the Master DB is at log in as far as I know.
  • ara3n
    ara3n Member Posts: 9,258
    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
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Nobody
    Nobody Member Posts: 93
    And to hit the extended stored procs "xp_ndo_...." for security.
  • ara3n
    ara3n Member Posts: 9,258
    Nobody wrote:
    And to hit the extended stored procs "xp_ndo_...." for security.

    If you are using windows authentication. :)
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • matttrax
    matttrax Member Posts: 2,309
    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.
    Matt Traxinger - ArcherPoint
  • neshdenial
    neshdenial Member Posts: 3
    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...
    iedge
  • jsrark
    jsrark Member Posts: 50
    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
    stryk Member Posts: 645
    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 ... ?!
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • David_Singleton
    David_Singleton Member Posts: 5,479
    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.
    David Singleton
  • ara3n
    ara3n Member Posts: 9,258
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • David_Singleton
    David_Singleton Member Posts: 5,479
    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.
    David Singleton

Categories