web stats
does the mirth user need to be the DB owner to connect in MS SQL 2008 - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 08-04-2011, 08:26 PM
taj127 taj127 is offline
Mirth Newb
 
Join Date: Aug 2011
Posts: 6
taj127 is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 08-05-2011, 05:13 AM
Bostad Bostad is offline
Level 85 Channel Wizard
 
Join Date: Mar 2009
Location: Stratford Ontario
Posts: 712
Bostad is an unknown quantity at this point
Default

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
Reply With Quote
  #3  
Old 08-05-2011, 07:53 AM
taj127 taj127 is offline
Mirth Newb
 
Join Date: Aug 2011
Posts: 6
taj127 is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 08-05-2011, 08:52 AM
Bostad Bostad is offline
Level 85 Channel Wizard
 
Join Date: Mar 2009
Location: Stratford Ontario
Posts: 712
Bostad is an unknown quantity at this point
Default

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
Reply With Quote
  #5  
Old 08-05-2011, 11:32 AM
upstart33 upstart33 is offline
Mirth Guru
 
Join Date: Dec 2010
Location: Chicago, IL.
Posts: 460
upstart33 is on a distinguished road
Default

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?
Reply With Quote
  #6  
Old 08-05-2011, 11:39 AM
taj127 taj127 is offline
Mirth Newb
 
Join Date: Aug 2011
Posts: 6
taj127 is on a distinguished road
Default

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)
Reply With Quote
  #7  
Old 08-05-2011, 11:51 AM
upstart33 upstart33 is offline
Mirth Guru
 
Join Date: Dec 2010
Location: Chicago, IL.
Posts: 460
upstart33 is on a distinguished road
Default

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?
Reply With Quote
  #8  
Old 08-05-2011, 12:42 PM
taj127 taj127 is offline
Mirth Newb
 
Join Date: Aug 2011
Posts: 6
taj127 is on a distinguished road
Default

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.
Reply With Quote
  #9  
Old 08-05-2011, 12:45 PM
taj127 taj127 is offline
Mirth Newb
 
Join Date: Aug 2011
Posts: 6
taj127 is on a distinguished road
Default

upstart33 if you right click on your database and slelect properties is your database owner the new HL7 login you created?
Reply With Quote
  #10  
Old 08-05-2011, 01:09 PM
upstart33 upstart33 is offline
Mirth Guru
 
Join Date: Dec 2010
Location: Chicago, IL.
Posts: 460
upstart33 is on a distinguished road
Default

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;
Reply With Quote
Reply

Tags
database owner, database writer, sql 2008

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 04:55 PM.


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