3

I have a python3 script I am running on MacOS 10.14 that connects to a a SQL Server database using pyodbc. When I run it manually, it works fine, but when I schedule it with crontab, I get this error:

'DRIVER=/usr/local/lib/libmsodbcsql.17.dylib;' pyodbc.Error: ('HY000','[HY000] [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider:  No credentials were supplied, or the credentials were unavailable or inaccessible. (458752) (SQLDriverConnect)')

I am running as a Managed user, connected through AD, and my account has permission to access the database using those credentials.

This is the python:

#!/usr/bin/env python

import pyodbc

conn = pyodbc.connect(
  'DRIVER=/usr/local/lib/libmsodbcsql.17.dylib;'
  'SERVER=[SERVER NAME];'
  'DATABASE=[DATABASE];'
  'Trusted_Connection=yes;'
)

cursor = conn.cursor()
cursor.execute("SELECT * FROM [TABLE]")

With the appropriate server, database, and table, of course.

I assume this has something to do with a subtle difference between the managed user and the cron user, but I don't know how to get around that problem. For security reasons, it would be a non-starter to create a SQL-Managed account for this server.

Jason Francis
  • 1,104
  • 2
  • 14
  • 25
  • Why are you using `/usr/local/lib/libmsodbcsql.17.dylib;` as the driver as aposed to `{ODBC Driver 14 for SQL Server}` just curious? – Tom Oct 03 '19 at 14:48

5 Answers5

3

cron jobs run as system and don't access user credentials on mac anymore (well, it's depricated).

You'll need to utilise launchd to run a user agent instead.

The basic steps are

create a job definition .plist for it

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
    <dict>
        <key>Label</key>
        <string>local.myJob</string>
        <key>Program</key>
        <string>/Users/user/Scripts/myScript.py</string>
        <key>RunAtLoad</key>
        <true/>
    </dict>
</plist>

Save the .plist to ~/Library/LaunchAgents/local.myJob.plist which makes it a user agent rather than a system daemon.

Put the script in the location specified in the .plist Program Key

Add the job to launchd with

launchctl load ~/Library/LaunchAgents/local.myJob.plist

Start with

launchctl start local.myJob
Najinsky
  • 628
  • 4
  • 12
2

The error message is telling you that you need credentials. Add UID=username;PWD=password to the pyodbc.connect string argument, e.g.,

pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
Neeraj Agarwal
  • 1,059
  • 6
  • 5
  • 1
    The Trusted_Connection=yes replaces the need for UID and PWD. UID and PWD are only for SQL-Managed credentials, but I need to rely on the active directory authentication. Sorry if that wasn't clear enough in my question. The above code works fine when I run it from the terminal; it is only a problem when I add it to crontab. – Jason Francis Sep 25 '19 at 11:04
0

You may be setting up your crontab with the default root user, which wouldn't presumably have the active directory credentials associated with it. Try setting up your crontab with crontab -u <username>, using the same username that you were able to manually run the script successfully with. Take a look at this answer for more details: https://stackoverflow.com/a/8476992/1506086

Rob Streeting
  • 1,675
  • 3
  • 16
  • 27
0

Run a os ping command before and after in your code with the target of the ping being the database and server name. Do a ping command with same database and server name in a cmd box shell script and compare to in SQL connect script. Use the results to modify your code.

#Look at the subprocess module in the standard library:
import subprocess
subprocess.run(["ping <server Name>", "-l"])
Joe McKenna
  • 135
  • 5
0

Try creating a cron job under your account. I tested on centos its working for me.

ashwin
  • 332
  • 2
  • 16