Question Re: Wrong value in connection_property('osuser') in Technology Q&A

Wrong value in connection_property('osuser')

Miro_Marek — Tue, 19 Jan 2021 08:44:32 GMT

I use a program that uses impersonate to log into the SQL Anywhere database. The registration (with integrated login) works fine. The mapped user is selected. The osuser is set correctly when logging in for the first time. But this osuser remains the same for the following logins with different integrated users. The AppInfo also has this old user. Can it be cached somehow? Or is the a possibility to get the originating user for integrated login (login mappings)?

Let me be a bit more precise on what our program is doing:

List item
A thread is started in the Local System user context
When a "piece of work" arrives from "User1"
The thread impersonates as "User1" and opens a database connection using integrated login. This works well.
After the piece of work is done the thread reverts back to Local System
A second piece of work arrives from "User 2"
The thread impersonates as "User2"
Since the users are different, the program does not re-use the existing connection but opens a new database connection, once more with integrated login.
The database connection is opened, User2 is mapped to the correct database user (which is different from the one User 1 is mapped to), but connection_property('osuser') returns "User 1".

Re: Wrong value in connection_property('osuser')

VolkerBarth — Tue, 19 Jan 2021 09:51:46 GMT

But this osuser remains the same for the following logins with different integrated users.

What does that mean, are connections re-used, say via connection pooling?

We are using integrated logins with v16, and the "OsUser" connection property works as expected, i.e. different OS users are mapped to identical database users, and the OsUser property does return the correct OS user name...

Re: Wrong value in connection_property('osuser')

Miro_Marek — Tue, 19 Jan 2021 10:02:16 GMT

Hi Volker, yes. The program use connection pooling. It is a service for e-mail registration. New user is correctly recognized and used for login. Also th binaries recognize correctly the windows user. Only triggers in the database don´t get the right value. The SQL Anywher version is 17.

Re: Wrong value in connection_property('osuser')

Breck_Carter — Tue, 19 Jan 2021 10:29:52 GMT

Please tell us exactly what kind of connection pooling you are using, as discussed in this Help topic Improve Application Performance with Connection Pooling; e.g., SQL Anywhere server side pooling versus .NET versus "other pooling product", etc etc etc.

Re: Wrong value in connection_property('osuser')

Breck_Carter — Tue, 19 Jan 2021 10:36:29 GMT

Just in case you can't get OSUSER to do what you want, setting CON=whatever in the connection string and using CONNECTION_PROPERTY ( 'NAME' ) in the trigger code might help.

Re: Wrong value in connection_property('osuser')

VolkerBarth — Tue, 19 Jan 2021 13:09:34 GMT

Only triggers in the database don´t get the right value.

Is there a possibility there's "database user impersonation" via SETUSER or via nested procedures at work, i.e. that the (second) connection calls procedure that run in different contexts (see SQL SECURITY DEFINER etc.) and thereby are mapped to different users? (Note, even if so, I would not know whether this would affect connection property OsUser.)

Re: Wrong value in connection_property('osuser')

VolkerBarth — Tue, 19 Jan 2021 13:11:04 GMT

After the piece of work is done the thread reverts back to Local System

So that does close the current database connection and give it back to the connection pool?

Re: Wrong value in connection_property('osuser')

Miro_Marek — Tue, 19 Jan 2021 13:41:18 GMT

We actually do not use CPOOL from connection parameter. This is done with custom code. But as I know the database connection stays open until it is needed for another request.

Re: Wrong value in connection_property('osuser')

Breck_Carter — Tue, 19 Jan 2021 14:12:53 GMT

> This is done with custom code

How does that "custom code" work? If you don't know what it is doing, it's unlikely anyone here will know 🙂

To put it another way, that custom code probably holds the key to your problem.

Re: Wrong value in connection_property('osuser')

former_SQLA_member1694901 — Wed, 20 Jan 2021 03:43:52 GMT

No, the connection is kept open for the next piece of work from User 1.

I'm not sure if we are talking about the same thing when mentioning "connection pooling". Our application keeps a set of database connections, to reduce the overhead of reconnecting. Each connection is reserved for use to a specific OS user. After some idle time connections are closed. Though we sometimes call this "connection pooling" (since this is what we do) we do not use the ConnectionPool parameter of Anywhere.

BTW, Miro an I are colleagues, we're both working on this issue.

Re: Wrong value in connection_property('osuser')

VolkerBarth — Wed, 20 Jan 2021 05:53:34 GMT

Each connection is reserved for use to a specific OS user.

Hm, if this is true, why then do you notice wrong OS user names?

If you really do use an existing connection, how would you expect the database server to know that the OS user running the process that uses the same connection has changed in-between? I'm quite sure the APPINFO connection is gathered during the connection's initialization and then kept unchanged.

A further note: What about temporary tables, connection variables and other connection-specific data - are they "cleaned up" when the according thread ends? AFAIK SQL Anywhere does have to do a lot of "cleaning up" when a connection is given back to the connection pool to make it look like a new one for the next usage, and I guess a home-brewn pooling would have to do the same, if that is possible at all...

Re: Wrong value in connection_property('osuser')

former_SQLA_member1694901 — Wed, 20 Jan 2021 10:29:40 GMT

I agree to your first point, I'd also expect that APPINFO always contains the OS user who opened the connection.

But we see the wrong OS user in a newly opened second connection. User 2 opens a new connection (in her own user context), because she isn't User 1, otherwise she'd use the existing one.

Concerning the second paragraph, since every connection is reserved for a specific OS user, it indeed is intended behaviour that nothing is cleaned up! So nothing has to be set up again when the next request comes in.

Just as background: this implementation originates many years ago, from a time when (AFAIK) connection pooling was not yet supported in Anywhere.

Re: Wrong value in connection_property('osuser')

VolkerBarth — Wed, 20 Jan 2021 10:45:05 GMT

Is there any login_procedure "magic" at work?

You may also try to diagnose the logins via Connect/Disconnect events and/or the LOG connection parameter...

Re: Wrong value in connection_property('osuser')

Breck_Carter — Wed, 20 Jan 2021 14:16:03 GMT

> this implementation originates many years ago, from a time when (AFAIK) connection pooling was not yet supported in Anywhere.

Thank you for that critically important information.

If you have access to the custom code which implements the connection pooling, please show us the actual code that establishes a new connection to SQL Anywhere that ends up with the wrong value being reported by CONNECTION_PROPERTY ( 'OsUser' );

If you don't have access to that code (i.e., it is a black box to you) then Volker's suggestions about adding diagnostic code on the database side are good.