Search code examples
pythonsql-servercronpyodbc

pyodbc on MacOS 10.14 connecting to SQL Server, fails with cron job

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.

Solution

  • 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