Add a new connection string in Connections.config using Config Editor

The following steps will show how to add a new connection string in Connections.config file using Config Editor:

  • In SheetKraft ribbon, click on Config Editor Button.

  • A new window will open

  • We can see two radio buttons present at the top,

  1. Load server config file: Loads the config files present at the server.

  2. Load addin config: Loads the config files present at the addin.

  • Then select SheetKraft Folder present in AppData\Local by clicking on the 3 dots buttons.

  • On selecting the folder, various connection dropdowns will appear, these are the connection files present in the SheetKraft folder

  • Click on the first dropdown, that is connectionStrings. We can see the details of already present Connection Strings.

  • To add a new Connection String, first enter the name you want to give to the Connection String given

And Click on Add button.

  • A new Window will open,

  • The first input is Connector, Select the desired Connector from the dropdown(default connector is SQL Server)

  • We need to select connector with respect to the type of Database Management Software which we will be connecting, such as SQL Server, PostgreSQL, Oracle, MySQL, etc.

  • When connecting to these database Management Applications, we need to have their respective drivers installed in our system.

    • To connect to SQL Server:

      • Select SQL Server from Connector dropdown

      • After selecting Connector, provide the Connection String details Data Source, User ID, etc. accordingly

      • For Data Source and Initial Catalog, Click on Get button present beside each field

  • After entering the details, click on Test button

  • If the Connection is successful, a new window with ‘Connection successful’ message will appear.

  • Click OK, then again Click OK button in the database Connection Window.


  • To connect to Oracle, ODP.NET must be installed:

    • Installing ODP.NET to access Oracle databases

    • Go to https://www.oracle.com/database/technologies/dotnet-odacdeploy-downloads.html

    • Scroll down to the section ODAC XCopy

    • Download one or both of the following (You will need an Oracle account - registration is free)

    • 64 bit ODAC 19.3 (or latest version)

    • 32 bit ODAC 19.3 (or latest version)

    • If you are installing on a server you only need the 64 bit version.

    • If you are installing for the SheetKraft addin, you only need the version that matches the bitness of Excel.

    • You need both only if you want to use the addin on 32 bit Excel on a machine to be used as a server also. Installation is more complex in this case.

    • Extract the zip into a new temporary directory. Don't use the Extract here feature of your zip software on a non-empty folder as you will get a lot of files in the current folder.

    • Open a command prompt with admin rights and cd to the newly extracted directory. This directory should have an install.bat file along with other files. Run the command:

    • install.bat odp.net4 C:\oracle\19.3.0\odac32 odac

or

    • install.bat odp.net4 C:\oracle\19.3.0\odac64 odac

    • depending on whether you are installing the 32 bit version or 64 bit version.

    • If you have downloaded a version different from 19.3, change the command appropriately.

    • If you are installing both 32 and 64 bit, open a command prompt as admin and cd to C:\Windows\System32 and run the command mklink /d odac193 C:\oracle\19.3.0\odac64 (Yes, 64 in System32!, 193 comes from 19.3, so change if version is different)

    • Then cd to C:\Windows\SysWOW64 and run the command mklink /d odac193 C:\oracle\19.3.0\odac32 (Yes, 32 in 64!)

    • Add C:\Windows\System32\odac193 and C:\Windows\System32\odac193\bin to the top of the system path.

    • If you are installing only one of 32 bit OR 64 bit.

    • Add C:\oracle\19.3.0\odac64 and C:\oracle\19.3.0\odac64\bin

    • OR C:\oracle\19.3.0\odac32 and C:\oracle\19.3.0\odac32\bin

    • to the top of the system path variable. To change the path you can go to Control Panel > System and Security > System > Advanced System Settings > Advanced > Environment Variables > System Variables and edit the Path variable.

    • Verify that the Users group does not have Modify access access on the C:\oracle\19.3.0 folder. This will ensure that a malicious dll cannot be placed in this location.


  • To connect to MySQL, MySQL ODBC Unicode Driver must be installed:

    • In the Config Editor Enter a desired name to the Connection String and Click on Add button.

  • In the next window, in the Connector Dropdown select MySQL (ODBC) – MySQL ODBC 8.0 Unicode Driver.

  • After selecting the Connector, enter the Connection String details such as Server, Port, User, Password, Database(After entering the Database click on Get button).

  • Now we can see the Connection String generated in the field below,

  • Click on Test button, if the Connection is successful, a new window with ‘Connection successful’ message will appear.

  • Click OK, then again Click OK button in the database Connection Window.


  • Now we can see the newly added Connection String details with previously present Connection Strings.

  • Click on Save button, to save changes.

  • We can view the newly added connection string in the Connections.config file.