OCM and SQL Server

Burke

Active Member
My configuration. E1 8.9 with Tools 8.97.26 | Oracle 10g | Citrix.

We have some data that we need to access on a SQL server. I created the Database Data Source for the SQL server and created a corresponding system DSN. Every time I try to create an OCM or access the table via UTB it prompts me for the user ID and password. The SQL server is setup with SQL authentication. I have created a SQL server connection in E1 in the distant past but it was NT authenticated.

The SQL server is managed by someone outside our company and therefore have limited access to it.

Any suggestions?
 
Burke,

You'll need to ask the DBA that manages the SQL server to give you SQL access to the database.
 
The System user assigned to your id in F98OWSEC should be able to access the SQL Server in question using SQL Server authentication using the password that is currently stored.

In case it is not the same and you cannot wait for the DBA to create an account for you , you should be able to create an addtional entry in F98OWSEC for the new datasource in question. The standard entry is for "DEFAULT" , which means it would use this for all data sources.

Do you have any credentials (SQL server authentication) to access this SQL Server DB.? If yes then you can add that as a new system user and create an additional entry in P98OWSEC for your user and the new datasource to use this new system user
 
The SQL server only has two user IDs at this time, sa being one of them. I can access the database via the second user ID. E1 will even allow me to create OCMs to the SQL server but prompts me every time.

Creating a user ID for every user on the SQL may be a solution at this time.

I understand what you are talking about with the P98OWSEC and data-source and tried it. It did not work. It may be that I type something wrong. I am verifying everything now.

Thank you
 
This is what I am receiving in the jde.log

3872/4752 WRK:Starting jdeCallObject Tue Dec 22 08:07:58.481001 Jdbodbc.c1340
ODB0000163 - SQLDriverConnect failure. rc=-1

3872/4752 WRK:Starting jdeCallObject Tue Dec 22 08:07:58.481003 Jdbodbc.c1340
ODB0000164 - DBC:00 [28000][18456] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'MYUSERID'.

3872/4752 WRK:Starting jdeCallObject Tue Dec 22 08:07:58.481005 Jdbodbc.c1340
ODB0000164 - DBC:01 [28000][18456] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'MYUSERID'.

3872/4752 WRK:Starting jdeCallObject Tue Dec 22 08:07:58.481007 Jdbodbc.c1350
ODB0000010 - SQLDriverConnect failed. ODBC DSN: DEVDB.

It seems that the user ID I setup for the datasource is not being passed.
 
So the user id reflected in the log - "MYUSERID" , is not what the same as what you have setup in P98OWSEC for this new Datasource.? It is attempting to use the ID assigned to the DEFAULT datasource.? If yes , then it would seem like a bug.

I'd be curious to see the debug log to see how it is determining which user to use. Your only other workaround would be to get an ID created on your SQL DB , with the same username and password as the system user which is used for your Oracle DB right now.
 
Yes, the MYUSERID is being passed instead of the system user that was used with the datasource.

I created a user ID that uses the system user equal to that on the SQL server and it works fine. If all else fails this would have to be the solution. I was trying to keep from creating a lot of users on the SQL server.

I will work on getting the debug log.

Thank you for assistance.
 
Try get separate debug logs for one when accessing your Oracle based datasource and one when accessing your SQL Server datasource.

Of course if there is a bug you will need to contact Oracle support and having the logs will help , there might be a fix.
 
I captured a debug log. I logged on then started UTB and then tried to access a table on the SQL server.
 

Attachments

  • 154162-jdedebug.txt
    574.9 KB · Views: 173
So your table name is - F57ZILNT and the datasource it is mapped to is - "Zilliant Pricing Test".

Its then trying to validate if the table exists in the Datasource using the BSFN - VerifyTableInDataSource , which is in turn using the system function - JDB_TableExists (Table F57ZILNT)

Unfortunately it only says "Try to fetch proxy info from cache " and no more details , my guess would be that it is only caching the DEFAULT datasource record..

And it fetched the proxy user as CBURKE. And this is the ID setup against the DEFAULT data source in F98OWSEC , and not the second record you have in F98OWSEC for datasource "Zilliant Pricing Test" ??

If this is the case then I would follow up with Oracle support , send them the logs and also screen shots of the P98OWSEC setup.

One question though , did you bounce your security server after adding the new record in F98OWSEC for the new datasource. If you cannot bounce (In case it is your Prod server) , try clearing cache at least
 
No, I did not bounce the security server after making the change but they have been bounced in the past couple of days.

Unfortunately, my company has terminated our service contract with Oracle support last year.

If this does not work then I will have to create a user account for all of my users on the SQL server.

Thank you for the assistance.
 
Back
Top