Configure SP to use SQL Server as SQL Datasource

[Tyler Ashbaugh]

Does anyone have experience configuring SimpleSAMLPHP with a sql store type utilizing the sqlsrv driver for storing session information in SQL Server?

Inside of config.php I have parameters setup like this:

'store.type'                    => 'sql',

'store.sql.dsn'                => 'sqlsrv:Server=DBServer-1,1433;Database=SAMLDB',

'store.sql.username'     => 'dbuser',

'store.sql.password'      => 'dbpassword',

'store.sql.prefix'            => 'SAML',

I have other PHP applications on the same server as SimpleSAMLPHP that are communicating with this specific SQL Server database and do not have any issues. 

With this configuration, when I utlize the Test Authentication Sources and click on my named SP I am appropriately redirected to the IDP. When I login to the IDP I am then redirected back to the SP but am presented with a State Information Lost page. 

When I inspect the database no new tables have been added with a prefix of SAML. No PHP errors are logged and nothing is in the logs directory of SimpleSAMLPHP. 

If I switch the store type back to phpsession then I the redirect back to the SP works and I have a valid session. 

What else can I look at to help determine a root cause?

Thanks in advance for your help.

[Tyler Ashbaugh]

Upon a tip from a friend, I checked the Windows Event Viewer where additional error messages had been logged. This error seems to be the root cause for sessions not getting stored in the SQL Server database:

SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Implicit conversion from data type nvarchar to timestamp is not allowed. Use the CONVERT function to run this query.

[Tyler Ashbaugh]

I created a pull request with a fix for this issue.  https://github.com/simplesamlphp/simplesamlphp/pull/142