Working with Yakov on this, we saw that for SCOPE_IDENTITY() to work on the SQL Serve side it needs to be in the same "scope" of the INSERT (for example in the same Stored Procedure), see here for reference.

So what Yakov ended up doing was encapsulating the INSERT and SELECT SCOPE_IDENTITY() into a Stored Procedure which returns the newly inserted Row ID, and call the SP via the Adapter, thus inserting the new record and getting back the new ID.

Congratulation to all winners!

And especially to our local representative - @Yuval Golan!

Yuval last year you were 3rd place, and this year up to 2nd... Bravo! @Kevin An beware next year 

Thanks Bob, yes, indeed JDBC/ODBC and using Couchbase's N1QL queries (and JSON results) is another connectivity option.

If someone "wrapped" such connectivity into some Adapter that could also be interesting.