none
Trouble connecting Crystal Report to SQL Server 2008R2 using ODBC

    Question

  • I am using SQL Server 2008 R2 with dynamics GP. I try to access the data from Crystal Reports XI professional through a new ODBC (RDO) connection from within CR, I get error

    SQL STate: 2800,

    SQL Server Error: 18456

    [microsoft][sql server native client 10.0][sql server] Login failed for user 'sa'.

    I tried changing all the settings of the odbcad32.exe and still the same problem. It CAN connect using an OLEdb connection but not ODBC. can you think of a reason?

    Thanks

    Tuesday, March 06, 2012 10:18 PM

Answers

All replies

  • Can you connect using SQL Server management studio and check that the instance is running in mixed authentication mode and that the 'sa' account is enabled. When you say it can connect using oledb are you using the same credentials from the same machine?

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem

    • Marked as answer by Raouf.naguib Tuesday, April 03, 2012 9:24 PM
    • Unmarked as answer by Raouf.naguib Tuesday, April 03, 2012 9:24 PM
    Wednesday, March 07, 2012 12:53 AM
  • It CAN connect using an OLEdb connection but not ODBC.

    Hi Raouf.naguib,

    Does the OLE DB connection use the same SQL Server login 'sa'? We usually get the error because the SQL Server 'sa' is disabled by default.

    Please refer to the following links to troubleshoot the error by viewing the error log:

    Troubleshooting: Login Failed for User 'x'
    http://msdn.microsoft.com/en-us/library/ms366351.aspx

    How to: View the SQL Server Error Log (SSMS)

    http://msdn.microsoft.com/en-us/library/ms187885.aspx

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.

    • Marked as answer by KJian_ Tuesday, March 13, 2012 3:12 AM
    • Unmarked as answer by Raouf.naguib Tuesday, April 03, 2012 9:24 PM
    • Marked as answer by Raouf.naguib Tuesday, April 03, 2012 9:25 PM
    Thursday, March 08, 2012 5:39 AM
  • Jian, Moor

    sorry for the delay in responding back. I appriciate your help.

    Yes I am using the same cridentials "sa" in both cases. The OLE and the ODBC. but with the OLE it connects smoothly while with the ODBC it doesnt, and returns the error I mentioned above.  I tried adjusting all kinds of different settings of the ODBC both in 64 and 32 bits.

    and yes when I connect to the SQL Server management studio using sa it connects with full access, so sa is not disablled.

    To make it more bizzar, there is an ODBC connection used by the dynamics GP clients and it works fine. the problem is mainly in Crystal Reports.

    Tuesday, April 03, 2012 9:30 PM
  • Jian,

    I just tried the links that you gave me in your reply and the error log showed:

    2012-04-03 16:09:43.08 Logon       Error: 18456, Severity: 14, State: 8.
    2012-04-03 16:09:43.08 Logon       Login failed for user 'sa'. Reason: Password did not match that for the login provided.

    2012-04-03 16:43:19.06 Logon       Error: 18456, Severity: 14, State: 8.
    2012-04-03 16:43:19.06 Logon       Login failed for user 'raouf'. Reason: Password did not match that for the login provided. [CLIENT: 192.168.0.239]

    but I am sure that the passwords I am using is correct, because I use it under SQL Server management studio and it connects.

    Tuesday, April 03, 2012 9:44 PM