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.