Skip to content

Lottabytes

Adventures in Enterprise Systems Management and Automation

  • My Gitlab
Lottabytes

Category: tsql

FreeTDS and Microsoft SQL Server Windows Authentication – Part 1

0
September 16, 2016

I’ve been trying to get the Zenoss SQL Transaction Zenpack working so that we can use Zenoss to run SQL queries for specific monitoring purposes and ran into a few things that might be worth sharing.

Using tsql for troubleshooting

Zenoss, among many other tools uses pymssql to connect to your SQL Servers; and pymssql uses FreeTDS behind the scenes. If you can’t get pymssql to work them you can go a layer deeper to see if you can find the issues. In my case I have the following configuration:

Fedora Server 23
freetds-0.95.81-1
pymssql-2.1.3

First off, FreeTDS uses a config file at /etc/freetds.conf that has a [Global] section and examples for configuring individual server types. This is important because you need to use TDS version 7.0+ for Windows Authentication to work.

If we try to connect using the diagnostic tool tsql (not to be confused with the language T-SQL) without changing the default TDS version or adding a server record in the config file our attempts will fail

To fix this you can either:
Change the Global value for “tds version” to be 7+ (sounds like a good idea to me if you only have MSSQL):

or you can add a server record for each Microsoft SQL Server and leave the global version less than 7.

The catch to second method is that when you do your queries you will have to call the name as shown in the config file (in this case us01-0-srs1) and you cannot use the FQDN or it will fail because it defaults back to the Global setting. This method also creates overhead in managing the list of MSSQL Servers in the freetds.conf file.
Either way, at this point you should have tsql being able to query your MSSQL Servers using Windows Authentication
Getting started with pymssql
To make sure that pymssql is working I threw together a quick bit of python that allows you to connect using Windows Authentication

It’s basically a simplified version of the example on the pymssql web page, but will prove if pymssql and MSSQL Windows Authentication is working or not.

————-BEGIN Code
import pymssql

print(‘Connecting to SQL’)
conn = pymssql.connect(server=’server.domain.com’, user=’DOMAIN\username’, password=’Super Secret P@ssW0rds’, database=’master’)

print(‘Creating cursor’)
cursor = conn.cursor()

print(‘Executing query’)
cursor.execute(“””
SELECT MAX(req.total_elapsed_time) AS [total_time_ms]
FROM sys.dm_exec_requests AS req
WHERE req.sql_handle IS NOT NULL
“””)

print(‘Fetching results’)
row = cursor.fetchone()
while row:
    print(row[0])
    row = cursor.fetchone()

print(‘Closing connection’)
conn.close

————-END Code 
After filling in the details on your MSSQL Server you can simply run it and get the results
Part 2 will cover the Zenoss specific aspects of this…
FreeTDS, linux, Microsoft, MSSQL, pymssql, Server, SQL, tsql, Windows Authentication, zenoss

About Me

My name is Caleb Stephenson, and I am a Sr. Cloud Reliability Engineer working on the Private Cloud Team at VMware and specializing in Systems Management and Automation. We make cloud computing happen at scale. If you have attended a VMworld since 2013 and taken a Hands-On-Labs or used HOL for another event, it is just one of the many things that we provide as a cloud provider.

I am a jack-of-all-trades and unfortunately, master at none. My various IT certifications over the years have included AWS Certified Solutions Architect – Associate,  VMware Certified Professional (VCP), Certified Scrum Product Owner (CSPO), ITIL-F, RHCSA (RHEL 7), MCSA 2003 and MCSE 2003. While I am an engineer, I am also the Product Owner for my team which means my career is a delicate balance of technical and managerial skills.

Everything on this blog should be considered mine personally and not representative of my employer. There may be stuff published here that is pertaining to VMware products or product issues/fixes or bits of code I wrote. My blog should be read just like any other blog and not as a representative of VMware in any way, shape or form.

Recent Posts

  • NFSv3 Usage and Audit Logging
  • EXEC useradd in Docker fills hard drive on host
  • Python Exception inside Try/Except Statement
  • Playing with Wavefront – Network Packet Loss
  • Playing with Wavefront – Missing Agents

Recent Comments

  • Caleb on Automatically Configure VMware Log Insight
  • Dan on Error Removing Host from vSphere
  • Caleb on Getting Fancy with Log Insight Alerting (aka. Monitoring DHCP pools via logs)
  • Caleb on Monitoring VMware vCenter Servers using HTTP Health checks
  • Karuna Yarlagadda on SSRS 2008 Domain User Issue

Archives

  • November 2018
  • August 2018
  • July 2018
  • December 2017
  • November 2017
  • October 2017
  • September 2017
  • June 2017
  • October 2016
  • September 2016
  • August 2016
  • July 2016
  • May 2016
  • March 2016
  • February 2016
  • December 2015
  • July 2015
  • July 2014
  • May 2014
  • March 2014
  • February 2014
  • November 2013
  • October 2013
  • August 2013
  • June 2013
  • February 2013
  • January 2013
  • December 2012
  • November 2012
  • October 2012
  • September 2012
  • August 2012
  • April 2012
  • March 2012
  • February 2012
  • November 2011
  • October 2011
  • August 2011
  • March 2011
  • February 2011
  • November 2010
  • October 2010
  • September 2010
  • July 2010
  • June 2010
  • May 2010
  • April 2010
  • March 2010
  • December 2009
  • July 2009
  • June 2009
  • May 2009
  • April 2009
  • March 2009
  • February 2009

Categories

  • .mp3
  • .vlcp
  • .wmv
  • 00000000000000d1
  • 0x8024402C
  • 2005
  • 2008
  • 29506
  • 5.5
  • 64bit VPN
  • 80072ee7
  • aag
  • Ac
  • Access is Denied
  • account
  • Acrobat
  • Active Directory
  • AD
  • Adobe
  • agent
  • agentless
  • alert
  • alwayson
  • analytics
  • ansible
  • API
  • Apple Mac XServer Cron Backup Windows File Server
  • APSB09-01
  • apt-get
  • ARP Poisoning
  • ASA
  • ASDM
  • audacity
  • Authentication
  • AutoDeploy
  • automation
  • availability group
  • Awesome
  • backup
  • BartPE
  • Bind
  • BitLocker
  • blinking folder with question mark
  • bluetooth
  • boot
  • bottleneck
  • bridge address-table
  • broken
  • bsod
  • Build
  • but no Image Profile is associated with it.
  • cannot display the page
  • Cannot open the Outlook Window
  • CDP
  • CentOS
  • cfapi
  • Change
  • chkdsk
  • Chuck Swindoll
  • CIM
  • Cisco
  • Cisco ASA 5510
  • cluster
  • cmd
  • Comparison
  • compliance
  • Configuration
  • Configuration Managenent
  • configure
  • Content Pack
  • converting
  • Crash
  • CSV
  • Custom Drivers
  • custom fact
  • database log
  • debugging
  • decrypting drive
  • deployment
  • detected corruption
  • DHCP
  • disable
  • dism
  • DN
  • DNS
  • Docker
  • does not have required permissions
  • Download
  • downtime.
  • Dr. Os Guinness
  • driver
  • drivers
  • DTS
  • ECM
  • Edge
  • error
  • Error: Enabling Active Directory failed
  • ESX
  • ESX Cluster
  • ESXi
  • ESXi 4
  • ethics
  • event viewer
  • exchange 2007
  • exhausted
  • Fails
  • FedEx
  • File DNS
  • find VM by MAC
  • finding rogue mac
  • firmware
  • fqdn
  • Freedom
  • FreeTDS
  • FTP
  • full
  • G7
  • get-vmhostadvancedconfiguration
  • GetDataBack for NTFS
  • Getting Started
  • Google Down
  • Government
  • guide
  • Hands On Labs
  • hangs
  • hangs on boot
  • Hardware
  • high CPU
  • History
  • Host
  • Host Profiles
  • HP
  • hung
  • hyperic
  • ID 57
  • identity source
  • IEESC
  • iLO driver
  • InetAddress Ping
  • InfluxDB
  • Install
  • Integrated
  • Integrity
  • IP-Pools
  • IPS
  • IPSec
  • iSCSI
  • Isolate
  • James
  • java
  • job
  • kb950772
  • kerberos
  • kernel
  • ldaps
  • License not available to perform the operation
  • linux
  • Linux Mint
  • log
  • Log Insight
  • log insight agent
  • Log Parsing
  • logging
  • Loginsight
  • logs
  • lust
  • Macbook
  • Make Availale Offline
  • McAfee
  • md5
  • Microsoft
  • mind
  • missing
  • missing public traffic
  • missing traffic
  • mntapi error: 176
  • module
  • Momentus XT
  • mon
  • monitoring
  • MSI
  • MSSQL
  • mssql. sql
  • multiple monitors
  • Nested 64bit
  • NetFN 0x36
  • NetGen
  • netio.sys
  • netios.sys
  • network
  • network adapter
  • new hire
  • ntbtlog.txt
  • Office12
  • Oops
  • Open Source
  • Openfiler
  • Orchestrator
  • Outage
  • Outlook 2007
  • P2V
  • password change
  • percent
  • performance issues
  • perl
  • plugins
  • pool
  • Postgres
  • PostgreSQL
  • PowerCLI
  • Powershell
  • PPTP
  • Process
  • Prometheus
  • proxy
  • Puppet
  • Puppet Master
  • purity
  • pymssql
  • python
  • reached target initrd default
  • reboot
  • recovery model
  • RedHat
  • removing bitlocker
  • replica
  • response traffic dropped
  • Review
  • RHEL
  • robocopy
  • routing
  • SCM
  • Script
  • Seagate
  • Security
  • selinux
  • serial
  • series
  • Serv-U
  • Server
  • server 2003
  • server 2008
  • Server 2012
  • services
  • set-vmhostadvancedconfiguration
  • sfc
  • sha-512
  • Sign-On and Discovery
  • simple
  • Socrates in the City
  • spn
  • SQL
  • SQL Management Studio Express
  • SS
  • ssl
  • sso
  • SSRS
  • SSRS 2008
  • svchost.exe
  • syslog
  • SYSTEM_THREAD_EXCEPTION_NOT_HANDLED
  • target
  • theology
  • This host has been added to VC
  • time
  • tongue
  • troubleshooting
  • tsdb
  • tsql
  • Uncategorized
  • undionly.kpxe.vmw-hardwired
  • unexpected
  • uninstall drivers
  • Update DNS
  • UPS
  • US
  • used
  • user
  • vC
  • vCD
  • vcenter operations manager
  • vcloud director
  • vCM
  • vCNS
  • vCO
  • vcops
  • vCSA
  • VDR
  • vFabric
  • VIBs
  • Virtual
  • vm
  • VMware
  • VMware Configuration Manager
  • VMware Data Recovery
  • VMware HA
  • VMware vCenter Configuration Manager
  • VMworld 2013
  • VMworld2013
  • vpxd_servicecfg
  • vRealize
  • vRLI
  • vs
  • vShield
  • vSphere
  • W32/Wecorl.a
  • Wavefront
  • Web Client
  • Wecorl.a
  • Windows
  • Windows 7
  • Windows Authentication
  • Windows cannot access the specified device
  • Windows Server 2003
  • Windows Update Error
  • Windows User Account Control (UAC) restrictions have been addressed
  • WinFF
  • winrm
  • wireshark
  • won't boot
  • wordpress
  • workaround
  • x64
  • XFCE
  • XP
  • You do not have the Backup and Restore Files user rights
  • zenoss

Meta

  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org

WordPress Theme: Idealist

This site uses cookies: Find out more.