Skip to Content
avatar image
Former Member

Problems connecting to MS SQL 2016 using SDK with VS 2015

I am having difficulty connecting to an MS SQL 2016 server from a VB.net desktop application using the Crystal Reports SDK.

Using the SDK development document and researching this problem on the web, I have researched and tried many different things. Below I have included a routine which includes the best practices as described by the CR documentation and found in various internet help sites. The routine does work as expected on my development workstation.

The application has various other routines that sign-on to the same database. When I run the application against the production server, the application does sign-on to the production database and functions as expected. The problem arises when the crystal report functions of the application attempts to connect a crystal report document to the production database. The attempt fails with the message: Logon failed

These are the connection strings which work successfully on my development workstation:

connectionString="Database=MySQLdb;Server=MyWorkstation\SQLEXPRESS;Integrated Security=true; 
connectionString="Database=MySQLdb;Server=\\.\pipe\MSSQL$SQLEXPRESS\sql\query;Integrated Security=true; 
connectionString="Database=MySQLdb;Server=MySQLdbAlias;Integrated Security=true;

These are the connection strings which work for the application but not for the CR routines against the production server.

connectionString="Database=MySQLdb;Server=SQL2016Prod\MySQLdbInstance;Integrated Security=true; 
connectionString="Database=MySQLdb;Server=MySQLdbAlias;Integrated Security=true; 


I have also tried connecting with a valid database user and password with the same results.

In the CR routine, all goes well until the reportDocument.VerifyDatabase() line which returns the error "Logon failed"

This is the routine I am using to load a CR document, connect to an SQL database, and display the report:

Public Sub SetReportSQL(ByVal ServerInstance As String, DatabaseName As String, IntegratedSecurity As Boolean, ByRef CrystalReportViewer As CrystalReportViewer, ByRef reportDocument As ReportClass, Optional UserName As String = Nothing, Optional Password As String = Nothing)

	Dim _connection As New ConnectionInfo
	With _connection
		.ServerName = ServerInstance
		.DatabaseName = DatabaseName
		.IntegratedSecurity = IntegratedSecurity
		If IntegratedSecurity = False Then
			.UserID = UserName
			.Password = Password
		End If
	End With
	If IntegratedSecurity = True Then reportDocument.DataSourceConnections(0).IntegratedSecurity = IntegratedSecurity

	For Each table As Table In reportDocument.Database.Tables
		table.LogOnInfo.ConnectionInfo = _connection
		table.ApplyLogOnInfo(table.LogOnInfo)
	Next

	For Each subrep As ReportDocument In reportDocument.Subreports
		For Each table As Table In subrep.Database.Tables
			table.LogOnInfo.ConnectionInfo = _connection
			table.ApplyLogOnInfo(table.LogOnInfo)
		Next
	Next

	If CrystalReportViewer.LogOnInfo IsNot Nothing Then
		For Each tbloginfo As TableLogOnInfo In CrystalReportViewer.LogOnInfo
			tbloginfo.ConnectionInfo = _connection
		Next
	End If
	
	reportDocument.VerifyDatabase() 'Returns "Log on failed."
	reportDocument.Refresh()
	CrystalReportViewer.ReportSource = reportDocument
	CrystalReportViewer.Refresh()
End Sub

The production database has the same alias assigned and DNS correctly points to the production SQL server. The development workstation does not have access to the production domain - the application is deployed to a production workstation; which; as I mentioned connects to the production SQL database and functions as expected in all respects except for the use of crystal reports.

Any help, thoughts or suggestions would be most welcome!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jun 19, 2017 at 01:40 PM

    Updated tag to CR for VS

    First question is what SDK are you using?

    CR no longer ships the .NET SDK so you need to use the one from here:

    https://wiki.scn.sap.com/wiki/display/BOBJ/Crystal+Reports%2C+Developer+for+Visual+Studio+Downloads

    Is the Production server also using SQL Express?

    If you are connecting via ODBC don't specify the Server Name in the connection string, that should be specified in the DSN.

    Be sure to use the MS SQL Native 10 or 11 client in the DSN.

    You may need to use ReplaceConnection() API, because of the change in DB Server.

    See this for a test app that does connection, you may need to modify it to use Trusted Auth"

    https://blogs.sap.com/2016/02/17/how-to-parameters-in-crystal-reports-for-visual-studio-net/

    Don

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 15, 2017 at 03:03 PM

    Hi, this is probably caused by the service/user account being used by the application producing the Crystal report. Also, make sure your users have the minimum required authorization on the production SQL instance to generate the reports you need.

    Add comment
    10|10000 characters needed characters exceeded