Recently at work, I was tasked with finding a method to retrieve data from a third-party SQL Server 2000-based system into our own MySQL-based application. It’s worth noting that both system are behind their own firewalls and I was trying to bear security in mind as I did this.
First, I had to actually get into the system with the SQL Server database. For this, I used Citrix GoToAssist. The company behind the software makes their money off of hosting the servers it uses and selling access to them as a service. The client software uses HTTPS for security and to get around firewall configurations. While their software is nice, I’d be interested to see if anyone knows of any OSS equivalents of it.
Once I had access to the system, I needed something akin to phpMyAdmin or Oracle SQL Developer in order to see what resources were available on the SQL Server itself. For that, I found a local copy of EMS SQL Manager already on the system. This wasn’t the most full-fledged product I’d seen for this type of purpose, but it was free, included everything I needed, and did the job in a pinch.
After doing some reconnaisance on the database structure using the information that had been given to me by the third-party vendor, I downloaded the ZIP archive containing the standard build of PHP 5.2 for Windows. Side note: one of the things I like about PHP on Windows is that, for CLI purposes, it only takes decompressing the ZIP archive, tweaking the configuration file to get the settings and extensions you want, and executing the binary to get it up and running.
With my SQL Server experience being dated by about six years, I started throwing PHP code at the system to see what would stick. I noticed that the server was already set up to accept trusted connections, and being that I was running the script on the local system, this made it likely that authentication wouldn’t present any issues, or so I thought.
I created my PDO instance like so:
$db = new PDO('mssql:localhost;dbname=...');And then attempted to call a particular stored procedure that I’d been told would have some of the data I was looking to extract. I was surprised to get this in response when calling PDO::errorInfo().
Array( [0] => HY000 [1] => 10007 [2] => Could not find stored procedure ... [10007] (severity 5) [EXEC ...] [3] => -1 [4] => 5)
Now I had just been in EMS SQL Manager and seen the stored procedure myself, so I knew it was there. I tried using the sa account, but that didn’t seem to work either. After some digging, I found that I had to create an account for the current Windows user on the current network domain in order to make PHP capable of seeing the stored procedure when using a trusted connection. Once I’d created the account and given it access to execute the specific stored procedure I was trying to call, I tried again and PDO::errorInfo then gave me this.
Array( [0] => HY000 [1] => 10007 [2] => Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier. [10007] (severity 5) [EXEC ...] [3] => -1 [4] => 5)
Apparently there are issues with the MS SQL stand-alone PHP extension and SQL Servers using unicode collation. Some more digging turned up that the only way to get around this was to use ODBC. Once I did that, I found that I was no longer getting an error when trying to call the stored procedure.
$db = new PDO('odbc:Driver={SQL Server};Server=localhost;Database=...;Trusted_Connection=yes;');Though I knew of stored procedures conceptually, my previous experience with SQL Server had never included using them. As such, I wasn’t familiar with the syntax and came across something rather strange while trying to troubleshoot it: if the arguments to a stored procedure are surrounded by parentheses in the query calling that stored procedure, the code silently fails and returns no results.
// $stmt = $db->prepare('EXEC ...(?, ?)'); // Fails silently$stmt = $db->prepare('EXEC ... ?, ?'); // Works as expectedLast on the list of “interesting things I experienced” while on this little trek wasn’t related to the database, but to what was happening when I attempted to push data from the SQL Server machine to our LAMP machine. I was using the PHP serialize() function to encode the data into a string, then using the streams HTTPS wrapper to send it via a POST request to a PHP file on the LAMP machine. This meant I wouldn’t have to poke a hole in the firewall on either side since both were already open to web traffic. When attempting to run the code for this, I got an error on the client side:
PHP Warning: file_get_contents(): SSL: An existing connection was forcibly closed by the remote host.PHP Warning: file_get_contents(https://...): failed to open stream: HTTP request failed!
The cause for this wasn’t immediately apparent. Upon checking Apache error logs, I came across this:
PHP Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 3381676 bytes)
The actual issue had to do with how much data I was trying to send; it was exceeding the value specified in the memory_limit configuration setting, which as it turned out was set to the recommended value for a significantly older version of PHP. After updating it to a more current recommended value and bouncing Apache, all was well.
Hope the details of my experiences prove helpful to someone else. Thanks to those who provided assistance along the way.
