NAV/SQL Performance - My Two Cents

Jörg Stryk (MVP - Dynamics NAV) about Performance Optimization & Troubleshooting with MS Dynamics NAV and MS SQL Server

I really have a deep conflict here … we have recently started a "Database Security" project where we are developing solutions to fix certain NAV issues … so yes, this means there are "security issues", and talking about potential solutions is not possible without describing the problem … we have the proverb "You should not wake up sleeping dogs", especially not the mean, filthy ones, having no good in mind … you see my problem?

Anyway, I think it is important to increase the awareness of these security risks, so I'd like to share my thoughts & experiences (again, we are in progress, nothing finalized, yet).
Well, due to the poll and some comments to this forum posting I decided to publish …

First of all it is important to mention the "Security Hardening Guide" (available on "Partner Source") which all administrators should know as it gives a good start into this matter.

 

Database Security Architecture

In NAV the DB Security is somewhat complex. Assuming the NAV "Security Model" STANDARD (recommended). All a "normal" User (no admin or developer) needs to logon to the system is this:

  • SQL Server Login; Server-Role "public"
  • SQL Database User (SQL or Windows Login); Database-Role "public"
  • NAV Login (DB or Windows); NAV-Roles

When the NAV Client logs on to the SQL Server, it firstly reads some NAV system tables ($ndo$serverproperty, $ndo$dbproperty, etc.) to fetch some parameters, for example the C/SIDE Client learns from $ndo$dbproperty which "Secuirity Model" (e.g. Standard) is used. To accomplish this the role "public" is sufficient enough.
Then the Client is signing in using a NAV "Application Role" – with "Standard" security this is $ndo$shadow, with "Enhanced" security each Login has its own AR called $ndo$ar${GUID}. Once the AR has taken over, only this AR is in charge: $ndo$shadow" is using the "Extended Stored Procedure" xp_ndo_enumusersids to transform the NAV defined user-rights into SQL site permissions.

[ With $ndo$shadow this happens "on-the-fly", while with $ndo$ar{GUID} the permissions are fixed, assigned by xp_ndo_enumusergroups when "Synchronizing" NAV's security system ]

Hence, once the AR has taken over the roles and permissions as defined within NAV are in place. But this applies only to the NAV Client – the application! Roles etc. within NAV are off topic here ..

… the problem is the security context outside the application!

 

Security Problem

Every user who has a SQL Server Login and SQL Database User defined could log on to the Server – now not using NAV, but any other Programm which is capable to connect! For example, this could be MS Excel, or any ODBC connection, or any SQL Client (also available for free in the internet).

In such a case, the only role "in charge" is "public" – the Application Role (e.g. $ndo$shadow) is bypassed. Hence, all NAV site roles & permissions are void.

"Public" actually should just provide several minimum permissions, similar to the NAV standard role "ALL" – thus, actually no one could do any harm with it. The strange thing is (remember, we are still investigating) that in some systems "public" is working OK, means, no one could read or write data from/to the NAV database with just this role. But in some cases a "public" user could access the data – read and write!!!

Reason is – in those cases we checked – that for the database role "public" INSERT, DELETE, UPDATE and SELECT permissions were granted. Yet, we have no idea where this is actually coming from, I guess it is simply because some administrators are fiddling with the SQL site security system without really knowing what they are doing here … Just think about how often you've read or heard about admins who assigned all user to "db_owner" or "sysadmin" roles, just as a stupid workaround for some "user right issue" (mostly using "Views"/"Linked Objects") and not knowing better!? Or simply to face standard NAV security issues (e.g. failed user synchronization in NAV with "Enhanced" security)!? We currently dig into that … (I'd appreciate any hint!)

Of course, the easiest way of "hardening" the "public" database-role is to correct this, by simply denying those rights:

USE [NAV] -- change db name
GO
REVOKE DELETE TO [public]
GO
REVOKE INSERT TO [public]
GO
REVOKE SELECT TO [public]
GO
REVOKE UPDATE TO [public]
GO

But as we don't know the reasons for all this (who knows if/when the problem is coming back?), we look into other "hardening" options, regardless of the "public" rights.

After all, in way too many cases "public" grants too many rights. For example, this means, that a user who is inside NAV not allowed to read/write from/to the "G/L Entry" table (I hope you agree with me that this in one of the most important tables at all), by simply logging on with SQLCMD (a command-line tool) he/she could do anything (SELECT, INSERT, UPDATE, etc.).

I dare to say this is "Hell on Earth" – a very, very, very high security risk!

And that's just with those enhanced "public" rights – imagine what a "db_owner" or "sysadmin" could do …

[
Remark regarding the forum discussion:
You see, it's not a bug (or is it?) or a new thing I'm talking about, so I think it's not really something to address to MS, because from a "standard" perspective it should work fine. In my opinion it's another issue of "awareness" …
]

To find out if a security issue exists, one could proceed like this:

-- 1. Create "public" Login/User for test
USE [master]
GO
CREATE LOGIN [public_test] WITH PASSWORD=N'test123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [NAV] -- change db name
GO
CREATE USER [public_test] FOR LOGIN [public_test]
GO

-- 2. Logon ("New Query") to SSMS as "public_test" to the NAV database; then execute the following TSQL:

-- 3. Determine permissions
-- User Permissions
SELECT * FROM fn_my_permissions('public_test', 'USER');
GO
-- >> expected: IMPERSONATE, VIEW DEFINITION, ALTER, CONTROL 

-- Server Permissions
SELECT * FROM fn_my_permissions(NULL, 'SERVER');
GO
-- >> expected: CONNECT SQL, VIEW ANY DATABASE

-- Database Permissions
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
GO
-- >> expected: CONNECT

-- Object permission
SELECT * FROM fn_my_permissions('Objekt', 'OBJECT'); -- NAV Object table; change on demand
GO
-- >> expected: nothing

 /* If the expected results are not shown, e.g. if more rights are granted, then a security/permission issue might exist! */

-- 4. Clean Up
-- Disconnect "public_test" to remove test login
USE [NAV] -- change db name
GO

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'public_test')
  DROP USER [public_test]
GO
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'public_test')
  DROP LOGIN [public_test]
GO

 

Database Hardening

Assumptions:

  1. No one writes any data into NAV outside the NAV application. Write permissions are completely denied.
  2. Read permission is restricted to a necessary minimum.

Denying Write-Permission:

SQL Server provides the database role "db_denydatawriter" – this role completely prohibits any writing (INSERT, UPDATE, DELETE) to the database. This DB-Role has to be assigned to all relevant users:

USE [NAV] -- change DB-Name
GO

DECLARE dbuser_cur CURSOR FAST_FORWARD FOR
  SELECT [name] FROM sys.database_principals
  WHERE [type] IN ('S', 'U')
  AND [name] NOT IN ('dbo', 'INFORMATION_SCHEMA', 'sys') -- Exclusions
  ORDER BY [name]
OPEN dbuser_cur
FETCH NEXT FROM dbuser_cur INTO @dbuser
WHILE @@FETCH_STATUS = 0 BEGIN

  -- db_denydatawriter
  SET @statement =
    'EXEC sp_addrolemember @rolename = ''db_denydatawriter'', @membername = ''' + @dbuser + ''''
  PRINT @statement
  EXEC (@statement)

  FETCH NEXT FROM dbuser_cur INTO @dbuser
END
CLOSE dbuser_cur
DEALLOCATE dbuser_cur
GO

Denying Read-Permission:

Well, there is also the DB role "db_denydatareader", but this role completely prohibits reading any data. If this one would be assigned to a user he/she could not logon to NAV - using the application - anymore (remember: $ndo$dbproperty & Co.). Thus, "db_denydatareader" is a little bit too tough, hence we have to create our own restrictive role; e.g. "db_restrictdatareader":

USE [NAV] -- change DB-Name
GO

-- Create new DB-Role "db_restrictdatareader"
IF NOT EXISTS(SELECT TOP 1 NULL FROM sys.database_principals WHERE [type] = 'R' AND [name] = 'db_restrictdatareader')
 
CREATE ROLE db_restrictdatareader
GO

This role should deny read (SELECT) permission on all table except those which are required for the NAV logon process:

USE [NAV] -- change DB-Name
GO

DECLARE @dbuser VARCHAR(128), @object VARCHAR(128), @statement VARCHAR(500)

-- Update rights for role "db_restrictdatareader"
-- DENY
DECLARE obj_cur CURSOR FAST_FORWARD FOR
  SELECT [name] FROM sys.objects
  WHERE [type] IN ('U', 'V')
  AND [name] NOT IN ('$ndo$dbconfig', '$ndo$dbproperty') -- Exclusions
  ORDER BY [name]
OPEN obj_cur
FETCH NEXT FROM obj_cur INTO @object
WHILE @@FETCH_STATUS = 0 BEGIN
 
SET @statement = 'DENY SELECT ON [' + @object + '] TO db_restrictdatareader'
 
PRINT @statement
 
EXEC (@statement)
 
FETCH NEXT FROM obj_cur INTO @object
END
CLOSE obj_cur
DEALLOCATE obj_cur

-- GRANT
DECLARE obj_cur CURSOR FAST_FORWARD FOR
  SELECT [name] FROM sys.objects
  WHERE [type] IN ('U', 'V')
  AND [name] IN ('$ndo$dbconfig', '$ndo$dbproperty') -- Inclusions
  ORDER BY [name]
OPEN obj_cur
FETCH NEXT FROM obj_cur INTO @object
WHILE @@FETCH_STATUS = 0 BEGIN
 
SET @statement = 'GRANT SELECT ON [' + @object + '] TO db_restrictdatareader'
 
PRINT @statement
 
EXEC (@statement)
 
FETCH NEXT FROM obj_cur INTO @object
END
CLOSE obj_cur
DEALLOCATE obj_cur
GO

Now this role could also be assigned to the users:

USE [NAV] -- change DB-Name
GO

-- Assign DB-Roles to DB-Users
DECLARE dbuser_cur CURSOR FAST_FORWARD FOR
 
SELECT [name] FROM sys.database_principals
 
WHERE [type] IN ('S', 'U')
 
AND [name] NOT IN ('dbo', 'INFORMATION_SCHEMA', 'sys') -- Exclusions
 
ORDER BY [name]
OPEN dbuser_cur
FETCH NEXT FROM dbuser_cur INTO @dbuser
WHILE @@FETCH_STATUS = 0 BEGIN
 
-- db_restrictdatareader
 
SET @statement =
   
'EXEC sp_addrolemember @rolename = ''db_restrictdatareader'', @membername = ''' + @dbuser + ''''
 
PRINT @statement
 
EXEC (@statement)
 
FETCH NEXT FROM dbuser_cur INTO @dbuser
END
CLOSE dbuser_cur
DEALLOCATE dbuser_cur
GO

Maintaining the Roles:

So far so good. From now on, no user could neither write to the database nor read data from it (except the $ndo$-things) without the NAV application. "Unfortunately" tables and/or user are added to a NAV system, so we need to take care of periodically updating the "db_restrictdatareader" role and the role assignments. This could be accomplished by encapsulating the scripts above into a "Stored Procedure" (ideally encrypted.
This SP e.g. could run daily to add new tables to the custom db-role and to update the user-assignments. The source code of the TSQL and SP (incl. real-life "inclusions" and "exclusion") should be considered confidential and only to be disclosed to trustworthy administrators, of course.

 

Security Monitoring

According to "hardening" a database it is also necessary to monitor the system, checking for potential security violations.

Generally, the SQL Server Instance should be configured (SSMS – Instance-Properties – Security) to log Unsuccessful Login Attempts at least. Frequent attempts to logon could indicate that someone is trying to breach security, so further investigation is necessary. SQL Server will log these login events into its ERRORLOG.

Also, SQL Profiler could be used to monitor "Audit: Login", "Audit: Failed Login" and "Audit: Logout" events.
// Added 17.02.2010:
See also using the "C2 Audit Mode", which could be enabled by sp_configure. Caution: this could record a LOT of data! See also this article.

Whenever a login fails, an SQL Server error is raised. This error uses the "Severity Level" 014 = "Insufficient Permission". These internal errors could also be used for monitoring (actually that's my favorite):

We could create a "SQL Alert" reacting on these events. Whenever this "Alert" is triggered, an administrator could be notified, e.g. using "Database Mail":

-- Create Alert for Operator-Notification
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'SSI: Permission Failure', 
    @message_id=0, 
    @severity=14, -- Insufficient Permission 
    @enabled=1, 
    @delay_between_responses=0, 
    @include_event_description_in=1
GO
EXEC msdb.dbo.sp_add_notification 
    @alert_name=N'SSI: Permission Failure', 
    @operator_name=N'Administrator', -- change operator 
    @notification_method = 1
GO

Now all failed login attempts would trigger the "Alert" which would notify an administrator. The same "Alert" could be also used to report "insufficient permissions" when data-access failed! In this case certain "Message IDs" have to be flagged to write a log entry; first of all ID 229:

-- Enable logging of permission failures
USE [master]
GO
EXEC sp_altermessage @message_id = 229,
 
@parameter = 'WITH_LOG',
 
@parameter_value = 'true'
GO

To see other messages of severity 014:

SELECT * FROM sys.messages WHERE [severity] = 14

 

So far … so good … , that's what we have developed so far ... we can detect security issues, fix them (?), implement advanced hardening and monitoring ... Currently we are running several "lab-tests", but we still need some practice expereinces ...
Changes/additions to this BLOG might follow. I would really appreciate if you could share your experiences or solutions you have developed. Any comments are most welcome.

 

  • Added "C2 Audit Mode" (very brief)

  • Yeah, I just mentioned briefly that it's not about a bug or new issue - the problem is as old as NAV on SQL, I think the real problem is, that many admins are (were ;c) ?) not aware of this issue or - worse - are ignoring it ... Just today I replied to a forum thread (MSDynamics.de) of someone who assigned db_owner ...

  • As usual an excellent blog entry. When you put it to us in the forum you didn't tell us what the issue was. Having access to the NAV database from outside apps, even with just the public role, has been an issue for years.

    Although I am a bit concerned that this is going to allow people to take advantage of these issues, it's good that this knowledge is made public. Perhaps there will be enough pressure to finally resolve it. Thank you for sharing, keep up the good work :).