Connecting local CR workstation to a remote database server over a TCP/IP oonnection

[Former Member]

Hello,

I'm revisiting Crystal Reports again and I recall that this is possible, but can't recall the steps to accomplish it. I need to connect a local workstation running Crystal Reports 2016 to a remote server box that contains the MSSQL database itself. Can this still be done?

Please provide the steps I need to set this up. Any help/advice would be greatly appreciated!

[former_member292966 · Accepted solution]

Hi Peter,

The SQL Server client ODBC driver should match the one on the server. Also, remember Crystal is 32 bit so you make sure you install the 32 bit client otherwise Crystal will never be able to connect. It is possible to have both 32 and 64 bit client installed.

Brian

[Former Member]

Hi Brian,

I wanted to get back to you and let you know I was able to make the remote ODBC connection. Everything you pointed out was correct. I needed to create a new user with a password to SQL that worked. Once I did that, I was in.

Thanks for your help!

[Former Member]

Brian, do you mean the SQL Server Client ODBC driver to match the one on the server? Or do you mean other software?

[former_member292966]

Hi Peter,

I believe I understand what you are trying but let me make sure I understand fully. You have a SQL Server on your network. In that SQL Server, you have a linked database configured that is connected to another SQL Server somewhere on the Internet. You want Crystal to use the tables from that linked database.

Is this correct?

If it is then there are a couple of things you need to know before you start designing reports against it. 1) Crystal won't be able to see the linked server because the driver can't see it. 2) Performance will be slow at best. Painfully, suck your brains out waiting for data awful most of the time. Whenever you try querying a linked server, the host database server has to parse out what part of the query is local and what part is remote and mash them together. Thankfully your remote database is also SQL Server otherwise SQL Server needs to translate the query into the proper flavor of SQL. That part is nasty.

What I recommend, is to create a View or Stored Procedure that uses the OpenQuery method. This will allow you to query a linked server, using that database's actual SQL syntax and return the records to the local database. This ensures the indexes on the Linked Server are being used and it will increase performance.

Better still, using the OpenQuery method and have it populate a working table on the local database and have the report go against that record set. The advantage here is you can have the OpenQuery filter out just the records and manipulation on the database side instead of in Crystal. Huge performance advantage doing it this way.

Good luck,

Brian

[Former Member]

Hey Brian,

Actually, the only SQL server is the one where our Internet server is located at a private IP provider. The only thing we have at our office network level are the Crystal Reports application and dashboard. Normally, we "Remote Desktop" to the IP address of that server. I have an ODBC connection set up successfully on the Internet server, but I can't seem to install the ODBC I need inside Crystal to connect up to the Internet server.

Hopefully, this is an easier scenario than the one you described above.

Thanks for your thoughts.

[former_member292966]

Hi Peter,

You need to download the SQL Server client for your desktop and create an ODBC datasource to connect to your database. It's your local ODBC connection that goes out to the database. Same as plugging in an electrical cord for a light. It's the cord on the light that makes the connection to get the electricity.

Make sure you install the same version of the SQL Server client as you have on your database server. As long as your database server is configured to accept external connections, you should be able to make the connection.

We don't allow external connections to our databases at my office, our database servers are installed on secured servers with no external connections so I do have to remote into a terminal server.

If you can't connect with ODBC, then you may need to look what security policies are in place on the server.

Good luck,

Brian

[former_member11696]

Hi Peter,

The connection is done through ODBC, select your Server you want to report off of. Must also be the 32 bit ODBC Admin., CR Designer is 32 bit only.

Select a System DSN, for the name what ever you want, for the connection select the IP Address to the Server and then fill in the user name, select the DB you want and test. Once ODBC connection works then simply select the DSN in CR Designer. If ODBC Test connectivity does not work then CR will not either.

For OLE DB, same thing, enter the IP Address and the User/PW and if you have access the drop down list for the DB's you have access to will show up. If it fails you may need to add the IP to your PC's HOST file located here:

C:\Windows\System32\drivers\etc

Don

[DellSC]

Like Vitaly said, you'll need to create an ODBC connection. However, you need to make sure that you use the correct version of the SQL Server Native Client (NOTE: This is NOT the SQL Server ODBC client!) in order for this to work.

For SQL Server 2005, use the version of the Native Client that is normally installed with Window (version 6.x, I think.)

For SQL Server 2008, use SQL Server Native Client 10.0.

For newer versions of SQL Server, use SQL Server Native Client 11.0.

-Dell

[Former Member]

Thanks Dell,

I'm using the Native Client 10.0 with SQL Server 2008. That installed no problem on the server side.

The question now is on the local Crystal Reports machine, in order to connect to the remote server, do I use the "Universal Web Service Connection" under the More Data Sources folder? The server is at a specific IP address over the Internet.

[DellSC]

Is your ODBC connection on the server or the client? Is the data provided in a web service or are you directly connecting to the data?

The ODBC connection needs to be on the application side and configured to connect to the database via IP address and port number instead of server/cluster name. Then you'll use this ODBC connection in your report.

-Dell

[Former Member]

Hi Dell, The ODBC connection is on the server and the data is on our Internet server at a different physical location.

[DellSC]

Hi Peter,

So you'll configure the ODBC connection as a System DSN to connect to the database via its IP address. Be sure that you're using the SQL Server Native Client for this - NOT the MS SQL ODBC driver, which Crystal doesn't work with. Use the following version of the Native Client based on which version of SQL Server you're connecting to:

SQL 2005 - use the version 6 Native Client that is usually installed with Windows.

SQL 2008 - use SQL Server Native Client 10.0

SQL Server 2012 and newer - use SQL Server Native Client 11.0

In Crystal, you'll select the Microsoft SQL Server connection type and then the ODBC connection you've created.

-Dell

[vitaly_izmaylov]

Create ODBC DSN using SQL Server Native Client driver for the MSSQL Database.

Then in CR Designer use ODBC connection to connect to it.