Land of the Landed Costs- Part 2

So, here I am with part two of the landed cost saga finally. Last time, we went through the process of setting up landed costs. In our example, we were setting up insurance costs so that the invoice from the insurance vendor could be matched back to the shipments received from the product vendor. But, before we get down the road that far, let's overview the three key ways that landed costs can be used.

Landed Costs by line item

• Specify a landed cost group on a line item when recieved (can default from the item setup, from the purchase order, or manually entered)
• Calculated amounts can be adjusted by line item on receipt

Landed Costs by apportionment

• Specify an individual landed cost for the entire receipt document
• Apportion the amount across the items on the receipt by qty, extended cost, or weight
• Landed cost must use flat amount cost method

Landed Costs by invoice match

• Landed cost group specified by line item on receipt (default from item setup, or from the purchase order, or manually entered)
• Does not have to calculate an estimate amount on receipt, but it can.
• Individual landed cost must be set up for Invoice Match, and for Revalue for Cost Variance if applicable
• Use Enter/Match Invoices to enter an invoice from the landed cost vendor, and match it to the shipment from the product vendor

So, let's take a look at each of these options. In Landed Costs by line item, we can specify a Landed Cost Group on the Purchase Order line item.

Transactions>>Purchasing>>Purchase Order Entry, click on a line item, and then click the expansion arrow to the right of the Item header.

Remember, this value defaults from the item/site combination in Cards>>Inventory>>Quantities/Sites. However, it can be changed here to provide a different default to the receipt.

So, when we get to Transactions>>Purchasing>>Receivings Transaction Entry, the landed cost group will appear on the line item once again. You can view it in the same way as you did on the purchase order, by clicking on the line item and then clicking on the Item header expansion arrow. However, you may find it more helpful to view the actual landed costs being calculated for the line item, based on the landed cost group assigned. This can be done by clicking on the line item, and then clicking on the expansion arrow to the right of the Unit Cost header.

You can override the amounts specified here. The percentage or amount fields will be available based on the calculation method you defined for the landed cost.

Also, on the receipt, we can do Landed Costs by Apportionment. To do this, we simply click on the Landed Costs button at the bottom of the Receivings Transaction Entry window to open the Receivings Landed Cost Apportionment window.

In this window, you can select Landed Costs to apportion across the entire receipt. You must select a landed cost that is setup with a flat amount calculation method in order to select quantity, value, or weight in the "apportion by" field.

The system calculates each method as follows:

• Quantity: (Line item's quantity shipped - the quantity rejected)/(Sum of all line items' quantity shipped- the quantity rejected)
• Value: [(Line item's quantity shipped -the quantity rejected)*Originating Unit Cost]/Sum of all line items [(quantity shipped -the quantity rejected)*Originating Unit Cost]
• Weight: (Line item's extended shipping weight)/(Sum of all line item's extended shipping weight)

In either case, Landed Cost by Apportionment or Landed Cost by Item, the distributions that result are the same:

• Debit to Inventory
• Credit to Accrued Purchases for Landed Cost (per Landed Cost Maintenance)

So, that leaves us with the last method of Landed Cost by Invoice Match. In this example, let's assume that we posted the estimated landed cost of 10% of extended for the INSCARRIER landed cost on the shipment receipt. Now, we have received an invoice from the actual insurance carrier, Associated Insurance, and find that the costs were actually much greater. Since we set up the INSCARRIER landed cost for invoice match, we can now record the invoice from Associated Insurance and match it back to the shipment from Advanced Office Systems.

To do this, we go to Transactions>>Purchasing>>Enter/Match Invoice and enter an invoice for Associated Insurance (NOT Advanced Office Systems):

There are just a few key differences in how you enter the landed cost invoice:

• Vendor ID is the landed cost vendor
• Mark the "LC" checkbox for the line item to identify it as a landed cost
• Select the landed cost to match for the item (rather than an actual item)
• Match the landed cost to the original shipment from the product vendor using the Matched to Shipment expansion button

The distributions that result depend on whether you have selected to Revalue Inventory for Cost Variance:

If you are revaluing:

• Debit to Inventory (if cost is greater than receipt)
• Debit to Accrued Purchases for Landed Cost
• Credit to Accounts Payable

If you are not revaluing:

• Debit to Purchase Price Variance for Landed Cost (if cost is greater than receipt)
• Debit to Accrued Purchases for Landed Cost
• Credit to Accounts Payable

So, I hope this helps clarify the different ways to approach landed cost in Dynamics GP. I find that some clients will use all three methods, but many also settle on one or two ways that work best for their goals. Please share your experiences, questions, etc.

Dynamics GP Backups. They aren't that difficult. Really. Really!!

Over the last few weeks, I've worked with three clients that did not have backups of their Dynamics GP databases, let alone a backup of their modified dictionaries and customizations. This is such a common situation that whenever I work on a client's Dynamics GP system for the first time, I try and always check their backups before I begin any work. Unfortunately, when I check, I often find that they have issues with their backups. Even when a client thinks that they have backups, the backup job often isn't working properly, or there is some issue that would, or does, prevent them from recovering their data when a problem occurs.

Backups aren't difficult. Really, they aren't. There are different approaches and techniques that can make certain types of backups technically complex, but for Dynamics GP, backups don't have to be difficult or complex. In general, I prefer to keep things simple, and backups are no exception. The last thing you want during a data loss crisis or system outage is to have to deal with complexity and the increased potential for breakdowns (both human and technical) that complexity can cause.

I can't cover every detail, so I'm just going to try and cover the basics and offer my thoughts on a few areas based on what I've observed over the years. I welcome comments to fill in any details or offer any other helpful ideas for GP backups. While we're at it, I also welcome any failed backup horror stories that can serve as a lesson of what not to do (I've got plenty to share!).


First, let's talk about the elements of a basic Dynamics GP backup strategy.

1) SQL Server database backups: Make sure to backup your Dynamics database and all of your production company databases. Always make sure to include the Dynamics database as part of your company database backups, since it can contain very valuable information, such as Business Portal data, custom SmartLists created using SmartList Builder, Analytical Accounting setup tables, and user access and GP security settings.

2) Dynamics GP application backups: At a minimum, make sure to backup all of your modified forms and reports dictionaries, the Dynamics.set file, VBA files, and any files installed in the AddIns and Data folders. An easy way to do this is to backup the entire Dynamics GP application directory--it's easy, fast, and often more reliable or less error prone than selecting individual files. If you are using shared dictionaries that are stored in a different location, make sure to backup those files as well.

3) SQL Server security settings backup: If your SQL server fails and you need to migrate to a new SQL server, it can be very helpful to have a backup of your SQL logins and users. The security settings can be scripted, allowing you to quickly recreate a large number of SQL logins. If you only have a few GP users, this step may not be worthwhile, but if you have more than 20 users, I would recommend running the script occasionally to save time in case you do lose your SQL Server.


With those items in mind, let's discuss when and how they should be backed up.

1) SQL Server databases: Ideally, the Dynamics and production GP company databases should be set to use Full recovery model, allowing for point in time recovery. Under the Full recovery model, the production databases should typically have a nightly full backup, and several transaction log backups should run throughout the day, typically every 1-2 hours during business hours. The frequency of the transaction log backups will reflect how much data you are willing to lose in case of a serious issue with the SQL Server. So if your transaction log backups run every 2 hours, you could possibly lose up to 2 hours worth of data entry or posting. If you are willing or able to tolerate the loss of an entire day's worth of data, you could do only nightly full backups and no transaction log backups, or you could switch to Simple recovery model with nightly backups. Using the SQL Server Maintenance Plan Wizard, it is very easy to setup a scheduled backup job that will backup your databases and transaction logs--it takes less than 60 seconds to setup and test a basic backup plan.

A few recommendations: If you use the Maintenance Plan Wizard, make sure that you create and enable a schedule. I recently saw a backup job at a client that had been setup over a year ago, but was never scheduled and therefore never ran, so the client didn't have a single backup. Also, I would strongly recommend NOT appending your backups to a single backup file. The file will quickly become very large and too cumbersome to archive--I just saw another client with a 150GB backup file containing 5 months of database backups. Finally, I personally recommend using a separate directory for each database--it helps to organize the files and makes specific backups easier to find if you have multiple GP companies setup.

When you setup your nightly backup job and transaction log backups, you should save the backup files to a separate physical disk than your database files. If your data drives fail, you don't want your backups to be on those drives as well.

Speaking of drives, I strongly recommend that you perform nightly standard SQL Server backups to disk first, and keep at least 3-5 days of backups on disk so that they are easily accessible. You can then backup those SQL backup files to tape or an archive disk for longer term storage. And I also strongly recommend that an organization keep several months of monthly backups (at least 3-6), since I have had several situations where the client needed to recover something that was older than the company's 2 week or monthly backup rotation.

I personally do not ever recommend using backup software to backup SQL databases directly to tape. It theoretically works, but when you need to urgently recover your GP databases, the last thing you want to do is have to rely on tapes and 3rd party backup software as your only backup source. I've been there several times, and it wasn't fun. Disk space is now so cheap that the traditional arguments for backing up SQL databases directly to tape no longer apply (at least for Dynamics GP). I see that 1 terabyte hard drives are now as low as $80, so the cost of storage is no longer a concern. Tape is fine as one element of an overall backup strategy, but I strongly recommend that you always have some SQL backup files that are accessible on disk.

2) Dynamics GP application: My personal favorite is to just manually use WinZip or WinRAR to zip the entire Dynamics GP application directory to a single file. Using a naming convention like "YYYY-MM-DD Dynamics GP Backup.zip", it's easy to manage the files and archive them. The resulting compressed file is usually less than 500MB, and is very convenient to use for a selective file restore, or a restore of an entire GP install. This backup doesn't have to be performed very often--typically only after a customization is added, a new GP module or 3rd party product is added, or GP is upgraded as part of a service pack or hotfix. I admit that it is very easy to forget to perform this backup after all of those events, so it may be a good idea to just schedule a monthly backup.

3) SQL security settings: Steps 1 and 2 of Dynamics KB Article 878449 provide instructions for using the "Capture_Logins.sql" script to backup all SQL Server logins and password information. If you have alot of GP users, this backup can be very helpful and can save alot of time for a server restore, migration, or simply setting up a new test or QA server. If you ever have to urgently recover your GP environment to a new SQL Server, having the logins scripted can reduce your stress and make a recovery much smoother. Like the Dynamics GP application backups, this step does not need to be performed very often. As long as you have the majority of your users in the script, you can easily add a few new users if necessary.


Once you have all of these backups setup and running successfully, make sure to have an on-site and off-site archiving strategy. You could buy two 1TB external drives and rotate them weekly to a relatively safe, relatively secure, off-site location. It's easy to find very real news stories about floods (Fargo), hurricanes (Texas, Louisiana, Florida, etc.), and my favorite Los Angeles recreational sport, earthquakes, to know that your building, server, and data are not immune from being completely destroyed. Online backup services have become so inexpensive and convenient that they are also an option for certain types and amounts of data. One caveat, however, is that if you are using the Dynamics GP Human Resources or Payroll modules, you will want to be especially careful with your backups. States such as California have laws that govern the storage and loss of private data, and common courtesy dictates that you care for SSNs and employee info, so if you do have sensitive data, you may want to consider encryption options for your off site backups.


Now that I've described what needs to be backed up, and how it should be backed up, I'll very simply describe the most important step of all.

TEST YOUR BACKUP PLAN USING A RECOVERY PLAN!

For most IT departments, this step is right up there with dieting, going to the gym, taking the daily vitamin, and flossing twice a day. Everyone knows that they should do it, but nobody ever actually does it diligently. But a recovery plan is actually easier than all of those other good practices, since you don't have to do it every day. A few times a year would be fantastic compared to most companies.

Here's how you can test your backups from scratch in less than two hours:

1) Install a new instance of SQL Server on a Windows server machine (it can be desktop with Windows Server 2003)
2) Install Dynamics GP on the machine
3) Unzip your GP application directory backup and overwrite the GP files on your machine
4) Restore your most recent GP database backups
5) Run the SQLLOGINS.sql script that you generated from Capture_Logins.sql
6) Create a DSN to point to the SQL Server instance
7) Launch GP, login as both sa and a standard user, and verify the data and users

And once that test system has been setup, future tests of your recovery plan would probably take less than 30 minutes, as you should only need to complete steps 3, 4, 5, and 7.

If you actually implement a backup strategy similar to what I described above, and if you actually TEST your recovery plan, you will definitely be in the 99th percentile relative to your peers.

Live long and prosper.



UPDATE:

Like I said, I couldn't cover all of the details. I see that Mark Polino also recently posted an article on Dynamics GP backups, and he has some great tips on backing up several other important items related to GP that are typically forgotten in backup routines.

And I found a few other backup anecdotes here and here, and here is an old computer joke that also makes the point.

Land of the Landed Costs- Part 1

Landed cost is the total cost it takes to "land" an item on your doorstep, including additional charges like freight, customs, and processing. More and more, I find that companies are interested in landed cost as they increase the use of international suppliers and manufacturers. By using landed cost, the "total" cost of the item is reflected in inventory and is valued as an asset, rather than immediately expensing those additional costs. Take the following example, where I purchase a widget from my European supplier:

• Purchase 100 widgets @ $20/each
• Overseas shipping costs are $1500
• Customs, Duties, and other Processing Fees are $500
• Domestic shipping costs are $400

If I were to value my widgets only at the cost from my vendor, the cost in inventory would be $2000 for the 100 widgets at $20/each. And then I would expense the additional $2400 in costs.
The situation changes though, if I am using landed costs. Using the same costs above, I would value my inventory at $4400 dollars in total, or 100 widgets at $44/each. Perhaps a more accurate picture of the inventory value? In turn, this increased cost will affect my Cost of Goods Sold on the sales side, impacting my margin appropriately.
So, now that we have an example, how about talking through how Landed Costs work in Dynamics GP? With Landed Costs in Dynamics GP, these costs can be:

• Recorded as an estimate amount on a receipt
• Matched to an invoice and revalued to provide a more accurate amount

There are five key aspects to Landed Costs in Dynamcs GP from my perspective:

• Landed Cost Setup: Includes setup of individual landed costs, landed cost groups, and assignment to item/site combinations
• Purchase Order Landed Cost: Assigning landed cost groups to POs
• Receivings Landed Cost: Recording of landed cost groups on Receipts
• Receivings Landed Cost Apportionment: Apportionment of landed costs across an entire receipt
• Invoicing Landed Cost: Recording invoices for landed cost vendors and matching them back to the original product receipts

Let's take a look at each aspect. In this post, I will cover the setup items. And in my next post, I will cover the actual transaction entry. First, Landed Cost Setup.

Cards>>Inventory>>Landed Cost

This is where you can set up each individual landed cost that you plan to track. If you plan to match invoices for the landed cost, you will want to put in a Vendor ID. Matching invoices for landed cost means that (in this example above), I could record the actual insurance invoice from Associated Insurance and match it back to the shipments from my supplier for the product I purchased.
You can also select a cost calculation method, this will be how the landed cost estimate on the receipt will be calculated. If you have chosen to match invoices, the invoice amount will be compared against the original estimate on the receipt. The resulting difference can be revalued, if Revalue Inventory for Cost Variance is marked. Otherwise, the difference will post to the Purchase Price Variance account.
The GL account specified are used in the following manner:

• Accrued Purchase Account: Used as the offset to the landed cost estimate on receipts, and cleared when the amount is invoiced or matched to invoice.
• Purchase Price Variance Account: Used for the variance between invoice and receipt when matching invoices but not revaluing.

Note that no inventory or expense account is specified because the item's account will be used, since landed cost is updating the inventory cost/value of the item.

Cards>>Inventory>>Landed Cost Groups

Landed Cost Groups are used to organize landed costs in to sets that can be assigned to item/site combinations and/or to document line items like Purchase Orders and Receipts. An individual landed cost can exist in more than one landed cost group. You might choose to have landed cost groups to represent a set of landed costs for:

• A specific location
• Certain items
• A type of shipping process

In my example above, I have chose to set up a landed cost group for my domestic shipments. It includes both the insurance costs and the freight costs. Now, I can assign this landed cost as a default for certain combinations of items and sites.

Cards>>Inventory>>Quantities/Sites

This step is not required. However, if appropriate, you can assign a landed cost group to a specific item/site combination in this window. Doing this will cause the landed cost group to default on the purchase order line item, where it can be changed if necessary.

More soon on the transaction side of all of this fun!

Sending Email to Business Portal Users Using SQL Server

In my last post, I discussed a scenario where a client wanted to send e-mails to Business Portal users, and how Christina and I were able to lookup e-mail addresses in Active Directory with a SQL query.

Once we figured out how to get the e-mail addresses out of Active Directory, we needed to write a routine that could query a GP table and notify users if a transaction was created, or if one had not yet been submitted.

Let's start with a quick overview of Database Mail. Database Mail in SQL 2005 is a significant improvement over the prior SQL Mail feature. The biggest improvement in Database Mail is that it now uses SMTP instead of MAPI, so you no longer have to have a mail client configured on the database server. It is also very easy to configure and use.

Configuring Database Mail is relatively straightforward, so I won't cover it in detail here (if you want more info on the configuration, post a comment and let me know).

Once you have Database Mail configured and tested, you are ready to test a SQL statement to send an e-mail. Here is a simple example of the sp_send_dbmail procedure:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMAIL',
@recipients = 'recipient@domain.com',
@body = 'Test Database Mail Message',
@subject = 'Database Mail Test';

After executing this procedure, if you don't receive the test message, make sure to check the Database Mail Log by right mouse clicking on the Database Mail object in SQL Server Management Studio and selecting "View Database Mail Log" to try and diagnose the problem.



So now, on to create a routine that can query a table, lookup e-mail addresses in Active Directory, and then send e-mails. I created a simple table called "Transactions" that contains a Username field, with values that match some test Active Directory users I setup.



This sample T-SQL will query the Transactions table, get the usernames, and loop through the users, sending an e-mail to each. If any users are not found in AD, or if any users do not have an e-mail address setup in Active Directory, it will send an e-mail to an Administrator with the list of these users.

--Query e-mail address and send e-mail

--Declare variables
DECLARE @UserID AS varchar(50)
DECLARE @Email AS varchar(50)
DECLARE @AdminEmail AS varchar(50)
DECLARE @EmailBody as varchar(max)
DECLARE @ErrorBody AS varchar(max)
DECLARE @CRLF Char(2)

SET @EmailBody = ''
SET @ErrorBody = ''
SET @CRLF=Char(13)+Char(10)

--Set admin e-mail to notify if user / e-mail is not found
SET @AdminEmail = 'admin@company.com'

--Create cursor to retrieve list of users to notify
DECLARE Recipients CURSOR FOR
SELECT Username FROM TEST..Transactions WHERE TrxDate > '2009-04-15'

--Open cursor and get next user
OPEN Recipients
FETCH NEXT FROM Recipients INTO @UserID

--Loop through cursor
WHILE @@FETCH_STATUS = 0
BEGIN

--Get the e-mail address for the given user
SELECT @Email = ''
SELECT @Email = LTRIM(RTRIM(mail))
FROM OPENQUERY
(ADSI, 'SELECT givenName, sn, mail, cn, displayName, sAMAccountName FROM ''LDAP://OU=Consultants,DC=precipio,DC=local'' WHERE objectCategory = ''Person'' AND objectClass = ''user''')
WHERE mail IS NOT NULL AND SAMAccountName = @UserID;

--If the e-mail address is not blank, send an e-mail to the user
IF RTRIM(@Email) <> ''
BEGIN
--Send e-mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMAIL',
@recipients = @Email,
@body = 'This message is being sent by SQL Server Database Mail. The recipient e-mail address is being selected using a SQL query against the Active Directory LDAP store.',
@subject = 'AD Email Test';
END

ELSE

--If the e-mail address is blank or user ID was not found, add the user id to the error body
BEGIN
SELECT @ErrorBody = @ErrorBody + 'No e-mail available for user ID: ' + @UserID + @CRLF
END

FETCH NEXT FROM Recipients INTO @UserID

END

--If errors were found, notify the admin
IF LEN(@ErrorBody) > 0
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMAIL',
@recipients = @AdminEmail,
@body = @ErrorBody,
@subject = 'Users or e-mail addresses not found';
END

CLOSE Recipients
DEALLOCATE Recipients


And here is a sample of the e-mail an administrator might receive:



I've highlighted in red some of the parameters that you will want to change for your testing purposes, and obviously you will want to edit the e-mail body text.

So there you have it--a query against Active Directory to lookup e-mail addresses, and then send e-mails, all through a relatively simple SQL query.

Accessing Active Directory From SQL Server

Christina recently had the need to send notification e-mails to Business Portal users based on certain transactions that had been entered. To keep things simple, she was looking for a solution that could be implemented in SQL Server. After discussing it briefly, we were pretty confident that we could setup a simple routine to send e-mail from SQL Server. But there was a catch--we only had access to the user's Windows /Domain username in the SQL database, and we did not have the user's e-mail address stored anywhere in the GP database. However, the e-mail addresses were stored in Active Directory. So the search was on to find a way to access Active Directory from SQL Server.

It turns out that it is surprisingly easy to setup a simple SQL Server query that will allow you to access information in Active Directory (AD). There are several methods for accessing AD, each with different pros and cons, but given our simple requirements, we went with the simplest approach.

After searching through a few Google results, I found this sparse blog entry that shared two straightforward commands for querying AD.

The first step is to create a linked server that will allow you to invoke the Active Directory Service Interface (ADSI) interface:


sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

Once that is setup, you can query AD directly using Lightweight Directory Access Protocol (LDAP) queries. The syntax is a bit obscure, but once you see a few examples it becomes pretty clear and straightforward. Here is a great site that offers Excel files listing many of the AD attributes that are exposed via LDAP.

After some testing, I came up with the following query which allowed me to retrieve my name and e-mail address from my Active Directory server:

SELECT * FROM OPENQUERY

(ADSI, 'SELECT mail, displayName, sAMAccountName FROM ''LDAP://OU=Consultants,DC=precipio,DC=local'' WHERE objectCategory = ''Person'' AND objectClass = ''user''')

WHERE mail IS NOT NULL AND sAMAccountName = 'steve'
Naturally, you will use your AD domain name for the "DC" parameters. And in my query, I chose to limit my search to the "Consultants" Organizational Unit (OU), but you can remove that filter to view all accounts in all OUs.

With a bit of searching, I found that the Windows username is called "sAMAccountName", and the e-mail address is simply called "mail".

So, this query allowed me to find the user's e-mail address in AD based on their username. Once I had the e-mail, I was able to write some SQL to generate an e-mail.

I'll cover that in my next post...

eConnect error: Fail to invoke remote RPC method

This appears to be a variant of the DTC and network configuration issues that I have discussed earlier, here and here.

Once again, DTCPing came to the rescue.

The client was running the eConnect integration on a laptop in a docking station, so it had both a wireless network connection and a wired connection.

When I ran DTCPing on the client, it ran fine and was able to connect to the server. But on the server, I received the familiar -->gethostbyname failure error message.

It turns out that the wireless network card was issued an address on a different subnet than the wired connection, which itself should be fine, but for some reason, the server could ping the wired IP address, but not the wireless IP address. And the server happened to be clinging to the wireless IP address for the laptop, preventing DTC from communicating back to the laptop.

To resolve the issue temporarily, I edited the hosts file on the server at C:\Windows\System32\drivers\etc\hosts and added a new line to force map the laptop computer name to the wired IP address.

After running nbtstat -R and ipconfig /flushdns, the server finally started to ping the laptop successfully on the wired network card.

Running DTCPing again, the gethostbyname error went away, and the usual Windows XP "access is denied" error returned, which I now ignore.

We then ran the integration on the laptop, and it worked fine.

The permanent solution for this client is a little more challenging. I suspect that their wireless network limits or blocks traffic to the wireless clients for security reasons, but that also prevents DTC from working properly. The best solution I could think of at the moment is to add a fixed address record in their DNS server for the laptop's wired network card, and/or give the laptop a static IP address for the wired network card, to try and force the GP server to always resolve to the wired IP address.

Cannot generate SSPI context error message

A client has been using an eConnect integration for months without any issues, but all of a sudden last Friday, he received this error when trying to run the integration:

"Cannot generate SSPI context"

Whenever I see a Dynamics GP or SQL Server related error with "SSPI", I instantly assume that it has something to do with Windows authentication on SQL Server. Here is a Microsoft KB article explaining more about the obscure mechanics of SSPI and the error.

To summarize that article, here are the steps that I took to try and troubleshoot the error:

1. From a workstation, open a command prompt
2. Type the command: ping sqlservername
3. Type the command: ping -a sqlserveripaddress

For #2, you should receive a response indicating "Reply from..." and the SQL server's IP address.

For #3, you should receive a similar reply, but you should also see the fully qualified name of the SQL server (i.e. gpsql.company.local)

If either one of those gives you an error or does not return the proper server IP address or name, you probably have a DNS issue that needs to be resolved.

If those steps look good, as they did for my client, try these steps.

1. On the GP SQL Server, open the Services applet (Start --> Run --> Services.msc)
2. Locate the service for the GP SQL Server instance (i.e. SQL Server (GP))
3. Look at the "Log On As" column, to see what account the service is using to run
4. If the SQL Server service is running with an account other than Local Service, it is likely that there is a problem with the account permissions, the account password, or the Domain communication.

In my case, I found that the SQL Server service had been setup to use the domain Administrator account (which is not recommended for several reasons).

And it turns out that the Administrator password was reset last Friday (one of the reasons why you should not use it for any Windows services), which was the day that the errors started occurring.

My speculation is that even though the SQL service was still running with the Administrator credentials, the password change affected Kerberos authentication for new Windows authentication connections to SQL server. And of course, because eConnect only uses Windows Authentication, and GP only uses SQL Authentication, that is why GP users didn't receive any errors, but the eConnect integration no longer worked.

So, I opened the service properties for each of the SQL Server services that used the Administrator account and updated the Administrator password for each of them. I was informed that the service would have to be restarted for the changes. After getting all users out of GP and restarting all of the SQL services (server, agent, browser, etc.), the error went away and the integration worked fine!

Of course, there is still the issue of the domain Administrator account being used for Windows services. I will be scheduling a time with the client to create a dedicated SQL services domain account, and switch the services over to use that account.

One caution about using a domain account to run SQL Server: You will need to be careful with the permissions. The account will need to have a fair amount of local access on the server so that it can manage the database files, and it will also need the ability to Log On As A Service, which is setup in the local security policy. Finally, it will need to have adequate permissions to create its own SPN for Kerberos authentication (as mentioned in the MS KB Article). So make sure to plan for some downtime and testing if you decide to switch to a domain account for SQL Server.


UPDATE: After reviewing the Google results further regarding this error, it appears that there are several other more innocuous or low level causes for the SSPI error. So if the above steps don't resolve the issue quickly, you may have a more complex issue on your hands. Here are two examples of other possible causes that are not explicitly referenced in the MS KB article:

http://blogs.msdn.com/sql_protocols/archive/2005/10/19/482782.aspx

http://sqlblogcasts.com/blogs/grumpyolddba/archive/2008/07/19/cannot-generate-sspi-context.aspx



UPDATE April 2018: Someone shared an additional angle to the SSPI context error saga. Apparently the error can occur if there is a problem with an "SPN".

https://msdn.microsoft.com/en-us/library/ms677949(v=vs.85).aspx


If, for some reason, the SPN associated with a Windows Service, such as the SQL Server service, becomes invalid, this can cause the infamous SQL Server SSPI errors.  I believe this might occur if the server name was changed.

The apparent solution for this is to inspect the SPN information for the domain account used by SQL Server and potentially delete and recreate the SPN.

You would use the "setspn" command to do this.

https://technet.microsoft.com/pt-pt/library/cc773257(v=ws.10).aspx


To view SPN information for the server, you apparently use "setspn -L"

You can use "setspn -D" to delete SPNs, then "setspn -S" to save new SPN information.

Note that there may be more than one SPN record for a given service account, so the -D and -S may need to be used more than once.

I have not performed this process before, but someone shared this example with me.  It isn't clear to me what the -L results actually show, as I don't see anything about SQL Server.

Note the -D and -S versions are called a second time with port 1433 listed.  I don't know if this is just a precautionary measure to clean up such records if they happen to exist, or if those are mandatory for SQL Server.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com