Announcement

Collapse
No announcement yet.

Has anyone had any luck connecting to SQLServer in Windows Azure?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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.

    Comment


    • #3
      Connect to Encrypted SQL Server Database

      Any chance you could share your connection string?

      Comment


      • #4
        Any chance you could share your connection string that worked?

        Comment


        • #5
          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.

          Comment


          • #6
            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.

            Comment


            • #7
              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).

              Comment


              • #8
                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?

                Comment

                Working...
                X