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)

 

MicroSoft SQL Server Integration with Mulesoft

 

Once added it will be show like

MicroSoft SQL Server Integration with Mulesoft

 

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=[email protected];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.”

MicroSoft SQL Server Integration with Mulesoft

 

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/

  


Thank you for taking out time to read the above post. Hope you found it useful. In case of any questions, feel free to comment below. Also, if you are keen on knowing about a specific topic, happy to explore your recommendations as well.
 
For any latest updates or posts on our website, you can follow us on LinkedIn. Look forward to connecting with you there.


Share this:
Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Mark Casagrande
Mark Casagrande
8 months ago

We were using the integratedSecurity=true option in Mule 3 for our MS/SQL to MS Active Directory authentication (instead of SQL Authentication). To do this we used the integratedSecurity=true key-value pair in the connection string for 6 years.. This integratedSecurity option allows the MS AD userid of the user process running Studio or the process owner of Mule Server to be verified against MS AD. So no user= or password= key/value pairs are placed in our Mule 3 Generic DB config option) connection string. But now we are moving to Mule 4, and we were referred to an article regarding how… Read more »