Skip to Content
avatar image
Former Member

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

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!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

7 Answers

  • Best Answer
    Jul 06, 2017 at 10:06 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 28, 2017 at 07:40 PM

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

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

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 29, 2017 at 01:50 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Jul 05, 2017 at 04:32 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 05, 2017 at 05:35 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Jul 06, 2017 at 09:08 PM

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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 11, 2017 at 04:58 PM

    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!

    Add comment
    10|10000 characters needed characters exceeded