Linux :- ODBC / SQL Server – Configuration

Background

On our Linux box, wanted to configure a Database Source to a SQL Server Instance.

Lineage

In a previous post, we installed SQL Server Command Line Tools and ODBC Development Tools.

That post is :-

SQL Server On Linux – Installing Command Line Tools
Link

ODBC Manager

Here are the ODBC Managers that ship with a Linux system :-

  1. iODBC
  2. unixODBC
    • Project Homepage
      Link

Microsoft relies on unixODBC and that fact is documented here :-

Docs / SQL / Connect your client to SQL / ODBC / Linux and Mac
Installing the Driver Manager
Link

Install MS SQL Server ODBC Driver

Linux

CentOS

Our os is Linux and so we will get Version Number for that os.

Get Version Number

Syntax

cat /etc/centos-release

Output

linux.version.20181204.1220AM.PNG

Explanation

The returned value is “CentOS Linux release 7.5.1804 (Core)“; version 7.

Installation

Driver

Register Repository

Syntax

sudo su

curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
exit

Install ODBC Driver ( msodbcsql17 )

Sample

sudo ACCEPT_EULA=Y yum install msodbcsql17

Output

yum.install.20181204.1233AM

Review ODBC Manager

odbcinst

odbcinst -j
Sample
odbcinst -j
Output

odbcinst-j.20181204.0224PM

Explanation
  1. version
    • unixODBC 2.3.1
  2. Drivers
    • /etc/odbcinst.ini
  3. System Data Sources
    • /etc/odbc.ini
  4. File Data Source
    • /etc/ODBCDataSources
  5. User Data Source
    • /home/dadeniji/.odbc.ini

Review Installed Library ( msodbcsql17 )

Folder List

Sample
ls -l /opt/microsoft/msodbcsql17/lib64
Output

msodbcsql.lib64.20181204.1241AM

ldd ( Print Shared Object’s Dependency )

Objective

Validate that the ODBC Driver can be successfully accessed and loaded.

Sample
ldd /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
Output

ldd.20181204.0550AM

dltest ( Library Symbol Test )

Objective

Test that driver’s module can be loaded and that specific function can be invoked.

Syntax

dltest /opt/microsoft/msodbcsql17/lib64/libmsodbcsql[version-number] SQLGetInstalledDrivers

Sample

dltest /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1 SQLGetInstalledDrivers

Output

dltest.20181204.1244AM

Data Source Registration

/etc/odbcinst.ini

cat

Sample

cat /etc/odbcinst.ini
Output

odbcinst.ini.20181204.1249AM

Data source Registration

/etc/odbc.ini

vi

sudo to vi and edit /etc/odbc.ini.

Add entries for targeted SQL Server Instance.

Sample

sudo vi /etc/odbc.ini
Output

odbc.ini.20181204.1257AM

List configured Data Sources

Syntax

odbcinst -q -s
Sample

odbcinst -q -s
Output

odbc.list.20181204.0100AM

Query

isql

Launch

Syntax


isql -v [datasource] [user] [pwd]

Sample


isql -v bible dadeniji mystupidpwd

Output

launch.20181204.0108AM

Query

Query – Get Current Database

Sample

select db_name as [dbname]

Output

query.getDBName.20181204.0207PM

Referenced Work

  1. Microsoft
  2. capside
    • Using Azure SQL Database and Azure SQL DataWarehouse with Perl
      Link
  3. MAPR
    • Support Portal
      • How to test ODBC connections for debugging in Linux using unixODBC
        Link
  4.  mkleehammer/pyodbc
    • mkleehammer/pyodbc
      • Connecting to SQL Server from RHEL or Centos
        Link
  5. Snowflake
    • DOCS » CONNECTING TO SNOWFLAKE » ODBC DRIVER
      • INSTALLING AND CONFIGURING THE ODBC DRIVER FOR LINUX
        Link
  6. azurewebsites
    • Create PHP apps using SQL Server on RHEL
      Link
  7.  isql
  8. Man7.org
    • Linux Programmers Manual
  9. unixodbc
    • odbcinst.ini
      • unixODBC without the GUI
        Link
    • dltest
      • dltest man page
        Link
  10. systutorials.com
    • odbcinst
      • odbcinst (1) – Linux Man Pages
        Link
  11. SQReam
    • ODBC for Linux documentation
      Link

SQL Server On Linux – Installing Command Line Tools

Background

It is time to start playing around with command line client tools for SQL Server On Linux.

os

Each os has its own installation platform.

Our os is CentOS and so that is the os we singularly target.

Components

Here are the actual components that are part of SQL Server Client Tools :-

  1. sqlcmd
    • SQL Query Tools
  2. bcp
    • Transfer data in and out from SQL Server to text-file

 

Repositories

Review Registered Repositories

Syntax


yum repolist

Output

yum.repolist.20181130.0947PM

Explanation

  1. packages-microsoft-com-prod
    • That is the repo we are are looking for

Register Repository

If the Repository is not registered, please register it!

Syntax

sudo curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo

Output

yum.register.20181130.1000PM

Explanation

  1. Added Repositories
    • Added msprod.repo

 

Install Application

Is Application installed

Syntax


sudo yum list installed | grep -i mssql

Output

yum.list.installed.20181130.0908PM

Explanation

  1. Review installed mssql applications
    • mssql-server.x86_64
      • Server
    • client???

Review Applications

List all available applications.

Our options are :-

  1. yum list
  2. yum search

yum list

Syntax

yum list | grep -i mssql

Output

yum.list.20181130.0903PM

yum search

Syntax


yum search mssql

Output

yum.search.20181201.0757AM

Explanation

  1. The applications we need are :-
    • mssql-tools.x86_64

 

Application Info

List all available applications.

Syntax

yum info mssql-tools.x86_64

Output

yum.info.mssql-tools.x86_64.20181201.0736AM

Install Application

Install application.

Syntax

sudo yum install mssql-tools unixODBC-devel

Output

yum.install.mssql-tools.20181130.1045PM.PNG

 

Application Installed Location

whereis

Syntax


whereis [app]

Sample


whereis sqlcmd


Sample


whereis bcp

Output

whereIs.20181201.0742AM

Explanation

  1. Our applications, sqlcmd and bcp, are installed in the /opt/mssql-tools/bin folder

 

Invoke Application

sqlcmd

Syntax


/opt/mssql-tools/bin/sqlcmd -S localhost -U dadeniji -Q "select @@servername as servername"

Output

query.servername.20181130.0930PM

Explanation

  1. We are prompted for the password to our username
  2. We entered the username
  3. And, the query returns the username

 

References

  1. Microsoft
    • Docs / SQL / SQL Server on Linux
      • Install sqlcmd and bcp the SQL Server command-line tools on Linux
        Link

SQL Server – Installation on Linux/CentOS

Background

Let us install SQL Server v2017 on our CentOS System.

Installation

Outline

  1. Repositories
    • Review registered Repositories
    • Add missing Repositories
  2. MS SQL Server Engine
    • Install
    • Configure
  3. Services
    • Service Review
  4. Adjust Network Firewall Rules
  5. Validate

Repositories

Review Registered Repositories

yum repolist

Syntax

yum repolist

Output

yum.repolist.20181126.0831PM.PNG

Explanation

We do not see the repositories that we need :-

  1. Repositories
    • packages-microsoft-com-mssql-server-2017

 

yum repository – add

Sample
Sample – Add SQL Server RTM

sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017-gdr.repo

 

Sample – Add Cumulative Patch

sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo

 

Review Registered Repositories

yum repolist

Syntax

yum repolist

Output

yum.repolist.post.20181126.1034PM

Explanation
  1. Repositories
    • packages-microsoft-com-mssql-server-2017

 

Install

Install SQL Server Engine

Syntax

yum install -y mssql-server

Output

install.20181126.0859PM.PNG

 

Configuration

License

Syntax

sudo /opt/mssql/bin/mssql-conf setup
Output

sudo-ms-config-20181126-0914PM.PNG

 

Explanation
  1. Licensing
    • Developer
  2. sa password
    • Enter and Confirm sa password

Services

Review Services :- mssql-server

Syntax

systemctl status [service]
Sample

systemctl status mssql-server

Output

systemctl.20181126.0917PM.PNG

Explanation
  1. Service is running

Firewall

iptables

Our Firewall Software is iptables.

Port TCP/1433 ( mssql-server )

/etc/sysconfig
Outline
  1. Using an editor load /etc/sysconfig/iptables
  2. Add Entry for TCP/port 1433
    • -A INPUT -p tcp -m state –state NEW -m tcp –dport 1433 -j ACCEPT
Image – Pre

firewall.etc.sysconfig.pre.20181126.0942PM.PNG

Image – Post

firewall.etc.sysconfig.post.20181126.1052PM

Reload
Syntax

sudo systemctl reload iptables

Validation

SQL Query

@@version

Syntax

select @@version

Output

@@version.20181126.1110PM

 

References

  1. CentOS.Org
  2.  DigitalOcean
    • Mitchell Anicas
      • How To List and Delete Iptables Firewall Rules
        Link
  3. Sharad Chhetri
    • How to start / stop / restart / reload iptables on CentOS 7 / RHEL 7
      Link

 

 

PowerShell – Get Computer name

Background

Wanted to highlight a couple of APIs for retrieving the computer name in Powershell.

Platform

Linux/Cent OS.

 

Outline

  1. Environment Variable
    • Get-ChildItem Env:HOSTNAME
  2. system.environment
    • machineName
  3. system.net.Dns
    • GetHostName
  4. Get-WmiObject ( only works on MS Windows )
    • Win32_ComputerSystem
      • Name

 

Environment Variables

Get-ChildItem Env:

Syntax


pwsh -Command "Get-Childitem Env:"

Output

Get-ChildItem.Env.20181112.0729PM

 

Get-ChildItem Env:HOSTNAME

Syntax


pwsh -Command "Write-Host( (Get-ChildItem Env:HOSTNAME).Value )"

Output

Get-ChildItem.Env.HostName.20181112.0723PM

system.environment

machineName

Syntax


pwsh -Command "Write-Host([system.environment]::machineName)"

Output

system.environment.machineName.20181112.0708PM

system.net.Dns

GetHostName

Syntax


pwsh -Command "Write-Host([system.net.Dns]::GetHostName())

Output

system.net.Dns.GetHostName.20181112.0706PM

 

Get-WmiObject.Win32_ComputerSystem.Name.20181113.0159AM.PNG

Win32_ComputerSystem

Syntax


powershell -Command "Write-Host((Get-WmiObject Win32_ComputerSystem).Name)"

Output

OS – MS Windows

Get-WmiObject.Win32_ComputerSystem.Name.20181113.0159AM.PNG

OS – Linux

Image

Get-WmiObject.Win32_ComputerSystem.Name.Linux.20181113.0203AM

Textual


>pwsh -Command "Write-Host( Write-Host((Get-WmiObject Win32_ComputerSystem).Name))"
Get-WmiObject : The term 'Get-WmiObject' is not recognized as the name of a cmdlet, function, script file, or operable program.
Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:25
+ Write-Host( Write-Host((Get-WmiObject Win32_ComputerSystem).Name))
+ ~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (Get-WmiObject:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

PowerShell Install On Linux / CentOS

Background

Still preparing our CentOS computer.

Our current step is to Install Microsoft’s PowerShell.

Guide

Our guide is :-

Installing PowerShell Core on Linux
Link

Outline

  1. Install
    • yum
      • Install
        • Install Powershell
      • Registered Repositories – Review
        • yum repolist
        • ls /etc/yum.repos.d
      • Registered Repositories – Update
        • Curl/tee
      • Install
        • Install Powershell
  2. Run Sample Powershell Scripts

Install

Install Powershell

yum install powershell

Syntax


yum install powershell

Output

Output – Image

yum.install.powershell.20181112.1030AM.PNG

Output – Text

>sudo yum install powershell
[sudo] password for dadeniji:
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirror.hostduplex.com
* extras: mirror.keystealth.org
* updates: mirror.keystealth.org
No package powershell available.
Error: Nothing to do
>

Explanation

Microsoft’s repository not registered on machine.

Registered Repositories – Review

Let us review the yum repositories that we have registered.

yum repolist

Syntax


yum repolist

Output

Output – Image

yum.repolist.20181112.1028AM.PNG

Output – Textual

>yum repolist
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirrors.sonic.net
* extras: mirrors.usc.edu
* updates: sjc.edge.kernel.org
repo id repo name status
base/7/x86_64 CentOS-7 - Base 9,911
extras/7/x86_64 CentOS-7 - Extras 434
updates/7/x86_64 CentOS-7 - Updates 1,614
repolist: 11,959

 

/etc/yum.repos.d :- List Files

Syntax


ls -la /etc/yum.repos.d

Output

Output – Image

ls.etc.yum.repos.d.20181112.1033AM.PNG

Explanation

  1. The only repositories registered are CentOS

 

Registered Repositories – Update

Create File /etc/yum.repos.d/Microsoft.repo

Let us add Microsoft’s Redhat repository to the list of registered repositories.

Syntax


curl https://packages.microsoft.com/config/rhel/7/prod.repo | sudo tee /etc/yum.repos.d/microsoft.repo

Output

Output – Image

curl.tee.20181112.1035AM.PNG

Output – Textual

>curl https://packages.microsoft.com/config/rhel/7/prod.repo | sudo tee /etc/yum .repos.d/microsoft.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 193 100 193 0 0 504 0 --:--:-- --:--:-- --:--:-- 505
[packages-microsoft-com-prod]
name=packages-microsoft-com-prod
baseurl=https://packages.microsoft.com/rhel/7/prod/
enabled=1
gpgcheck=1
gpgkey=https://packages.microsoft.com/keys/microsoft.asc
>

 

yum install

Using yum, let us install powershell.

yum install powershell

Syntax


sudo yum install -y powershell

Output

Output – Image
Output – Image – #1

yum.install.powershell.20181112.1037AM.PNG

Output – Image – #2

yum.install.powershell.20181112.1039AM.PNG

Output – Image – #3

yum.install.powershell.20181112.1041AM.PNG

Output – Image – #4

yum.install.powershell.20181112.1043AM.PNG

Output – Image – #5

yum.install.powershell.20181112.1045AM.PNG

 

 

Sample

Run Sample Scripts

Sample #1

Get PowerShell Version Number

Syntax


pwsh -Command "Write-Host((Get-Host).version) "

Output

Output – Image

powerShell.version.20181112.1218PM

Sample #2

Get Username for current user.

Syntax


pwsh -Command "Write-Host( 'Hello ' +  [Environment]::Username) "

Output

Output – Image

helloWorld.20181112.1137AM

 

Summary

Couple of quick points :-

  1. Installation Errors
    • Error :- Repository not available
      • Message
        • No package powershell available.
          Error: Nothing to do
      • Remediation
        • Update Repository – Add Microsoft
  2. In MS Windows, the Powershell executable is powershell.
    In Linux, is is pwsh.

 

 

Keep your laptop running when LID is Closed

Background

There is really not much need to keep a laptop’s lid opened when connected to an external monitor.

OS

Linux

Outline

  1. Launch terminal
  2. sudo to root
  3. edit /etc/systemd/logind.conf
    • Change folder to /etc/systemd
    • Backup current logind.conf
    • Launch editor ( vi) and pass along file name ( logind.conf )
    • Keep current HandleLidSwitch=suspend commented out
    • Add HandleLidSwitch=lock
    • Save Changes
    • Exit Editor
  4. Restart Services
    • Restart systemd-logind.service
      • Command :- systemctl restart systemd-logind.service

 

Images

logind.conf
logind.conf – Original

logind.conf.20181108.0740PM.PNG

logind.conf – Revised

logind.conf.20181108.0746PM.PNG

 

Windows

Outline

  1. Access Control Panel \ Power Options\ System Settings
    • Select “Choose what the Power Buttons do”
    • The “Control Panel\All Control Panel Items\Power Options\System Settings” window appears
      • Access the “Power and sleep buttons and lid settings” group box
      • Focus on “when I close the lid” area
        • There are two options
        • The first been “On Battery” and the other “When plugged in”

Images

Control Panel \ All Control Panel Items \ Power Options \ System Settings

Original

systemSettings.closeLid.20181108.0756PM.PNG

Revised

systemSettings.closeLid.20181108.0758PM.PNG

 

Validation

The steps above were validated on the OSes listed below:

  1. Linux
    • Cent OS
  2. MS Windows
    • Version 10

References

  1. Redhat
    • Products & Services > Product Documentation > Red Hat Enterprise Linux > 7 > Desktop Migration and Administration Guide
      • 13.10. Preventing the Computer from Suspending when Closing the Lid
        Link

CentOS – Blank Screen – No Login

Background

Yesterday, I installed CentOS v7 on a refurbished box.  I went the “Server with GUI” option, but upon starting up for the first time, the usual login screen is not coming up.

I have a dull blank screen, rather than one that lists my username.

Remediation

Idea

Took to the net and found a few people had similar problems.  The most widely promulgated option is to edit the /etc/default/grub configuration file and look for the “GRUB_CMDLINE_LINUX” entry.

And, go on to add “video=LVDS-1:d” to the end of the current contents of the entry.

Steps

Outline

  1. Access Terminal window via pressing the key combination CTRL-ALT-F2
  2. Login from the terminal by entering the username and password
  3. The configuration file /etc/default/grub is read only and so change the file to writable
  4. Edit in vi or your choice editor as sudo
    • Look for the “GRUB_CMDLINE_LINUX” entry and add “video=LVDS-1:d” to the end of the current value.
  5. Reconfigure using grub2-mkconfig
  6. Reboot your computer

 

Details

Here are the steps in detail.

CTRL-ALT-F2

Access Terminal mode by pressing the key combination CTRL-ALT-F2.  The Terminal Screen should appear.

 

Logging from the Terminal

Logging from the Terminal by entering your username and password.

 

Launch vi in sudo mode


sudo vi /etc/default/grub

 

Edit /etc/default/grub

Add ” video=LVDS-1:d
Before

grub-20160813-1143PM ( Before )

Add

grub-20160813-1146PM ( Before)

Reconfig Grub


sudo grub2-mkconfig -o /boot/grub2/grub.cfg

Reboot

reboot

Credit

No original thoughts here!

Credits go out to the guys ( scubastevesama) who spoke about it on Reddit.

And, the ones ( Josef Holland ) who blogged about it.

References

CentOS Login Screen

  1. centos 7 installation issue, no login screen – scubastevesama
    Link
  2. Josef Holland – Disable Laptop LCD Completely on boot
    Link
  3. [RESOLVED] Atom D510 – 6.0 updated to 6.1 – became confused about monitor configuration
    Link
  4. Install on a laptop with external monitor only
    Link

 

Editing /etc/default/grub

  1. Amanda Folson – Modify File Permissions with chmod
    Link
  2. I need to change the Read Only in gedit to Read/Write
    Link