SQL Server Security - SQL Injection

For the past year I've been working on security policies and procedures. One of the most challenging part of security is knowing which technology and best practice to use. In my case, I tried all avenues including books, forums,etc. and asked relevant questions then worked myself from top to bottom, meaning from Policies to the procedures, then formulated a plan which included securing the SQL Server database.

One of more common security threat to SQL database is SQL Injection. This is a hacking method that allows an unauthorized attacker to access a database server then executes SQL queries. Once the attacker gains access to the database, he/she is then free to export, add, modify, or delete content from the database. If the hacker is very knowledgeable of other hacking tools, he can penetrate past the database server and into the operating system which could harm the whole system.

To secure your company data against SQL Injection attacks, administrators should implement the principle of least privileged access, granting users rights to part of the applications required for them to their daily work. If their sole purpose is to extract user information, limit permissions to a SQL account used by them to SELECT on target database objects. Another approach to limiting access rights is implementing application roles.

Unfortunately, Microsoft Dynamics Great Plains don't use application roles but uses Database Roles. One of the pain in Great Plains database design is the DYNGRP database role. Here's a classic example:

A company needs to create a new user for an external auditor who wants to access Great Plains, run Reports and do Inquiries. The best way to do this was to create the user in Great Plains, go to SQL Enterprise Manager-Security then open the user's Properties. When you go to the Database Access tab, you can mark db_datareader then the user can only do SELECT statements on the database BUT DYNGRP premissions override the db_datareader option. Being a member of DYNGRP gives users access to run all of the SQL commands and they need to be in that group to even be able to log into Great Plains.

The solution from Great Plains about this security flaw was either to modify the field level security on each window and remove access to the save button OR just give them access to Inquiry and Reports window only.

How about SQL Injection on web-applications? The solution is to build web applications and consider security during design, not post-release. Analyze the resulting error messages from malformed sql statements, ensure that you make them as generic as possible. Download the latest patches and use Microsoft Baseline Security Analyzer(MBSA).

GP Product Report III

PROBLEM REPORT:
The bug ID or problem report number is 18409. Title is 'The payment number is not reserved in Receivables Management' message. This applies to all versions of Great Plains eEnterprise including 9.0.

COMMENTS:
When you create a sales invoice from an order with Credit Card, Check or Cash payment and try to post the document, you'll receive an error in the sales edit list - 'The payment number is not reserved in Receivables Management'.

MBS would send you a fix by deleting & recreating RM00401 but it only worked once in my case. The solution would be to:
1) Remove the sales payment on the invoice then Save
2) Enter the same sales payment on the invoice then Save
3) Delete the General Ledger entries because when the sales payment was entered the first time the entries were also posted. If you post the sales payment distribution entries, this would create a duplicate entry

NOTE: If you're trying to remove a sales payment on a previous year, make sure you open the Financial & Sales module on that month/year.

Microsoft SQL Server 2005 System Table Map

You can find the poster of the SQL Server 2005 System Table Map in the December 2005 issue of SQL Server Magazine.

Here is the online version.

Microsoft Dynamics GP 9.0

I recently attended a user-group meeting hosted by our vendor and they showed us v9.0. I would say there's not much to expect since 8.0 in terms of functionality. Most of the investment were put to technology and I'm very excited with this version. I like the role-based home pages because it's customised or personalized. There are 21 templates available from Accounting/HR/Production/Purchasing/Operations/IT Managers to Dispatcher, Bookkeeper and even Order processor. Other features I'm looking forward includes Metrics in the home pages, KPIs, SQL Reporting Services and SQL 2005 Password Policies. For detailed information on the features I just mentioned, please open the link below and this would open the Microsoft Dynamics GP 9.0 homepage then to the Feature of the Day pages:

https://mbs.microsoft.com/customersource/productsservices/
products/articles/GP90_FeatureofDay.htm

Great Plains ver7.5 Bug Report II

Let me rephrase the title because GP don't use bug, instead I'll use Great Plains ver7.5 Product Report II.

In Smartlist, sometimes a user can accidentally open an object by mistake. In this case instead of using Receivables Transactions, he/she opens Receiving Transactions. If this happen, you'll get this message:

FP: Null Field Address Form: - XX Wind:XXX Fld:XXXX

When you close the message, the Unhandled script exception error opens. Now you can't get out of the error mesaage and you're stuck. The next thing the user needs to do is contact the Great Plains administrator to clear your userid in the database or use User Activity to delete your session.

I've reported this to GP and the PR code is 42126 which they said doesn't appear that it's slated to be addressed in the coming releases.

If you experience the same error on other objects, please go to CustomerSource and report the incident. The more PR code created of the same nature, will move the PR priority even though this isn't a critical error to resolve in the first place.

Tek-tips Question I

I'm going to create a series for Tek-tips questions from Great Plains users. This maybe a simple question or complex question but would be handy for new and old users of the system.

The first would look at Drop-ship sales orders. Here's the user question:

"We are having a problem with the invoicing distributions when we post a drop ship order. We have a mysterious entry showing up at the end that is not visible in the original distribution screen. It is type for inv and cogs. Does anyone one know where it is coming from?"

ANSWER:
Inventory and Cost of Goods Sold are created during invoice posting and should be coming from the sale line item distribution accounts. This is by design because the cost of the items are not known until the invoice is posted.

If the sales line item distribution is blank you can manually enter accounts, some companies do this whenever a non-inventoried item was entered.

If this is an inventoried item, the accounts should be coming from the item card. If the Drop Ship items account is blank on the item card the system would look at the posting setup for inventory (Setup>Posting>Posting Setup>Display:Inventory) then Drop Ship Items.

Microsoft Dynamics Brand

I've been reading a lot about the new branding that Microsoft did to its MBS products and I thought why not give my own opinions about it.

Back to old times I would say.

Yesterday, it was Great Plains Dynamics Ctree or Btrieve and Great Plains eEnterprise.

Today, we have Microsoft Business Solutions–Axapta, Microsoft Business Solutions–Great Plains, Microsoft Business Solutions–Navision and Microsoft Business Solutions–Solomon and Microsoft CRM.

Tomorrow, it's going to be Microsoft Dynamics CRM, Microsoft Dynamics GP, Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics SL.

Do you see any resemblance?