Sage 100

Working on an upgrade to Sage 100 2015 premium this weekend. Everything server side has gone well but none of the workstations or citrix server client installations have been able to print or preview properly. Getting the following error: The database login failed. Failed to open the connection. Details [Database Vendor Code: 17] Failed to open the connection. GL_ChartofAccounts. This last message changes depending on which report you're trying to run. in this case GL COA

A couple of things to try: (1) Sage 100 Premium ERP SQL Settings Utility - reset server name and credentials (2) Make sure the firewall on the server is not blocking connections to SQL server. Default port is 1433, but could be different on your SQL install

Thanks Phil. I'm just waiting for someone with permissions to the SQL server to connect with me and run the utility. We ran it two weeks ago when we did the pilot but perhaps it needs a refresh after doing the go live migration last night.

Running the SQL utility didn't fix it. I'm having someone check the firewall settings now to make sure the SQL port isn't blocked

Some further updates on this one. Firewall settings are good. I have two workstations that are functional, however the client installation on the Citrix server cannot preview or print. The same database login error. The Citrix server is the most important client install as that's where most users run the program. If I can't get this working I need to revert back to Sage 100 advanced. Anyone out there have any suggestions?

I've checked permissions in SQL for both MAS_User and MAS_Reports and everything is as it should be. On the two workstations that are functional we can print any of the reports that are failing on Citrix and the other workstation that doesn't work.

This happened last week to us. The firewall settings looked good but getting this same error as you. Asked IT guy to disable firewall anyway then it started working. If he asks why can you even start up MAS the answer is that connection is from App Server to SQL Server, whereas with reports its directly from wkstn (Citrix server) to SQL Server.

Hello Alnoor
This is an old post, but now I'm upgrading this same client to Sage 2020 PU3, working with multiple IT teams, networking, firewall and SQL DBA's. 
Reviewing the multiple entries here, did we conclude that the Citrix Server needs to have the SQL 1433, 1434 open?

------------------------------
John Kerr
Net at Work
------------------------------

Original Message:
Sent: 12-13-2015 14:07
From: Alnoor Cassim
Subject: Working on an upgrade to Sage 100 2015 premium thi

This happened last week to us. The firewall settings looked good but getting this same error as you. Asked IT guy to disable firewall anyway then it started working. If he asks why can you even start up MAS the answer is that connection is from App Server to SQL Server, whereas with reports its directly from wkstn (Citrix server) to SQL Server.

If there are multiple SQL instances on the same server, the port number for each is not necessarily what you expect.  I remember struggling once and forcing the new primary SQL instance to 1433 which should not be necessary.  One of the SQL tools allows you to play with the port #'s... which is not recommended.
On the SQL side, for Windows firewall, I just put an incoming rule for the SQL service program (which you can get from the Services applet... a tip from @Steve Iwanowski during a MOTM pre-conference session a few years ago. ).
​​

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
------------------------------

Original Message:
Sent: 09-12-2022 09:22
From: John Kerr
Subject: Working on an upgrade to Sage 100 2015 premium thi

Hello Alnoor
This is an old post, but now I'm upgrading this same client to Sage 2020 PU3, working with multiple IT teams, networking, firewall and SQL DBA's.
Reviewing the multiple entries here, did we conclude that the Citrix Server needs to have the SQL 1433, 1434 open?

------------------------------
John Kerr
Net at Work
------------------------------


Original Message:
Sent: 12-13-2015 14:07
From: Alnoor Cassim
Subject: Working on an upgrade to Sage 100 2015 premium thi

This happened last week to us. The firewall settings looked good but getting this same error as you. Asked IT guy to disable firewall anyway then it started working. If he asks why can you even start up MAS the answer is that connection is from App Server to SQL Server, whereas with reports its directly from wkstn (Citrix server) to SQL Server.

Thanks Alnoor. They will need to get that resolved for next go round. I had to roll back to 4.50 advanced in the meantime. I had to deal with all the pvx files that were newer and causing 4.50 not to work. But it's working now.

Not sure if this is the solution or not but I have seen where the user only created a TCP firewall rule and not a UDF firewall rule for the sql server and reports did not work properly until both rules were applied to the firewall. I honestly can't remember if it was on Sage 100 Premium or X3 but it may be worth looking into.

That was it Tim. UDP 1434 needed to be opened as well as normal TCP 1433, 1434. They also opened TCP 51101 but I don't know specifically why.

This was some feedback I received from the IT guys that were assisting me with this issue. This was after I sent them @Alnoor comment to me. They used the MAS_Reports user credentials to authenticate. I worked with Adam and we were able to query the SQL PROD server and MAS_XXX database using the same ""SQL Server Native Client 10.0"" ODBC; the same one Sage was using in the SOTA.INI file. We were able to do a select * on CI_Item table without any connectivity issues. I don't see any firewalls issues between Citrix App Server and SQL Server.

That is not a fair test at all because doing a simple SELECT is only using a database engine connection on TCP 1433 (also MAS uses SQL OLEDB instead of ODBC but that isn't as important). The simple query doesn't test UDP 1434 which I've learned seems to be a requirement for running reports off the Sage 100 Launcher (which is different than say creating a report directly in the Crystal Reports designer). Maybe the best way for them to see this is completely open the firewall temporarily and then run reports from the Launcher.

That's very useful feed back Alnoor. Here's some more info which makes the scenario still more confusing. I was working with the Citrix admin and two other IT people. I have a laptop supplied to me by this client. I was able to install the 2015 premium client on the laptop I use and run reports. One of the other IT people did the same. So we were left with two laptops working properly but no success with one laptop and Citrix published app. If the firewall ports TCP and UDP were blocked, then none of us could run these reports. The application is installed on it's own dedicated server and the SQL databases are located in a separate data center SQL server. I'm based on the east coast and the other IT person who's laptop worked is based in LA. So there are a lot of data hops here.

So are the ports blocked on the Citrix server?

I just posed the question to them and I'm waiting to hear back from them. There are multiple IT teams handling these servers and it always takes a while for them to get back with an answer.

Change requests have been made and the ports should be opened this weekend. Will report back with findings.