SQL Error when Importing FOB file.

I frequently get support calls from customers that get an error message similar to the following when trying to import a FOB that includes changes to tables:

The following SQL Server error(s) occurred while accessing the TestTable table: 1088,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the object "Cronus New Zealand Ltd_$TestTable" because it does not exist or you do not have permissions.

SQL:
ALTER TABLE "DATABASENAME"."dbo"." Cronus New Zealand Ltd_$TestTable" ALTER COLUMN "Message" VARCHAR(240) NOT NULL

The solution is simple and can be found in the standard documentation. In order to be able to make changes to table definitions, you need to be a member of the sysadmin server role or be a member of the db_owner database role for this database.

You may need to ask your SQL database administrator to grant these privileges to your user account and it is likely they will grant you the lower db_owner privileges rather than sysadmin and still grumble about it.

OK problem solved. Everyone’s happy, but…

If you’re anything like me, you may wonder why this is the case. Why do you need to be granted special rights by a database administrator for this task when all other rights are granted to you from within the NAV application? To be able to answer this question we need to understand a little bit about how NAV users access the database.

I first came across this issue when trying to access the NAV SQL database from other applications (such as Excel or Reporting Services) and I soon discovered that my NAV login did not allow me access to the data contained in the NAV database. New users added to Dynamics NAV through the NAV application will have no permissions on the database (well actually the user has been granted the public database role which means they have permission to connect to the database but not to do anything else.)

Incidentally, when you create a Windows login (as opposed to a Database Login) from the Dynamics NAV client, the system will create the SQL Login and map the user to the database with the public role. If you are trying to use a Database login, you must create the SQL Login first through SQL Server Management Studio.

So the question remains: how can we read the data from the tables when we have no rights to read the data? The magic happens through something called an “Application Server Role”. If you look under Security for the database you are using in SQL Server Management Studio, you will see groups for Users, Roles, Schemas, Asymmetric Keys, Certificates and Symmetric Keys. Open the Roles and then expand the Application Roles and you will one called $ndo$shadow. This role has all the permissions needed to read from every table in the NAV database.

An application server role is used to solve exactly the problem we are investigating, that is, how can you give users full access to a SQL database through your application (in this case NAV) but not let them do anything when they try and use Excel to query the data? Since security is controlled by NAV, when the user tries to access the data directly they are by-passing the permissions that have been given to them by the NAV security administrator.

The $ndo$shadow application server role is created by the application and is given a password that is too big for users to remember even if they knew how to find it. When the user runs the application, one of the first things that NAV does is run a stored procedure to set the application server role for the current session using this big password. The SQL documentation tells us that this will allow the user to use the rights allocated to the application server role for the current session until the session is terminated.

In a mysterious move, Microsoft changed this security model in version 4.0 and introduced a whole bunch of $ndo$shadow application server roles with a funny GUID thing on the end. It looks like these new roles were used for each user instead of having one for all users. I am a bit unsure as to how these roles worked since they didn’t actually work very well and I soon stopped using them. Any changes to security needed to be synchronized to these roles and many users complained of the system locking up for long periods of time. In 4.0 SP3 (I think) Microsoft introduced the ability to switch this new security mode off and revert to the good old single $ndo$shadow role for everyone. Interestingly the option allows you to select your security as being either “Enhanced” or “Standard”. I think they would have been better off calling these options “Rubbish” and “The one that works”.

So now we know how we get access to the data when our user account doesn’t have any rights, but the question of why we need dbo rights on a user account (that is not being used) to change tables is still unanswered.

I spent a few hours trying to figure this out using SQL Profiler and stepping through various scenarios. After a long frustrating session, I gave up and went to bed. Then I figured it out. Before NAV changes to use the $ndo$shadow application server role, it first checks to see if your user is a dbo and if you are a dbo it doesn’t switch to the application server role. Simple really – but worth noting that if you are a dbo, you are using your own credentials and permissions and not those of the application server role so adding permissions to the $ndo$shadow role will have no effect.