执行OPENQUERY从MySql Server中提取数据并使用PDO从php脚本将其连接到SQL表？

Question

I have been facing an issue after another wile trying to access data from MySql Server from SQL server. Here is what I have done so far.
1) installed MySQL ODBC Driver 5.2w on my SQL Server 2) I created a link server from Microsoft SQL Server Managment S (MSQLSMS) following the instructions on this site http://sql-articles.com/blogs/creating-linked-server-to-mysql-from-sql-server/

Now in my MSQLSMS I opened a connection to a server called "SERV1" I executed the following query and it is working with no problem.

SELECT
p.team_name,
p.fullname,
SUM( ISNULL(i.CallDurationSeconds, 0) ) AS totalTime
FROM OPENQUERY(SERVPHP, 'SELECT
                        CAST(t.name AS CHAR) AS team_name,
                        CAST(TRIM(REPLACE(CONCAT(su.first_name, " ", su.middle_name , " ", su.last_name), " ", " ")) AS CHAR) AS fullname,
                        CAST(su.login_user AS CHAR) AS username,
                        CAST(p.account_id AS UNSIGNED) AS account_id
                        FROM call_managment_system.phone_calls AS p
                        INNER JOIN call_managment_system.users AS su ON p.owner_id = su.user_id
                        INNER JOIN call_managment_system.teams AS t ON su.team_id = t.team_id
                        WHERE p.status = 2 AND t.client_id = 1 AND p.last_attempt_on BETWEEN "2013-01-01 08:00:00" AND "2013-04-30 07:00:00"
                        GROUP BY t.team_id, p.owner_id') AS p
INNER
JOIN OPENQUERY(SERVPHP, 'SELECT
                        CAST(cn.contact_number AS CHAR) AS phone_number,
                        CAST(cn.account_id AS UNSIGNED) AS account_id
                        FROM call_managment_system.contact_numbers AS cn
                        WHERE cn.contact_link = "Account"
                        UNION
                        SELECT CAST(cn2.contact_number AS CHAR) AS phone_number,
                        CAST(cn2.account_id AS UNSIGNED) AS account_id
                        FROM call_managment_system.contact_personal AS cp
                        INNER JOIN call_managment_system.contact_numbers AS cn2 ON cn2.person_id = cp.person_id AND cn2.contact_link = "Account" ') AS cn ON cn.account_id = p.account_id
INNER
JOIN I3_IC.dbo.CallDetail AS i ON p.username = i.LocalUserId AND RIGHT(i.RemoteNumber, 10) = cn.phone_number
WHERE i.I3TimeStampGMT BETWEEN '2013-01-01 08:00:00' AND '2013-04-30 07:00:00'
GROUP BY p.team_name, p.fullname

Now Since I am using PHP to execute this query. I have done the following 1) using PDO api to connect to the SQL server "SERV1" where I executed this query before. 2) try to execute that same query listed above. But this time I get error where I am not able to solve.

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SERVPHP"

Note that "SERVPHP" is the server where my PHP application is running and my MySql Server is running. "SERV1" if where my SQL server is running and where I installed the ODBC driver.

More, When I run this query the same way from my PHP application it works

SELECT
LocalUserId AS loginName,
RemoteNumber AS PhoneNumber,
SUM(CallDurationSeconds) AS totalTalk
FROM CallDetail
WHERE LocalUserId = 'test' AND
I3TimeStampGMT BETWEEN '2013-01-01 08:00:00' AND '2013-04-30 07:00:00'
GROUP BY LocalUserId, RemoteNumber

Why is it now working? What do I need to make this query execute from my PHP server?

Thanks

Answer 1

Using the suggestions in the comments above, I discovered it was a permission issue.

I had to go to the Linked Server Properties and clicked on the "Security" menu on the left. Then I had to add a user and a password. I also selected "Be made using the login's current security context".