+44(0)1908 545 770
YOUR EXPERT RPA PARTNER

Team up with our RPA strategy and tech specialists for maximum automation impact

Find out more
Harness the power of UiPath in your business.
WATCH NOW

Save money and spend more time on priority work.

Book consultation
READY, SET, AUTOMATE

Let's choose, design and deploy a process automation solution for your business.

Find out more
TRANSFORM YOUR BUSINESS

Let's analyse your business to discover opportunities for innovation and automation.

Find out more

Begin your business transformation journey

Book consultation
AUTOMATE EVERY FUNCTION

Automate where it matters most. Scale to solve challenges in every area

Find out more
GIVE EVERY TEAM THE TOOLS FOR SUCCESS

Equip people with user-friendly solutions that save time and make work easier

Find out more

Which business problem
can we solve first?

Book consultation
03 September 2016

Using Azure SQL Server for your K2 Application Database

In a previous post we looked at how you can use Azure Blob storage as a file storage location and integrate that with your K2 application. In this post we will be looking at how you can let your application database live in the cloud.

K2 Automated Testing Software

BenchQA allows full test automation of K2, including fully automated K2 SmartForms and K2 Workflow testing. It promotes test driven development for K2 and ensures continued quality assurance for K2 solutions. Easily apply changes to test cases to accommodate changes to K2 apps and ensure all apps are regression tested to avoid defects and assure continuous quality.

 

Free K2 Five Upgrade Review

The first step is to create your SQL database in Azure.

1 - Create Azure SQL Database

For this example I use the following settings:

2 - Azure Database settings

Most of the settings here are self-explanatory, the most important thing to note here is that the collation needs to be Latin1_General_CI_AS, as this is the only collation officially supported by K2.

The other thing to note as that, if you have not created a SQL Server Instance yet, you can do so from here by expanding the server node, and clicking on ‘Create a new server’.

3 - Azure SQL Instance

After creating the database you can open SQL Server Management studio and connect to the SQL Server you created it on. To do this you need the server name. This is easy to find by clicking on All resources > Your DB Name > Overview, and looking at the Server Name.

4 - Azure SQL Instance

You can then try to connect to the database using the server name and the login credentials previously supplied, you might however first receive an error because of the SQL Server firewall in Azure not letting your IP address through.

5 - Connect to Azure SQL Database

6 - Connect to Azure SQL Database

You can add you IP address to the firewall by going to All Resources > Server Name > Firewall. Add the IP and then press Save.

7 - Connect to Azure SQL Database

Now you should be able to connect to the database.

For this example I just created a simple table called Test with ID and Name columns, and added one row to it, with ID=1 and Name = Test.

8 - Create a test database table

The next step is to create a login which you will be using to connect to your database from your K2 Server instance. Execute the following SQL for that.

9 - Create Database login name

Now you are in a position to add the SQL Server Service Instance through the SmartObject and Services Tester.

The non-default settings on this screen is

On Different SQL Server – true
Database – Test
Servert – tcp:blogtest123.database.windows.net,1433

10 - Register Azure SQL Database K2 SQL Server Service Instance

For testing purposes I am just going to Create Default SmartObjects from this Service Instance by right-clicking on the service and selecting ‘Generate SmartObjects’.

11 - Generate SmartObjects

The newly created Smart Object will now be available, and we can successfully execute a read against our Azure database.

12 - Test K2 SmartObject

But when we try to execute the List method we will be presented with a security error!!!

13 - Test K2 SmartObject - List Method

This is due to the inability of the K2Server to create a linked server.

The easy workaround without having to struggle with Kerberos and all the rest is to create your linked server manually.

This can be accomplished by executing the following script against the SQL Server on which your K2 database has been installed. Please note case sensitivity, and that there is no space between the server name and the port. This is important, else K2 will not realise that the linked server already exists.

14 - Linked Server Setup

You will now notice that a Linked Server has been created in your SQL Server instance.

15 - Linked Server Setup

And finally we are in a position to execute a list method on our SmartObject

16 - Test K2 SmartObject - List Method - Success

Great stuff! Our application database are in the clouds, along with our customers – now they also can brag that they have made the move to cloud storage!

Need further technical advice or support?