Post through from Microsoft Dynamics GP Manufacturing

As if Post Through wasn’t hard enough to understand for the core financial and distribution modules (take a look at my previous article Why my transactions don’t post through GL even when I have the flag selected?) here comes Manufacturing. Take a look at these comments from Microsoft’s Becky Berginski:

“Within Microsoft Dynamics GP there is a place to setup your modules posting options. However, there is not an option specifically for Manufacturing. Manufacturing looks at the Inventory and General Ledger options to determine the MFG posting options. If the transactions you enter in Manufacturing post adjustments to Inventory then the system looks at the settings for your Inventory module. (i.e. To determine if it is stops in GL or posts through GL.) If the transactions don’t update inventory (i.e. closing MO), but GL adjustments are made then it looks at your GL settings to determine how it posts.

Go to Tools Setup Posting Posting. Review the settings for both Inventory and General ledger transaction entry options.The posting journals that print after a posting of an MO Receipt are those associated with Inventory Adjustments. You can turn these off (or have them saved to a file) using the Posting Setup window (Tools – Setup – Posting – Posting). Be sure to choose the Inventory series and the Transaction Entry origin. Keep in mind that these settings are now on those reports for regular Inventory posting also and not just the posting of Manufacturing Components.

There are certain labor transactions that will not post through the GL. Data Collection transaction never post Through the GL. You would need to locate the Journal Entry and adjust the Transaction Date. Data Collection entries will have a Source Doc code of ‘DC_ADJ’ and reference to the MO number.”

Becky then goes on to clarify that,

“There are some transactions that will not post to GL. Most of them do. Below is a listing of what does. There are various postings that will come from Manufacturing. Some of these postings go directly to the GL. Some of the postings go through Inventory first and then update the GL. The transactions that go through Inventory utilize the posting setup for that series. The transactions that post directly from manufacturing to GL utilize the posting setup for the financial series. Here are some examples of the various places that one can post from in Manufacturing:

1. If they are using the Quick MO Entry window, postings will occur when they click on the Close MO button.

A. They will have one adjustment posted in Inventory to reduce quantities for the components. This entry will update GL when it finishes posting in IV.

B. They will have a second adjustment posted in Inventory for the receipt of the finished goods. This also will update GL when it finishes posting in IV.

C. If there is labor or machine time set up for the finished good, a journal entry for those costs will post directly to the GL.

D. Also if there are any variances, a journal entry to record those will post directly to the GL.

2. If they are using the basic MO Entry window, postings may occur a number of different times and from different windows:

A. Components can be issued from the Component Transaction Entry window. This posts a decrease adjustment in IV and then GL when the posting completes in IV.

B. Any labor and machine time recorded in any of the WIP windows will post costs directly to the GL.

C. The posting of MO receipts will post an increase adjustment for the finished good in IV and then will update GL. If components are backflushed a decrease adjustment will be posted to IV and GL will be update after the IV posting is complete. Labor and machine costs may post to GL if they are backflushed.

D. When closing the MO, variances will be posted directly to GL. Also additional component quantities may be included–this means a decrease adjustment in IV and a related posting to GL.

One other setting that sometimes prevents users from posting thru GL is in the Security Setup window (Tools – Setup – System – Security). Choose product of Microsoft Dynamics GP, Type of Windows, and Series of Financial. Be sure that the users posting in Mfg have access to the following two windows:

GL Open Files
GL Open Financial Files – internal

As I mentioned transactions stemming from labor or machine data collection will never post through the GL.“

Hope you find this information useful and a good 2010 opening article.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Microsoft Dynamics GP network data encryption

If your organization happens to be PCI-compliant you are already too familiar with the data encryption requirements imposed by the financial institutions you currently deal with.

As part of your certification and compliance process, you probably had to find a way to encrypt customer and credit card data traveling between your organization and the credit card processor. This means you had to enable SSL encryption on your servers all the while obtaining a certificate issued by a certificate authority.

But what about your customer data traveling between your SQL Server and your Microsoft Dynamics GP client, and viceversa.

Here are three methods to encrypt Dynamics GP data traveling over the network:

1) You can enable strong data encryption at the DSN level if using a Microsoft SQL Server Native Client DSN to connect to your Dynamics GP databases on a Microsoft SQL Server 2005 or Microsoft SQL Server 2008 database server. To enable encryption, check the Use Strong Data Encryption option on the 4th dialog page, as shown below.

If using this option, you will want to make sure it’s enabled on all workstations, and provide a centralize way to administrate it, for example, via the Group Policy Data Sources preferences extension.

2) To encrypt data traveling from clients to server, you can use Microsoft SQL Server Configuration Manager to configure the SQL Server Native Client protocols used to communicate to the SQL Server Database Engine.

The Force protocol encryption option will request a connection using SSL.

When Trust Server Certificate is set to No, the client process attempts to validate the server certificate. The client and server must have each have a certificate issues from a public certification authority. If the certificate is not present on the client computer, or if the validation of the certificate fails, the connection is terminated.

When set to Yes, the client does not validate the server certificate, thereby enabling the use of a self-signed certificate.

Trust Server Certificate is only available if Force protocol encryption is set to Yes (See method 3).

3) You can use Microsoft SQL Server Configuration Manager to setup a server side certificate, preferrably issued by a certificate authority. To encrypt connections, you should provision the SQL Server Database Engine with a certificate. If a certificate is not installed, SQL Server will generate a self-signed certificate when the instance is started. This self-signed certificate can be used instead of a certificate from a trusted certificate authority, but it does not provide authentication or non-repudiation.

NOTE: Secure Sockets Layer (SSL) connections encrypted using a self-signed certificate do not provide strong security. They are susceptible to man-in-the-middle attacks. You should not rely on SSL using self-signed certificates in a production environment or on servers that are connected to the Internet.

The login process is always encrypted. When Force Encryption is set to Yes, all client/server communication is encrypted, and clients connecting to the Database Engine must be configured to trust the root authority of the server certificate.

While your organization may not necessarily be seeking PCI-compliance, having strong data encryption for your accounting data is just another way to position your company with customers and vendors.

Related Articles

Encryption Connections to SQL Server – Microsoft SQL Server Books Online.
How to: Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager) – Microsoft SQL Server Books Online.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Securing VBA projects

It seems that nowadays we can add security to pretty much every aspect of the Microsoft Dynamics GP application, but when it comes to VBA projects things are not too clear for many developers and Dynamics GP systems administrators.

Follow these simple steps to secure your VBA project.

1. Open the Visual Basic Editor. Go to Microsoft Dynamics GP > Tools > Customize > Visual Basic Editor, or press ALT+F11 from your keyboard.

2. Open the project’s Properties window. Right-click on the project within Project Explorer and select the project Properties option. You can also access the project Properties window by highlighting the project and using the Tools menu.

3. Click on the Protection tab, then click on the Lock project for viewing checkmark.

4. Enter a password to prevent unauthorized access, then confirm the password entered.

5. Press Ok to apply the settings.

6. Save your VBA project and exit Dynamics GP. When you access the application next time and attempt to access your VBA project you will be prompted to enter a password to display it’s content.

If you have multiple projects, you will need to apply security individually to each by following the above outlined steps. Hopefully you will use the same password across to make things easier

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Microsoft Dynamics GP 10 POWERUSER role vs Microsoft SQL Server sysadmin role

Dynamics GP system administrators and Microsoft SQL Server DBAs often ask, “Why can’t I setup users if I am a member of the POWERUSER role in Dynamics GP?“. The question can sometimes be paraphrased as “Why the SQL Server system administrator (sa) user is the only one that can setup users?“. The answer is simple: the Dynamics GP POWERUSER role is application specific, while the SQL Server sysadmin role is database specific.

Since sa is a member of both the POWERUSER role in Dynamics GP and the sysadmin role in SQL Server, it can perform all maintenance operations of users in Dynamics GP, along with the setup of additional users. This allows the sa user login, in turn, to create the necessary logins in SQL Server. This is good if you are a DBA, but what happens when you are out and more users need to be added to the system.

So you may now be asking, “How do I make other Dynamics GP users have the same abilities to setup users like the sa user?“. You will have to make the Dynamics GP user a member of a role in Dynamics GP with ability to create users — perhaps, the POWERUSER role or the ADMIN_SYSTEM_001* security role — and a member of the sysadmin role in SQL Server. To do this follow these steps (assuming you want the user to have full access to all Dynamics GP options throughout the system):

1. Log into Microsoft Dynamics GP as sa.

2. Assign the Dynamics GP user to the POWERUSER role. Go to Microsoft Dynamics GP > Tools > Setup > System > User Security. Choose the user login and mark the POWERUSER role in the access list.

3. The system will warn about the user access to all application functionality. Click on OK to continue.

NOTE: Depending on your security requirements, you may not want to grant access to the POWERUSER role. You can always create a custom role with access to the User Setup window or use the built-in ADMIN_SYSTEM_001* role.

4. Now, proceed to assign the user login to the sysadmin role in SQL Server. Open Microsoft SQL Server Management Studio, open the Security folder, open the Logins subfolder.

5. Double-click on the corresponding user login to open the Login Properties window. Select the Server Roles page and mark the sysadmin role.

6. Click the OK button to finalize the configuration.

Now your Dynamics GP user should be able to setup new users and maintain existing ones, along with performing other SQL Server maintenance activities within the application.

Related Articles

• The Microsoft Dynamics GP Application Level Security Series. David Musgrave at Developing for Dynamics GP. Click here.
• Microsoft Dynamics GP Password Implementation. Click here.

Until next post!

MG.-
Mariano Gomez, MIS, PMP, MVP, MCP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Support Debugging Tool Redux

Let me start by saying, if you are not using Support Debugging Tool you really are making things more difficult for yourself than you should!

Back in August, I was part of a selected group of beta testers and was introduced to the tool, and have since writen a number of articles about it, including my First Look article, which also got featured in MSDynamicsWorld.com. Back then, I presented some of the coolest features I had had the opportunity to explore in detail. This time, David Musgrave does it again with this revised version. Let me say it again just in case: if you are not using Support Debugging Tool you are truly making things more difficult than they should for yourself.

This build of Support Debugging Tool has been enhance with a number of performance improvements and now introduces two new features I believe will blow you away: Security Profiler and Screenshot capturing.

Security Profiler

Security Profiler is by far the most notorious new feature in Support Debugging Tool. Short of talking, this feature has been enhanced to provide detail information about security issues as they are happening and can be setup to trap [security] errors automatically and instantly when they occur. In addition, users now have the ability to export the security log to an XML document that can be uploaded by administrators for troubleshooting purposes — no more guessing what security roles and security tasks are out of wack!

As the security log is built, Security Profiler identifies the state of the object being accessed, this is, whether the object is original, modified, or alternate modified. This also improves the chance of detecting whether the user is seing the right object to begin with.

* Click on image to enlarge

Once the security log is built to the point of an error condition, the user can then export the log to an XML file that can be used by an administrator to troubleshoot the problem. Once the administrator imports the file, he or she can pretty much replay the sequence of actions leading up to the security error condition.

Now get this!

Through each step, the administrator can right-click – you read correctly! Right-click! — on the object to obtain detail security information. The Security Information window is the brain of the Security Profiler and breaks down the security tasks and roles associated with the current user, and further more, provides information at the system level of all the tasks and roles with access to the object.

* Click on image to enlarge

Long gone are the days trying to figure out what roles and tasks are associated to what objects. You can also double-click on a role or a task to obtain more information. This will open up standard GP security windows.

Screenshot Capturing

Support Debugging Tool adds a new shortcut for those of you who love to find interesting shortcuts in GP, that is CTRL + S. Screenshot Capturing is a new powerful tool included as part of this new build of Support Debugging Tool. It does as the name suggests, with a twist: it allows the user to incorporate in an email valuable troubleshooting information such as a copy of the DEX.INI settings file, the DYNAMICS.SET launch file, and a complete System Summary of the user’s workstation. How is this important? Ever wonder if the user is pointing to the correct REPORTS.DIC or FORMS.DIC dictionary files? Ever wonder as an administrator if the user is missing a customization? Ever wonder if the user machines is missing some GP service pack? All these questions are answered with this feature.

The email is jammed pack with information for administrators and will definately take the guessing game out of troubleshooting. One note though, Support Debugging Tool will not be able to capture screenshot images for modal dialogs and error conditions, these will have to be captured through traditional print screen methods and submitted in addition to, or as additional attachments to your email.

* Click on image to enlarge

Please visit David Musgrave’s blog, Developing for Dynamics GP, for his release notes on this new exiting build of Support Debugging Tool.

Downloads

Support Debugging Tool Build 10 is available from PartnerSource. Please use the following links to obtain a copy or contact your Microsoft Partner for more information.

Support Debugging Tool for Microsoft Great Plains 8.0
Support Debugging Tool for Microsoft Dynamics GP 9.0
Support Debugging Tool for Microsoft Dynamics GP 10.0

Until next post!

MG.-
Mariano Gomez, MIS, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Smartlist Builder Security for SQL Server Views

On a recent Microsoft Dynamics GP newsgroup post, a user created a Smartlist Builder based on a SQL Server view. After granting permissions to the DYNGRP database role, the Smartlist was still not accessible to the end user. After testing a few things, fellow MVP Victoria Yudin was able to resolve the problem by assigning a specific Smarlist Builder security task to the user’s Dynamics GP role.

Recreating the issue

The following steps are needed to recreate the issue.

1) Run the following statement against your SQL Server company database. This simple view will show all contact information on sales documents that have been purchased from suppliers.

CREATE VIEW dbo.vwPurchasedSOPDocuments ASSELECT a.sopnumbe, a.docdate, a.custnmbr, a.custname, a.cntcprsn, c.ponumber, c.vendorid, c.vendname, c.docdate as podate, c.contactFROM SOP10100 a INNER JOIN SOP60100 b on (a.SOPNUMBE = b.SOPNUMBE) and (a.SOPTYPE = b.SOPTYPE)INNER JOIN POP10100 c on b.PONUMBER = c.PONUMBERGO

GRANT SELECT ON dbo.vwPurchasedSOPDocuments TO DYNGRPGO

2) Login to GP as SQL Server’s system administrative user (sa) and open Smartlist Builder SQL Table Security window and grant access to the created object. Choose the Views radio button to display the recently created view. Click OK to continue.

3) Setup a new Smartlist object as follows:

NOTE: Use the SOPNUMBE and PONUMBER fields as keys to the SmartList.

4) Save and open Smartlist to build the newly created object.

5) For this demonstration, grant access to the newly created Smartlist by assigning security to the DEFAULTUSER security task. Go to Microsoft Dynamics GP > Tools > Setup > System > Security Tasks.

6)Switch users. Select a user with DEFAULTUSER security task and log on as that user into GP. For this sample, I will be using LESSONUSER1 in Fabrikam.

7) Open Smartlist and try to launch the Smarlist previously created. The user (LESSONUSER1) will obtain the following error:

While all the above procedures to setup security priviledges to the newly created Smartlist are standard and would seem accurate, there is one more step that was omitted throughout. For an end user to have access to Smartlist Builder objects created from views, it is necessary to grant one more access to the corresponding security task, as follows:

1) Log into Dynamics GP as SQL Server systems administrator (sa).

2) Open the Security Task Setup window and choose the task ID. In the case of this example I selected DEFAULTUSER. Under Product, choose SmartList Builder; under Type, choose SmartList Builder Permissions; and under Series, choose SmartList Builder. Mark View SmartLists with SQL Tables in the Access List pane. Click Save to continue.

3) Log on as the user in question (LESSONUSER1), then attempt to open to launch the newly created SmartList. At this stage, the security issue should have been resolved and the user is now able to view the Smartlist.

Good catch Victoria!

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Microsoft Dynamics GP 10 security series

The following 2 weeks will bring tons of Microsoft Dynamics GP 10 security articles from David Musgrave over at Developing for Dynamics GP. It’s no secret to anyone that the new role-base security has not been without its shared dosis of confusion and frustrations for companies and users upgrading from previous releases and even new comers to Microsoft Dynamics GP. David has published a list of the topics to be covered, as follows:

• How to transfer Security Tasks and Roles between Systems

• How to identify the Security Tasks and Security Roles associated with a specific window or report

• How to identify the Security Tasks and Security Roles using the Support Debugging Tool

• How to resolve Security errors on Login

• How to resolve Security Privilege errors

• How to resolve Dictionary not loaded errors

• Information about v10 Security Conversion Tool

Don’t forget to mark your calendars for Monday, Wednesday, and Friday over the next two weeks to get these important topics.

Update 11/12/2008 – Added link to article “How to identify security tasks and roles…”

Update 11/14/2008 – Added link to article “How to identify security tasks and roles using Support Debugging Tool”

Update 11/17/2008 – Added link to article “How to resolve security errors on login”

Update 11/18/2008 – Added entry to a future article to be released on the GP v10 Security Conversion Tool. This article will (possibly) wrap up David’s work with tons of information for those of you converting from earlier versions of GP to v10 and will help pave the road in deciding whether to convert your existing security or simply start from scratch.

Update 11/18/2008 – Added link to article “How to resolve security priviledge errors”

Update 11/21/2008 – Added link to article “How to resolve dictionary not loaded errors”

Update 11/24/2008 – David completes (at least for now) his security series with this not to miss article on the Dynamics GP v10 Security conversion tool. There has been a number of questions and confusions about what the tool does and does not do and David addresses those as well.

Until next post,

MG.-
Mariano Gomez, MIS, MCP, MVP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Microsoft Dynamics GP Password Implementation

David Musgrave answers the most common questions about Microsoft Dynamics GP user password implementation by exploring a number of topics including, but not limited to the history of passwords in GP, password encryption, password policy enforcement, and Windows authentication. Be sure to check this article, because this is a very frequent topic that comes up in various newsgroups and discussion boards and the answers are as varied depending who you ask.

Until next post!

MG.
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Microsoft Dynamics GP 10.0 Service Pack 2 Login Issue Fixed

Mark has interesting information posted on his blog on a security fix addressed in Microsoft Dynamics GP 10.0 Service Pack 2 (10.00.1061). The original issue had to do with turning on password control with password expiration in GP, which is in turn address by Active Directory. Go on to read Mark’s post on the issue to get the full details.

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Restricting Access to Metrics on Microsoft Dynamics GP 10 Homepage

When it comes to securing metrics on GP 10’s homepage, very little can be found outside of KB articles 918313 and 914898. But a few of us have decided to put this issue to rest — I say a few of us because this ‘how to’ guide could not be possible without the assistance of FlieHigh and Tim Foster of Trudell Medical Limited in London, Ontario, Canada, both regular contributors in the Microsoft Dynamics GP community — by combining our different approaches on the subject.

Lets get started! There are two approaches to lock down the metrics for a particular user in GP.

Method 1: T-SQL with SQL Security

1) Open Query Analyzer (SQL 2000) or SQL Server Management Studio (SQL 2005). Execute the following statement against the DYNAMICS database.

UPDATE SY08100 set Visible = 0 WHERE(DictID = 0) and (SectionID = 3) and (UserID = 'userid')

The following is a list of Section ID values:

1 — To Do’s
2 — Outlook
3 — Metrics
4 — My Reports
5 — Quick Links

2) Open Enterprise Manager (SQL 2000) or SQL Server Management Studio (SQL 2005). Open the Tables folder of the DYNAMICS database and locate the SY08100 (syHomePageLayout) table. Click on Permissions.

3) In Enterprise Manager, hightlight the DYNGRP and click on the Columns button. Double-click on the Visible column under the Update heading to make sure that this option is set to Deny.

In SQL Server Management Studio, highlight DYNGRP in the Permissions window, select Update from the Explicit Permissions window, and click on the Column Permissions… button. Highlight the Visible field and change click on the checkmark under the Deny column

Method 2: SQL Server Trigger

Yes, folks! Thanks to Tim Foster you can now deny access to changing the visibility of the metrics section with a trigger as follows:

USE [DYNAMICS]SET ANSI_NULLS ONGO

SET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Tim Foster-- Create date: 2008-07-04-- Description: Prevent users from adding Metrics content on the Home Page-- =============================================IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[dbo.X_SY8100_Prevent_Metrics]'))   DROP TRIGGER [dbo].[dbo.X_SY8100_Prevent_Metrics]GO

CREATE TRIGGER [dbo.X_SY8100_Prevent_Metrics] ON dbo.SY08100 AFTER INSERT,UPDATEASBEGIN

print 'X_SY8100_Prevent_Metrics trigger active'-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.

SET NOCOUNT ON;

IF USER_NAME()'dbo'BEGIN   DECLARE @SECTIONID INTEGER, @VISIBLE INTEGER

   SELECT @SECTIONID = SECTIONID, @VISIBLE = VISIBLE FROM INSERTED   IF @SECTIONID = 3 AND UPDATE(VISIBLE)   BEGIN      UPDATE dbo.SY08100 SET dbo.SY08100.VISIBLE = 0      FROM dbo.SY08100 INNER JOIN INSERTED ON dbo.SY08100.USERID = INSERTED.USERID        AND dbo.SY08100.SECTIONID = INSERTED.SECTIONID         AND dbo.SY08100.DICTID = INSERTED.DICTID      WHERE dbo.SY08100.USERID = INSERTED.USERID         AND dbo.SY08100.SECTIONID = INSERTED.SECTIONID         AND dbo.SY08100.DICTID = INSERTED.DICTID

      PRINT 'You are not allowed to turn that content on'   ENDEND

PRINT 'X_SY8100_Prevent_Metrics trigger Complete'ENDGO

Maintaining metrics and other homepage elements safe is key to information security. Hope these two approaches ease the pain of dealing with metrics.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/