May 22, 2014 at 7:10 am
What am I missing? The datastage team insists that their connection fails when I have permissions ratcheted down to read-only. They are asking for db_owner permissions on the database in order to ensure connectivity.
Actually, the required permissions per the development team are read-only on a few tables within the database, so db_datareader is overkill in my eyes.
As far as troubleshooting, the connection never even reaches the server, so I have no failed login, and no record of a successful login, other than when a co-worker of mine set permissions to db_owner. This happened when the datastage tech tried to go around me to get higher permissions.
When comparing the DSN settings on the datastage server, the entry for this particular SQL instance looks exactly like a working DSN for another computer, except the server names are different. The working DSN connects to SQL2008R2SP2, while the non-working DSN connects to SQL2008R2CU7. The error received on the Linux Datastage server is as follows:
DSR.MetaGeta(GET.TABLES)(SQLConnect('DSN_Name','loginname')) : BCI Error : SQLSTATE=IM001,CODE=0,[DataStage][SQL Client][ODBC][DataDirect][ODBC lib] Data source name not found and no default driver specified
Thanks
May 22, 2014 at 7:28 am
You've probably already checked this, but I've had problems in the past (in Windows) where the DSN's default database was different than the one I was trying to access, and that was causing a connection error when I had permissions turned off on the default database. You could double-check your .odbc.ini file and make sure the default database is the one you are trying to get info from.
May 22, 2014 at 8:09 am
Actually, David, I had not considered that at all. Thank you for the suggestion, and we will try it out immediately. Will update when I hear from the ETL guy.
Thanks,
Jeff Bennett
May 23, 2014 at 12:30 pm
No dice. Unfortunately, the DataStage admin guy's troubleshooting skills amount to Work/Doesn't Work, so I am trying to diagnose this problem without access to the datastage server.
Thanks, it was worth a try.
Jeff Bennett
May 23, 2014 at 1:06 pm
In that case, I'd temporarily give the login the access it needs to connect, then run a sql trace on the login and see everything it's touching. There must be something there that it isn't getting the permissions it needs. Pay special attention to entries to the master database - that's a common default for DNS because it's on every sql server.
May 23, 2014 at 2:15 pm
Again, David. Great idea. No joy, however. Eventually granted sysadmin level permissions and connection still fails within datastage. Trace is empty.
Thanks
Jeff
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply