SQL Password Prompts in -some- custom reports and intermittently Sage Inteligence

SUGGESTED

Hi Everyone,

Our company has ~20 custom reports setup in Sage 300 as well as a handful of Sage Inteligence reports created by our finance team. Recently, 2 of the custom reports have started prompting for SQL login credentials at the same time Sage BI is intermittently failing to login using the same user account. I've spoken with everyone on the IT team and no one has modified the rpt files, ODBC DSNs, or the SQL Server user account. It's possible these two things are unrelated but they started at the same time and both have to do with authenticating with the SQL server with the same account which seems like more than coincidence.

Notes on things I've tried and observed:

- If I enter the password for the broken reports it logs in and works as expected from there.

- I tried restoring the rpt file from a last known-good version in our source control

- I followed the process listed here several times to no avail

- From the Crystal Reports application I can 'verify database' and refresh report data. (save data with report is not set)

- The SQL account in question is the same as is used by the rest of Sage 300

- The SQL account has sysadmin privileges on the SQL box (which I'm not fond of but should rule out a lack of permissions)

- Specific error in Sage Inteligence is: "[Microsoft] [ODBC SQL Server Driver] [SQL Server] Login failed for user '<user name>'." Number: "-2147217843" Source: "<blank>" Driver Error: "Unknown" SQL State: "Unknown"

I've been bashing my face into my keyboard for longer than I care to admit at this stage. Any help would be greatly appreciated.

Cheers,

Will

Parents Reply Children
  • 0 in reply to Jay Converse Acumen

    Thanks Jay.

    Correct, and the ODBC connection used by both Sage 300 and Crystal reports is a SQL user and password, not windows. The use trusted connection is turned off. I verified the credentials by logging into SSMS with the SQL credentials and set the ODBC connection up with the same. The test of the connection worked. I also resupplied the password within Crystal Reports with no luck.

    Any other ideas?