In recent years MuleSoft has become a proven Data Integration and API management platform. It connects almost any databases including SQL Server. In one my recent data integration projects, I had a development server that has multiple instances with different SQL Server versions. While connecting to SQL Server default instance through MuleSoft is easy – like native ADO or OLEDB, talking with SQL Server Instances through JDBC is however, bit tricky. Let me show you how to overcome this issue.
SQL Server Instances – to connect MuleSoft
As specified earlier, I have two SQL Server Instances, DevServer-01 and DevServer-01\SQL2014. The former one is legacy 2012 and latter has bit newer, SQL 2014 version installed. Using MuleSoft, I need to integrate data from both servers.
So, what is the issue
Connecting to default SQL Server Instance (DevServer-01) in MuleSoft was quiet hassle free,
Figure-02: Successful connection to Default instance
But when I attempted to connect SQL Server 2014 instance in MuleSoft just by changing the Host name like “DevServer-01\SQL2014”, I got the following error:
Cannot get connection for URL jdbc:sqlserver://DevServer-01\SQL2014:1433;databaseName=CallData : Cannot open database "CallData" requested by the login. The login failed.
Wait a minute! what does this error say “Cannot open database “CallData” requested by the login”. It does mean, JDBC tries connecting to my default instance where database CallData does not exists. But in the connection string I have mentioned the instance name like “DevServer-01\SQL2014“! so where is the problem?
The remedy
So, the issue was in the default port, 1433 that I have used in order to connect SQL Server instance. Although SQL Server native driver (ADO, OLEDB) can handle this issue, but JDBC (MuleSoft) needs the TCP Dynamic Ports to be specified in connection string instead of the default one. Now we need this, right? let’s find out the TCP Dynamic Port.
- Login the server and open SQL Server Configuration Manger
- Expand SQL Server Network Configuration and select “Protocol for {Your Instance Name}. In my case it is “Protocol for SQL2014”
- Select TCP/IP from right panel
- Pick up port number from “TCP Dynamic Ports”. For me it shows 64203. For you, it could be a different one.
Now you have what you need, just use this port number instead of the default (1433) one in mule connection string.
You are done!
Finally, one thing to remember, as “TCP Dynamic Ports” might be changed in server restarts, do not forget to get and update your connection string with newly generated port in MuleSoft. Hope this helps!