Sign In Help
Schneider Electric
HelpSign In
Schneider Electric Exchange
  • Home
  • Collaborate
  • Develop
  • Shop
Home Collaborate Develop Shop Log in or Register Help

Invite a Co-worker

Send a co-worker an invite to the Exchange portal.Just enter their email address and we’ll connect them to register. After joining, they will belong to the same company.
You have entered an invalid email address. Please re-enter the email address.
This co-worker has already been invited to the Exchange portal. Please invite another co-worker.
Please enter email address
Send Invite Cancel

Invitation Sent

Your invitation was sent.Thanks for sharing Exchange with your co-worker.
Send New Invite Close
  • Home
  • Collaborate
  • Exchange Community
  • :
  • SCADA & Telemetry Solutions
  • :
  • Geo SCADA Expert Forum
  • :
  • Re: "Database logon failed" when generating Crystal Report from ViewX using ODBC connection to SQL Server Database.
Community Menu
  • Forums
    • By Topic
        • EcoStruxure IT
          • EcoStruxure IT forum
        • Industrial Automation
          • Industry Automation and Control Forum
          • Alliance System Integrators Forum
          • Machine Solutions in the Digital Transformation
          • EcoStruxure Automation Expert / IEC 61499 Forum
          • Industrial Edge Computing Forum
          • Level and Pressure Instrumentation Forum
          • Modicon User Group
          • PLC Club Indonesia
          • SEE Automation Club Forum
          • Fabrika ve Makina Otomasyonu Çözümleri
          • Форум по промышленной автоматизации СНГ
        • SCADA & Telemetry Solutions
          • Geo SCADA Expert Forum
          • SCADA and Telemetry Devices Forum
        • Power Distribution IEC
          • Power Distribution and Digital
          • Power Standards & Regulations
          • Paneelbouw & Energie Distributie
        • Power Distribution Softwares
          • EcoStruxure Power Design Forum
          • SEE Electrical Building+ Forum
          • LayoutFAST User Group Forum
        • Solutions for your Business
          • Solutions for Food & Beverage Forum
          • Solutions for Healthcare Forum
    • By Segment
        • Food & Beverage
          • Solutions for Food & Beverage Forum
        • Healthcare
          • Solutions for Healthcare Forum
      • EcoStruxure IT
        • EcoStruxure IT forum
      • Industrial Automation
        • Industry Automation and Control Forum
        • Alliance System Integrators Forum
        • Machine Solutions in the Digital Transformation
        • EcoStruxure Automation Expert / IEC 61499 Forum
        • Industrial Edge Computing Forum
        • Level and Pressure Instrumentation Forum
        • Modicon User Group
        • PLC Club Indonesia
        • SEE Automation Club Forum
        • Fabrika ve Makina Otomasyonu Çözümleri
        • Форум по промышленной автоматизации СНГ
      • SCADA & Telemetry Solutions
        • Geo SCADA Expert Forum
        • SCADA and Telemetry Devices Forum
      • Power Distribution IEC
        • Power Distribution and Digital
        • Power Standards & Regulations
        • Paneelbouw & Energie Distributie
      • Power Distribution Softwares
        • EcoStruxure Power Design Forum
        • SEE Electrical Building+ Forum
        • LayoutFAST User Group Forum
      • Solutions for your Business
        • Solutions for Food & Beverage Forum
        • Solutions for Healthcare Forum
      • Food & Beverage
        • Solutions for Food & Beverage Forum
      • Healthcare
        • Solutions for Healthcare Forum
  • Blogs
    • By Topic
        • Industrial Automation
          • Industrial Edge Computing Blog
          • Industry 4.0 Blog
          • Industrie du Futur France
        • SCADA & Telemetry Solutions
          • SCADA and Telemetry Blog
        • Power Distribution IEC
          • Power Events & Webinars
          • Power Foundations Blog
        • Power Distribution NEMA
          • NEMA Power Foundations Blog
        • Power Distribution Softwares
          • EcoStruxure Power Design Blog
          • SEE Electrical Building+ Blog
        • Solutions for your Business
          • Solutions for Food & Beverage Blog
          • Solutions for Healthcare Blog
          • Solutions for Retail Blog
    • By Segment
        • Food & Beverage
          • Solutions for Food & Beverage Blog
        • Healthcare
          • Solutions for Healthcare Blog
        • Retail
          • Solutions for Retail Blog
      • Industrial Automation
        • Industrial Edge Computing Blog
        • Industry 4.0 Blog
        • Industrie du Futur France
      • SCADA & Telemetry Solutions
        • SCADA and Telemetry Blog
      • Power Distribution IEC
        • Power Events & Webinars
        • Power Foundations Blog
      • Power Distribution NEMA
        • NEMA Power Foundations Blog
      • Power Distribution Softwares
        • EcoStruxure Power Design Blog
        • SEE Electrical Building+ Blog
      • Solutions for your Business
        • Solutions for Food & Beverage Blog
        • Solutions for Healthcare Blog
        • Solutions for Retail Blog
      • Food & Beverage
        • Solutions for Food & Beverage Blog
      • Healthcare
        • Solutions for Healthcare Blog
      • Retail
        • Solutions for Retail Blog
  • Ideas
        • Industrial Automation
          • Modicon Ideas & new features
        • SCADA & Telemetry Solutions
          • Geo SCADA Expert Ideas
          • SCADA and Telemetry Devices Ideas
  • Knowledge Center
    • Building Automation Knowledge Base
    • Industrial Automation How-to videos
    • Ask Exchange
    • Digital E-books
    • Success Stories Corner
    • Power Talks
  • Events & Webinars
  • Support
    • User Guide
    • Leaderboard
    • Releases Notes
How can we help?
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
49242members
Join Now
242456posts
Join Now

"Database logon failed" when generating Crystal Report from ViewX using ODBC connection to SQL Server Database.

Options
  • Subscribe to RSS Feed
  • Mark Topic as New
  • Mark Topic as Read
  • Float this Topic for Current User
  • Bookmark
  • Subscribe
  • Mute
  • Printer Friendly Page
Back to Geo SCADA Expert Forum
AutoGui20
Crewman AutoGui20
Crewman
‎2020-05-22 03:41 PM
0 Likes
10
2621
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-05-22 03:41 PM

"Database logon failed" when generating Crystal Report from ViewX using ODBC connection to SQL Server Database.

Hi,

 

I have already submitted a request to support for this issue but I thought I would see if anyone else had any thoughts on this or had run into this issue before and got it resolved.

 

I built a crystal report that pulls in data from a SQL table using an ODBC connection. The report is pointed to the table and is not using a command to pull in the data. I know that the connection, specifically the credentials, is working because I can preview the report in the Crystal Reports editor which successfully pulls in the data.

 

The problem is that when I try to generate the report through ViewX, I am seeing in the events and the crystal report logs that the report failed to generate the report because the database logon failed.

 

This seems to be a ClearSCADA issue because Crystal Reports is able to connect and retrieve data from SQL Server using the ODBC connection.

 

I also checked SQL Profiler to see if there was any error on the SQL side but I did not seen messages. I was able to see through the query that Crystal Reports used to retrieve the data when previewing the report in the editor. I even verified that the query would execute successfully in SQL directly. So its not an issue with the report, its contents, or the connection information.

 

This is a local setup and I am an administrator on my machine and in SQL. The user I provided in the ODBC connection is also an admin.

 

Any help would be appreciated.

Labels
  • drivers
  • ViewX
Tags (9)
  • Tags:
  • connection
  • CrystalReports
  • error
  • odbc
  • ODBCconnection
  • reports
  • server
  • sql
  • sqlserver
Share
Reply
  • All forum topics
  • Previous Topic
  • Next Topic
10 Replies 10
BevanWeiss
Admiral BevanWeiss
Admiral
‎2020-05-24 05:11 PM
0 Likes
9
2610
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-05-24 05:11 PM

Re: "Database logon failed" when generating Crystal Report from ViewX using ODBC connection to SQL Server Database.

 

To run through a few things...

The DSN that you have here is correct? and configured as you expect it to be (i.e. 64-bit / 32-bit as appropriate)?

2020-05-25 10_05_48-Server Configuration [Crystal Reports on Local_localhost].png

 

And then you have the Location and User configured correctly here?

2020-05-25 10_08_05-Local_~System.Reports.AC Power Fail Site Listing - ViewX.png

And the user has the right security permissions?

I'm not sure entirely if this tick box needs to be ticked for ODBC access in new versions, but I'd probably give it a try if it's not already in your system

2020-05-25 10_10_34-Local_~Config.Security.Users.~External Interfaces.CrystalReports - ViewX.png

 

 

Let us know how you go with confirming those.

 

 


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
Share
Reply
sbeadle
Sisko sbeadle Sisko
Sisko
‎2020-05-25 12:42 AM
0 Likes
0
2606
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-05-25 12:42 AM

Re: "Database logon failed" when generating Crystal Report from ViewX using ODBC connection to SQL Server Database.

Some additions/changes:

The 'can edit SQL' setting does not affect Crystal, so is not needed.

 

The Crystal Report setting Location = 'Local Database' means that whatever database connection settings and credentials the report might have, they will all be replaced by the Geo SCADA connection DSN configured in the top image, with the user specified under the Location setting. So if you are accessing a different database then it will not work. Choose the other remote database option and all of the connection and credential information you save after editing them in the report will be used during report generation.

 

Share
Reply
AutoGui20
Crewman AutoGui20
Crewman
‎2020-05-26 03:06 PM
0 Likes
7
2595
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-05-26 03:06 PM

Re: "Database logon failed" when generating Crystal Report from ViewX using ODBC connection to SQL Server Database.

I did not have have the correct the DSN in the location of the first image you sent (Server configuration ->Global Parameters ->Crystal Reports). So I tried both the 32 bit and 64 bit DSN for SQL with the local database setting for "Location" and I set a user with the permission to edit/access everything (including the edit SQL option). However, I still received the same error.

 

A bit more information on what I had tried before your suggestion: I originally was using local database for the "Location" setting with no user defined. However, that was giving me an error that the SQL table couldn't be found. After speaking with a colleague, he suggested using the remote database and that's when I started to get the Database logon error and I have stuck with using the Remote Database option since then.

Share
Reply
BevanWeiss
Admiral BevanWeiss
Admiral
‎2020-05-26 07:45 PM
0 Likes
6
2593
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-05-26 07:45 PM

Re: "Database logon failed" when generating Crystal Report from ViewX using ODBC connection to SQL Server Database.

Screen shots or it didn't happen... 😉

Show us the DSN settings as you configured them from the Server Configuration window.

I have a suspicion that you still don't have the DSN settings right.

 

I'd recommend that you go with Local for the Report Location, and enter an appropriate user here (unless you are indeed trying to access a different System / different SQL entirely).

 

If you didn't specify a User, then it would make some sense that it complained it couldn't find the tables you referenced... the Everyone account possibly doesn't have Read / Browse permissions.

 

Go back to Local.  Put in an appropriate user account for Crystal Reports to use.

If that doesn't work, then show us what you have for the DSN settings.


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
Share
Reply
AndrewScott
Lieutenant AndrewScott
Lieutenant
‎2020-05-27 03:33 AM
0 Likes
1
2585
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-05-27 03:33 AM

Re: "Database logon failed" when generating Crystal Report from ViewX using ODBC connection to SQL Server Database.

As your report is querying a Microsoft SQL Server database, and not the ClearSCADA database, you must set the report location to remote and not local. The DSN specified in the "Server Configuration \ Global Parameters \ Crystal Reports" section only applies to reports where the report location is local, so it is not applicable in this case. If the report location is set to local then only the local ClearSCADA database can be queried by the report as all of the DSN's specified in the report will be replaced with the one specified in the server configuration tool.

 

For a report using the remote report location to work you must create a 64-bit System DSN on the ClearSCADA server(s) which has the exact same name as the DSN used when designing the report. Also, the credentials required to access the database must be saved in either the report or the DSN itself. If generating the report prompts you to enter credentials (e.g. a password) then the report will not work with ClearSCADA.

 

Bear in mind that the report will be generated using the Windows user account specified in the "Server Configuration \ System Configuration \ Printing" section, which may affect the logon, depending on what type of authentication is being used.

 

The online help has more information, see "Driver Reference > Crystal Reports Driver > Creating a Report and Defining the Report Properties > Specify the Data Source".


Andrew Scott, Lead Engineer, AVEVA
Share
Reply
AutoGui20
Crewman AutoGui20
Crewman
‎2020-05-27 06:55 AM
0 Likes
3
2582
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-05-27 06:55 AM

Re: "Database logon failed" when generating Crystal Report from ViewX using ODBC connection to SQL Server Database.

Here is a screenshot of the Crystal Reports DSN setup in the Server Configuration:

CrystalReportDSN.PNG

 

and here are the ODBC connections on my machine:

ODBC_Setup.PNG

 

I am currently using the 32 bit ODBC connection but I have also tried the 64 bit version. One thing that was weird was that I had to type in the DSN into the server configuration because when I would click on the setup button and then selected the DSN it would not change on the server configuration. However, after some testing, it does seem to take it properly because I entered a DSN that didn't exist and when I tried to generate the report, it gave an error that it could not create a connection.

 

Here is the current setup for the location of my report:

ReportSetup.PNG

 

and here is the settings for my user:

UserSettings1.PNGUserSettings2.PNGUserSettings3.PNG

 

One thing that I wasn't sure about was what the ClearSCADA user, assigned under the "Location" setting, was for because the DSN and the report have the username and password that would be used to log into SQL Server. What ever the case may be, I put in the windows user I would like to use to generate the reports under printing in the server configuration since it had been mentioned above. Here is a screenshot of those settings:

 

PrintingSettings.PNG

Share
Reply
AutoGui20
Crewman AutoGui20
Crewman
‎2020-05-27 07:12 AM
0 Likes
0
2577
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-05-27 07:12 AM

Re: "Database logon failed" when generating Crystal Report from ViewX using ODBC connection to SQL Server Database.

I did have the report location set as Remote. I recently changed it and have been trying Local since it had been suggested. In my previous response I mentioned that I setup the DSN for Crystal Reports in the server configuration but I still got the same results.

 

I created both a 32 and 64 bit system DSN on my machine (everything is setup locally). What I noticed is that in the report, it can only locate the 32 bit version DSN. Here is a screenshot of that:

ReportODBC.PNG

 

The username and password were entered into the DSN and the report. However, I have noticed that the report asks for the credentials after I've restarted the ClearSCADA server. So I'm not sure if the report actually saves the credentials. It might just hold it in memory but the report still fails to generate even after I've entered the credentials and I save it.

 

Could there be an issue if the report is using the 32 bit DSN version while ClearSCADA uses the 64 bit version?

 

Lastly, in my previous reply, I mentioned that I setup the windows user that I want to generate the reports with in the server configuration under printing. This user had admin permissions in SQL but it is a different user that what I specified in the DSN and the report. That user is not a windows users, it's only an SQL user with admin permissions in SQL.

Share
Reply
AndrewScott
Lieutenant AndrewScott
Lieutenant
‎2020-05-27 07:40 AM
0 Likes
2
2575
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-05-27 07:40 AM

Re: "Database logon failed" when generating Crystal Report from ViewX using ODBC connection to SQL Server Database.

The Server Configuration DSN setting in the first screenshot above is not valid. This DSN must be a 64-bit ClearSCADA DSN for the local ClearSCADA database, but you've specified a 32-bit SQL Server DSN. The correct DSN for this setting is "ClearSCADA" based on the ODBC connections screenshot, however this isn't relevant to this particularly report as it isn't querying the ClearSCADA database.

 

The third screenshot shows the database source location as "Local Database", but this report isn't querying the local ClearSCADA database, but an SQL Server database and therefore the location must be set to "Remote Database(s)".

 

The second screenshot shows two SQL Server data sources called "SQLServer32" and "SQLServer64". What is the name of the data source used in the report definition? You must create a 64-bit SQL Server data source with the exact same name to be able to generate the report in ClearSCADA. Given that Crystal Reports is a 32-bit application and ClearSCADA is a 64-bit application you will need two different SQL Server data sources with the same name.


Andrew Scott, Lead Engineer, AVEVA
Share
Reply
AutoGui20
Crewman AutoGui20
Crewman
‎2020-05-28 08:14 AM
0 Likes
1
2552
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-05-28 08:14 AM

Re: "Database logon failed" when generating Crystal Report from ViewX using ODBC connection to SQL Server Database.

Right, the DSN configuration was originally "ClearSCADA", before I changed it. I have set it back to "ClearSCADA".

CrystalReportDSN_V2.PNG

 

I have gone ahead and updated the SQL DSN for the 32 and 64 bit to match:

ODBC_SetupV2.PNG

 

I have also changed the report from using Local to using Remote for "Location":

ReportSetupV2.PNG

 

Lastly, I updated the report to use the new "SQLServer" DSN name and could get it to generate a preview with data:

ReportODBC_V3.PNGReportPreview.PNG

 

However, when I tried to generate it through ViewX again, it failed with the same database logon failed:

ReportEvents.PNG

Share
Reply
AndrewScott
Lieutenant AndrewScott
Lieutenant
‎2020-05-28 10:04 AM
0 Likes
0
2547
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-05-28 10:04 AM

Re: "Database logon failed" when generating Crystal Report from ViewX using ODBC connection to SQL Server Database.

All of these settings now appear to be correct.

What kind of authentication are you using in the SQL server DSN's?

I've been unable to get reports to work with Integrated Windows authentication (formally Windows NT authentication). It appears that something about user impersonation (for the printing Windows user) isn't compatible with this form of authentication.

If its possible, it may be worth trying SQL Server authentication. I've not been able to try this myself.


Andrew Scott, Lead Engineer, AVEVA
Share
Reply
Top Experts
User Count
sbeadle
Sisko sbeadle Sisko
1
geoffpatton
Lieutenant geoffpatton
1
BevanWeiss
Admiral BevanWeiss
1
See More Top Experts
Find a Service Provider
Find a certified partner to help you address your integration, installation, maintenance and project needs.
View all Providers
Support

Have a question? Please contact us with details, and we will respond.

Contact Us
FAQ

Look through existing questions to find popular answers.

Learn More
About

Want to know more about Exchange and its possibilities?

Learn More

Full access is just steps away!

Join Exchange for FREE and get unlimited access to our global community of experts.

Connect with Peers & Experts

Discuss challenges in energy and automation with 30,000+ experts and peers.

Get Support in Our Knowledge Base

Find answers in 10,000+ support articles to help solve your product and business challenges.

Ask Questions. Give Solutions

Find peer based solutions to your questions. Provide answers for fellow community members!

Register today for FREE

Register Now

Already have an account?Log in

About Us FAQ Terms & Conditions Privacy Notice Change your cookie settings
©2020, Schneider Electric