trying the sql server driver for php on windows 2008 - part 2

Overview of the complete function list.
Details to follow, but just from the function list you may see, that a simple search-and-replace will do the work to switch from mssql to sqlsrv-functions in a very simple case only.

old php driver (mssql_)	sqlsrv php driver	Description
Server and Connection
mssql_connect	sqlsrv_connect	Open MS SQL server connection / Creates and opens a connection.
mssql_pconnect	-	Open persistent MS SQL connection
mssql_close	sqlsrv_close	Close MS SQL Server connection / Closes a connection. Frees all resources associated with the connection.
mssql_select_db	-	Select MS SQL database
-	sqlsrv_configure	Changes error handling and logging configurations.
-	sqlsrv_server_info	Provides information about the server.
-	sqlsrv_client_info	Provides information about the client.
-	sqlsrv_get_config	Returns the value of the specified configuration setting.
mssql_min_error_severity	-	Sets the minimum error severity
mssql_min_message_severity	-	Sets the minimum message severity
querying data
mssql_query	sqlsrv_query	Send MS SQL query / Prepares and executes a Transact-SQL query.
mssql_free_statement	sqlsrv_free_stmt	Free statement memory / Closes a statement. Frees all resources associated with the statement.
mssql_bind	-	Adds a parameter to a stored procedure or a remote stored procedure
mssql_data_seek	-	Moves internal row pointer
mssql_execute	sqlsrv_execute	Executes a stored procedure on a MS SQL server database / Executes a prepared statement.
mssql_rows_affected	sqlsrv_rows_affected	Returns the number of records affected by the query / Returns the number of modified rows.
mssql_result	-	Get result data
-	sqlsrv_fetch	Makes the next row of data available for reading.
mssql_free_result	-	Free result memory
	sqlsrv_prepare	Prepares a Transact-SQL query without executing it. Implicitly binds parameters.
mssql_next_result	sqlsrv_next_result	Move the internal result pointer to the next result / Makes the next result available for processing.
mssql_init	-	Initializes a stored procedure or a remote stored procedure
-	sqlsrv_begin_transaction	Begins a transaction.
-	sqlsrv_commit	Commits a transaction.
-	sqlsrv_cancel	Cancels a statement; discards any pending results for the statement.
-	sqlsrv_rollback	Rolls back a transaction.
getting results
mssql_fetch_array	sqlsrv_fetch_array	Fetch a result row as an associative array, a numeric array, or both / Retrieves the next row of data as a numerically indexed array, an associative array, or both.
mssql_fetch_assoc	-	Returns an associative array of the current row in the result
mssql_fetch_batch	-	Returns the next batch of records
mssql_fetch_field	sqlsrv_get_field	Get field information / Retrieves a field in the current row by index. The PHP return type can be specified.
mssql_fetch_object	sqlsrv_fetch_object	Fetch row as object / Retrieves the next row of data as an object.
mssql_fetch_row		Get row as enumerated array
-	sqlsrv_fetch
Metadata
mssql_field_length	-	Get the length of a field
mssql_field_name	-	Get the name of a field
mssql_field_seek	-	Seeks to the specified field offset
mssql_field_type	-	Gets the type of a field
mssql_num_fields	sqlsrv_num_fields	Gets the number of fields in result / Retrieves the number of fields in an active result set.
mssql_num_rows	sqlsrv_num_rows	Gets the number of rows in result / Reports the number of rows in a result set.
	sqlsrv_field_metadata	Returns field metadata.
	sqlsrv_has_rows	Detects if a result set has one or more rows.
Error-handling
mssql_get_last_message	-	Returns the last message from the server
-	sqlsrv_errors	Returns error and/or warning information about the last operation.
Other
mssql_guid_string	-	Converts a 16 byte binary GUID to a string
-	sqlsrv_send_stream_data	Sends up to eight kilobytes (8 KB) of data to the server with each call to the function.

trying the sql server driver for php on windows 2008 - part 1

We are talking about a Windows 2008 Server 64 bit installation here and trying to set up apache (with ssl), php and a php application that uses a connection to a MS SQL Server (2005 or higher).

Another prerequisite: we want to use the most recent available versions for all involved products.
(it's February 2010 so we are using Apache 2.2.14, php 5.3.1, w2008SR2).

I will try to point out some of the points that you may stumble upon (used apache/php/sqlsrv file-versions, comparison of mssql_* vs. sqlsrv_* driver functions, specific issues with the sqlsrv_* functions) in several blog posts over the next few weeks.

So let's go.
You may have noticed that the php_mssql.dll driver on windows does no longer work with php 5.3 or higher and even with php 5.2.11 (upon apache start you get these typical "file not found" errors that just point out that the module can not be loaded for some reason.
It has not been forgotten and it will not come back in a next version afaik.

Basically you have 3 options:

1. You can give up here and switch back to php 5.2.10 or an older version. And I definitely advise you to do so if you don't want to get in lots of troubles...
2. switch the webserver part of your application to linux: if you use the freetds driver (that just replaces the original mssql_* functions 1:1) you are done and you will have advanced sql server functionality in this driver (utf8 support etc.).
   
3. rewrite your application to use the new and opensource sql-server-driver for php that was written by Microsoft (you are reading right here, MS is writing an opensource driver for php)! The thing is: They dont' just take over the mssql_* functions like freetds does. The functions all start with 'sqlsrv_'. And they have (in some cases) completely different semantics. It's not just a search and replace work, it's a lot to do!
4. We are not talking about php database abstraction layers for php (DBA, ODBC, PDO, whatever) and functions here. But this may also be an option for you.

We will focus on option 3.

First issue: Installing the Server environment

So you download the latest apache from an apache mirror, download php, the sql server driver and start?
Uhm... not really. Let's see:

1. We will start by downloading php binaries.
   But wait: For php 5.3 there are 12 different possible files to download. And they are all x86? So wich is the right one?
   If you prefer the installer, the zip or the debug-zip is a personal-flavour thing (unless you are developing php itself I don't recommend the debug-zips). I use a zip-File, because I want as much control as possible.
   We will use a VC9 file. That means: it has been compiled using visual studio 2008 (the most recent Visual Studio version). This is very important to keep in mind for later!
2. Now we need to find an apache binary that will work with the downloaded php version.
   I recommend to try apachelounge, (read the first sentence on the donwload page wich states, that the donwloadable binary is compiled with VC9 also, but the original apache binaries from apache.org are still compiled with VC6!)
3. Now we go to the MS Download page to get the SQL Server driver for PHP binary. The current version is 1.1.

You think you can start now? Uhm... not really. We have to download some additional components to make everything work:

• The SQL Server driver for php is actually just a wrapper for the SQL Server Native Client database connector driver. So we need the Native Client driver as well. There is a standalone version available, but if you are running the DB (SQL Server 2005 or higher) on the same host you already have this component installed. For all others: Download it from the SQL Server 2008 Feature pack download page (scroll down to "Microsoft SQL Server 2008 Native Client"). You can use the x86 or the 64 bit version. I will use the 64bit sqlncli.msi (be careful if you download both: there is no difference in the file name!)
• Now as we are using software that has been compiled using Visual Studio, we need the right version of the VC runtime environment: Microsoft Visual C++ 2008 Redistributable Package (x86)
• and (if you have downloaded the 64-bit Native client) the Microsoft Visual C++ 2008 Redistributable Package (x64) as well (!).
  Be sure to not mix up the different versions (there are also 2005- Versions and 2008 SP1 versions available - but these do not work in our environment)! Again: the download filenames are the same (vcredist_x64.exe / vcredist.exe for all versions), so be careful!

Remember to check the certificate or md5 sum of the downloaded files (to check the file hashes I use the windows hashtab explorer extension - the coolest thing ever ;-)
And install the components.
Now you can install the apache, include php and load the (right) sql server driver file via php.ini. According to what we have downloaded we will use the VC9 thread safe php 5.3 file, that is extension=php_sqlsrv_53_ts_vc9.dll
Maybe use the absolute path to where you unzipped the files.

If you now run php info, you will get the
sqlsrv support enabled
block somwhere at the end.

That's it for today. Next time we are comparing the availabe functions (sqlsrv vs mssql).

One short note: I will not be able to give support (unless you pay me for). However if you post a comment, other people may know the answer and post it as comment as well.

google squared - some (not so) artificial intelligence

Google squared seems to be the answer to wolfram alpha. The difference is, that for google squared the content ist searched from the internet automatically. This can be very nice and sometimes even perfect. However almost every time you try something you will start laughing. Making it obvious that we are still miles away from something that could be called "semantic web".
For example try to square for "states". What do you expect? A listing of all 52 US states with some detail info, didn't you? Thats what seems to happen on the fist few pages, but when you scroll onto the last page, you find Estonia, Russia and Finnland in the list. QED. ;-)
One additional note: if you think it's better to search for "us-states", you are right. However there is another funny exception in the list. And this one is actually a lot more intuitive...

Identity bus - yet another authentication abstraction layer?

At EIC there was a very interesting panel discussion regarding an „Identity bus“ idea and how to concretisize it (http://www.id-conf.com/sessions/562).

I just want to pick some small parts and write about some of the thoughts I catched from the discussion and what came to my mind afterwards.

First: there are some different understandings about what an Identity bus should be in detail. However the main idea is quite clear: to get rid of the authentication and authorization mess for any application: Just plug in your application into the Identity Bus and the users could immediately login and authorize with the application. So this means to introduce another general abstraction layer (for authN/authZ only) between the Application and the Identity providers.

This should be a protocol (because that's what is working on bus'es). Or what else could it be?

The bus should provide authentication, authenticated sessions (as token, whatever). It also should contain routing, caching, and mapping mechanisms. We are not talking about a real physical bus here (so you don't have to add another hardware device to your server). It is a virtual, software based bus. And it should work not only on a LAN base but across the Internet.

Now some additions that have not been part of the discussion (in detail):

There could be the following device types plugged in into the Identity bus:

• identity providers

• service providers

• auditing and logging devices (logging everything that is going on on the bus)

• network components: gateways/gatekeepers for routing, (auto)-registration of the devices, mapping/translation and caching.

The bus should also contain a logout mechanism (global/local session kill).

Let's take this a bit further by adding some ideas (however I think that these are not really new, I just didn't have the time to grab for them on Google).

I think for the authentication/authorization-part shibboleth can be seen as a start of such an identity bus. It is even called “authentication middleware”. In most of the cases shibboleth uses SAML for authentication/authorization handling, but it has all other opportunities built in (infocard,kerberos,pki,...).

But there are some problems that currently prevent shibboleth from being a real “identity bus”:

• the metadata problem (which component is allowed to do what in which way – it's kind of a device/component level AuthN/AuthZ problem).

• the WAYF ("where are you from") problem which makes the logon an (at least) 2-step procedure.
  These two fix shibboleth based solutions to a federative context.

• The "silo" problem: You may get verified and true information/attributes about a users identity at the moment of logon but when the user did not log on a while you can not be sure if this attributes are still correct (or if the user still exists).
  

So there is no (real/easy) scalability in the current implementation of shibboleth and the federative AuthN-approaches in general. This is why the big vendors are laughing about it.

But however I think the problems can be solved:

• the wayf problem: to get rid of this, you need to add the authentication target (where to authenticate) to the 1^st authentication step. So we need kind of an additional factor. If we are working with some authentication device (pki,smartcard,yubikey next generation etc.) this should be an easy thing although there might be high costs on adapting the hardware-based infrastructure.

• The metadata problem: This is the biggest part. The first step could be to implement kind of on demand metadata requests or to somehow make the metadata more dynamic and federation-independent. There are already some thoughts in this direction. However the basic question "whom shuld I trust" has to be answered somehow. We definitely need a chain of trust for that. It's already used in federative contexts, and it is also used on a global level for the SSL-certs and does work quite well.

• The "silo"-problem can be solved with a bus based architecture quite easily, but only by a (daily, whatever) check-back, or by "broadcasts" from the IDPs.
  

There are still lots of other open topics. But I think the Identity Bus idea can be a leading direction for the ID-based development in the next few years. And it is, in fact, a lot more than just another abstraction layer.

European Identity Conference

Being a speaker presenting the (comparatively small) SAP IDM implementation at my employer I have been staying at the EIC 2009 for two days.
Very interesting tracks and talks! Some buzzwords: SAML 2, Identity bus (I am going to write a comprehensive post about this one soon), strong authentication, GRC (wich one could say to be "strong authorization").
The main problem is still to make authorization simple for the user. There are a lot of possibilities now but they are still not used at a large scale.
SAML seems to become the standard protocol for cross-domain authN/authZ.

The SWITCHaai federation has been mentioned several times so it definitely has to be presented next year in more detail.

another high quality weekend

Let me take a huge jump from IdentityCamp Bremen to another event that was high quality, but completely different: I am talking about the "schweizer Gesangfest 2008" in Weinfelden. I haven been there on Friday Juni 13, with the male choir of our village (Männerchor Dittingen). For our presentation we got the highest possible rating ("vorzüglich"). This was really a surprise.

But the most impressive thing on that day was the concerto with the "Choir des Jeunes de Lausanne". I never ever heared such clear and grandiose voices. If you ever have the opportunity to listen to them, do it! They even have some non-professional mp3 live recordings to download on their site.

IdentityCampBremen outcomes

First of all: thank you Ralf & co. for organizing this fantastic event! Many new thoughts and ideas are continuosly jumping around in my head!
All the web2.0 IDM stuff is still very much under development.

Because I was the only one contributing something about shibboleth, I had some very interesting discussions. For example about interoperability between CardSpace and shibboleth (but did not know much about it).
I just found some interesting (but rather old) blog entries that shibboleth is planning to integrate Cardspace support.
https://mail.internet2.edu/wws/arc/i2-news/2007-05/msg00009.html
http://www.identityblog.com/?p=779 (by Kim Cameron!)
And, yes: cardspace support is indeed integrated into shibboleth 2.0 as a plugin (but still in alpha state): https://spaces.internet2.edu/display/SHIB2/SP+Infocard
However I don't know yet if there is any productive implementation. I'll try to set up a testing environment.

Another theme that is worth mentioning is the problem with openID that it supports just a few attributes. There are some ideas to extend this (see for example http://axschema.org), however it is impossible to find an attribute set and attribute definitions that fit all needs. I am going to post some thoughts that I firstly wrote just by mail but I think are worth mentioning (sorry, it's german):


Es ist in der Tat nicht so einfach, ich hab aber hier etwas gefunden, was vielleicht interessant sein könnte. Ist aber fast zu ausführlich und leider nicht aktuell (2005 :-( .

http://middleware.internet2.edu/dir/docs/draft-internet2-mace-dir-higher-ed-person-analysis-latest.htm
Zwei Profs haben sich die Mühe gemacht, zu analysieren, wo (im higher education Bereich) welche zusätzlichen LDAP-Attribute genutzt werden.
Offenbar aufbauend auf die LDAP-Attribute aus RFC 4519 http://www.faqs.org/rfcs/rfc4519.html

Das ist alles natürlich rein LDAP und garnicht openID spezifisch.
Und das Resultat ist auch recht entmutigend:
jeder macht weitgehend, was er will.

Man kommt in der Tat langsam darauf, warum bei openID zunächst mal nur so wenige Attribute verwendet werden: alles andere ist noch schwerer zu vereinheitlichen und funktioniert, egal wie man es definiert, nur in bestimmten Bereichen (leider meist geographischen).

Vorläufig sehe ich noch keine Lösungsmöglichkeit (die nicht irgendwelche zentralistischen Merkmale hätte).
Vielleicht muss man in die Richtung meta-descriptions weiter denken, so wie axschema mit ihren xml-descriptions angefangen haben.
Vielleicht könnte man innerhalb einer (axschema oder was auch immer) Attribut-Definition zusätzliche tags wie "comparableAttribute/relatedAttribute" mit (ggf.) irgendwelchen Transformationsregeln (xslt oder regex) definieren...

Und eigentlich sind wir jetzt schon mitten im Bereich DataPortability und/oder semantic web :-)