I am having problems connecting wordpress to sql server on iis. Any ideas?

Greetings mates,

We have been struggling for the past three days trying to connect wordpress to sql server using iis.

We are using iis because management doesn't want us to use WAMP or XAMMP.

Anyway, we used Microsoft web Platform installer to install, PHP and PHP Manager.

Configured, tested and phpinfo is rendered fine.

We downloaded wp-db-abstraction plugin, created a folder called MU-Plugins and put the  wp-db-abstraction plugin inside this folder,  grabbed wp-db.php that came with this plugin and put it inside the wp-content folder.

Then we downloaded sql server driver for php.

The version of php we installed is 5.6 ( I know).

We registered these dlls in php.ini. Then copied these dlls into the php/ext folder.

The version registered is for php 5.6

Then we fired wordpress =  http://servername/index.php and then got this error:

Error establishing database connection

This either means that the username and password information in your wp.config.php incorrect or we can't contact the database server at xxxxxxxxxxxxx.

Are you sure you have the connect username?
Are you sure you have the correct password?
Are you sure you have the correct hostname?
Are you sure database server is running?


Does any one of you experts please help me out on this?

Thank you in advance
LVL 29
sammySeltzerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gr8gonzoConsultantCommented:
Are you certain you're using Microsoft SQL Server? Usually WAMP will install MySQL, and that's the default database that WordPress runs on. Microsoft SQL Server is an entirely different product than MySQL.
1
sammySeltzerAuthor Commented:
Thanks for your response.

As indicated in my original post, I am NEITHER using WAMP NOR XAMPP.

We are using iis webserver.

There is another important point I mistakenly left out; when I fire up phpinfo, I don't see any entries for any of the sql DDLs I registered in php.ini.

That leads me to ask, when php manager or php are installed and configured automatically via Microsoft web platform installer, where is the phpinfo that is installed automatically located?

I suspect that's the reason I am not seeing the sql server DL entries.
0
Terry WoodsIT GuruCommented:
The default WordPress install is intended for MySQL, but it looks like it might still be possible to get it running on SQL Server. It looks like there is a fork of the software maintained here: https://projectnami.org/ (click the Download button to get into the site... weird, but that's how it is!)

I also found a guide, but the install files it tried to link to no longer appear to exist, so I think you'll need the above version instead. Still, the guide might be helpful, even though it's a bit old: https://blogs.msdn.microsoft.com/brian_swan/2010/05/12/running-wordpress-on-sql-server/
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

David FavorLinux/LXD/WordPress/Hosting SavantCommented:
WordPress will only work reliably with MariaDB/MySQL.

Not MS SQL or PostgreSQL or SQLite3 or anything else.

If you expect to have a working WordPress site, only use MariaDB/MySQL.

There's no problem running IIS + MySQL... well except you'll have far less problems running pure LAMP Stack...
0
gr8gonzoConsultantCommented:
Sorry, I was rushing through things earlier and I misread the original post as saying that you DID use WAMP. My mistake.

That said, the same issue still applies - WordPress is built for MySQL, not for MS SQL Server. You could try using the fork mentioned by Terry, but I really wouldn't do it since you also have to consider that all the WordPress plugins also assume you're using MySQL.

So all of the SQL that comes in WordPress and all its plugins will be written for MySQL, not for SQL Server, and you have an extremely high likelihood of not being able to run it the way you want to run it. Even if you got the connection working, you'll likely run into different SQL syntax (e.g. SQL Server's "TOP ###" vs. MySQL's "LIMIT ###") that will result in errors when you try to use the product.

As far as IIS goes, unless you're an absolute guru on IIS and PHP, I wouldn't recommend that combination. More often than not, there are issues with the PHP CGI binary crashing under IIS or hanging around instead of ending cleanly, leading to memory errors. You can Google around for PHP and IIS and you'll come across a lot of frustrated people that post about their problems. It's not IMPOSSIBLE to get set up, but it's just a lot trickier and more sensitive than getting PHP running under Apache. (And if you don't mind running on a different port, you can run both IIS and Apache at the same time)

Also, to be clear, "WAMP" is not a product. It is simply an idea or a concept:

W-indows
A-pache
M-ySQL
P-HP

So if you're running WAMP, it simply means you're running 3 different products: Apache, MySQL, and PHP all together on Windows.

Products like XAMPP or WampServer are just pre-packaged installers that will do the setup and configuration of those products (and often times, more than that) for you, but you do not need to use them. You can install each product individually and have great stability.

The strong recommendation here is to use Apache one way or another. The builds from apachelounge.com are well-optimized and come with updated versions of mod_ssl and so on - they run fantastic:

https://www.apachelounge.com/download/
1
Dave BaldwinFixer of ProblemsCommented:
It is unlikely that you will ever get Wordpress to work with MS SQL Server.  It isn't written for it and the SQL is different enough that it would be difficult to even modify the code to work.  And I have had to uninstall the Microsoft web Platform installer and remove everything it installed because I couldn't get it to work with all the needed extensions.
0
sammySeltzerAuthor Commented:
@gr8gonzo, sure WAMP or XAMPP are concepts.

However, my point is that management doesn't want us using them for two reasons.

1, You always have to start apache and MySQL before your app that depends on them work.
2, if you log into the server and log out, you kill them.

Those have been our experiences using them so far.

Since I have used PHP a few times in the past to connect to SQL server and perform various data manipulations, and the PHP was installed with PHP Manager on iis, we have had only one problem where file size had to be increased, not in php.ini but within iis.

Besides, while I am not arguing with you guy's assertions that we are unlikely to get WordPress to work on Server, are all e articles all over the web lying?

The issue with those are articles is that none of them has put together a comprehensive, step by step approach to installing, configuring and using wordpress to connect to sql server.

For instance Terry Woods, article that guides you through the entire installation and configuration steps does not mention wp-db-abstraction that is supposed to talk to wp-config.php file where your database connection strings are set up.
0
Dave BaldwinFixer of ProblemsCommented:
Every computer I have with a web server, 10 in this room and another 6 in other rooms, is set up to start the web servers at boot.  That includes several versions of IIS, a standalone Apache install on Windows, several WAMP and XAMPP installs, and several Apache installs on Linux.  I have MS SQL Server on 4 of them and MySQL/MariaDB on the rest, also set up to start at boot.

'phpinfo()' is a function that is built into PHP.  It is Always there.  It scans the installed settings and extensions to show you what you have.

One of the common issues with MS SQL Server is that the ODBC Client must also be installed to use the 'sqlsrv' PHP drivers.  That is probably why you are not seeing them in 'phpinfo()'.
0
gr8gonzoConsultantCommented:
First of all:
are all articles all over the web lying?
If they are saying that WordPress works natively with Microsoft SQL Server, then yes, they are incorrect. If you look at the core database file (wp-includes/wp-db.php), you'll see that it uses mysqli_ and mysql_ functions (just for compatibility with old/deprecated PHP installations). There is no native way for these extensions to talk directly to MS SQL Server.

At best, there might be some kind of intermediate proxy server that does on-the-fly SQL translation, and even if that exists, it would be prone to failure (e.g. it might not handle more complex queries) and performance problems (the result sets and queries have to now jump through 2 extra hoops). It would be like trying to have a really long conversation with someone who speaks a completely different language, so you have an interpreter in the room with you that is trying his/her best to translate back and forth, which would significantly slow down the conversation.

So that is why nobody has a guide. It's the same reason nobody has a guide on how to take a brand-new Toyota car and fill it with jet fuel and make it run. Someone might have a guide on how to modify the engine of the car to make it compatible with jet fuel, but it won't work with that kind of fuel without major changes.

That said, several people have created VERSIONS of WordPress where they've literally changed the core code to work with Microsoft SQL Server. But when you do this, you:

1. Lose the ability to quickly and easily update WordPress. If you do update WordPress, it would overwrite the changes and break the site until you re-applied the changes (or you'd have to rely on the author of the distribution to release an update, which means you're potentially vulnerable while you wait).

2. Lose the ability to use many 3rd party WordPress plugins, since their SQL syntax will be incompatible with SQL Server's T-SQL.

Moving onto your management's reasons for not using WAMP:
1, You always have to start apache and MySQL before your app that depends on them work.
2, if you log into the server and log out, you kill them.
These are both incorrect claims.

When something is configured to run as a Windows service, it means that the program starts up when the machine boots up, and it continues working regardless of whether you log in or log out.

In the exact same way that IIS and MS SQL Server are typically configured as Windows services, both Apache and MySQL are usually configured as a service, too (at least on production machines where uptime is a priority), and it's extremely easy and simple to do. You simply run one command for each to create the web service, and then one command to start the service:

Apache:
C:\path\to\Apache\bin\httpd.exe -k install

Open in new window

C:\path\to\Apache\bin\httpd.exe -k start

Open in new window


MySQL:
C:\path\to\MySQL\bin\mysqld.exe --install

Open in new window

NET START MySQL

Open in new window


Here's the official documentation for both:
Apache: https://httpd.apache.org/docs/2.4/platform/windows.html
MySQL: https://dev.mysql.com/doc/refman/8.0/en/windows-start-service.html

I would recommend stopping IIS completely while you configure Apache, since two programs cannot both listen on the same port at the same time.

You can go to Windows -> Run and then run "services.msc" to access the list of Windows services on your computer. You should see services for Apache and MySQL and IIS ("World Wide Web Publishing Service" is the service name for the IIS web server) and SQL Server. You can right-click on any one of them, go to properties and then change the Startup Type option so you can tell it if you want it to start when the machine boots up or not. The value of "Automatic" will make it start up immediately on bootup (before you even log in). The value of "Manual" will make it start only when you go into this services popup and manually start it. And "Disabled" will prevent it from starting altogether.

So while you set up and test Apache/MySQL, just disable IIS so you don't run into port conflicts.

If you want to run both at the same time...
Then either IIS or Apache have to use a non-standard port number (e.g. 8080 or 8000 or 8888 are all common alternatives). Just be mindful that when you're using a non-standard port, you have to include that port number in the URL. For example:
http://localhost:8000/whatever

On the topic of configuration, you'll have to do these additional configuration steps at minimum:

1. Configure Apache to handle .php files with the php7apache2_4.dll file:
LoadModule php7_module "c:/path/to/php/php7apache2_4.dll"
AddHandler application/x-httpd-php .php
PHPIniDir "c:/path/to/php/"

Open in new window


2. Update the path in your Windows system environment variables and add the PHP folder.

3. Restart the Apache service to have it take the configuration changes (right-click on the Apache service and there's a restart option).
4
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
gr8gonzo summed this up well...

"If they are saying that WordPress works natively with Microsoft SQL Server, then yes, they are incorrect."

If you find a site that suggests WordPress can work natively with any other database besides MariaDB/MySQL, best blacklist the site where you read this, because their statement is... as gr8gonzo said... incorrect...

If the site you're reading got this simple thing wrong, all other content they produce is suspect...

IMHO...
0
sammySeltzerAuthor Commented:
Very good write ups by both @gr8gonzo and Dave.

Dave, you first, thanks for that information about ensuring ODBC is installed. I will try that out shortly.

About phpinfo(), where exactly can you find it?

In the past, when I used PHP to connect to SQL Server, I had to create phpinfo() manually to test to test see if all relevant info are available.

In this case, since it was installed with MS web platform installer, and there was one already, I would like to know where it resides.

I found an article yesterday that shows how to add a couple of sql commands to see if it is reaching those files in relation to why it is not showing in phpinfo. I added it to the phpinfo() that was created previously by a colleague but it did nothing.

That let me to think it was the wrong phpinfo(). So, if you don't mind, I will like to know the location of it when it is installed with web platform installer.

@gr8gonzo,
Thanks also for your detailed explanations.

However, unfortunately, I have to disagree with your assertion that once xampp is started, it stays on whether you log off or not.

We have a server right now that when I log off, our wordpress app that currently runs on MySQL will stop. Then it turns out that my logging off triggered xampp to stop. This is the primary reason management wants us to use iis  in the first place.

Now, if there is some additional configuration I will need to do to keep xampp on whether we log on or off, I will be grateful to know how.

Thanks to all three of you for your inputs.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
It looks like you have your original question answered - Running WP with MSSQL is not possible or a good idea.

Some observations as I run a windows server using IIS with php (Fast CGI) and primarily MSSQL Server.  I also have Wordpress sites with MySql Server as well all running on iis.  Everything works perfect and no issues with some heavy db apps on the sql server side.

One of the issues I have found when trying to run apache alongside iis is you have to allocate a fixed amount of memory to apache and depending on how much RAM you have / require, that could be an issue.  Locally for dev, I have iis and run php, sql server and mysql/wordpress. I removed any of the apps that require apache from my server.

If your requirement is to use a CMS and MSSQL server, look for other options like Modx https://modx.com/ or One of the CMS apps for Laravel.
0
gr8gonzoConsultantCommented:
However, unfortunately, I have to disagree with your assertion that once xampp is started, it stays on whether you log off or not.

Remember that XAMPP is simply one of the pre-packaged installers and it may not install its components as WIndows services by default. In fact, any installer (XAMPP or otherwise) that claims to be portable will NOT install the servers as Windows services because that would make them non-portable.

You can follow those same instructions I laid out for installing Apache and MySQL as Windows services - you'd just find the Apache and MySQL exe files somewhere within the XAMPP distribution and the same commands would apply (the "-k install" for Apache and "--install" for MySQL). Once they are services, you can set their Startup Type to Automatic and they'll run all the time.

All that said, I'd recommend not using XAMPP. I'm not sure if the bug is fixed yet or not, but for a while they had a significant problem with their SSL implementation in the copy of Apache that they were distribution, where it was VERY unstable, especially if you used any OpenSSL extensions/functions within PHP scripts. I usually just do each component manually (so I don't have to deal with any extra unwanted branding or any unwanted components from XAMPP), but if you don't care about any of that, then XAMPP might be okay.

If you need a pre-packaged installer, then I've had decent luck in the past with EasyPHP DevServer, but again, it's just an installer. Really, the best way is to do each piece individually so you know exactly what is getting installed and it makes it straightforward to update those components in the future.
0
gr8gonzoConsultantCommented:
@Scott:
One of the issues I have found when trying to run apache alongside iis is you have to allocate a fixed amount of memory to apache and depending on how much RAM you have / require, that could be an issue.

I don't really follow that. IIS, Apache, and PHP all enforce memory limits, but that's a good thing. That helps prevent a runaway script from taking up all the RAM on the system and causing issues for the rest of the system processes. I'm not sure where you're seeing that Apache is somehow different in that regard, but I might just be missing something.

On a production site, I'd never suggest running anything more than what's absolutely necessary, so either run IIS or run Apache, but not both. I love a lot of Microsoft things, but I've never liked IIS, especially when combined with PHP. It could be just anecdotal evidence, or maybe it's specific to a particular PHP extension, but I've just seen a lot of people getting weird crashes and resource leaks with IIS and PHP together.
0
Dave BaldwinFixer of ProblemsCommented:
As I said, 'phpinfo()' is a function that is built into PHP.  This page tells you about it and shows how to use:  http://php.net/manual/en/function.phpinfo.php
0
sammySeltzerAuthor Commented:
As I said, 'phpinfo()' is a function that is built into PHP.

I get that Dave but like everything php, there is got to be a file somewhere you can open to do some debugs, etc.

What if php is manually installed, then it is no longer built into which means you create your own phpinfo() file.

So, sometime is off.
0
gr8gonzoConsultantCommented:
Sammy,

The phpinfo() function is a core function. It is a part of the PHP engine itself, it is not specific to any installer. You can literally go to any site in the world running PHP, open up a new file and put one line of code into it:

<?php phpinfo();

Open in new window


...save and view/run that file and you'll get that whole PHP info screen. It doesn't matter what the file is called, although almost everyone seems to call their file either phpinfo.php or info.php. It's a shortcut function for debugging. It has nothing to do with XAMPP or the web platform installer or anything else.

Go and search Google for this exact string (quotes included):
"Additional .ini files parsed" "Loaded Configuration File" "SCRIPT_URI" "PHP License"

You should see several results that come back of people on all sorts of different servers and platforms that have a public copy of their phpinfo page (which is a bad security practice).

If you want to see the source code behind the phpinfo() function itself (to see how the PHP engine builds that page), then you can download the full, uncompiled source code for PHP from the site and it'll be in there.
0
Dave BaldwinFixer of ProblemsCommented:
I get that Dave but like everything php, there is got to be a file somewhere you can open to do some debugs, etc.
No.  Read the page.  http://php.net/manual/en/function.phpinfo.php
0
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
This question is sort of rambling off into Neverland.

Original question is...

"We have been struggling for the past three days trying to connect wordpress to sql server using iis."

Answer is, WordPress requires MariaDB/MySQL + will not work reliably with any other SQL based server.
1
gr8gonzoConsultantCommented:
Any updates, Sammy?
0
sammySeltzerAuthor Commented:
Sorry all, I had a death in the family that forced me out of the country.

At this point, we have decided to use the  projectNami which has successfully allowed us to connect to sql server in azure.

Thanks all for your assistance with this.
1
gr8gonzoConsultantCommented:
I'm sorry to hear about the death in the family.

Out of curiosity, what's the rationale for trying to go down the path of SQL Server?

For others who might come across this and be trying to the same thing - unless this is an unimportant project or site (e.g. a site about a hobby or something), then you're investing into a product that is only actively supported by two developers. Should either one or both of them decide to give up on the project (which can happen if their lives fill up with higher priorities, since the free product very likely does not earn them any income), then you may be stuck in a situation where you're getting farther and farther away from the security updates and patches.

The internet is filled with abandoned projects that were once just like this one, and equally filled with posts from people who were left high and dry by the project abandonment, forcing them to look for yet another alternative or figure out some way to migrate back to the original.

All that said, it's your risk to take, but when the day comes that Project Nami suddenly stops seeing new releases and you're getting pressure to patch the site (or you get hacked), don't say we didn't warn you.
0
sammySeltzerAuthor Commented:
I'm sorry to hear about the death in the family.

Thanks a lot @gr8gonzo.

No, this is NOT an unimportant project.

Actually, we are redesigning our intranet.

Management suggested and we agreed that we need to connect WordPress to SQL Server.

It is not just two guys who came up with this idea of connecting WordPress to SQL Server.

Several others have done so successfully.

A former co-developer at a former company has done so successfully as well.

projectNami appears to be the only one so far who came up with addins.

My next phase of the ongoing project is to ensure that I can successfully implement suggestions that so many others have tried and succeeded like this guy in his youtube video which I tried but didn't work most likely due to a step or two I may have missed.

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

I have also read several articles of folks successfully connecting wordpress to sql server.

All of them could not have hated themselves to throw away their reputations with false information.
0
gr8gonzoConsultantCommented:
It is not just two guys who came up with this idea of connecting WordPress to SQL Server.
That's not what I said. I said that Project Nami specifically is a free project maintained solely by two developers, which puts this specific project at a high risk for future abandonment. I am not guaranteeing that it will become abandoned (time travel hasn't been invented yet), I'm simply saying there's a high risk of that happening, based on a LOT of established history of projects with the same characteristics (I was about to go into some specific numbers but this is getting long already - if you actually doubt the high risk of abandonment, then that can be a follow-up question perhaps and we can go through the exercise of gathering open-source project commit data from GitHub and such).

This is not a question of whether it's possible or not. I fully believe you and believe that there have been lots of people who have been able to get it to work. And a lot of people will post about GETTING it to work, because that in itself is an achievement. Anything is possible if you're willing to modify the core source code (which is what Project Nami does). But GETTING it to work and KEEPING it working long-term are two very different things.

Getting something to work is fine if it's not an important piece of your business, because getting it to work has no future guarantees. If something breaks later, it's okay if it takes the developers a month or two to fix it because it's not critical to your business. Or if the project is abandoned, that's okay because you have plenty of time to get around to finding a replacement or just starting over from scratch again.

If it's a critical part of your business, then downtime is usually unacceptable. It's why businesses will pay money for some services or products that they could get for free - they're ensuring that the developers are being paid to continue work on the product. If the intranet is as important as you say it is, then I find it very hard to believe that your management has fully considered the risks here and has a good handle on the comparisons of technical implementations.

Saying that the management suggested it isn't really a reason for doing it. It's more of a question of WHY they suggested it, and are they technically competent to understand the compromises and risks of their suggestions? Are they relying on you to explain those risks to them?

For example, if Project Nami is abandoned in a year or two, are you willing and able to take over the updates and figure out how to merge the WP core updates into your existing code via some form of a development cycle (e.g. test first, then implement in production)? Are you going to suddenly be the target of the wrath of management, saying, "Why didn't you warn us that this could happen?" Are you willing to bet your job that they wouldn't do that?

Just some personal experiences:

1. I had one semi-technical manager who was simply a Microsoft fanboy, and assumed that our support contracts with Microsoft would cover the products that were based on SQL Server. He pushed the IT team to try and re-code an network inventory application from Oracle to SQL Server, and a bug tracking system from MySQL to SQL Server. It took months of expensive development, and when it was finished, the ONLY change was simply the backend. There was no performance nor any other benefit of any kind, and actually we had to make an expensive adjustment to the backup processes to account for the additional DB counts. Pretty much the only thing that came out of it was that he could look at the data with Enterprise Manager (this was many years ago, when mgmt studio was still called Enterprise Manager) - he didn't want the inconvenience of using separate GUI tools on the rare occasion he actually went into the DB.

2. There was another boss who figured MSDN subscriptions gave him unlimited usage of the products, so he had IT build several projects on the MSDN version of SQL Server. One disgruntled ex-employee reported him to MS and he had to pay a hefty fine and ended up having the team convert all the apps to MySQL because he didn't want to pay the continued license fees for SQL Server. The end result was all the apps performed the same and it lowered backup costs.

3. On at least five different occasions, I've witnessed managers who assumed that their team members would push back on ideas that were technically bad. The team members assumed that the managers were in their positions because they had heavy experience and wouldn't suggest something that wasn't well-thought-out. One of the five managers was an angry guy who didn't like anyone disagreeing with him, so he just forced his will at all times. In all five of the occasions I'm thinking of, the managers passed the blame for failures onto the team, and in 3 of the 5 occasions, someone got fired for it and it was never the manager.

These are personal experiences - I have no idea if or how they relate to your managers here. I'm simply urging caution when being pushed to go down a path that incurs a lot of future risk with little to no technical benefit.
0
sammySeltzerAuthor Commented:
@g8gonzo,

Again, my point about the two developers is that they are not the only one who have successfully connected WordPress to SQL Server.

I do agree with you, however,  because they have to make some core changes to original WordPress files to make it work, an update to WordPress will wipe those changes, great point there.

This is why I said that in phase two of the projects, I will be looking at examples such as the YouTube post I posted earlier where there are no changes to plugins or WordPress themes and it WORKED.

These kinds of examples a durable.

Finally, I agree with you again that simply because management wants it done one way, doesn't mean it should be done that way.

For instance, we built a WordPress that connects to MySQL DB via IIS.

Management wanted it done that way and we did but before we did, we made sure they understand the caveats associated with installing and configuring WordPress with IIS.

However, what I cannot do is go back to my manager and say it is not possible to connect WordPress to SQL Server simply because that is the consensus response I have gotten from you guys here.

A more acceptable responses are the ones you just posted where we let me management know that yes, we can do what you are asking us to do but here are some vices you should be aware of.

If they insist we do, I know the meaning of being insubordinate and will as instructed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.