Check sql server logs for connection attempts

UnderSeven
UnderSeven used Ask the Experts™
on
I have a sql server instance which a third party solution (GE RIS) is supposed to connect to in attempt to get data.  It goes through crystal reports to do this.  In the past this has worked, but since a new hardware upgrade it stopped.  The error it throws it that it can not find the stored procedure. What I would like to know is how I can check the sql server logs for the connection attempt from the third party applicatoin (GE RIS), so I can see what user it is attempting to connect as.

I have already tested and confirmed that the Crystal reports can access the stored procedure.

All connections are done using trusted security.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
" The error it throws it that it can not find the stored procedure."

"I have already tested and confirmed that the Crystal reports can access the stored procedure."


Which one of the above is TRUE? If both then try to run under the same user you "... tested and confirmed that the Crystal reports can access the stored procedure."

Author

Commented:
I don't see why those have to be unique.  

Here is an example of the error:
IDXUserID [0] Function [ReportMgr.AlterConnectionProperties] ErrorNum [-2147189180]  Description: [Crystal Reports ActiveX Designer]The table 'RCS_OffCodingQueue2_Revised;1' could not be found.

Yet in crystal I can run it.  

Part of the problem is I'm not positive what the third party software is using as a user to connect. I know it can connect, but maybe it can't see the same procedures I can.  How can I see which users attempted to connect when to figure out which user it was?

Author

Commented:
The problem is I can't change how the third party app connects, and I don't know what user it is connecting with.  I'm trying to figure out what use the third party app is using to access sqlserver.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I've attempted running it under specified user, but it still doesn't work and actually I doubt it's using the user Id I'm giving it, I think it maybe using it's own user id.   The event viewer is still throwing the errors:

IDXUserID [0] Function [ReportMgr.AlterConnectionProperties] ErrorNum [-2147189180]  Description: [Crystal Reports ActiveX Designer]The table 'RCS_OffCodingQueue2_RevisedTEST;1' could not be found.

IDXUserID [0] Function [ReportMgr.cls.PrintCrystalReport] ErrorNum [0]  Description: Error on Crystal.Export :Failed to retrieve data from the database.
Details:  [Database Vendor Code: 916 ]   RPTFILE = c:\data\wwwroot\Imagecast\ReportManager\UM_offCodingQueue2test.rpt ReportOutputID = 7965294

IDXUserID [0] Function [ReportMgr.cls.PrintReport] ErrorNum [0]  Description: Error on Crystal.Export :Failed to retrieve data from the database.
Details:  [Database Vendor Code: 916 ]   RPTFILE = c:\data\wwwroot\Imagecast\ReportManager\UM_offCodingQueue2test.rpt ReportOutputID = 7965294

I can change the connection to use a specific user or integrated security.  This crystal runs either way, but once I try to run it through the application it throws this error.  Furthremore I have other crystal reports that pull off the same database, same owner, different procedures that work.  The connection strings seem to identical.  The vendor is GE which basically means they are no help at all.
Database Analyst
Commented:
"This crystal runs either way, but once I try to run it through the application it throws this error.  Furthremore I have other crystal reports that pull off the same database, same owner, different procedures that work."

All you need then is to figure out what user are they running under (fo the one that work) and make sure the SP that does not work will get the same permissions in ordr to run.

Author

Commented:
This maybe what you were trying to communicate the first time.  What I discovered following this line was the account it was trying to use was orphaned when the database was restored.  Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial