python

OperationalError: (‘08001’, ‘[08001] [Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection (0) (SQLDriverConnect)’) with pyodbc on Mac OS x

Joseph Jenkins1 Comment

I was getting the following weird error when running pyodbc on my Mac and trying to connect to my Windows Database:

  • OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection (0) (SQLDriverConnect)')

Using the wonderful powers of Google I came across this article:

https://github.com/microsoft/homebrew-mssql-release/issues/59#issuecomment-928957200

Following the information from JH88:

brew install openssl@1.1

# you might need to delete the old symlink first
# rm /usr/local/opt/openssl

ln -s /usr/local/Cellar/openssl@1.1/1.1.1l /usr/local/opt/openssl

I still wasn’t getting it to work, turns out Brew had installed version 1.1.1l_1 on my machine so I needed to do the following:

rm /usr/local/opt/openssl

ln -s /usr/local/Cellar/openssl@1.1/1.1.1l_1 /usr/local/opt/openssl

After that quick fix everything started working correctly again.

Installing pyodbc on a mac

Joseph Jenkins1 Comment

After installing pyodbc onto my linux server that runs my scripts I needed to create my development environment on my mac so I could develop the scripts and then transfer them over. I went through a couple of websites to put together my steps:

http://www.christophers.tips/pages/pyodbc_mac.html
https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Mac-OSX

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

brew update

brew install unixodbc freetds

Pip install pyodbc

Giving up my 100 days of code

Joseph JenkinsLeave a comment

So I am 20 days into my 100 days of code and I am going to stop going through the class I was taking on Udemy. I have finally gotten to a point where a lot of the modules they are trying to have me use aren’t working and I am spending more time troubleshooting their stuff than I am actually learning. So now I am going to start my own exercises and continue my learning on my own. I am going to try and cherry pick some of the courses in the class that still work, but will end up refining my code with what I have already learned and trying to put some the practices into place to improve my code. I will also work on automating more of the functions that need to be automated in my environment. In all I am frustrated by the fact that I could complete the Udemy course, but thankful for the few things that I have learned.

Starting my 100 days of coding

Joseph JenkinsLeave a comment

I took up the 100 days of coding challenge to work on my coding skills in Python. I am pretty good with the requests module, but that is about it. I figured this was a way to work on my coding skills and help learn some additional skills that I didn’t have before. Already 7 days in I’ve managed to learn a few things I didn’t know before and also managed to build out some programs so I can update my existing stuff and add in some password randomization. I built a git repository for all of the coding projects under one single heading. Figured this way I could reference it later if needed:

 

https://github.com/undrwatr/100daysofcode

Python script to pull ip addresses from Meraki and then update Microsoft trusted locations in Azure AD

Joseph JenkinsLeave a comment

I needed a script that would automatically get all of the ip addresses for my stores and then upload them to Microsoft on a weekly basis and create a trusted location. I did this so that we wouldn’t have to use MFA for all of the stores and they would be able to log in with just their username and password. Since all of the data was sitting in Meraki I decided to pull it from there and then upload it to Microsoft. Here is the script that goes through all of the wireless devices in my Meraki org and then updates Microsoft with that data and sends an email when it’s been done successfully. I’ve posted the script for reference and anonymized the portions of the script that I didn’t put into the cred.py file.

I used the wireless devices from the Meraki cloud because they were easier to pull out and integrate into the script.

I posted the code to my GitHub repository so that others can see what I did and make recommendations for changes or other things that should be added.

 

https://github.com/undrwatr/Update_Location

 

Future changes for the script:

Keep just one database of the ip addresses and update it as needed instead of creating a new site

Additional error checking in case the script doesn’t run

Scripts to migrate hosts from ASA to Fortigate

Joseph JenkinsLeave a comment

For my job I am in the process of migrating from an ASA to a FortiGate firewalls. Part of this has been moving the configuration that we already have in place on the ASA and translating it too FortiGate. I needed to convert several address lists. Some of those address lists where hundreds of addresses long and I didn’t want to type those in. So I started using my python skills to build out the configuration by taking in a list of the ip addresses and then outputting the configuration needed for the FortiGate firewall.

Here is the current version of that script:

# variables needed througout:
file = input(“Name of file? “)
title = input(“Name of hosts and group? “)

addresses = open(file, “r”)

print(“config firewall address”)

incr = 1
host_entries = ‘set member’
for address in addresses:
    address = address.rstrip(‘\n’)
    print(‘edit “H_’ + title + str(incr) + ‘”’)
    host_entries = (host_entries + (‘ “H_’ + title + str(incr) + ‘”’))
    print(“set subnet ” + address + ” 255.255.255.255”)
    print(“next”)
    incr = (incr + 1)

print(‘end’)
print(“config firewall addrgrp”)
print(‘edit “G_’+ title + ‘”‘)
print(host_entries)
print(‘next’)
print(‘end’)

Firesight API Explorer

Joseph JenkinsLeave a comment

I am having to make some changes to an object group in the Firesight management platform for my firewalls. Some of these changes required me to add and remove a 50-100 network entries at one time and doing it by hand was going to take a while and could also be prone to errors. I started to research the options for using the api interface for Firesight and came across this YouTube video from Cisco Devnet showing how to use the API Explorer built into firesight and showing how I could easily take and copy out a program so that I could build the object group very easily with a python script. So now I have taken what was started in the API Explorer and modifying it to pull the data out of my central address repository, format the data, and then build the script to run. You can see the in process script here.

Python Program to sort RAW vs JPG files from a photo shoot

Joseph JenkinsLeave a comment

I shoot a lot of photos at airshows and needed a quick to sort the RAW photos from the JPGs and to put them into different directories. I may not always use the RAW photos, but I don’t want to discard, I also may not want them in my Lightroom library. This program will quickly sort them, name them, and put them into separate directories for import into Lightroom.

The programs repository is here: https://github.com/undrwatr/SONY_FILE_SORT

This is of course version 1:

#!/usr/bin/env python3

#Program to sort my photo files between Sony RAW and JPG files. Sorts into two separete directores so I can chooose what to import into Lightroom.

import os
import shutil

# ask for the name of the directory
SORT_DIR = os.getcwd()

DIRECTORY = input("What is the name of the Directory to be created? ")


DIRECTORY_RAW = (DIRECTORY + '_RAW')
DIRECTORY_JPG = (DIRECTORY + '_JPG')



# List the files in the source directory
list_ = os.listdir(SORT_DIR)

# rename the files based on the requested directory name
INCR = 0

for file_ in list_:
    name, ext = os.path.splitext(file_)
    INCR = INCR +1
    os.rename(file_, ((DIRECTORY) + "-" + str(INCR) + ext))


# create a directory for jpg
# create a directory for raw files

os.mkdir(DIRECTORY_RAW)
os.mkdir(DIRECTORY_JPG)

CURRENT_DIR = os.getcwd()

list1_ = os.listdir(SORT_DIR)

# Sort the photos into the directories, but do not sort the directory

for file1_ in list1_:
    if file1_.endswith('.ARW'):
        shutil.move(((CURRENT_DIR) + "/" + (file1_)), ((CURRENT_DIR) + "/" + (DIRECTORY_RAW)))
    if file1_.endswith('.JPG'):
        shutil.move(((CURRENT_DIR) + "/" + (file1_)), ((CURRENT_DIR) + "/" + (DIRECTORY_JPG)))
    else:
        continue

Installing PYODBC onto Ubuntu

Joseph JenkinsLeave a comment

Steps to install PYODBC on Ubuntu and the requirements that needed to be done to convert from PYMSSQL to PYODBC for connecting to an MS SQL database to pull information. Just wanted to get all of these steps in one place and everything that I went through to get it working in my Linux environment.

sudo curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add –

#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version

#Ubuntu 16.04
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

#Ubuntu 18.04
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

#Ubuntu 19.10
curl https://packages.microsoft.com/config/ubuntu/19.10/prod.list > /etc/apt/sources.list.d/mssql-release.list

sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql17
sudo apt-get install unixodbc-dev

Once the driver is installed you then need to install pyodbc with pip
pip3 install pyodbc

After I did these steps it then did require me to reinstall the requests module for some reason to resolve some HTTPS issues. Not really sure what that issue was or the reason for it.

Reference links for the installs from Microsoft:

https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15

Reboot Meraki APs

Joseph JenkinsLeave a comment

I have found myself several times over the last couple of months needing to reboot all of the APs within a Meraki network. Sometimes due to changes or sometimes due to them not responding for some reason. There really isn’t a clean way of going through and rebooting them aside from one a time within the console. I thought hey I can make this one better and do it via the API. So I went through and built this script to allow someone to put in the Org id and then it will pull back all of the networks that are in that Org and allow you to choose one to reboot all of the APs. It will ask should it go as fast as possible or would you like to put in a delay so that they all don’t go down at the same time. I’ve tested it a couple of times and everything works as its supposed to. As always I look forward to any comments or updates that I can put into the code to make it better.

https://github.com/undrwatr/MERAKI_AP_REBOOT

As usually my code isn’t fancy or special, just serviceable and able to get done what I need and save me some time and headaches.