Other: Linked servers

When the ERP database is not located on the same SQL server as the AGR databases, you will often need to use a linked server to connect to the ERP data. The linked server feature in SQL Server can be used to connect to another instances of SQL server or other types of databases like Oracle, IBM DB or even connect to excel documents and text files. It is usually the responsibility of the customers sysadmin to provide and set up the linked server but we still need to be able to provide requirements and occasionally set up the linked servers by ourself.

The basics

It would be pretty futile for me to write pages and pages on linked servers when we have a ton of youtube videos and articles online. I suggest you check out the following links:

https://www.sqlshack.com/how-to-create-and-configure-a-linked-server-in-sql-server-management-studio/

https://www.youtube.com/watch?v=Fjlqa1y3ru0

Requirements & setup

In the picture below you can see a (made up) typical setup of AGR. It has an APP server which hosts the website and app service and a DB server that hosts the SQL server and databases. It is connected to an ERP server, in this case Navision, via linked server.
We have 2 scenarios

  • 1: We use a SQL user to connect the ERP and AGR DB servers
    • This is by far the simplest setup and most error resiliant. All users that connect are mapped through the same SQL user and there are no problems with domain users or domains security. That being said it can be argued that this is not as secure as using the domain user method so in some cases we might not be able to persuade the sysadmin to use this method
  • 2: We use a domain user to connect the ERP and AGR DB servers

inst Notes

  • In the above picture we assume that both the website and app service are logging in using a domain user agr\service_user but it is in fact possible to run them as a different user.
  • The agr\service_user and agr\jeremy users must be mapped on the AGR-SQL-01 server (with proper permissions) so that the app and web can connect and Jeremy can connect to the server using SSMS

The general rule is that we only use the linked server to fetch data from ERP tables, not to write data to the tables. This means that the user (sql or domain) will only need database read permissions. Some of our procedures require access to system tables on the ERP side so read permissions per table are not enough. The db_datareader role is preferable.

To fully understand the difference between the 2 scenarios mentioned above, it is important to understand the difference between windows and sql server authentication. Here is a fine article that explains it in details:

https://www.virtual-dba.com/blog/differences-windows-sql-server-authentication/

Problems and solutions

Knowledge is power! In order to properly diagnose linked server problems it is imperative that you understand how linked servers work. So if you have not watched the youtube video and read the article mentioned above I highly suggest that you do so. That being said there are a few common problems that we should address:

  • Problem: I upgraded to 6.2 and now the linked server does not work anymore. I am getting this error message: Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’
    • Prior to version 6.2. the sql server agent was running the daily jobs but now the AGR App Service is running the daily jobs. There are 2 things that we have found that can go wrong when updating:
      • 1: The sql server agent and the agr app service are running as different domain users and the linked server is not configured to accept connections from the user that is running the app service. This should be pretty easy to fix. Either run the app service as a different user or add the user to the linked server
      • 2: We have a double hop kerberos problem. YES! It is as bad and complicated as it sounds. Before the upgrade the sql server agent was running the daily job and since it is located on the sql server computer it has no problems connecting via the linked server. But the app service is on the app server and it first needs to connect to the agr sql server and from there via the linked server to the ERP sql server. This is called a “double hop” and it can cause problems if there is no “domain trust” between these machines. I might be explaining this wrong but we have encountered these errors and if the sysadmin has no idea what you are talking about I suggest you try to convice him to change the authentication to using a sql user. Here is some “light” reading material on the subject: https://www.sqlservercentral.com/articles/a-brief-explanation-and-solution-for-the-double-hop-problem
  • Problem: I can run a staging element and query an ERP table in SSMS but it fails in the daily job.
    • When you start SSMS you are probably logging in as “yorself”, e.g. agr\sigrun or agr\jeremy. You might have the proper configuration and permissions through the linked server but the app service is usually running as some other domain user, a “service user” and this user is not properly configured. Go to the linked server security config (if you have access) and check the configuration.