Skip to main content

WAN link inventory management

Project description

Inventory of all Widw Area Network links supplied by various Telecom Service Providers.
For this project it is assumed that there was a leagacy monolithic application which used to
serve the purpose of these link inventory management and the inventory data is alrady available in a
pre exising Microsoft SQL database.

This microservice will first establish connection with the pre-existing database instead of creating one and
enable rest api to query the data. In future it is expected to provide new features in link inventory
management and will become independent with its own database.

install pyodbc - For configuring ubuntu16.04 to connect to MS SQL server you may follow a detial article here or follow the section below for a summary

lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 16.04.4 LTS
Release: 16.04
Codename: xenial

sudo su
curl | apt-key add -
curl > /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql17
sudo ACCEPT_EULA=Y apt-get install mssql-tools
sudo apt-get install unixodbc-dev freetds-bin freetds-dev tdsodbc
#install pyodbc within virtual environment
pip install pyodbc
pip install mssql-cli

sudo mv /etc/odbcinst.ini /etc/odbcinst.ini.bak

sudo vi /etc/odbcinst.ini

Description = v0.91 with protocol v7.2
Driver = /usr/lib/x86_64-linux-gnu/odbc/
FileUsage = 1
UsageCount = 1

sudo vi /etc/freetds/freetds.conf

# TDS protocol version
; tds version = 4.2

# Whether to write a TDSDUMP file for diagnostic purposes
# (setting this to /tmp is insecure on a multi-user system)
; dump file = /tmp/freetds.log
; debug flags = 0xffff

# Command and connection timeouts
; timeout = 10
; connect timeout = 10

# If you get out-of-memory errors, it may mean that your client
# is trying to allocate a huge buffer for a TEXT field.
# Try setting 'text size' to a more reasonable limit
text size = 64512

# A typical Sybase server
host =
port = 5000
tds version = 5.0

# A typical Microsoft server
host =
port = 1433
tds version = 7.0
# A typical Microsoft server
host = dbserver
port = 1433
tds version = 7.0

sudo vi /etc/odbc.ini

Driver = FreeTDS
ServerName = MSSQL # maps it to DSN name not the actual host name
Port = 1433
TDS_Version = 7.2

Insttall a Microsoft Sql docker container with persistent volume

sudo docker pull
#sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=welcome@123' -p 1433:1433 --name sql1 -d

for data persistance with volume -

sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=welcome@123' --name sql1 -p 1433:1433 -v sqlvolume:/var/opt/mssql -d
docker volume ls

the volume is created at the host level by the docker, even if the container is deleted the volume is persisted
and the next docker run command with -v sqlvolume will mount this existing volume

local sqlvolume

for data persistence with host directory

sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=welcome@123' -p 1433:1433 -v <host directory>:/var/opt/mssql -d

sudo docker exec -it sql1 "bash"
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'welcome@123
SELECT Name from sys.Databases
CREATE LOGIN user1 WITH PASSWORD = 'user@1234';

GRANT ALL PRIVILEGES ON infooper.* TO 'user1'@'localhost';

From remote computer , pip install mssql-cli or
and run the client commands

telnet dbserver 1433

mssql-cli -S -U SA -d infooper -P welcome@123
CREATE TABLE vw_comm_links (serial_no INT NOT NULL PRIMARY KEY, circuit_id NVARCHAR(50), division_name NVARCHAR(50), fa_end NVARCHAR(50), bandwidth NVARCHAR(50), link_type NVARCHAR(50), )
INSERT INTO vw_comm_links VALUES (1, 'circuitid1', 'division1', 'faend1', '10MB', 'MPLS' );
INSERT INTO vw_comm_links VALUES (2, 'circuitid2', 'division2', 'faend2', '5MB', 'PTP' );
INSERT INTO vw_comm_links VALUES (3, 'circuitid3', 'division3', 'faend3', '20MB', 'MPLS' );
SELECT * FROM vw_comm_links

test that tds is working

isql -v MSSQL SA welcome@123

install the application within a virtual environment

virtualenv -p python3 venv
source venv/bin/activate
pip install linkInventory

The main configuration file nfor the application is /etc/tokenleader/linvInventory_configs.yml

sudo vi /etc/tokenleader/linkInventory_configs.yml

host_name: # for docker this should
host_port: 5004
ssl: disabled # not required other than testing the flaks own ssl. ssl should be handled by apache
ssl_settings: adhoc
TDS_Verson: 7.0
Server: dbserver
Port: 1433
Database: infooper
db_pwd_key_map: db_pwd
engine_connect_string: 'mssql+pyodbc:///odbc_connect={}'

secrets_file_location: linkInventory/tests/test_data/secrets.yml # where you have write access
fernet_key_location: linkInventory/tests/test_data/fernetkeys # where you have write access and preferebly separated from secrets_file_location
db_pwd_key_map: db_pwd # when using encrypt-pwd command use this value for --kemap
tokenleader_pwd_key_map: tl_pwd

generated an encrypted password for the db

encrypt-pwd -k db_pwd -p <your password here>

start the service


Project details

Release history Release notifications | RSS feed

This version


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

linkInventory-1.2.tar.gz (9.1 kB view hashes)

Uploaded source

Supported by

AWS AWS Cloud computing Datadog Datadog Monitoring Facebook / Instagram Facebook / Instagram PSF Sponsor Fastly Fastly CDN Google Google Object Storage and Download Analytics Huawei Huawei PSF Sponsor Microsoft Microsoft PSF Sponsor NVIDIA NVIDIA PSF Sponsor Pingdom Pingdom Monitoring Salesforce Salesforce PSF Sponsor Sentry Sentry Error logging StatusPage StatusPage Status page