How To Connect An External Database To Business Central

 

In this post I would like to share the way you can connect an external SQL Server database to Business Central. This way you can check historic data from an older versions of Nav.

Retrieve data from other databases and work with them as if you had a Business Central table.

Create the table to connect

First of all, we need to create a table in Business Central. As an example, we will connect to an older version of Nav and get the customers from the database.

The table type will be ‘ExternalSQL’ and the external name will be the same that it´s in SQL Server (Company Name + Table Name + App ID).

Add the fields you wish to retrieve from the Sql table.

An example of a table would look like this.

Note that if there is no primary key, BC will assign it to the first field.

Then, if you will login with Windows authentication or username and password. With this information create the connection string:

//Windows Authentication: 'Data Source=<DatabaseServer>\<DatabaseServerInstance>;Initial Catalog=<DatabaseName>;Integrated Security=SSPI;' //Sql Authentication: 'Data Source=<DatabaseServer>\<DatabaseServerInstance>;Initial Catalog=<DatabaseName>;User ID=<username>;Password=<password>'

Finally, build the connection. The structure of the connection will always be like the following, only changing the name of your database and the connection string.

Create the page

To verify that the connection is successful we will create a page with the information of the external table. On the ‘OnInit’ trigger you need to place the connection code.

Run the page in the web client, the connection will be stablished and you will see the data from Sql:

Note that the connection is bidirectional. If you modify the data from Business Central the external database will be modified as well.

Please share your comments

Thanks & Regards

Srk.A