How to Connect to CDL SQL Server Database via Internet

There are several different methods available for connecting to the CDL SQL Server. Microsoft Windows has built in tools that are very easy to implement because they have been integrated with ODBC or OLE DB Provider SQL Server drivers, and you don’t need to install anything. Depending on the application you are trying to connect to the database you may need to set up a 32-bit or 64-bit driver – see the notes for each method to ensure you are using the correct version.

The SQL Server instance is “Prod”, and internally we use port 1433 to connect to the SQL Server. Externally via the internet, we use port 34500 to connect to 66.46.104.13. Microsoft uses the format

“66.46.104.13:34500\Prod”, and now transfers to “66.46.104.13,34500\Prod”.

1. Using an ODBC

First, you need configure an ODBC DSN name. In ODBC Administrator, there are two different kinds of DSN names; user DSN and system DSN. User DSNs are for regular applications while System DSNs are for web applications. In the Windows control panel, within the Administrative tools group, you can find the “data Sources (ODBC)” shortcut. It is a 32 bit driver administrator referring to Windows\system32, so it can only create a 32 bit driver DSN. Mostly we use this to create DSN.

If you didn’t install the SQL Server client, you will only see “SQL Server” driver. If you have installed SQL Server you will see both “SQL Server Native Client 10” driver and “SQL Server” driver. They both can be used as follows:

The only difference here is that native clients can use the alias name you configured in SQL Server client in the Server field.

In the authentication page, we can only use SQL Server authentication because we can’t join domains out of office, except using VPN. Now we can use ‘test/test1234’. The next steps will use default options to test if it works.

If you use FileMaker (64 bit) to connect to CDL SQL Server through your ODBC, you will need to configure a 64 bit DSN. The administrative tool (odbcad32.exe) is located in “c:\windows\sysWOW64”. It is also a 32 bit tool but can manage 64 bit drivers. If you create a DSN that is the same as a 32 bit DSN, it will overwrite it without notice. We recommend you include “32” or “64” at the end of the DSN name ex. “CDL_SQL32” or “CDL_SQL64”.

After you create an ODBC DSN, you can use it in other applications.

2. Using Excel In Excel, you can get data from CDL SQL Server through an ODBC driver or direct from SQL Server. They are located in the “Data” tab and within “From Other Sources” shown below:

By choosing “From SQL Server”, you will see the whole table data with no choices. However, from Microsoft Query through ODBC, you can choose columns, set conditions and sort data.

The wizard will let you choose the DSN (32 or 64 depends on your excel edition), enter a username and password, choose table and columns, set conditions, and select the data layout. See above.

3. Using Access Through an access database, you can create a linked table or import a table from CDL SQL Server. The linked table is very fast and the most useful. First, you need to create a database or open an existing database, click “ODBC Database” in “External Data” tab. The wizard will let you choose to import or create a linked table. Choose ODBC DSN (32 or 64) and choose multiple tables.

4. Using SpotFire SpotFire can be connected to CDL SQL server as Data Tables. Go to FileAdd Data Tables. In the ‘Add Data Tables’ windows, click ‘Add’ button and scroll down to ‘Microsoft SQL Server’.

In the ‘Microsoft SQL Server Connection’ window, enter 66.46.104.13,34500\Prod for Server field and select ‘SQL Server authentication’ for ‘Authentication Method’. The Database option FRAC will be available after ‘Connect’ button clicked with correct Username and Password.

Click ‘OK’ button, then ‘Views in Connection’ window shows available tables.

5. Using SQL Server and SQL Server Linked Server Connect to CDL SQL Server using SQL server Management studio is very easy. Just enter “66.46.104.13,34500\Prod” in the Server field and enter your username and password. If you use the SQL Server linked server feature, the CDL SQL Server needs to be configured. Now it has been configured. On the linked server side, the best way is to create an alias name (you can directly create linked server without alias but the name([66.46.104.13,34500\Prod]) is too long to use). The only challenge is you need to first create a 32 bit client alias name, like “CDL”, and then create the same name in 64 bit driver. If only create a 64 bit alias name, you can’t connect to CDL SQL Server using it. If you only create a 32 bit alias name, you can only connect to CDL SQL Server using it, but can’t create a linked server through it. If you create two matching names, you will create a linked server quickly.

Using this method, the linked server name is the same as the SQL Server alias name.

You can also use other data source options (such as Microsoft OLE DB Provider for SQL Server), but you may need to enter lots of information very carefully for it to be successful.