There is currently a bug with Microsoft SQL Server 2016 Express that causes it to fail when installing a new instance on top of a server with instances from older versions of SQL Express (2014, 2012, 2008). What we know is this:
- This issue impacts SQL 2016 Express, even when it has nothing to do with Sage.
Why it is an issue for Sage 300 CRE 2017 installation: Sage SQL Installer routine forces its own SQL 2016 instance creation with no customizations other than to specify an SA password. Of course, we already know that installing with an SA password entered fails, so when Sage forces an SA password (it will not allow a blank password) it is essentially breaking the install.
- So far we know that the issue is triggered when you are attempting to add a SQL 2016 instance and another instance of SQL Express previously exists. In other cases where a customer has software different than Sage we have seen the same issue. This is NOT unique to Sage.
- When Sage 300 is NOT involved, and a new instance of SQL Express 2016 is being added, there are two possible work arounds:
- Use Mixed Mode WITHOUT an SA password and the install will finish successfully.
- Install using Windows Authentication and it succeeds.
We worked with Kyle at Sage and came up with a manual work around to install a SQL instance manually, and build it out to fit the Sage requirements. Then, we hook Sage into that manually installed instance. Here are the instructions for building out the instance around Sage's system requirements:
1. Installation Type – Perform a new installation
2. Minimal Feature Selection:
- Database Engine Services
- SQL Server Replication
- Full-Text search
Note: Any additional ones may be included, root directory can also be changed
3. Recommended instance name and ID is “SAGE300CRE”.
4. Use LOCAL SYSTEM service account. This is required to get this to properly work, and to have the service be able to properly start up post-installation.
5. Collation must be “SQL_Latin1_General_CP1_CS_AS” which is NOT the default.
We are installing as Windows Authentication mode for the initial install. This gets around the issue at hand, and after the initial install finishes - it can be changed back to Mixed Mode and you can specify an SA password.