ninigi.log

作業ログ。ネットワーク, python, RoR, Reactなど。

Python3 SQL Server 2016 接続設定

やりたいこと

  • Python3 からリモートの SQL Server 2016 に接続する

動機

PythonからSQL Serverに接続する際に情報が少なくて調べるのが大変だったのでまとめた。

ちょっと調べるとローカルホスト上にSQL Serverを立てて接続する方法などはたくさん出てくるがリモートサーバへの接続やWindows以外のOSからの接続方法などは断片的な情報しか出てこない。

本記事では、 ubuntu 18.04 python 3.6.8 を用いて SQL Server 2016 に接続する方法について書いていく。

設定前の状況

  • SQL Serverは動作しており、ローカルからの接続 (SQL Studio 使用) は成功している
    • ただし、認証にはWindows認証を用いている(認証については後述)
  • クライアントPCには、pythonのモジュールである pyodbc がインストール済み
  • クライアントPCには、コマンドラインツール sqlcmd もインストール済み (上記の過程でインストール済み)
  • ping によってIP的には通信ができていることは確認済み
  • Pythonpyodbcにおけるドライバーのエラーは解決済み(他の記事に書く)

以上のような状況で、接続に失敗する。

Pythonからの試行

接続用のスクリプト

conn = pyodbc.connect('DRIVER={/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.1.1};'
                                      'Server=10.0.0.124;'
                                      'UID=user1;'
                                      'PWD=pass;'
                                      'Database=master;'
                                     )

このスクリプトを実行時のエラー

Traceback (most recent call last):
  File "dbtest.py", line 7, in <module>
    conn = pyodbc.connect('DRIVER={/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.1.1};'
pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

IPアドレス、UID、PWDは合っていることは確認済み。

ここからでは何が原因かわからないため、CLIからコマンドを叩く。

CLIからの試行

接続試行用のコマンド

> sqlcmd -S 10.0.0.124 -U user1
Password:

コマンド実行時のエラー

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x102.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

接続自体ができていないということがわかった。Windows Server側の設定が必要らしいのでこれについてまとめていく。

SQL Server 接続設定

リモートからの接続の許可

f:id:ninigishi:20190911155413p:plain リモートからの接続を許可にチェックを入れる

TCP/IPによる接続を許可

SQL Server Configuration Managerを起動する。 f:id:ninigishi:20190911155537p:plain

SQL Server Configuration Manager は日本語では SQL Server 構成マネージャー。

f:id:ninigishi:20190911155738p:plain

[SQL Server ネットワークの構成] >> [SQL EXPRESSのプロトコル] を選択。

右ペインのTCP/IP を右クリックして、プロパティを選択。以下のようなポップアップが開く。

f:id:ninigishi:20190911160024p:plain

Enabledを"はい"に変更。[IPアドレス]タブを選択。

f:id:ninigishi:20190911160211p:plain

一番下の [IP ALL] の欄の[TCP port] の値を1433 にする。別に他の値でも良いが、Windows SQL Serverのデフォルトポートは1433。

適用ボタンをクリック。設定を適用するためにはサービスの再起動が必要。

f:id:ninigishi:20190911160535p:plain

左ペインで [SQL Serverのサービス] を選択。右ペインの [SQL Server] を右クリックして[再起動] を選択。サービスの再起動が行われる。

これで、SQL Server的にはTCP/1433 でコネクションが張れるようになった。

Windowsはデフォルトでファイアウォールが設定されているので、次にポートの開放を行う必要がある。

Windows Firewall の設定

Windows Firewallを起動する。名前はちょと違うかもしれない(手元にWindows環境がないので確認できない)。

[新規の受信規則] を選択して新たなルールを追加する。

f:id:ninigishi:20190911161139p:plain

ポートベースで許可を行う。プログラムベースの方がスマートかもしれない。

f:id:ninigishi:20190911161213p:plain

TCPにチェックを入れる。[特定のローカルポート]の値を1433にする。先程違うポート番号を設定した場合はそれを入れる。

f:id:ninigishi:20190911161332p:plain

接続を許可するにチェックを入れる。

f:id:ninigishi:20190911161402p:plain

ここは自分のポリシーに従って。今回は全部許可にした。

f:id:ninigishi:20190911161434p:plain

今作成しているファイアウォールのルールに名前をつける。適当に。

SQL Serverの認証設定

ここまでで接続自体はできるようになっていて欲しいが、できない。SQL Serverには"Windows 認証" と "SQL Server 認証"の2つのユーザ認証方式をサポートしている。

Windows認証の仕組みはどうなっているのかよくわからないが、ユーザ名だけで認証できるようだ。今回は、ユーザ名とパスワードによって認証を行う一般的な認証を行いたい。従って、SQL ServerSQL Server認証を許可しなければならない。

環境構築の段階で設定していればここは飛ばして良い。

f:id:ninigishi:20190911163005p:plain

[SQL Server認証モードとWindows認証モード] にチェックを入れ、[適用] をクリック。

ここでもサービスを再起動する必要があるので、先程同様にSQL Serverのサービスを再起動する。

この時点で、接続自体はできるようになった。

> sqlcmd -S 10.0.0.124 -U user1
Password:
1>

プロンプトに 1> と表示され、接続できていることが確認できた。

pythonで冒頭のスクリプトを叩いてもエラーが発生しないことが確認できた。

netbox-dockerのホスト移植

やりたいこと

  • 現在ホストA上でnetboxをdocker-composeで動かしている。
  • これをデータも含めてホストBに移行したい

動機

自分のサーバ上でとりあえず試運用していた。よさそうだったので、監視サーバに移行させたい。

現環境の確認

自分のサーバ上で動いているnetboxの状況を把握する。 3ヶ月前に入れたっぽいけどどうやって入れたかは覚えていない。

まずは動作確認

> docker ps
e4db7494e06c        netboxcommunity/netbox:latest   "/opt/netbox/docker-…"   3 months ago        Up 3 months                                           netbox-docker_netbox_1
b3840d12c8f9        netboxcommunity/netbox:latest   "python3 /opt/netbox…"   3 months ago        Up 3 months                                           netbox-docker_netbox-worker_1
c65c2d8ec2bf        postgres:10.4-alpine            "docker-entrypoint.s…"   3 months ago        Up 3 months         5432/tcp                          netbox-docker_postgres_1
fb74f3512a09        redis:4-alpine                  "docker-entrypoint.s…"   3 months ago        Up 3 months         6379/tcp                          netbox-docker_redis_1

動いてそう。これ CONTAINER ID とか載せても大丈夫なんかな、何もわからない。止めるからいっか。

$HOME/netbox-docker というディレクトリがあったので覗いていみるとgit で管理されているディレクトリだった。

docker-compose.ymlがあった。

$ cd $HOME/netbox-docker
$ docker-compose ps
            Name                           Command               State                Ports             
--------------------------------------------------------------------------------------------------------
netbox-docker_netbox-worker_1   python3 /opt/netbox/netbox ...   Up                                     
netbox-docker_netbox_1          /opt/netbox/docker-entrypo ...   Up                                     
netbox-docker_nginx_1           nginx -c /etc/netbox-nginx ...   Up      80/tcp, 0.0.0.0:32769->8080/tcp
netbox-docker_postgres_1        docker-entrypoint.sh postgres    Up      5432/tcp                       
netbox-docker_redis_1           docker-entrypoint.sh sh -c ...   Up      6379/tcp    

ここで動いてることが確認できた。

redisって何で使っているんだろうか。docker-compose.yml の中身を見てみると、netboxnetbox-workerdepends_on されてる。何だろうセッション管理とかかな?

それなら移行すべきデータはpostgres のデータのみになる。

gitの詳細を確認してみる。

> git config --list | grep remote.origin.url
remote.origin.url=https://github.com/netbox-community/netbox-docker.git

ここからcloneしてきたらしい。そんな気もする。

github.com

普通に作られていればコンテナ自体を移植する必要はないはず。docker pull とかすれば同じものが入るはず。だからデータだけを移植すればいい。 さらに、データはpostgres のみを移行してやればよさそう。

これからデータコンテナの移植をいろいろ調べようと思っていたけど正解がここにあった。

  • To create a database backup run docker-compose exec postgres sh -c 'pg_dump -cU $POSTGRES_USER $POSTGRES_DB' | gzip > db_dump.sql.gz
  • To restore that database backup run gunzip -c db_dump.sql.gz | docker exec -i $(docker-compose ps -q postgres) sh -c 'psql -U $POSTGRES_USER $POSTGRES_DB'.

うーん、とりあえずこれでやるけどデータコンテナの移植とかはやってみたいな。kubernetesなら簡単にできるのかな。

移行先サーバの設定

環境

  • OS: ubuntu 16.04.4
  • docker: None

dockerを入れるところから始めなければいけないらしい。

dockerのインストール

これ。詳しくは書かない。

docs.docker.com

docker-composeもインストールする。これ。

docs.docker.com

# curl -L "https://github.com/docker/compose/releases/download/1.24.1/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
# chmod +x /usr/local/bin/docker-compose
# ln -s /usr/local/bin/docker-compose /usr/bin/docker-compose
# docker-compose --version

作業ディレクト

サーバは共有サーバというかラボネットワークのマネジメントサーバなんだけど、こういうときはどこにgit cloenとかすればいいんだろうか。ユーザ自体は全員分あるんだけど、自分のディレクトリに置くのはなんか違う。こういうときのベストプラクティスは…?

とりあえずたまたま*labというユーザがあったのでそこのホームディレクトリを使ってみる。

netbox-dockerのセットアップ

READMEに従う。

# docker-compose pull
# docker-compose up -d

データの移植

@host A

# docker-compose exec postgres sh -c 'pg_dump -cU $POSTGRES_USER $POSTGRES_DB' | gzip > db_dump.sql.gz

@local

$ scp host-a:~/netbox-docker/db_dump.sql.gz /tmp
$ scp /tmp/db_dump.sql.gz host-b:~/

@host B

# gunzip -c db_dump.sql.gz | docker exec -i $(docker-compose ps -q postgres) sh -c 'psql -U $POSTGRES_USER $POSTGRES_DB'

https://example.com:32768 に行ってみるとデータが追加されていない。おかしい。適当にvlanを手動で追加してみると以下のような画面が。

f:id:ninigishi:20190907122919p:plain

言われたとおりにmigrationしてみる。

# docker-compose exec netbox /bin/bash

bash-5.0# python3 manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, circuits, contenttypes, dcim, extras, ipam, secrets, sessions, taggit, tenancy, users, virtualization
Running migrations:
  Applying extras.0019_tag_taggeditem...Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
psycopg2.errors.DuplicateTable: relation "extras_tag" already exists


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.7/site-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.7/site-packages/django/core/management/__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.7/site-packages/django/core/management/base.py", line 323, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.7/site-packages/django/core/management/base.py", line 364, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python3.7/site-packages/django/core/management/base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/django/core/management/commands/migrate.py", line 234, in handle
    fake_initial=fake_initial,
  File "/usr/local/lib/python3.7/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.7/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.7/site-packages/django/db/migrations/executor.py", line 245, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/usr/local/lib/python3.7/site-packages/django/db/migrations/migration.py", line 124, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/usr/local/lib/python3.7/site-packages/django/db/migrations/operations/models.py", line 92, in database_forwards
    schema_editor.create_model(model)
  File "/usr/local/lib/python3.7/site-packages/django/db/backends/base/schema.py", line 307, in create_model
    self.execute(sql, params or None)
  File "/usr/local/lib/python3.7/site-packages/django/db/backends/base/schema.py", line 137, in execute
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.7/site-packages/cacheops/transaction.py", line 99, in execute
    result = self._no_monkey.execute(self, sql, params)
  File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.7/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
django.db.utils.ProgrammingError: relation "extras_tag" already exists

なんかうまくいかない。色々調べていくとこれで動くらしい。

# bash-5.0# python3 manage.py migrate --fake
Operations to perform:
  Apply all migrations: admin, auth, circuits, contenttypes, dcim, extras, ipam, secrets, sessions, taggit, tenancy, users, virtualization
Running migrations:
  Applying extras.0019_tag_taggeditem... FAKED
  Applying circuits.0015_custom_tag_models... FAKED
  Applying virtualization.0009_custom_tag_models... FAKED
  Applying tenancy.0006_custom_tag_models... FAKED
  Applying secrets.0006_custom_tag_models... FAKED
  Applying ipam.0025_custom_tag_models... FAKED
  Applying dcim.0070_custom_tag_models... FAKED
  Applying extras.0020_tag_data... FAKED
  Applying extras.0021_add_color_comments_changelog_to_tag... FAKED
  Applying dcim.0071_device_components_add_description... FAKED
  Applying dcim.0072_powerfeeds... FAKED
  Applying dcim.0073_interface_form_factor_to_type... FAKED
  Applying extras.0022_custom_links... FAKED
  Applying extras.0023_fix_tag_sequences... FAKED
  Applying extras.0024_scripts... FAKED
  Applying extras.0025_objectchange_time_index... FAKED
  Applying ipam.0026_prefix_ordering_vrf_nulls_first... FAKED
  Applying ipam.0027_ipaddress_add_dns_name... FAKED

上手く行った風だけど動かない…さっきと同じエラー画面が表示される。

netboxのバージョンが上がったから互換性ないっぽい?データベースが変更されてる?

github.com

とりあえず元のdockerイメージをアップデートしてから、同様の方法でデータを移行したらうまくいった。

Zabbix 4.0 導入 (Ubuntu 16.04)

やりたいこと

研究室ネットワークのSNMP監視がしたい。  

動機

現状ではcactiが動いているがUIがイケてないし、cactiの勉強をしてアップデートするよりは新しいのを入れて勉強していきたい。  

環境

OS: ubuntu 16.04.4 LTS MySQL: 5.7

とりあえず自分のサーバにいれて動かしてみてよさそうだったら監視サーバに移行する

事前環境設定

MySQLのインストール

Zabbix 4.0ではMySQL8.0をサポートしているらしい。

現環境には既に使用されているMySQL 5.7が入っている。アップデートするのは面倒だし、8.0が必須ではなさそうなので8.0はパス。

Zabbix 4.0の環境構築

公式ドキュメントに従う。 www.zabbix.com

2つの選択肢がある。

  • dockerで
  • serverにインストール

今回は勉強のためにもサーバにインストールすることにした。

dockerで入れるの簡単だけど、なんか味気ないしサーバにインストールしていく。次やる機会があったらdockerでやる。

実際に手を動かしてインストールした方が愛着がわく

リポジトリのインストール

$ cd /tmp
# wget https://repo.zabbix.com/zabbix/4.0/ubuntu/pool/main/z/zabbix-release/zabbix-release_4.0-3+xenial_all.deb
# dpkg -i zabbix-release_4.0-3+xenial_all.deb
# apt update

Zabbix 4.0のインストール

# apt -y install zabbix-server-mysql zabbix-frontend-php zabbix-agent

データベースの作成

Zabbixが使うデータベースの設定。 (当然)passwordは設定したいものに変更する。

$ mysql -u root -p
mysql> create database zabbix character set utf8 collate utf8_bin;
mysql> grant all privileges on zabbix.* to zabbix@localhost identified by 'password';
mysql> quit;

初期データの投入

zcat /usr/share/doc/zabbix-server-mysql*/create.sql.gz | mysql -uzabbix -p zabbix

zcatってなんだろう。そこそこ時間がかかる。

$ mysql -uroot -p
mysql> use zabbix;
mysql > show tables;
+----------------------------+
| Tables_in_zabbix           |
+----------------------------+
| acknowledges               |
| actions                    |
| alerts                     |
| application_discovery      |
| application_prototype      |
| application_template       |
| applications               |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| usrgrp                     |
| valuemaps                  |
| widget                     |
| widget_field               |
+----------------------------+
144 rows in set (0.00 sec)

なにやらいろんなデータが投入されている。

Zabbix -> MySQLのアクセス設定

をするためにパスワードの情報をいれる

sudo vim /etc/zabbix/zabbix_server.conf

124行目付近

### Option: DBPassword
#   Database password.
#   Comment this line if no password is used.
#
# Mandatory: no
# Default:
DBPassword= hogehoge

Zabbix のフロントエンド用のPHPの設定

タイムゾーンを設定する必要がある。

sudo vim /etc/zabbix/apache.conf

ドキュメントによると以下を変更して設定するらしい。

# php_value date.timezone Europe/Riga

20行目と30行目にそれぞれタイムゾーンの設定をする場所がある。入っているPHPのバージョンの部分だけを変えればいいっぽい。どっちも変えても何の問題もないだろうけど。

現環境にはPHP 7.3が入っていたので、 <IfModule mod_php7.c></IfModule> の方を変更

- # php_value date.timezone Europe/Riga
+ php_value date.timezone Asia/Tokyo

Rigaってどこだ

Zabbixの起動

# systemctl restart zabbix-server zabbix-agent apache2
# systemctl enable zabbix-server zabbix-agent apache2

Start Zabbix server and agent processes and make it start at system boot:

ちょっと関係ないけど systemctl enable ってそういう意味だったんだ。linuxのブート関連とかデーモンとかそこら辺全然わかんない。

Zabbixのフロントエンドの設定

http://server_ip_or_name/zabbix にアクセス。

グローバルIPDNSを自由に設定できる環境はこういうときには便利。

トップ画面はこう。[Next] を選択

f:id:ninigishi:20190906232309p:plain
Zabbixのトップ画面

なにやらエラーっぽいのが表示されている。何かが足りないらしい。

f:id:ninigishi:20190906232215p:plain
Zabbixフロントエンド設定

PHP bcmath extension missing (PHP configuration parameter --enable-bcmath).
PHP gd extension missing (PHP configuration parameter --with-gd).
PHP gd PNG image support missing.
PHP gd JPEG image support missing.
PHP gd FreeType support missing.
PHP xmlwriter extension missing.
PHP xmlreader extension missing.
sudo apt install php-bcmath
php-bcmath is already the newest version (2:7.3+69+ubuntu16.04.1+deb.sury.org+2+php7.3).

??? よく見るとZabbixで認識しているPHPのバージョンとターミナルで php --version としたときのバージョンが違う。これが原因っぽい。

php7.1をアンインストールしよう。

sudo apt purge php7.1-*
sudo systemctl restart apache2

......あ。起動しなくなった。ミスったかも /etc/zabbix/appache.conf も何故か消えた。。

うーん、今まで叩いたコマンドを振り返ると。 zabbix-frontend-php がそれっぽいのでこれをもう一回叩く。

sudo apt -y install zabbix-frontend-php

普通にインストールが始まったからpurgeしたときにこれも飛んでいったらしい。/etc/zabbix/apache.conf も復活した。

sudo vim /etc/zabbix/apache.conf
sudo systemctl restart apache2

なんかPHPソースコードが表示される。沼った。わけわからん。適当にググっていたら以下のサイトを見つけたので、その通りにやる。

gn000.sakura.ne.jp

ls /etc/apache2/mods-enabled/ | grep php

ない。リンクを貼る。

cd /etc/apache2/mods-enabled
sudo ln -s ../mods-available/php7.3.load ./
sudo ln -s ../mods-available/php7.3.conf ./

これで復旧した。よかった。後は適当に設定する。設定した画面を張っていく。

f:id:ninigishi:20190906235730p:plain

f:id:ninigishi:20190907000138p:plain

f:id:ninigishi:20190907000248p:plain

ログイン

f:id:ninigishi:20190907000354p:plain

www.zabbix.com 初期ユーザ・パスワードなどはここに書いてある。グローバル振ってるので早急にパスワードを変更する。

f:id:ninigishi:20190907000734p:plain [Administrations] >> [Users] >> [Admin] の順にクリック、次の画面で[Change Password]をクリックして変更。

とりあえず、ここまでが導入だと思うので一旦終了。snmpの設定などはまた今度。