In this blog post I would like to record below topics regarding integrating SQL Server in a Mule flow:
- Connecting to SQL Server
- Different authentication mechanisms
- Run scripts/stored procedures
- Limitation
1. Connecting to SQL Server
Mule provides DataBase connector which can be configured to connect different databases like Oracle, MySQL, Derby and in our case SQL Server as well.
Before we start using the Database connector, you will need to download Microsoft JDBC Driver for SQL Server. Once downloaded and unzipped, copy sqljdbc42.jar file to your Java build path (right click on Mule project –> Build Path –> Configure Build Path –> Libraries tab –> Add External JARs and select the sqljdbc jar file)
Once the driver is specified in build path, drag and drop Database connector and add connector configuration by selecting “Generic Database Configuration” option as shown below:
Generic Database connector will need to know the driver class which needs to be used for connecting to the DB.
Specify the driver class name by either clicking on the search glass and typing “com.microsoft.sqlserver.jdbc.SQLServerDriver” or directly specifying “com.microsoft.sqlserver.jdbc.SQLServerDriver” for Driver Class Name. SQLServerDriver is available as you have included the sqljdbc jar in the build path in first step.
Url for database can differ based on the type of authentication needed to connect to SQL Server.
2. Different Authentications:
SQL server can be authenticated in two different ways:
- Username and password or SQL Server Authentication
- Windows Authentication
Using a username and password: You may want to choose username/password to connect SQL db, if the service account under which Mule runs, is not configured in the windows domain (assuming this would be common if you are using CloudHub)
If you choose to use username/password for authentication, specify url for Database Configuration in below format:
jdbc:sqlserver://${sql.servername}:${sql.port};instanceName=${sql.instancename}; databaseName=${sql.dbname};user=${sql.username};password=${sql.password};
where, sql.servername = servername of the sql server you are trying to connect
sql.port = port number being used by sql server, this can be viewed from Network configuration manager
sql.instancename = this is the instance name of SQL Server you are connecting to. This can be verified by either visiting the SQL Server Network Configuration manager or from SQL Service as well
sql.dbname = name of the database you want to connect to
sql.username = username which should be used for connecting to DB
sql.password = password for the username used for connecting to DB
Ex: jdbc:sqlserver://localhost:51282;instanceName=SQLEXPRESS;databaseName=muletest;username=mule;password=test;
Hit “Test Connection” to make sure you are able to authenticate
Note: Inorder to use SQL Server Authentication, SQL server should be configured to accept Mixed mode authentication and not just Windows Authentication(by default). You can modify this by, right click on SQL Server, Properties –> Security Tab –> Server Authentication.
Using windows authentication: You may want to prefer windows authentication instead of username/password if you have a domain service account under which Mule is running or if SQL server is configured to accept only Windows Authentication. This provides better security as DBA no longer has to share or maintain username/password with development teams.
If you choose to use windows authentication, specify url for Database Configuration in below format:
jdbc:sqlserver://${sql.servername}:${sql.port};instanceName=${sql.instancename}; databaseName=${sql.dbname};integratedSecurity=true;
Ex:jdbc:sqlserver://localhost:51282;instanceName=SQLEXPRESS;databaseName=muletest;integratedSecurity=true;
If you hit “Test Connection”, at this point you will get below error indicating “This driver is not configured for integrated authentication”
To resolve this, you will need to add sqljdbc_auth.dll in the java.library.path. This can be done by adding the folder where sqljdbc_auth.dll resides as Native Library location for sqljdbc jar file under the build path(as included in the first step of post).
You can also specify this as runtime argument by specifying -Djava.build.path=<<folder location of sqljdbc_auth.dll>>
3. Run scripts/stored procedures
Executing script is straight forward as you just have to specify the operation and the script as shown below:
If you need to call Stored Procedure, syntax will be as below:
4. Limitation
One of the limitations of SQL JDBC driver is that you cannot use OUTPUT clause in your INSERT/UPDATE/DELETE statements. If you are executing Update statement with OUPUT clause as shown in the below screen shot, you will get an error indicating – “com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.“. The reason being, OUTPUT will return a resultset and you need a object of ResultSet to hold the data.
This error has nothing to do with the Database connector but the way Microsoft SQL JDBC driver executes these statements. Workaround for this, would be to use the statement in a Stored Procedure and call the Stored Procedure instead of executing the Update script directly from the flow.
