| Author |
Message
|
| jby |
 Posted: Wed Apr 02, 2003 7:03 am Post subject: Accessing multiple DB's (with different user/pwd) from WMQI |
 |
|
Newbie
Joined: 24 Jan 2003
Posts: 9
Location: Netherlands
|
I need to access multiple application databases from a message flow. Each database alows access to a specific,different user/password.
mqsichangebroker allows me to set a single user/password only.
The databases are external and there is no single/common user with access to all databases.
Is there a way to associate a different user/password with each ODBC definition?
Other details: WMQI V2.1 Broker on AIX, accessing external Oracle 8.x databases.
Any ideas welcome. |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Wed Apr 02, 2003 7:49 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Basically, there is no way currently to set a database specific user id and password, as I understand it.
However, there are some subtlties.
This is what I've found using DB2 on Windows for the broker database, and DB2 and SQL server for the app databases.
The user that is presented to any DB2 database, regardless of where, is the user that the broker uses to access the broker database.
The user that is presented to any SQL Server database is the broker Service user ID.
I don't know how different it is with Oracle and an AIX broker. I imagine you will at least have to grant the single user rights to all your application tables. |
|
| Back to top |
|
|
| kwelch |
| Posted: Wed Apr 02, 2003 8:51 am Post subject: |
|
|
Master
Joined: 16 May 2001
Posts: 255
|
We are currently using an external application Oracle Database and it uses the id that was used to create the Broker. That id had to be given access by the DB folks in order to be able to connect. Also, we had a security standard at our company where there were special characters in our passwords, such as @ and we found Oracle would not accept a password with those characters and had to change it.
Karen |
|
| Back to top |
|
|
| kirani |
| Posted: Wed Apr 02, 2003 11:33 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
| jby wrote: |
Is there a way to associate a different user/password with each ODBC definition?
|
You will come across two different user-id's when dealing with the broker.
Service User-id: This user-id is the id under which the broker service runs. This user-id must be a member of mqgrkrs group and is specified by -i parameter in mqsicreatebroker command.
Datasource User-id Broker uses this user-id to connect to all databases (including Broker database). This is an optional parameter and is specified using -u parameter. If you have specified this parameter when creating a broker, it will ALWAYS use this user-id to connect to all databases. If this parameter is not specified then the Broker will use service user-id to connect to all databases.
In the next release of WMQI they are planning to have ODBC level user-id/pwd.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| Donna |
| Posted: Wed Apr 02, 2003 12:00 pm Post subject: Operational DBs |
|
|
Newbie
Joined: 02 Apr 2003
Posts: 3
|
Hi All,
Any one know how many operational database can be used by the broker?
Thanks |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed Apr 02, 2003 6:39 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Kirani, I have to disagree with you based on personal experience for what you call the Datasource User ID.
When communicating from a Windows Broker to a Microsoft SQL Server database, the broker presents the Service User ID to the SQL Server, and not the Datasource User ID. However, the same Windows Broker does present the Datasource User ID to any DB2 database. I don't know which of the two it would present to an Oracle db.
This makes life a little interesting when working with tables stored on both SQL Server and DB2. Particularly when working with DB2 across platforms. |
|
| Back to top |
|
|
| kirani |
| Posted: Wed Apr 02, 2003 6:51 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Jeff,
That's interesting! In our environment we are connecting to MSSQL Server DB and DB2 UDB databases thru Broker and it works fine for us. We are using WMQI 2.1 CSD4 on Windows 2K platform. It even worked for us with MQSI 2.0.2 version.
What is the command you used to create your broker? Have you opened a case with IBM regarding this?
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Apr 03, 2003 8:02 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
I didn't see any need to open a ticket with IBM. I kinda like it this way, anyway. It gives me *two* database IDs... 
I used the wizard to create my broker, which ended up building a command like
mqsicreatebroker <brokername> -i <service user> -a <service user password> -q <queuemanager> -n <broker datasource> -u <broker datasource user> -p <broker datasource password>
I only authorized the -a user to my SQL Server, and it's working. I ended up working backwards from our mainframe to create the broker datasource user so that it would be authorized against the DB2 there.
I wonder if the difference is in the settings on the ODBC datasource for the SQL Server? Mine is a system level data source, defined against the SQL Server driver in the control panel. Did you create yours through the DB2 Client Configuration Assistant? (Is that possible for an SQL server?)
I'm using WMQIB v2.1, CSD04 on Win2k. |
|
| Back to top |
|
|
| kirani |
| Posted: Thu Apr 03, 2003 9:07 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
We did the same, we created a System level DSN using DataSource ODBC tool and configured it to use SQL Server ODBC driver. I think using DB2 Client Configuration assistant you can create ODBC connection to DB2 database only.
So, have you ever tried using Datasource User-id to connect to SQL Server DB? I am sure if you use this user-id it will still work.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Apr 03, 2003 9:43 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
I suppose I could try using the datasource ID from something else.
I'm quite sure it *won't* work, though, as I get security violations at SQL Server and ODBC errors within WMQI when the service user ID does not have access to a table or etc.
Is your datasource set up to "Verify the authenticity of the login ID" using SQL server authentication or Windows NT authentication? This could be the difference. I'm pretty sure mine is set up to use NT authentication. |
|
| Back to top |
|
|
| kirani |
| Posted: Thu Apr 03, 2003 10:01 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
| JeffLowrey wrote: |
I'm quite sure it *won't* work, though, as I get security violations at SQL Server and ODBC errors within WMQI when the service user ID does not have access to a table or etc.
|
Well, before you use any of these user-id's you need to setup proper authorizations in the database.
| JeffLowrey wrote: |
Is your datasource set up to "Verify the authenticity of the login ID" using SQL server authentication or Windows NT authentication? This could be the difference. I'm pretty sure mine is set up to use NT authentication.
|
We have selected "With SQL Server authentication using a login ID and password entered by the user".
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Apr 03, 2003 1:58 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| Quote: |
| We have selected "With SQL Server authentication using a login ID and password entered by the user". |
That's the difference then. I bet if you change that to NT authentication, you'll see the service user being presented instead of the datasource user.
Interesting. |
|
| Back to top |
|
|
| kirani |
| Posted: Thu Apr 03, 2003 2:42 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
So in summary,
The rules for accessing any DB by the Broker remains same (as described in my earlier post), unless you override the user-id when setting up the ODBC connection.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
|
|
