Announcement

Collapse
No announcement yet.

does the mirth user need to be the DB owner to connect in MS SQL 2008

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

  • does the mirth user need to be the DB owner to connect in MS SQL 2008

    I am new to mirth and have been working on setting up a production connection to a database on our MS SQL Server 2008 cluster at my job. The problem is when testing I used MS SQL 2008 on my local machine and created the database with a mirth user I created. I followed all of the settings suggested and mirth connected to the database with no problem. For the production database our dba created the database using their sa account and created a mirth user with the role db owner. When I try to connect to this database mirth will not connect. The only difference between my local machine and the production database is on my local machine the database owner is listed as the mirth user I created. On the production system the owner is sa and I am connecting with user mirth which is apart of the db owner role for the database. All permissions are identical to what is on my local machine the database owner is the only difference that I can see.
    So for SQL server does the mirth user have to be the actual owner of the database in order to connect?

  • #2
    Hi Taj;

    I usually have the user I connect to a SQL Server db from (I use 2005) set as the dbo for the database I'm accessing. I can't recall if I started doing that because I had the same problem accessing the db when I started off.

    When I read your post, I'm wondering if, in your sql statement you're referencing elements using the dbo. prefix? I'm not an expert in SQL Server (especially with security settings), but I'm thinking if you have done that and the connected user isn't the owner, then maybe that's the problem?
    I can be reached through gmail and Google Talk using davidrothbauer at gmail dot com
    http://www.linkedin.com/pub/david-rothbauer/5/923/518
    codeismydrug.wordpress.com
    hl7coders.wordpress.com

    Test all my code suggestions prior to implementation

    Comment


    • #3
      Thank your for the reply Bostad

      I did not use the dbo prefix in my SQL statement. My connection URL is
      jdbc:jtds:sqlserver://ServerName:16001/DatabaseName

      From what I read it seems that everyone that writes to SQL Server in mirth is connecting using the dbo user. Unfortuanely in my environement the dba will not allow any logins to be the database owner except for their sa login.

      Comment


      • #4
        what permissions does your user have at the database and server levels?

        Edited to add: What error do you get?
        I can be reached through gmail and Google Talk using davidrothbauer at gmail dot com
        http://www.linkedin.com/pub/david-rothbauer/5/923/518
        codeismydrug.wordpress.com
        hl7coders.wordpress.com

        Test all my code suggestions prior to implementation

        Comment


        • #5
          To connect my to my SQL server, I just created a new HL7 login and made it a member of 'public' then assigned it to the dB's I need to access.

          Are you having trouble actually connecting to the server or executing PRCs/selecting from tables?

          Comment


          • #6
            These are my SQL settings
            General -Database role membership-db_owner
            Securables - nothing
            Extended Properties - nothing

            The error I get is
            [2011-08-05 10:17:57,690] ERROR (org.mule.impl.DefaultComponentExceptionStrategy:9 5): Caught exception in Exception Strategy for: be663f95-35ac-4093-bda9-b3fe74f69ca7: org.mozilla.javascript.WrappedException: Wrapped java.sql.SQLException: Invalid object name 'APM_eWebHealth'. (8bd90614-75d1-42a1-a516-f983f939034d#10)
            org.mozilla.javascript.WrappedException: Wrapped java.sql.SQLException: Invalid object name 'APM_eWebHealth'. (8bd90614-75d1-42a1-a516-f983f939034d#10)
            at org.mozilla.javascript.Context.throwAsScriptRuntim eEx(Context.java:1781)
            at org.mozilla.javascript.MemberBox.invoke(MemberBox. java:183)
            at org.mozilla.javascript.NativeJavaMethod.call(Nativ eJavaMethod.java:247)
            at org.mozilla.javascript.Interpreter.interpretLoop(I nterpreter.java:1702)
            at script.doDatabaseScript(8bd90614-75d1-42a1-a516-f983f939034d:10)
            at script(8bd90614-75d1-42a1-a516-f983f939034d:13)
            at org.mozilla.javascript.Interpreter.interpret(Inter preter.java:845)
            at org.mozilla.javascript.InterpretedFunction.call(In terpretedFunction.java:164)
            at org.mozilla.javascript.ContextFactory.doTopCall(Co ntextFactory.java:426)
            at org.mozilla.javascript.ScriptRuntime.doTopCall(Scr iptRuntime.java:3157)
            at org.mozilla.javascript.InterpretedFunction.exec(In terpretedFunction.java:175)
            at com.mirth.connect.connectors.jdbc.JdbcMessageDispa tcher.doDispatch(JdbcMessageDispatcher.java:96)
            at com.mirth.connect.connectors.jdbc.JdbcMessageDispa tcher.doSend(JdbcMessageDispatcher.java:162)
            at org.mule.providers.AbstractMessageDispatcher.send( AbstractMessageDispatcher.java:164)
            at org.mule.impl.MuleSession.sendEvent(MuleSession.ja va:191)
            at org.mule.impl.MuleSession.sendEvent(MuleSession.ja va:130)
            at org.mule.routing.outbound.AbstractOutboundRouter.s end(AbstractOutboundRouter.java:85)
            at org.mule.routing.outbound.FilteringMulticastingRou ter.route(FilteringMulticastingRouter.java:54)
            at org.mule.routing.outbound.OutboundMessageRouter$1. doInTransaction(OutboundMessageRouter.java:78)
            at org.mule.transaction.TransactionTemplate.execute(T ransactionTemplate.java:48)
            at org.mule.routing.outbound.OutboundMessageRouter.ro ute(OutboundMessageRouter.java:82)
            at org.mule.impl.model.DefaultMuleProxy.onCall(Defaul tMuleProxy.java:247)
            at org.mule.impl.model.seda.SedaComponent.doSend(Seda Component.java:209)
            at org.mule.impl.model.AbstractComponent.sendEvent(Ab stractComponent.java:277)
            at org.mule.impl.MuleSession.sendEvent(MuleSession.ja va:201)
            at org.mule.routing.inbound.InboundMessageRouter.send (InboundMessageRouter.java:176)
            at org.mule.routing.inbound.InboundMessageRouter.rout e(InboundMessageRouter.java:143)
            at org.mule.providers.AbstractMessageReceiver$Default InternalMessageListener.onMessage(AbstractMessageR eceiver.java:487)
            at org.mule.providers.AbstractMessageReceiver.routeMe ssage(AbstractMessageReceiver.java:266)
            at org.mule.providers.AbstractMessageReceiver.routeMe ssage(AbstractMessageReceiver.java:225)
            at com.mirth.connect.connectors.vm.VMMessageReceiver. getMessages(VMMessageReceiver.java:221)
            at org.mule.providers.TransactedPollingMessageReceive r.poll(TransactedPollingMessageReceiver.java:108)
            at org.mule.providers.PollingMessageReceiver.run(Poll ingMessageReceiver.java:97)
            at org.mule.impl.work.WorkerContext.run(WorkerContext .java:290)
            at edu.emory.mathcs.backport.java.util.concurrent.Thr eadPoolExecutor.runWorker(ThreadPoolExecutor.java: 1061)
            at edu.emory.mathcs.backport.java.util.concurrent.Thr eadPoolExecutor$Worker.run(ThreadPoolExecutor.java :575)
            at java.lang.Thread.run(Unknown Source)Caused by: java.sql.SQLException: Invalid object name 'APM_eWebHealth'.
            at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnos tic(SQLDiagnostic.java:365)
            at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(Td sCore.java:2781)
            at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCor e.java:2224)
            at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(T dsCore.java:628)
            at net.sourceforge.jtds.jdbc.JtdsStatement.processRes ults(JtdsStatement.java:525)
            at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL (JtdsStatement.java:487)
            at net.sourceforge.jtds.jdbc.JtdsStatement.executeImp l(JtdsStatement.java:664)
            at net.sourceforge.jtds.jdbc.JtdsStatement.execute(Jt dsStatement.java:1114)
            at com.mirth.connect.server.util.DatabaseConnection.e xecuteUpdate(DatabaseConnection.java:107)
            at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
            at sun.reflect.NativeMethodAccessorImpl.invoke(Unknow n Source)
            at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)
            at java.lang.reflect.Method.invoke(Unknown Source)
            at org.mozilla.javascript.MemberBox.invoke(MemberBox. java:161)

            Comment


            • #7
              From the looks of it, it looks like it is connecting but is having trouble finding the 'APM_eWebHealth' table/object.

              Does APM_eWebHealth exist in the database you are trying to connect to?

              Comment


              • #8
                APM_eWebHealth is the database name and it does exist. Not sure why this message is appearing.
                When in the database writer window if I hit the get tables button nothing happens if I hit the validate connection button I get the error.

                Comment


                • #9
                  upstart33 if you right click on your database and slelect properties is your database owner the new HL7 login you created?

                  Comment


                  • #10
                    I am actually using a Postgres database for Mirth itself, that connects to a SQL database where all of the data I need is.

                    All I did was create a new Server Login for Mirth, assign it to 'public' role, and give it access to the database where the data is. It doesn't have any other properties other then that and isn't the owner of any db or schema.

                    My Source is a Database Reader (SQL Server/Sybase)
                    I used this for the URL: jdbc:jtds:sqlserver://servernameort/database

                    Then I clicked 'Use Javascript' and put in the following to call a PRC from the database:

                    var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://servernameort/database','username','password');

                    var result = dbConn.executeCachedQuery("exec [mis_db].[dbo].[prc_lab_results_outbound2]");
                    dbConn.close();

                    return result;

                    Comment


                    • #11
                      Thank you guys for all of your help. The name of the table I was trying to access was not correct, upstart33 was correct. So once I corrected the table name I was able to connect to the database. Please note I now know if your user is not the owner you can not use the insert command to view tables. Again I really appreciate all the help.

                      Comment

                      Working...
                      X