on 12-11-2009 1:51 PM
I have written a view in SQL server 2005 that shows the current logged in user in the pc and I am using a crystal report to that uses this view to show the results. Everything works ok in the VS2005 code, but the moment I publish the web site and run it, I get the following error
Logon failed.
Details: [Database Vendor Code: 18456 ]Database Connector Error: ' [Database Vendor Code: 18456 ]'Logon failed.Error in File Report1 {E519B136-0B07-4E41-99AD-639E0AEDB03A}.rpt:
Unable to connect: incorrect log on parameters.
Details: [Database Vendor Code: 18456 ]
I checked the logon information and everything is ok. If I run a crystal report that connects to a regular table in SQL server, everything works. I am using the below code to run this, and I am sure that I get the error with where I set the location. If I remove it, I don't get the error, but still it won't connect.
Dim subRepDoc As New ReportDocument()
Dim crSections As Sections
Dim crReportObjects As ReportObjects
Dim crSubreportObject As SubreportObject
Dim crDatabase As Database
Dim crTables As Tables
Dim crLogOnInfo As TableLogOnInfo
Dim crConnInfo As New ConnectionInfo()
Dim dbuser As String = "testuser1"
Dim dbpwd As String = "testpas123"
Dim dbname As String = "Test_data"
Dim dbserver As String = "Test_data"
crDatabase = repDoc.Database
crTables = crDatabase.Tables
For Each crTable As CrystalDecisions.CrystalReports.Engine.Table In crTables
crConnInfo.ServerName = dbserver
crConnInfo.DatabaseName = dbname
crConnInfo.UserID = dbuser
crConnInfo.Password = dbpwd
crConnInfo.Type = ConnectionInfoType.SQL
crConnInfo.AllowCustomConnection = True
crConnInfo.IntegratedSecurity = True
crLogOnInfo = crTable.LogOnInfo
crLogOnInfo.ConnectionInfo = crConnInfo
crTable.Location = "Test_data.dbo." & crTable.Location
crTable.ApplyLogOnInfo(crLogOnInfo)
Next
I tried different ways of setting the location, but received the same result.
crTable.Location = "dbo." & crTable.Location.Substring(IIf(crTable.Location.LastIndexOf(".") > 0, crTable.Location.LastIndexOf(".") + 1, 0))
crTable.Location = crConnInfo.DatabaseName & ".dbo." & crTable.Name
crTable.Location = crTable.Name
Any help is greatly appreciated.
I resolved similar issue. I am interfacing a legacy VB 6.0 application with CR XI R2. In my case the error occurred because user name was in caps in my app and the report was created using a lower case username (sql user). The report is migrated from CR 7.0 to CR XI R2. The user name while interfacing from VB 6.0 to CR XI R2 is case sensitive.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Sounds like your database server isn't configured for both NT and SQL authentication.
or you can use sa user that have access over target database.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Database Vendor Code: 18456 is coming straight out of the database. E.g.; CR is just reporting what the database is telling it. Googling the error, it looks like there is some authorization/authentication issue. It may be that the appropriate permission is not give to the user on the view(?).
The following re. the error may also be helpful:
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
In view of the above note, you should ensure you have the latest patches. Not knowing what version of CR, I am not able to point you in the right direction there.
If the view is in a subreport, make sure you are also passing the logon to the subreport.
Finally, Process Monitor may tell you something re permissions;
http://technet.microsoft.com/en-ca/sysinternals/bb896645.aspx
Ludek
Follow us on Twitter http://twitter.com/SAPCRNetSup
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I have crystal reports 2008 installed. I have been testing this a lot and found the following strange and interesting thing with Crystal reports 2008.
When I am creating a report if I add the SQL command first and then add a SQL view everything works without any issues, but if I add the SQL view first, then I get the above error. Initially I did not believe this. Then I ended up creating about 10 reports and testing and I am 100% sure that this is the case.
Where coiud I find the llatest service packs for Crystal 2008? I want to give it a try.
Latest SP is here;
https://smpdl.sap-ag.de/~sapidp/012002523100009038092009E/cr2008win_sp2.exe
Latest FP is here;
https://smpdl.sap-ag.de/~sapidp/012002523100015097962009E/cr2008_fp22.exe
I'll check with the database guys, see if they have any insight on this.
Ludek
Follow us on Twitter http://twitter.com/SAPCRNetSup
Three questions;
1) Are the SQL command and SQL view linked in the report?
2) The report works fine in the designer?
3) You are not changing the datasource at runtime?
Ludek
Follow us on Twitter http://twitter.com/SAPCRNetSup
Hi Don,
You did not say which driver you are using, ODBC, OLE DB etc. It may be there are differences between versions of MDAC installed between your Dev PC and the web server. Microsoft has a tool to test the version installed, see if they are the same.
Next test is try hard coding in a valid user name and password, a WEB app may be passing in the local system account info which is typically what IIS is running under, that user likely doesn't exist in the DB server.
Next is to download Modules, you'll find in the download area by clicking on Businessobject tab above, Downloads on the left and then CR 2008 and all. It's typically listed at the end of the list of files.
Run Modules on both your Dev PC and the WEB server and then run the same report. Then run modules and save and compare listings using the Differences option. Look for CR differences first, either crqe.dll, querybuilder.dll, crdb_ole.dll or which ever driver you are using. They should be the same version as your test machine.
Another option is to turn on SQL Profiler in MS SQL Server tools and see if your web server is getting denied access though tracing.
A final test would be to install Crystal Reports on the WEB server to see if any missing runtime or some runtime files did not get updated. But that's the last resort, it typically fixes the issues if it is a CR distribution problem.
Thank you
Don
Thanks for you reply. I will try all the solutions that you mentioned. I think the problem occurs when I set the location for each table.
Because of this problem I have already installed the VS2005 and Crystal 2008 in the web server. And it works fine in the code, but not when I deploy the application to the same server. And I am using ODBC for SQL server. I have already installed all the latest patches for Crystal 2008. Still no luck.
OK, I have changed the code to the following to login to SQL tables in crystal
Dim crLogin As New ApplyCRLogin
crLogin._dbName = txtDatabase.Text
crLogin._serverName = txtServer.Text
crLogin._userID = txtUser.Text
crLogin._passWord = txtPassword.Text
crLogin._integratedSecurity = True
crLogin.ApplyInfo(myReport)
crLogin = Nothing
Public Class ApplyCRLogin
Public _dbName As String
Public _serverName As String
Public _userID As String
Public _passWord As String
Public _integratedSecurity As Boolean
Public Sub ApplyInfo(ByRef _oRpt As CrystalDecisions.CrystalReports.Engine.ReportDocument)
Dim oCRDb As CrystalDecisions.CrystalReports.Engine.Database = _oRpt.Database()
Dim oCRTables As CrystalDecisions.CrystalReports.Engine.Tables = oCRDb.Tables()
Dim oCRTable As CrystalDecisions.CrystalReports.Engine.Table
Dim oCRTableLogonInfo As CrystalDecisions.Shared.TableLogOnInfo
Dim oCRConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo
oCRConnectionInfo.ServerName = _serverName
oCRConnectionInfo.UserID = _userID
oCRConnectionInfo.Password = _passWord
oCRConnectionInfo.IntegratedSecurity = _integratedSecurity
For Each oCRTable In oCRTables
oCRTableLogonInfo = oCRTable.LogOnInfo()
oCRTableLogonInfo.ConnectionInfo = oCRConnectionInfo
oCRTable.ApplyLogOnInfo(oCRTableLogonInfo)
If (oCRTable.Location.IndexOf(".")) > 0 Then
oCRTable.Location = oCRTable.Location.Substring(oCRTable.Location.LastIndexOf(".") + 1)
Else
oCRTable.Location = "dbo." & oCRTable.Name
End If
Next
End Sub
End Class
Now when the statement hits the oCRTable.Location line, it gives me an error "
The table 'Pn_OrData' could not be found. Error in File.
I have triple checked the report and the table does exists in SQL server. This error some when I run the application inside VS2005.
So I did the following.
commented all set location statements.
Open the machine.config file from C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG
and edited the <processModel statement with the following
<processModel enable="true" userName="ntsdw\ntadmin" password="pasntad"/>
It works in the VS2005 and when I publish the site everything works like a charm in my current development pc (Windows XP).
So I did the same changes in my actual web server (Windows 2003 enterprise edition) machine.config file and published the site there. Now when I run the report it connects to the SQL server using the the logon info that I entered in machine.config file, but in my win xp pc, site uses the user id and password that I provide when I log in to the app.
I am not sure why windows 2003 server is ignoring the logon info that I provide.
Could any one help please? This is the closest that I have come to solving this issue and I am running out of time.
Any help is greatly appreciated.
Edited by: Don Perera on Dec 18, 2009 7:08 PM
Hi Don,
Question for you:
Dim dbuser As String = "testuser1"
Dim dbpwd As String = "testpas123"
Dim dbname As String = "Test_data"
Dim dbserver As String = "Test_data"
Is the dbserver "test_data" the name of your DSN or the PC name where SQL is installed? And is your DB name the same as the DSN name? It should not be an issue but try using a different name for your DSN/dbserver variable.
Also, is your DSN a system DSN on the WEB server and can you test the connection to the server through ODBC Admin?
Is SQL install on the same PC as the web server? Test by pinging the server if they are not, verify the WEB server has access to the DB server, could be a firewall or network trust issue.
One last check is to enable the Guest account on the WEB server. I've seen problems before if it's not enabled.
Thank you
Don
Thanks for the reply.
Is the dbserver "test_data" the name of your DSN or the PC name where SQL is installed? And is your DB name the same as the DSN name? It should not be an issue but try using a different name for your DSN/dbserver variable.
test_data is the DSN name. And yes, my DB name is the same as DSN name. I tried a different name. Still did not work.*
Also, is your DSN a system DSN on the WEB server and can you test the connection to the server through ODBC Admin?
DSN is a system DSN (ODBC for SQL server). i have tested the connection and it works.
Is SQL install on the same PC as the web server? Test by pinging the server if they are not, verify the WEB server has access to the DB server, could be a firewall or network trust issue.
No, SQL server is on a different pc. I can ping the server. And yes, the web server has acceee to the db server
One last check is to enable the Guest account on the WEB server. I've seen problems before if it's not enabled.
Enabled the Guest account. Still the same error
I am thinking that this is a configuration issue. I have crystal installed on the web server and the report runs fine without any issues, but through the web it always uses the user in machine.config file. If I remove the user from the machine.config file, the report does not run.
It works fine in the Win XP deployement environment. I am missing something with the Windows 2003 server.
Hi Don,
No, sorry. I suggest you purchase a case and have a dedicated support engineer work with you directly:
Whomever picks up the case can do a Netviewer session with you and see what's happening and gets logs etc.
Thank you
Don
I'm completely stumped as well. In my code, I set up several reports that are chosen from a drop down list and all the other reports work except 2 that have the exact same error, the Database Vendor Code:18456. I was thinking that since I've connected to a new server that an error is occurring as a result of the stored procedure, but the same proc is used in several other reports that are chosen from the same drop down list. Nothing's unique about this report except how it manipulates to data.
The point in the code where it logs on, loads fine with no errors, but when its time for the viewer to render this one particular report, I get this error. Furthermore, I recreated the report from scratch from within Visual Studio 2012 and got the same error... this extremely frustrating and stressful. I find myself having to move away just to curb the physical reaction to the frustration...
Its not a logon issue, but this is how its reported. One more thing, the report runs fine within visual studio 2012... a thought... could this be a crystal licensing issue?
Hello Clarence
I understand your frustration, but the first thing we tell anyone with "Vendor Code xxxx" is that this is an error passed directly from the database through the report engine. Completely unmodified. So the 1st thing for you to do is to look at your database documentation and see what that error means. Second, you should google the error. I did, and came up with a number of suggestions - most not CR related. E.g.; the error comes up with non CR solutions also.
Finally, make sure you have updated your SQL driver (if you are using MS SQL) to SQL Native 10 Driver. See this MS content:
http://msdn.microsoft.com/en-us/library/ms131035(v=sql.105).aspx
- Ludek
Senior Support Engineer AGS Product Support, Global Support Center Canada
Follow us on Twitter
Thanks for your help... the biggest confusion I experienced was that several reports were working and 2 reports were not working using what was supposed to be the same code, however, the logon information was stored in cache in the browser which related to the report by GUID... when I cleared the browser, all of the reports had the same issue. When I enabled database logon prompt to the viewer, before I reset the cache, only the two problem reports prompted for the logon information with the password information missing. However, after clearing the cache, all of the reports prompted me for the password.
After changing the driver to the Native Driver, then switching from SetLogon to ApplyLogOnInfo finalized the fix for me... by the way all of this started when my client upgraded from MS SQL 2008 R2 to MS SQL 2012.
DataSourceConnections dscn = rd.DataSourceConnections;
IConnectionInfo ci = dscn[0];
ci.SetConnection(DBServer, DBCatalog, integratedSecurity);
ci.SetLogon(rptID, rptPW);
TO
ConnectionInfo cnnInfo = new ConnectionInfo();
cnnInfo.ServerName = DBServer;
cnnInfo.DatabaseName = DBCatalog;
cnnInfo.UserID = rptID;
cnnInfo.Password = rptPW;
foreach (CrystalDecisions.CrystalReports.Engine.Table tbl in rptDoc.Database.Tables)
{
TableLogOnInfo tli = tbl.LogOnInfo;
tli.ConnectionInfo = cnnInfo;
tbl.ApplyLogOnInfo(tli);
foreach (ReportDocument rd in rptDoc.Subreports)
{
foreach (CrystalDecisions.CrystalReports.Engine.Table subTbl in rd.Database.Tables)
{
TableLogOnInfo tli2 = subTbl.LogOnInfo;
tli2.ConnectionInfo = cnnInfo;
subTbl.ApplyLogOnInfo(tli2);
}
}
}
User | Count |
---|---|
78 | |
10 | |
7 | |
6 | |
6 | |
6 | |
6 | |
6 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.