MicroSoft SQL Server Integration With Mule

In this tutorial we have demonstrate MicroSoft SQL Server Integration With Mule and configure the Microsoft Sql Server Connection

To start with we need to  download sqljdbc4-2.0.jar and add it as a reference library in the project – sqljdbc4-2.0.jar (please change the jar name – remove _.txt from file name)

Once added it will be show like

Drag and drop database connector in mule flow and click on add connector configuration

Select Generic Database Configuration

• Driver Class Name put – com.microsoft.sqlserver.jdbc.SQLServerDriver
• Connection string for MS SQL Server –jdbc:sqlserver://${mssql.server}:${mssql.port};databaseName=${mssql.database};user=${mssql.user};password=${mssql.password}
• Test to check if connection is established or not

Different connection string can be created as described –

MS blog – https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-2017

If all goes well then, we can see the connection successful message.

While connecting with SQL server using SQL Server Authentication mode then enable this option in SQL server

Things to be taken care when connecting with Azure MS SQL server –

When we connect with Azure MS SQL server using SQL username/password then we have to updated the connection to include server name in username

jdbc:sqlserver://myserver.database.windows.net:1433;databaseName=mydatabase;user=myuser@myserver;password=mypassword

otherwise we will get error like –

Cannot open server “machine name” requested by the login. The login failed.

Also if we try to connect with Azure MS SQL where our IP is not configured on Azure side then we will get similar error like –

“Cannot open server ‘XXXXXXXXX’ requested by the login. Client with IP address ‘XXXXXXXXX’ is not allowed to access the server. To enable access, use the SQL Azure Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.”

While connecting with Azure we must add the client IP in allowed IP in Azure portal, please go through this like to add the required client IP

Ref: https://blogs.msdn.microsoft.com/azuresqldbsupport/2015/04/29/configuring-the-firewall-for-client-access/