After much trial and error I’ve finally figured out the magic formula to get SugarCRM 5.5.2 running on Windows Server 2008 R2 with a remote SQL 2005 server. I have two servers for this project, Server1 (Web Server), and Server2 (SQL 2005 Server). Here are the steps I took to make this work…
- Server2 (SQL) already existed so all I needed to do here was create a SQL login account and database for Sugar. I created a database from SQL Management Studio called “SugarCRM”. I then created a user called _sugarcrm and assigned it’s default database to SugarCRM. I used the User Mapping section to grant “db_owner” rights to the SugarCRM database.
- One of the requirements to run SugarCRM on SQL, is to enable “Named Pipes”. To do this, I went to the SQL Server Configuration Manager tool and clicked on Protocols for MSSQLSERVER. Right-clicking the Named Pipes object gives you the option to enable. (Note the SQL Service will need to be restarted for this to take effect. I just went ahead and cycled the whole server since this is a development sql server)
- Back on my web server (Server1), I started by installing IIS from Server Manager. I added the role services of “CGI, ISAPI Extensions, and ISAPI Filters“. I’m not really sure that all of these are needed but throughout the troubleshooting process of trying to get this up and running all of these seemed to be required as pre-requisites at one point or another. (I restarted the server after the installation of IIS)
- Next, I downloaded the latest version of PHP. In my case that would be 5.3.2. I used the “Non-Thread-Safe VC9” version. After accepting the license terms of the installer, I made one minor change to the installation by changing the destination folder to C:\PHP\ instead of C:\Program Files (x86)\. I read somewhere that avoiding spaces in the PHP directory path is advisable where possible.
I selected IIS Fast CGI from the Web Server Setup options and left the items to install at their default values.
- The next step was to download the Microsoft SQL Server Driver for PHP. The latest version appears to be 1.1. The installer basically just unpacks the required DLL files into a directory you specify. In my case I created a new directory called C:\MSPHP\
- Once the files were unpacked, I navigated to the C:\MSPHP\ directory and found the driver I needed for my version on PHP. In this case the driver was named php_sqlsrv_53_nts_vc9.dll I copied this DLL file into the C:\PHP\ext\ directory.
- The next step was to modify the php.ini file to enable use of this new driver. I did so by adding the following lines of text to the extensions at the bottom of the file. (Note the php.ini file is at the root of your PHP directory, in my case C:\PHP\)
At this point, I also made a few other changes to the php.ini file as recommended by the Installing SugarCRM with SQL Server wiki article. The changes I made were:
Add the following text under the [MSSQL] section:
; Specify client character set.
; If empty or not set the client charset from freetds.comf is used
; This is only used when compiled with FreeTDS
mssql.charset = “UTF-8”
Uncomment the line mssql.datetimeconvert and set its value to Off
Next step was to install the Microsoft SQL Server 2008 Native Client. Yes, I realize that it sounds incorrect as I’m running SQL 2005, but through much trial and error I figured out the hard way that the SQL 2008 native client is a requirement for the Microsoft SQL driver for PHP. If you use the 2005 native client the configuration wizard stops at the point where you specify the database server and instance with the error:
“The provided database host, username, and/or password is invalid, and a connection to the database could not be established. Please enter a valid host, username and password.
The provided database administrator username and/or password is invalid, and a connection to the database could not be established. Please enter a valid user name and password.”
- I restarted the entire server after this step to reload the php.ini file and for good measure before proceeding with the installation of the actual web server files.
- The next few steps were taken word for word from the typical SugarCRM installation manual. I downloaded the .ZIP file SugarCE-Full-5.5.2 and unpacked it to my IIS content directory C:\InetPub\wwwroot\ folder.
- I setup “Write” NTFS privileges for the local IUSR account on the directories: cache, custom, data, modules, and for the file config.php This allows the user running IIS, in my case IUSR to access and make changes to these directories.
- That completes the pre-requisites necessary to launch the SugarCRM configuration wizard. Next step was the configure Sugar using the configuration wizard. I did this by launching my web browser on the web server and calling up http://localhost If all goes well, you should be greeted with the Main Config page wizard. If not, something is likely not setup properly with your PHP configuration and IIS.
- I chose to use the “Custom” installation option. The next screen should show your options for databases. If your PHP file is setup with the changes above, you will see the option for “SQL Server”
The next page is the Database Configuration page. Getting Sugar to see my database took a few attempts at the Host Name \ Host Instance field. Since my database server only has one instance called MSSQLSERVER I assumed that needed to be present in the Instance field but that was not the case. I ended up just putting in the database server name (Server2) and leaving the Instance Name blank. I filled out the rest of the information on the page with the SQL account setup previously and chose the option for “Same as Admin User” under Sugar Database Username. (Technically you can specify the sa account or another admin account to have Sugar create the database but I feel more comfortable creating the database manually through the previous steps and only specifying the account created specifically for accessing the SugarCRM database.)
You will be warned that Sugar has detected the database already exists and will be overwritten if you continue. Obviously I’m aware of this so I clicked “I Accept”
I specified the URL of the webserver on the next screen as well as a System Name and admin password before clicking next.
I left the Security options at their default values and clicked next on the Site Security page.
The locale settings page allowed me to change any options specific to locality. In my case everything looked OK so I clicked Next.
On the confirm settings page, I noticed a warning under the “Upload File Size” section. The warning states that your PHP configuration should be changed to allow files of at least 6MB to be uploaded. At this point, I opened my php.ini file and searched for upload to find the appropriate section. Under the “File Uploads” section, I modified the option: upload_max_filesize to 20M This seemed like an appropriate number for my environment.
Back on the configuration wizard page, I clicked Install. After a bit of disk whirring and an IE progress bar the installation completed successfully!