web stats
Has anyone had any luck connecting to SQLServer in Windows Azure? - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 03-20-2015, 11:52 AM
EricD EricD is offline
Mirth Newb
 
Join Date: Oct 2009
Posts: 14
EricD is on a distinguished road
Default Has anyone had any luck connecting to SQLServer in Windows Azure?

Hello all, we're trying to move HL7 messages to a SQLServer instance running in Windows Azure, but have had zero luck getting it to go through successfully.

We're using the DB Writer destination type, and have tried both Javascript and not.

We've tried the following connection string, with variations:

Code:
jdbc:jtds:sqlserver://XXXXXXXXXX.database.windows.net:1433;database=phi;user=XXXXXXX;password=XXXXX;encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;
Of course, we're attempting to connect with encryption and SSL.

We've even tried replacing the open source SQLServer driver with the Microsoft one (com.microsoft.sqlserver.jdbc.SQLServerDriver).

So, have any of you had any success, and could point us in the right direction?

Thanks,
Eric
Reply With Quote
  #2  
Old 04-16-2015, 01:09 PM
EricD EricD is offline
Mirth Newb
 
Join Date: Oct 2009
Posts: 14
EricD is on a distinguished road
Default

So I've figured this out, and am posting here for everyone's benefit.

First, download Microsoft's SQLServer JDBC driver. I downloaded the tar.gz archive, since I'm running Mirth in linux. I don't know where the exe installer places the jar file.

Second, copy the jar file to the custom-lib folder in your mirth install directory. If you're on *nix, make sure you set the owner and permissions properly - including the execute permission.

Third, navigate to the conf folder in your mirth install directory, and edit dbdrivers.xml to add the new driver. It'll look something like this:
Code:
<drivers>
        <driver class="sun.jdbc.odbc.JdbcOdbcDriver" name="Sun JDBC-ODBC Bridge" template="jdbc:odbc:DSN" selectLimit="" />
        <driver class="com.mysql.jdbc.Driver" name="MySQL" template="jdbc:mysql://host:port/dbname" selectLimit="SELECT * FROM ? LIMIT 1" />
        <driver class="oracle.jdbc.driver.OracleDriver" name="Oracle" template="jdbc:oracle:thin:@host:port:dbname" selectLimit="SELECT * FROM ? WHERE ROWNUM &lt; 2" />
        <driver class="org.postgresql.Driver" name="PostgreSQL" template="jdbc:postgresql://host:port/dbname" selectLimit="SELECT * FROM ? LIMIT 1" />
        <driver class="net.sourceforge.jtds.jdbc.Driver" name="SQL Server/Sybase" template="jdbc:jtds:sqlserver://host:port/dbname" selectLimit="SELECT TOP 1 * FROM ?" />
        <driver class="org.sqlite.JDBC" name="SQLite" template="jdbc:sqlite:dbfile.db" selectLimit="SELECT * FROM ? LIMIT 1" />
        <driver class="com.microsoft.sqlserver.jdbc.SQLServerDriver" name="SQL Azure" template="jdbc:sqlserver://host:port/dbname" selectLimit="SELECT TOP 1 * FROM ?" />
</drivers>
Next, restart the Mirth Connect service.

Finally, grab the connection string from Windows Azure for the DB you want to connect to, and set up your destination accordingly.
Reply With Quote
  #3  
Old 01-13-2016, 04:33 PM
eszokolay eszokolay is offline
Mirth Newb
 
Join Date: Jan 2016
Posts: 8
eszokolay is on a distinguished road
Default Connect to Encrypted SQL Server Database

Any chance you could share your connection string?
Reply With Quote
  #4  
Old 01-13-2016, 04:39 PM
eszokolay eszokolay is offline
Mirth Newb
 
Join Date: Jan 2016
Posts: 8
eszokolay is on a distinguished road
Default

Any chance you could share your connection string that worked?
Reply With Quote
  #5  
Old 01-17-2016, 01:39 PM
CastroXL CastroXL is offline
What's HL7?
 
Join Date: Jan 2016
Posts: 1
CastroXL is on a distinguished road
Default

Like he said you just need to go into sql azure and copy the JDBC connection string and put in the password.

Worked like a charm for me. Fixed my issues with the default driver.
Reply With Quote
  #6  
Old 01-26-2016, 09:06 AM
eszokolay eszokolay is offline
Mirth Newb
 
Join Date: Jan 2016
Posts: 8
eszokolay is on a distinguished road
Default Still need connection string

I'm actually not connecting to Azure so I don't have a connection string example. I'm just trying to connect to a local SQL Server with encryption enabled.
Reply With Quote
  #7  
Old 03-21-2016, 01:54 PM
thildemar thildemar is offline
Mirth Newb
 
Join Date: Mar 2012
Posts: 7
thildemar is on a distinguished road
Default

Thanks Eric,

This fixed my issue. We needed to enable force encrypted connections for our SQL server and JTDS just would not connect with SSL correctly.

I followed your steps to grab the Microsoft driver and used this connection string for the local server:

Code:
var dbConn = DatabaseConnectionFactory.createDatabaseConnection('com.microsoft.sqlserver.jdbc.SQLServerDriver', 'jdbc:sqlserver://SERVERNAME:PORT;databaseName=DBNAME', 'USERNAME', 'PASSWORD');
Connected up with no encryption errors. Note that you may need to use sqljdbc4.jar if you are using an older JRE (look in your Mirth log if you run into issues).
Reply With Quote
  #8  
Old 04-24-2018, 08:48 AM
borric74 borric74 is offline
OBX.1 Kenobi
 
Join Date: May 2014
Posts: 39
borric74 is on a distinguished road
Default Did anyone get azure databases to work correctly?

If you did?

what was "database" type value (sqlserver,derby,azureetc)
what was database.url format

what ended up in customdrivers.xml
what ended up in custom driver folder
what ended up being called out on custom driver setting

Did you run into any other problems with latency, connection closing, encryption settings?
Reply With Quote
Reply

Tags
cloud, sql server, windows azure

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 11:49 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation