AdminSysGNULinux

sudo yum install java-1.8.0-openjdk-headless

Téléchargement de sqlline \ Nous avons deux fichiers :

• jline-1.0.jar
• sqlline-1_0_2.jar

Téléchargement du driver JDBC \ Nous avons besoin que d'un fichier :

• mssql-jdbc-12.2.0.jre8.jar (pour Java 1.8)
• ou mssql-jdbc-12.2.0.jre11.jar (pour Java 1.11)

java -Djava.ext.dirs=. -jar sqlline-1_0_2.jar

Au prompt de sqline taper :

!connect jdbc:sqlserver://serveur_base_de_données:1433;databaseName=leNomDeMaBase;

Nous pouvons passer des options au driver JDBC de la base comme dans notre exemple : \ ;databaseName=leNomDeMaBase;

L'option integratedSecurity=true; semble propre à windows

Après avoir tapé le nom et mdp de connexion nous avons l'erreur :

Error: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "No negotiable cipher suite". ClientConnectionId:c83221ff-76a4-4e9a-8206-092b76475256 (state=08S01,code=0)

Pour déboguer nous faisons :

java -Djava.ext.dirs=. -Djavax.net.debug=all -jar sqlline-1_0_2.jar

Mais comme c'est un pb de “cipher suite” nous prenons une version plus récente de Java

sudo yum remove java-1.8.0-openjdk-headless
sudo yum install java-11-openjdk-headless

Il ne faut pas oublier non plus de changer le driver JDBC pour mssql-jdbc-12.2.0.jre11.jar

La syntax change :

java -cp ./sqlline-1_0_2.jar:./jline-1.0.jar:./mssql-jdbc-12.2.0.jre11.jar sqlline.SqlLine

-Djava.ext.dirs= a été remplacé par l'équivalent -cp= ou --class-path= qui permet de changer le ClassPath, mais ça ne semple fonctionner que pour les fichiers et non plus pour les répertoires. \ Voir : https://skywalking.apache.org/docs/skywalking-java/v8.9.0/en/faq/ext-dirs/

A présent voici l'erreur :

Error: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target". ClientConnectionId:88db8777-73f0-4bd7-b75d-c45d79d91bff (state=08S01,code=0)

;trustServerCertificate=true; n'est pas pris en charge par sqlline \ ;Trusted_Connection=yes; ne semble avoir aucun effet.

L'option qui nous intéresse est : \ ;trustServerCertificate=true;

!connect jdbc:sqlserver://127.0.0.1:1433;databaseName=dbname;trustServerCertificate=true;

$ java -cp ./sqlline-1_0_2.jar:./jline-1.0.jar:./mssql-jdbc-12.2.0.jre11.jar sqlline.SqlLine
sqlline version 1.0.2 by Marc Prud'hommeaux
sqlline> !connect jdbc:sqlserver://127.0.0.1:1433;databaseName=mirror;trustServerCertificate=true;authenticationScheme=NTLM;integratedSecurity=true;Domain=ACME;
Connecting to jdbc:sqlserver://127.0.0.1:1433;databaseName=mirror;trustServerCertificate=true;authenticationScheme=NTLM;integratedSecurity=true;Domain=ACME;
Enter username for jdbc:sqlserver://127.0.0.1:1433;databaseName=mirror;trustServerCertificate=true;authenticationScheme=NTLM;integratedSecurity=true;Domain=ACME;: USER1
Enter password for jdbc:sqlserver://127.0.0.1:1433;databaseName=mirror;trustServerCertificate=true;authenticationScheme=NTLM;integratedSecurity=true;Domain=ACME;: *************************
Connected to: Microsoft SQL Server (version 13.00.5882)
Driver: Microsoft JDBC Driver 12.2 for SQL Server (version 12.2.0.0)
Autocommit status: true
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:sqlserver://127.0.0.1:1433>

Voir :

• https://pymssql.readthedocs.io/en/stable/faq.html
• nmap ms-sql-ntlm-info

Comme ça ne marche pas en JDBC on essaie en ODBC

sudo rpm -Uvh msodbcsql18-18.2.2.1-1.x86_64.rpm unixODBC-2.3.11-1.rh.x86_64.rpm

Essayons d'abord avec sqlcmd le CLI officiel (qui utilise ODBC)

sudo rpm -Uvh mssql-tools18-18.2.1.1-1.x86_64.rpm

$ #/opt/mssql-tools18/bin/sqlcmd -S 127.0.0.1,1433 -U MonUser -P pwd -Q 'SELECT name FROM sys.databases;'
$ /opt/mssql-tools18/bin/sqlcmd -S 127.0.0.1,1433 -U MonUser@acme.local -d dbname -C
Password:
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Login failed for user 'MonUser@acme.local'..

Ça ne marche pas. Passons en pur ODBC

cat /etc/odbcinst.ini

/etc/odbcinst.ini

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.2.so.2.1
UsageCount=1

~/.odbc.ini

[mssql_test]
Driver = ODBC Driver 18 for SQL Server
Server = tcp:127.0.0.1,1433
Encrypt = yes
TrustServerCertificate = yes

Le TrustServerCertificate = yes est bien pris en compte

$ iusql -v mssql_test mon_user 'Passw0rd'
[28000][unixODBC][Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user 'mon_user'.
[ISQL]ERROR: Could not SQLConnect

Voir aussi :

• Notes Python DB - ORM - DAL - SQLAlchemy

import pyodbc
conn = pyodbc.connect("Driver={driver};Server=tcp:{serverName},{port};Database={masterDB};Uid={rootUser}@{serverName};Pwd={rootPass};TrustServerCertificate=yes;integratedSecurity=true;Domain=ACME;".format(serverName='127.0.0.1', masterDB='dbname', rootUser=r'user1', rootPass=r'Passw0rd!', driver="ODBC Driver 18 for SQL Server", port='1433'), autocommit=False)

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.InterfaceError: ('28000', "[28000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user 'user1@127.0.0.1'. (18456) (SQLDriverConnect)")

Le TrustServerCertificate=yes est bien pris en compte mais ça ne marche pas. Le plus simple est d'utiliser FreeTDS puis de faire un connecteur ODBC avec le driver de FreeTDS.

La solution consiste à utiliser DOMAIN\user1 à la place de user1@domain.local

$ tsql -v -S localhost:1433 -U user1@acme.local -P 'Passw0rd!'
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Msg 18456 (severity 14, state 1) from SRVWINSQL1\PLOP Line 1:
        "Login failed for user 'user1@acme.local'."
Error 20002 (severity 9):
        Adaptive Server connection failed
Error 20002 (severity 9):
        Adaptive Server connection failed
There was a problem connecting to the server

$ tsql -v -S localhost:1433 -U 'ACME\user1' -P 'Passw0rd!'
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Changed database context to 'master'.
Changed language setting to us_english.
1>
1> SELECT 1;
2> GO
1> SELECT name FROM sys.databases;
2> GO

Il peut également être nécessaire d'ajouter use ntlmv2 = yes dans ~/.freetds.conf

~/.odbcinst.ini

[FreeTDS]
Description=FreeTDS Driver for Linux & MSSQL
Driver=/usr/lib64/libtdsodbc.so
UsageCount=1

~/.odbc.ini

[PlopODBC]
Description         = Test to SQLServer
Driver              = FreeTDS
Servername          = PlopTDS

iusql -d, -b PlopODBC 'ACME\user1' 'Passw0rd!' <<< "SELECT @@version;"
iusql -d, -b PlopODBC 'ACME\user1' 'Passw0rd!' <<< "SELECT db_name();"
iusql -d, -b PlopODBC 'ACME\user1' 'Passw0rd!' <<< "SELECT name FROM sys.databases;"

Pour diag

$ osql -S PlopODBC -U 'ACME\user1' -P 'Passw0rd!'
checking shared odbc libraries linked to isql for default directories...
strings: Warning: '/usr/lib64' is a directory
        trying /tmp/sql ... no
        trying /tmp/sql ... no
        trying /etc ... OK
checking odbc.ini files
        reading /home/jean/.odbc.ini
[PlopODBC] found in /home/jean/.odbc.ini
found this section:
        [PlopODBC]
        Description         = Test to SQLServer
        Driver              = FreeTDS
        Servername          = PlopTDS
looking for driver for DSN [PlopODBC] in /home/jean/.odbc.ini
  found driver line: "  Driver              = FreeTDS"
  driver "FreeTDS" found for [PlopODBC] in .odbc.ini
found driver named "FreeTDS"
"FreeTDS" is not an executable file
looking for entry named [FreeTDS] in /etc/odbcinst.ini

#!/usr/bin/env python3
import pyodbc
conn = pyodbc.connect(DRIVER='FreeTDS', Server='127.0.0.1', Port='1433', Database='dbname', UID=r'ACME\user1', PWD=r'Passw0rd', TDS_Version='7.1', UseNTLMv2='Yes')
 
cursor = conn.cursor()
row = cursor.execute('SELECT @@version;')
for row in cursor:
    print(row[0])

Voir : urllib.parse

ping-mssql.yml

#!/usr/bin/ansible-playbook
---

- name: test connection MsSQL
  hosts: localhost

  tasks:
    - name: set_facts SQL Cred
      set_fact:
        mssql_login_user: "{{ lookup('env', 'MSSQL_LOGIN_USER') }}"
        mssql_login_password: "{{ lookup('env', 'MSSQL_LOGIN_PASSWORD') }}"
        mssql_host: "{{ lookup('env', 'MSSQL_LOGIN_HOST') }}"
        mssql_port: "{{ lookup('env', 'MSSQL_LOGIN_PORT') |int }}"
        mssql_database: "{{ lookup('env', 'MSSQL_LOGIN_DATABASE') }}"

    - name: Check DB connection
      community.general.mssql_script:
        login_user: "{{ mssql_login_user }}"
        login_password: "{{ mssql_login_password }}"
        login_host: "{{ mssql_host }}"
        login_port: "{{ mssql_port }}"
        db: "{{ mssql_database }}"
        script: "SELECT name FROM sys.databases;"
      register: sql_result

    - name: show result query
      debug:
        var: sql_result.query_results

cp /etc/freetds.conf ~/.freetds.conf
vim ~/.freetds.conf

~/.freetds.conf

[global]
tds version = auto
dump file = /tmp/freetds.log
 
[PlopTDS]
host = localhost
port = 1433
use ntlmv2 = yes
database = dbname

tsql -v -S PlopTDS -U 'ACME\user1' -P 'Passw0rd!'

Voici le pb initiale

$ python3
Python 3.9.13 (main, Nov  9 2022, 13:16:24)
[GCC 8.5.0 20210514 (Red Hat 8.5.0-15)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pymssql
>>> conn = pymssql.connect(server='mssql01.acme.local', port='1433', user='USER01@acme.local', password='******', database='dbname')
Traceback (most recent call last):
  File "src/pymssql/_pymssql.pyx", line 647, in pymssql._pymssql.connect
  File "src/pymssql/_mssql.pyx", line 2109, in pymssql._mssql.connect
  File "src/pymssql/_mssql.pyx", line 701, in pymssql._mssql.MSSQLConnection.__init__
  File "src/pymssql/_mssql.pyx", line 1818, in pymssql._mssql.maybe_raise_MSSQLDatabaseException
  File "src/pymssql/_mssql.pyx", line 1835, in pymssql._mssql.raise_MSSQLDatabaseException
pymssql._mssql.MSSQLDatabaseException: (18456, b"Login failed for user 'USER01@acme.local'.DB-Lib error message 20018, severity 14:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (mssql01.acme.local)\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (mssql01.acme.local)\n")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "src/pymssql/_pymssql.pyx", line 653, in pymssql._pymssql.connect
pymssql._pymssql.OperationalError: (18456, b"Login failed for user 'USER01@acme.local'.DB-Lib error message 20018, severity 14:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (mssql01.acme.local)\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (mssql01.acme.local)\n")

pymssql utilise FreeTDS Si FreeTDS ne fonctionne pas il est inutile d'aller plus loin.

Voici un petit bout de code qui fonctionne

#!/usr/bin/env python3
import pymssql
conn = pymssql.connect(server='localhost', port='1433', user=r'ACME\user1', password=r'Passw0rd!', database='mirror')
cursor = conn.cursor()
cursor.execute('SELECT name FROM sys.databases;')
for row in cursor:
    print(row)
conn.close()

Voir : urllib.parse

Voir aussi :

• Notes Python DB - ORM - DAL - SQLAlchemy

Ou encore avec pyDAL (FreeTDS)

#!/usr/bin/env python3
from pydal import DAL, Field
 
db_user = 'ACME\user1'
db_pass = 'Passw0rd!'
db_name = 'dbname'
db_host = '127.0.0.1'
db_port = 1433
db = DAL(f'mssql4://{db_user}:{db_pass}@{db_host}:{db_port}/{db_name}', driver_args={'DRIVER': 'FreeTDS', 'TDS_Version': '7.3'})
 
result = db.executesql('SELECT @@version;')
for row in result:
    print(row[0])
db.close()