| Calling stored procedure on remote SQL Server machine with PDO [message #175053] |
Mon, 08 August 2011 23:12  |
Marchand
Messages: 1
Registered: August 2011
Karma: 0
|
Junior Member |
|
|
Both our Web server (Server A, running IIS 7.0) and the server running
SQL Server (Server B) are Windows Server 2008 boxes, both in the same
domain. I'm trying to find a way to have a PHP file on Server A
execute a SQL stored procedure on Server B, and failing miserably. Any
advice would be most welcome -- the code is below.
Some observations:
> Setting the $uid and $pwd values to the "sa" parameters for the database don't work; nor does setting them to a domain account with appropriate rights within SQL Server. I generally get the idea that it's the service running PHP which actually executes the code, but since IIS is running under a local Server B account -- and that can't change for political reasons -- I'm looking for a way around that. It seems that the $uid/$pwd are effectively ignored when calling into a remote server (?!).
> Since IIS is running under a local account, Server B can't "see" Server A so as to add users on that machine/domain into groups on Server B on either the Windows or the SQL Server level. (Or at least I can't find a way to do that).
> Once things are sorted out here, the application will be moved to an iMac as the Web server, hence the desire to use PDO. Other suggestions are welcome, of course, particularly if they solve the core problem.
Finally: If the stored procedure is a SQL "SELECT" statement, things
actually work OK, but anything more complex and all I get is a 01000
response code, with no obvious way to extract the "WITH_INFO"
information (advice welcome here as well). What I'm hoping to find is
a general purpose way to execute any stored procedure on Server B from
a PHP file running under IIS on Server A -- passing whatever
parameters/certificates which are needed. Is there a simple way to do
this, or do I have to wade through the Kerberos "double hop" maze?
(Hope not both for my own sanity and given OSX/OpenDirectory will soon
be in the mix.
Thanks!
--------------------------
try {
$conn = new PDO( "sqlsrv:server=$serverName;Database = $database",
$uid, $pwd);
$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
}
catch( PDOException $e ) {
die( "Error connecting to SQL Server" . ": sqlsrv:server=
$serverName;Database = $database" . " : " . $uid . " : " . $pwd );
}
$stmt = $conn->prepare("{ CALL spBackup2}");
try
{
$stmt->execute();
echo $stmt->errorCode();
}
catch( PDOException $e2)
{
echo $e2->getMessage();
}
----
|
|
|
|
| Re: Calling stored procedure on remote SQL Server machine with PDO [message #175097 is a reply to message #175053] |
Sun, 14 August 2011 19:29  |
Heiko Richler
Messages: 2
Registered: January 2011
Karma: 0
|
Junior Member |
|
|
Am 09.08.2011 01:12, schrieb Marchand:
> Both our Web server (Server A, running IIS 7.0) and the server running
> SQL Server (Server B) are Windows Server 2008 boxes, both in the same
> domain. I'm trying to find a way to have a PHP file on Server A
> execute a SQL stored procedure on Server B, and failing miserably. Any
> advice would be most welcome -- the code is below.
I can call Stored Procedures from PHP 5.3. Can you execute the SPs using
the same Login in SQL Server Management Studio?
dsn='sqlsrv:server=Sql;Database=Northwind;';
$db = new PDO($dsn, $user, $password);
$sql = 'Exec MySQL:PID, :LSTR, :PSTR';
$sth = $db->prepare($sql,
array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->bindValue(':PID', $PID, PDO::PARAM_INT);
$sth->bindValue(':LSTR', $values['LSTR'], PDO::PARAM_STR);
$sth->bindValue(':PSTR', $values['PSTR'], PDO::PARAM_STR);
$sth->execute();
> Some observations:
>> Setting the $uid and $pwd values to the "sa" parameters for the database don't work; nor does setting them to a domain account with appropriate rights within SQL Server. I generally get the idea that it's the service running PHP which actually executes the code, but since IIS is running under a local Server B account -- and that can't change for political reasons -- I'm looking for a way around that. It seems that the $uid/$pwd are effectively ignored when calling into a remote server (?!).
My IIS is runing a none AD-User, too. I am using a SQL-User on the
SQL-Server.
>
>> Since IIS is running under a local account, Server B can't "see" Server A so as to add users on that machine/domain into groups on Server B on either the Windows or the SQL Server level. (Or at least I can't find a way to do that).
>
Did you give Execute rights to that user on the Stored Procedures?
http://msdn.microsoft.com/en-us/library/ms345484.aspx
Heiko
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]