When connecting to a remote server (e.g., proxy table) via an ODBC DSN, does the middleware ever make use of a user id and password that is recorded in the DSN registry entry?

I think the answer is no, not even for a SQL Anywhere remote server... you must either define an EXTERNLOGIN or specify UID and PWD values in the remote server connection string.

If you do neither, it will use the user id and password that was used to make the current connection (on the source or client database).

Related question: How can DSN EncryptedPassword / ENP values be handled?

asked 19 Apr '10, 15:01

Breck%20Carter's gravatar image

Breck Carter
25.8k428592852
accept rate: 20%


First, I am going to assume that the "middleware" that you are referring to w.r.t. ODBC will be ODBC driver manager or similar transport software...

The UID (user id) and PWD (password) fields of the ODBC DSN is, by definition, the information that is used by the remote server to determine the credentials of the connection and therefore it does not have any meaning to the "middleware".

This is not to say that someone would not be able to write a piece of software that could do something with the UID/PWD data (e.g. record it in a log file) because this could certainly be done.

So to answer your primary question, in my limited knowledge of the middleware to which you refer, I do not know of any middleware that uses it... other than to possibly record it in a trace log (and even then, the password value is typically written as asterisks so as to not divulge the actual value).

To answer your related question, SQL Anywhere decrypts the SA's connection DSN's encrypted password (ENP) value when the connection is made to the SA server and therefore it knows the unencrypted password value and can pass it to the remote server if needed (i.e. there is no password in the remote DSN and/or no extern login defined for the remote). But note that the SA server knows nothing about what is in the DSN that is given in the remote/proxy server connection - it is up to the ODBC driver manager and/or driver to read the DSN and handle its contents.

permanent link

answered 19 Apr '10, 17:38

Mark%20Culp's gravatar image

Mark Culp
23.0k9130270
accept rate: 40%

Sorry to muddle things... by middleware I mean OmniConnect etcetera... I know it's built in now but historically it's still middleware to me :)... I think Omni does not let the ODBC driver manager use the user id and password stored in the DSN even if no user id and password is provided in the EXTERNLOGIN or connection string. I think it provides (for some unfathomable reason) the client-side user id and password... at least that's what the Help for EXTERNLOGIN says. The logic behind that escapes me.

(19 Apr '10, 18:27) Breck Carter

When a DSN is used, an ODBC connection string is composed by OMNI in the form of DSN=dsnname;uid=username;pwd=passwordvalue and then it is left up to the driver manager or driver to do what it will with it. The idea of passing through the SA-connected userid/password is both an ease-of-use feature and security feature - ease-of-use because you then don't need to create an externlogin for every db user and a security feature because you don't need to store the pwd in the dsn nor in the db. I do understand that there is a case when this behaviour is not desireable. I will see what we can do.

(19 Apr '10, 22:36) Mark Culp

The SQLServer connections certainly don't, can't speak for the others.

permanent link

answered 19 Apr '10, 15:43

Justin%20Willey's gravatar image

Justin Willey
6.7k108141208
accept rate: 20%

That's my SQL Server experience, too.

(22 Apr '10, 11:02) Volker Barth

Just to add a later answer (actually a comment) from Karim on a related question:

The userid and password in the DSN gets overridden when making remote data access connections.

So the answer does fit Breck's expectation (or at least his impression...):

No, the credentials from the DSN are never used, and as a consequence (to cite from Breck's question):

You must either define an EXTERNLOGIN or specify UID and PWD values in the remote server connection string. - If you do neither, it will use the user id and password that was used to make the current connection (on the source or client database).


CAVEAT: AFAIK, this does also apply to remote calls from within stored procedures and the like: It's still the current user (and not the procedure owner) who does make the remote connection - and therefore needs his own externlogin or needs fitting remote credentials (unless the remote credentials are already specified in the remote server connection string).
I'm adding this note as I've stumbled too often over that by falsely assuming the remote call would be made in the context of the procedure's owner (just in the way permissions are checked in the context of the procedure's owner)... - that's different here.

permanent link

answered 23 Jan '13, 03:30

Volker%20Barth's gravatar image

Volker Barth
30.6k306456662
accept rate: 32%

2

Volker,

With regards to your CAVEAT above, the behavior that you have correctly observed with respect to remote calls from within stored procedures has been corrected in the next release of SQL Anywhere (SA 16). SA 16 now respects the "effective user" when establishing remote connections. Hence if a user logs in as A, queries a proxy table, then calls procedure B.p1() which was created as DEFINER, and if B.p1() then makes a remote request to the same remote server, then two remote connections will be established, one with A's external credentials and the second one with B's external credentials.

There is also an option to revert back to SA 12 and below behavior to ensure that current applications do not break.

(24 Jan '13, 13:03) Karim Khamis
Replies hidden

That's good news - we usually face the situation that user A (a normal "user") does not make direct remote connections but may use procedures owned by B (i.e. the DBA...) that do remote calls - and then in V16 only B would need remote credentials. Today, I still have to create externlogins for A, too. - So yes, I consider this a clear improvement.

BTW: I haven't found that change listed in the v16 beta docs. Have I missed a point?

(25 Jan '13, 03:24) Volker Barth

Volker,

Have a look at the write up for the new extern_login_credentials option. I will, in the meantime, make sure the documentation team adds a new feature note.

(25 Jan '13, 15:33) Karim Khamis
Replies hidden

Thanks for the hint. Currently, that option is only listed as a new option (as "Beta: under construction") which made me oversee this one - but its own doc page does make the usage clear.

(27 Jan '13, 15:32) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×124

question asked: 19 Apr '10, 15:01

question was seen: 2,025 times

last updated: 27 Jan '13, 15:32