ZF2和SQL服务器'以用户身份执行'

Question

Currently we are running Zend Framework 2 and SQL server for our internal application. We have a stored procedure in place for our AuditTrail. This AuditTrail include a AuditUser who made the change/insert. At this moment this "AuditUser" is username of PDO connection.

We are working towards a situation to change this AuditUser is the active user who made the change and would like to use EXECUTE as USER='[REALUSER]'. And their strange behaviour starts. When executing this statement, everything is doing fine. But when I refresh the page, SQL server returns following errors:

Fatal error: Uncaught exception 'PDOException' with message
'SQLSTATE[HY000]: [Microsoft][SQL Server Native Client 11.0]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.'

Zend\Db\Adapter\Exception\InvalidQueryException: Statement could not be executed (08S01233 - [Microsoft][SQL Server Native Client 11.0]Shared Memory Provider: No process is on the other end of the pipe.

And after refreshing again, it's executing fine again.

I think it has something todo with session from SQL/PDO(http://msdn.microsoft.com/en-us/library/ms181362.aspx, says session is switched). But I'm unable to resolve this issue. Could somebody help or suggest a way forward?

Regards,

Pim

Answer 1

For those of you who experienced the same problem - this link helped me: https://blogs.msdn.microsoft.com/brian_swan/2010/11/17/sql-server-driver-for-php-connection-options-connection-pooling/

If you are using sqlsrv function, as I am, the only thing you need to do is setting ConnectionPooling to false, as described in given link:

e.g. $options = array("Database"=>"master", "UID"=>"user", "PWD"=>"password", "ConnectionPooling"=>0);