| Author |
Message
|
| anajnar |
 Posted: Wed May 02, 2012 1:00 am Post subject: Problem accessing SQL Server DB via WMB6.0 domain id |
 |
|
Newbie
Joined: 01 May 2012
Posts: 3
|
Hi,
I have an issue with WMB accessing SQL Server 2008 DB from WMBv6.0 messageflow.
The issue is with the way SQL Server is authenticating the connection.
In SQL Server users can be authenticated using a Windows Authentication
(Windows AD user) or SQL Server Authentication(local user)
WMB on Solaris SPARC V6101
SQL Server 2008 SP2 on Windows
Success scenario: Using a local account for the DB, my messageflow is able to access the SQL Serevr DB.
Problem scenario: But when I use a Domain account(Windows) the connection is not successful.
Question: Is there a way to specify from the broker connection/config how once would like to be authenticated on the conn to SQL Server DB on WMB v6.1?
I see errors like so
BIP2321E: Database error: ODBC return code '-1'. : BROKER1.e0197ad8-3201-0000-0080-b9ff0b664c54: /build/S610_P/src/DataFlowEngine/ImbOdbc.cpp: 230: ImbOdbcHandle::checkRcInner: :
BIP2322E: Database error: SQL State '28000'; Native Error Code '18456'; Error Text '[DataDirect][ODBC SQL Server Driver][SQL Server]Login failed for user 'DOMAINABC\USER1'.'. : BROKER1.e0197ad8-3201-0000-0080-b9ff0b664c54: /build/S610_P/src/DataFlowEngine/ImbOdbc.cpp: 359: ImbOdbcHandle::checkRcInner: :
BIP2322E: Database error: SQL State 'IM006'; Native Error Code '0'; Error Text '[DataDirect][ODBC lib] Driver's SQLSetConnectAttr failed'. : BROKER1.e0197ad8-3201-0000-0080-b9ff0b664c54: /build/S610_P/src/DataFlowEngine/ImbOdbc.cpp: 359: ImbOdbcHandle::checkRcInner: :
The datasource is set like so
[MARSDB]
Driver=/var/mqsi/ODBC64/V5.2/lib/UKmsss22.so
Description=DataDirect 5.2 64bit SQL Server Wire Protocol
Address=serverA\InstanceA
AnsiNPW=Yes
Database=StagingTable
QuotedId=No
ColumnSizeAsCharacter=1
mqsisetdbparms has been run to use the domain(Windows) account or local account say test.
Any help with this matter is most appreciated.
Best |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Wed May 02, 2012 1:44 am Post subject: Re: Problem accessing SQL Server DB via WMB6.0 domain id |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| anajnar wrote: |
| Question: Is there a way to specify from the broker connection/config how once would like to be authenticated on the conn to SQL Server DB on WMB v6.1? |
The only way I can understand this as a question is that you want to specify the user and password that Broker will use when connecting to the datasource.
| anajnar wrote: |
I see errors like so
BIP2321E: Database error: ODBC return code '-1'. : BROKER1.e0197ad8-3201-0000-0080-b9ff0b664c54: /build/S610_P/src/DataFlowEngine/ImbOdbc.cpp: 230: ImbOdbcHandle::checkRcInner: :
BIP2322E: Database error: SQL State '28000'; Native Error Code '18456'; Error Text '[DataDirect][ODBC SQL Server Driver][SQL Server]Login failed for user 'DOMAINABC\USER1'.'. : BROKER1.e0197ad8-3201-0000-0080-b9ff0b664c54: /build/S610_P/src/DataFlowEngine/ImbOdbc.cpp: 359: ImbOdbcHandle::checkRcInner: :
BIP2322E: Database error: SQL State 'IM006'; Native Error Code '0'; Error Text '[DataDirect][ODBC lib] Driver's SQLSetConnectAttr failed'. : BROKER1.e0197ad8-3201-0000-0080-b9ff0b664c54: /build/S610_P/src/DataFlowEngine/ImbOdbc.cpp: 359: ImbOdbcHandle::checkRcInner: : |
So the authentication you've provided is not correct.
| anajnar wrote: |
| mqsisetdbparms has been run to use the domain(Windows) account or local account say test. |
okay, if you know about mqsisetdbparms, why are you asking how to set the user id and password that broker will present when connecting to a datasource?
Just because you provide a Domain account to broker, doesn't mean that the SQLServer is configured to accept it.
Also, it's very unclear what version of Broker you are using. You talk about both 6.0 and 6.1. You should, regardless of which you are using, make sure you are at the most recent fixpack level. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed May 02, 2012 1:48 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Have you checked to see if your SQLServer instance can accept the credentials you are using?
In the ODBC config snapin, you can choose the authentication type you want to use, enter the username/password and test the connection.
What happens if you do this?
did you specify the ODBC connection as a SYSTEM DSN?
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed May 02, 2012 1:53 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| smdavies99 wrote: |
| did you specify the ODBC connection as a SYSTEM DSN? |
It appears to be running on Unix, not Windows. |
|
| Back to top |
|
|
| anajnar |
| Posted: Wed May 02, 2012 1:58 am Post subject: |
|
|
Newbie
Joined: 01 May 2012
Posts: 3
|
Hi Mqjeff
WMB v6.1.0.1 (apologies for the typo)
I have specified a Windows userid with domain and password 'DOMAINABC\USER1' and used mqsisetdbparms to set this on the Broker.
The id/password work - I have tried this on a windows box using SQL Server Management Studio.
DBA has confirmed that the userid passed has the domain details. But what I'm unable to do is set "what type of autherntication" on the conneciton string. SQL Server is authenticatiing the conn details as "SQL Server Authentication" as opposed to "Windows Authentication".
On a Windows Box this can be selected interactively using the db client gui.
Dear Smdavies99
Thanks for your update. Yes I can confirm my credentials are correct.The id/password work - I have tried this on a windows box using SQL Server Management Studio.
The broker is on Solaris Sparc not Windows and so there is no way of interactively selecting auth type. Maybe there is and I'm unaware. |
|
| Back to top |
|
|
|
|
