Does anyone successfully connect to a SQL Server via LS:DO and ODBC in a scheduled agent?
The only way we can get it to connect is if we are running the Lotus Domino Server service under Administrator credentials. If it is running under the default LocalSystem account then it does not appear to have the credentials to connect. Any ideas how to solve this?
Currently we are getting around it by running the service under Administrator credentials however when doing this you don’t see the console on the desktop and this is not acceptable in our environment.
Any ideas are appreciated!
Thanks,
Dustin
Subject: Connecting to SQL Server with scheduled agent
I’m thinking (which may not be a good thing mind you) that you just need to pass username/password though the ODBC connection object when you connect to the datasource.
This assumes you created an ODBC System DSN datasource on the Domino server and that SQL is setup to manage it’s own users/passwords.
Sounds like you’re relying on the NT service permissions rather than a user/password combo that SQL would give access to.
Explain how SQL is setup for authentication and describe how the ODBC System DSN is configured. That’ll help someone to narrow down the problem for you.
Subject: RE: Connecting to SQL Server with scheduled agent
Thanks for responding Matt.
I’ve got the ODBC DSN setup using SQL username and password authentication, which is why it baffles me that the credentials running the service are what is affecting it. My code to test this is simple:
Dim session As New NotesSession
Dim db As NotesDatabase
Set db = session.CurrentDatabase
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim res As New ODBCResultSet
con.SilentMode = True
If con.ConnectTo("vmfgcor", "agents", "agents") Then
Print "CONNECTED!"
con.Disconnect
Else
Print "NOT CONNECTED!"
End If
Thanks for your time.
Subject: RE: Connecting to SQL Server with scheduled agent
I have two different agents that run scheduled everyday night that goes to SQL, one imports, one exports. However, I did not create the agent and cannot help you much with the inner workings of the ODBC connection, but if you want me to, I can paste the code here so you can see how we are connecting to SQL. You might be able to figure it out by seeing how we have ours setup.
Let me know.
Subject: RE: Connecting to SQL Server with scheduled agent
I always like to see others code so if you could post it that would be great!
Thanks,
Dustin
Subject: RE: Connecting to SQL Server with scheduled agent
Here is the piece that I might be able to help you, I hope it does…
OPTIONS START
Option Public
Option Declare
Use "WebUtils"
Uselsx "*LSXODBC"
OPTIONS END
DECLARATION START
Dim tn As String, tm As String
Dim ses As NotesSession
Dim db As NotesDatabase
Dim dbCtl As NotesDatabase
Dim viewCtl As notesview
Dim docCtl As notesdocument
Dim ACview As NotesView 'Agent Control view
Dim ACdoc As notesdocument 'Agent Control document
Dim MBdb As notesdatabase 'Mail.box
Dim maildoc As notesdocument 'Mail memo
Dim Logdb As noteslog 'Agent Log
Dim AgentNew As Notesagent
Dim Agnt As NotesAgent
Dim LastRun, ThisRun
Dim TextDate As String, strDateTime As String
Dim view As notesview
Dim doc As notesdocument
Dim item As notesitem
Dim rtitem As NotesRichTextItem
Dim dc As notesdocumentcollection
Dim server As notesname
Dim SQLCon As ODBCConnection
Dim SQLCon2 As ODBCConnection
Dim SQLErr As Integer
Dim QryContactMaster As ODBCQuery
Dim ResultContactMaster As ODBCResultSet
Dim QryContactMasterBill As ODBCQuery
Dim ResultContactMasterBill As ODBCResultSet
Dim CCN As String, billCCN As String, key As String
Dim logagent As Variant
Dim agent As String
Dim i&, cntAdds&, cntMods&, cntDeletes&, pos&
Dim readersname$ (5)
Dim namesList
'Global Constants
Const principal$ = "Contact Management Dabase"
Const sendto$ = "name@mailaddy.com"
Const logdbtitle$ = "Agent Central Log"
'SQL tables and views
Const ContactMaster = "ContactMaster"
DECLARATIONS END
INITALIZE START
Sub Initialize
Set ses = New NotesSession
Set db = ses.CurrentDatabase
Set view = db.getview ("NotesView")
Set server = New notesname (db.server)
agent = ses.currentagent.name
'Controls database
Set dbCtl = ses.GetDatabase(db.server, "apps\Controls.nsf")
'Agent Log db
Set logagent = ses.CurrentAgent
Set logDb = New noteslog (db.title & " Db: " & logagent.name & " on " & server.abbreviated)
Call LogDB.OpenNotesLog(db.server, "apps\ALog.nsf")
Call LogDB.LogAction (" ")
Call LogDB.LogAction (">===== Start Import Entity Docs =====<")
'Get last run date.
agent$ = ses.currentagent.name
Set ACview =db.getview("AgentControls")
Set ACdoc = ACview.getdocumentbykey (agent$, True)
If ACdoc Is Nothing Then
SendMemo "SUB Initialize: Get last run date", "AgentControls document not found."
Exit Sub
End If
LastRun = ACdoc.lastrun(0)
ThisRun = Now()
textDate = Format (LastRun, "mm/dd/yyyy")
readersname(0) = "LocalDomainServers"
readersname(1) = "Admins"
readersname(2) = "[NotesAdmin]"
readersname(3) = "[NationalMgr]"
readersname(4) = "[Reviewer]"
readersname(5) = "Name/Cert/Company"
Set SQLCon = New ODBCConnection
If SQLCon.ConnectTo ("ODBC Connection Name","Username","Password") Then
Set SQLCon2 = New ODBCConnection
If SQLCon2.ConnectTo ("ODBC Connection Name","Username","Password") Then
Call DefineQueries
If Not SQLErr Then Call ProcessData 'Read through the SQL tables and update this Db
If Not SQLErr Then 'Update Agent Control document
ACdoc.ID_NO = ""
ACdoc.LastRun = ThisRun
Call LogDB.LogAction (">===== End Import Entity Docs =====<")
Else
ACdoc.ID_NO = CCN 'Save last CCN so job can restart where it left off
Call LogDB.LogAction ("Job failed while processing Entity " & CCN)
End If
Call ACdoc.save (True, False, False)
SQLCon.DisConnect
Else
Call SendMemo ("SUB Initialize: SQL2 connect error",_
"SQL2 Connect to ""SQL Table"" could not be established")
End If
Else
Call SendMemo ("SUB Initialize: SQL connect error",_
"SQL Connect to ""SQL"" could not be established")
End If
End Sub
INITALIZE END
Subject: Is it a System DSN?
Can’t get any simpler than that…
Can you confirm that your ODBC DSN is set up as a “System” DSN and NOT a “User” DSN?
Subject: System DSN?
Can you confirm that you are using a System DSN and not a User DSN?
Subject: RE: System DSN?
It setup as a system DSN, and we’ve been doing this same thing for years with another db engine. Just seems that the SQL ODBC driver isn’t working the same.
Also tested as both domain admin and local admin interactively and the agent could connect both times. So it is confined to being a problem running as a scheduled agent.
Thanks for you time and help!
Subject: MDAC Version?
What version of MS SQL drivers do you have installed?
We use MDAC 2.8 here, connecting to SQL 2000 servers.
MDAC 2.8 download page: http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c&DisplayLang=en
I honestly don’t think you are causing any problems with your code. It has to be either user permissions, driver version, or networking issue of some sort.