No usable version of the libssl was found

[waghsk]

after installing it on python:2 docker container i get error.
I have openssl (1.1.1d-0+deb10u3) on the docker

[bpaterni]

Just writing to say "me too"

$ pipenv run mssql-scripter [OPTIONS] 
No usable version of the libssl was found
Scripting request: 1 encountered error: Scripting request encountered a exception
Error details: ('End of stream reached, no output.',)

This is running from a debian testing system (openssl 1.1.1g-1) with the following installed in the venv:

$ pipenv run pip list
Package        Version
-------------- --------
enum34         1.1.10
future         0.18.2
mssql-scripter 1.0.0a23
pip            20.1.1
setuptools     46.4.0
wheel          0.34.2

[stitetsati]

same

[duscheln]

Same here

[stitetsati]

ok i got it working... here's the dockerfile

FROM python:3.7-slim 
RUN echo "deb http://ftp.us.debian.org/debian/ jessie main" >>/etc/apt/sources.list
RUN apt-get update && apt-get install -y libicu63 libssl1.0.0 libffi-dev libunwind8 python3-dev && pip install --upgrade pip && pip install mssql-scripter

pls note that newer version of libssl won't work. seems to be only working with 1.0.0

[c4s4]

I would complete your Dockerfile with an entrypoint:

FROM python:3.7-slim 
RUN echo "deb http://ftp.us.debian.org/debian/ jessie main" >>/etc/apt/sources.list
RUN apt-get update && apt-get install -y libicu63 libssl1.0.0 libffi-dev libunwind8 python3-dev && pip install --upgrade pip && pip install mssql-scripter
ENTRYPOINT ["mssql-scripter"]

You should build the image with docker build -t mssql-scripter ., and you can run command with script mssql-scripter:

#!/bin/sh

set -e

docker run --rm -it mssql-scripter "$@"

[add1989]

$  mssql-scripter -S ServerName -d DatabaseName -U sa --include-objects ObjectName -f ./Result.sql
No usable version of the libssl was found
Scripting request: 1 encountered error: Scripting request encountered a exception
Error details: ('End of stream reached, no output.',)

I get this problem using any seemingly newer distro (which doesn't come with the older version of OpenSSL).

I managed to downgrade the system version of libssl (to 1.0.0) on a personal Arch VM and then mssql-scripter worked, but I wouldn't really want to do that on a real machine - plus I'm restricted to using WSL (so Ubuntu 20.04 with OpenSSL 1.1.1f 31 Mar 2020) at work and so can't do the same steps.

I have tried copying libssl.so.1.0.0 into my lib directory on Ubuntu/WSL and running ldconfig (wondering whether, if I have the lib, wouldjust mssql-scripter will use it and not the whole system), but I still get the same problem.

Is it an actual incompatibility or do we just need a new release which is aware of the newer SSL library?

UPDATE
The libssl1.0.0 package was removed from Ubuntu's repos in version 18, so if I add the xenial source to my sources.list and then run apt install libssl1.0.0 this makes mssql-scripter work. Would still be good to get a fix to the issue though.

[c4s4]

Why don't you use docker image? This would be much simpler than messing up you distro with old libssl. Nevertheless, in my own experience this tool doesn't work well to extract real world SQL schema, and fail miserably with an error and some random SQL statements.

[ianthetechie]

Boo! Seems everything from MS is completely dysfunctional on anything remotely modern. I can confirm the above fix (adding the jessie repo and installing the old version) works.

[jbzcooper]

Having same issue. Using dockerfile as prescribed above also didn't work. BUMP!

[brendanlong]

Adding the Jessie repo to the latest python:3.7-slim doesn't work anymore due to some sort of key signing issue (as in #236 (comment)), so I ended up downloading the .deb file from https://packages.debian.org/jessie/amd64/libssl1.0.0/download and then copying it into Docker:

FROM python:3.7-slim
ENV TERM dumb
RUN apt-get update && apt-get install -y libicu63 libffi-dev libunwind8 multiarch-support
COPY libssl1.0.0_1.0.1t-1+deb8u12_amd64.deb .
RUN dpkg -i libssl1.0.0_1.0.1t-1+deb8u12_amd64.deb
RUN pip install --upgrade pip && pip install mssql-scripter==1.0.0a23
ENTRYPOINT ["mssql-scripter"]

[ianthetechie]

Bump? Anyone from Microsoft actually using this? This is BROKEN and the library you forcibly reference is ancient.

[ianthetechie]

Update for anyone still reading this a year later: if you're dockerizing your usage of this, you can do the following. Note the python tag. This is two years old so this is unacceptable as a long-term hack, but should hold us over short term. Note that you have to install an ancient version of libicu as well since this is, well, oldoldstable in Debian land.

# Terrible hack required to get MS's tools to work.
FROM python:3.6-slim-jessie AS sqlgen

RUN apt-get update && apt-get install -y --no-install-recommends freetds-bin sed libunwind8 libicu52 libssl1.0.0
RUN pip install --upgrade pip
RUN pip install mssql-scripter

...

[rbrok]

Thanks for your help and suggestions.
I have created a container with Debian Stretch and libssl1.0.2
Libssl1.0.0 is very old and isn't updated since 2016. (libssl1.0.2 is out of support since January 2020)
PRs are welcome, if you like to improve my container or share something

[deltragon]

There is another workaround for this.
The problem is that mssql-scripter bundles a rather old version of https://github.com/microsoft/sqltoolsservice - however, the MSSQLTOOLSSERVICE_PATH environment variable can be used to override this and provide a newer version instead.
I managed to make this work on Ubuntu 21.04 with this version of sqltoolsservice - just download and extract the Microsoft.SqlTools.ServiceLayer-rhel-x64-netcoreapp3.1.tar.gz file and reference the extracted folder in MSSQLTOOLSSERVICE_PATH, eg:

MSSQLTOOLSSERVICE_PATH=~/path/to/extracted/sqltoolsservice-folder/ mssql-scripter -U sa -Pwhatever -d mydb --server=localhost --script-create -f ./out.sql

Note that for me, the -f out.sql was necessary - printing to stdout did not work in my case.

[rbrok]

There is another workaround for this.
The problem is that mssql-scripter bundles a rather old version of https://github.com/microsoft/sqltoolsservice - however, the MSSQLTOOLSSERVICE_PATH environment variable can be used to override this and provide a newer version instead.
I managed to make this work on Ubuntu 21.04 with this version of sqltoolsservice - just download and extract the Microsoft.SqlTools.ServiceLayer-rhel-x64-netcoreapp3.1.tar.gz file and reference the extracted folder in MSSQLTOOLSSERVICE_PATH, eg:

MSSQLTOOLSSERVICE_PATH=~/path/to/extracted/sqltoolsservice-folder/ mssql-scripter -U sa -Pwhatever -d mydb --server=localhost --script-create -f ./out.sql

Note that for me, the -f out.sql was necessary - printing to stdout did not work in my case.

I have tested that under Fedora 34 too. It is also possible to replace the folder sqltoolsservice in your python package folder.
I can't remember the exact path and name: ~/.local/lib/python3.9/site-packages/mssqlscripter/sqltoolsservice

The provided sqltoolsservice is using an old dotnetcore library with defaults to Open SSL 1.0.x
You can find here more information.

It should also be possible to package a private mssql-scripter version with a replaced tar.gz or zip files.
I couldn't find descriptions about versions and changes in sqltoolsservice, but the biggest difference seems to be the used netcore version.

[snippins]

There is another workaround for this. The problem is that mssql-scripter bundles a rather old version of https://github.com/microsoft/sqltoolsservice - however, the MSSQLTOOLSSERVICE_PATH environment variable can be used to override this and provide a newer version instead. I managed to make this work on Ubuntu 21.04 with this version of sqltoolsservice - just download and extract the Microsoft.SqlTools.ServiceLayer-rhel-x64-netcoreapp3.1.tar.gz file and reference the extracted folder in MSSQLTOOLSSERVICE_PATH, eg:

MSSQLTOOLSSERVICE_PATH=~/path/to/extracted/sqltoolsservice-folder/ mssql-scripter -U sa -Pwhatever -d mydb --server=localhost --script-create -f ./out.sql

Note that for me, the -f out.sql was necessary - printing to stdout did not work in my case.

For newcomers, who for some reasons couldn't use docker for this:

I'm able using this workaround with this version of sqltoolsservice, but had to do one more step and manually patch the main.py to use 'utf-16' after installation:

# with io.open(parameters.FilePath, encoding=u'utf-8') as script_file:
with io.open(parameters.FilePath, encoding=u'utf-16') as script_file:
    for line in script_file.readlines():
        sys.stdout.write(line)

[bowmanjd]

@snippins that is just brilliant, and works wonderfully.

Any ideas on what changed (in sqltoolsservice, presumably) to make it necessary for the character encoding to be UTF-16?

Do you think a PR would be welcome? It would require updating sqltoolsservice, and testing to ensure the it still works on a variety of Linux distros, as well as Win and Mac

[deltragon]

@bowmanjd I would strongly assume that this was a side effect from updating from Python 2 to 3 where strings went from just being bytes to requiring valid UTF-8. I assume a PR fixing this would make sense, it seems this project has gained some activity again.

[davisriska]

There is another workaround for this. The problem is that mssql-scripter bundles a rather old version of https://github.com/microsoft/sqltoolsservice - however, the MSSQLTOOLSSERVICE_PATH environment variable can be used to override this and provide a newer version instead. I managed to make this work on Ubuntu 21.04 with this version of sqltoolsservice - just download and extract the Microsoft.SqlTools.ServiceLayer-rhel-x64-netcoreapp3.1.tar.gz file and reference the extracted folder in MSSQLTOOLSSERVICE_PATH, eg:

MSSQLTOOLSSERVICE_PATH=~/path/to/extracted/sqltoolsservice-folder/ mssql-scripter -U sa -Pwhatever -d mydb --server=localhost --script-create -f ./out.sql

Note that for me, the -f out.sql was necessary - printing to stdout did not work in my case.

For newcomers, who for some reasons couldn't use docker for this:

I'm able using this workaround with this version of sqltoolsservice, but had to do one more step and manually patch the main.py to use 'utf-16' after installation:

# with io.open(parameters.FilePath, encoding=u'utf-8') as script_file:
with io.open(parameters.FilePath, encoding=u'utf-16') as script_file:
    for line in script_file.readlines():
        sys.stdout.write(line)

Can you give some instructions on how did you do it? Do I have to pull the repo, patch it and build it or can i just patch an already installed version(pip install mssql-scripter)? If i can patch an already installed version, where do the files usually go?

[deltragon]

@davisriska Note that I was able to sidestep the encoding issue without any patch, just using the -f out.sql flag to avoid the broken codepath.
With this, an unpatched pip installation of mssql-scripter works for me (with the MSSQLTOOLSSERVICE_PATH environment variable of course).

[davisriska]

@davisriska Note that I was able to sidestep the encoding issue without any patch, just using the -f out.sql flag to avoid the broken codepath. With this, an unpatched pip installation of mssql-scripter works for me (with the MSSQLTOOLSSERVICE_PATH environment variable of course).

Ahh, I wish I read this before I figured out how to patch it in inside a docker image I was building. For anyone having trouble here is what I did to make it work.

FROM ubuntu:21.04

# .... everything else you want to do and pip install mssql-scripter

ARG sqltools=https://github.com/microsoft/sqltoolsservice/releases/download/v3.0.0-release.205/Microsoft.SqlTools.ServiceLayer-rhel-x64-net6.0.tar.gz

RUN curl -SL $sqltools -o Microsoft.SqlTools.ServiceLayer.tar.gz \
    && mkdir Microsoft.SqlTools.ServiceLayer \
    && tar -xzf Microsoft.SqlTools.ServiceLayer.tar.gz -C Microsoft.SqlTools.ServiceLayer \
    && mv Microsoft.SqlTools.ServiceLayer /opt/Microsoft.SqlTools.ServiceLayer \
    && sed -i 's/utf-8/utf-16/g' /usr/local/lib/python3.9/dist-packages/mssqlscripter/main.py

ENV MSSQLTOOLSSERVICE_PATH=/opt/Microsoft.SqlTools.ServiceLayer

[shanike]

@davisriska Note that I was able to sidestep the encoding issue without any patch, just using the -f out.sql flag to avoid the broken codepath. With this, an unpatched pip installation of mssql-scripter works for me (with the MSSQLTOOLSSERVICE_PATH environment variable of course).

How would I write queries with this?
The out.sql file is being written with ALTER commands and more, but how can I write SELECT scripts into the mssql db?

[bowmanjd]

How would I write queries with this?

@shanike can you help us understand how your question pertains to the libssl and sqltoolsservice issue described here? Are you having similar issues?

[shanike]

How would I write queries with this?

@shanike can you help us understand how your question pertains to the libssl and sqltoolsservice issue described here? Are you having similar issues?

Hi yes I had the same issue as described, (no docker) and when using the env variable and --script-create -f ./out.sql fixed the error. But my goal was to get a mssql command line for running queries in db.

[shanike]

How would I write queries with this?

@shanike can you help us understand how your question pertains to the libssl and sqltoolsservice issue described here? Are you having similar issues?

Hi yes I had the same issue as described, (no docker) and when using the env variable and --script-create -f ./out.sql fixed the error. But my goal was to get a mssql command line for running queries in db.

[alexeyvino]

@davisriska Note that I was able to sidestep the encoding issue without any patch, just using the -f out.sql flag to avoid the broken codepath. With this, an unpatched pip installation of mssql-scripter works for me (with the MSSQLTOOLSSERVICE_PATH environment variable of course).

Ahh, I wish I read this before I figured out how to patch it in inside a docker image I was building. For anyone having trouble here is what I did to make it work.

FROM ubuntu:21.04

# .... everything else you want to do and pip install mssql-scripter

ARG sqltools=https://github.com/microsoft/sqltoolsservice/releases/download/v3.0.0-release.205/Microsoft.SqlTools.ServiceLayer-rhel-x64-net6.0.tar.gz

RUN curl -SL $sqltools -o Microsoft.SqlTools.ServiceLayer.tar.gz \
    && mkdir Microsoft.SqlTools.ServiceLayer \
    && tar -xzf Microsoft.SqlTools.ServiceLayer.tar.gz -C Microsoft.SqlTools.ServiceLayer \
    && mv Microsoft.SqlTools.ServiceLayer /opt/Microsoft.SqlTools.ServiceLayer \
    && sed -i 's/utf-8/utf-16/g' /usr/local/lib/python3.9/dist-packages/mssqlscripter/main.py

ENV MSSQLTOOLSSERVICE_PATH=/opt/Microsoft.SqlTools.ServiceLayer

August 2023 version ;-)

FROM ubuntu:22.04

# .... everything else you want to do and pip install mssql-scripter
RUN  apt-get update && apt-get install -y python3-pip libicu70 \
  && pip3 install mssql-scripter \
  && ln -s /usr/bin/python3 /usr/bin/python


ARG sqltools=https://github.com/microsoft/sqltoolsservice/releases/download/v3.0.0-release.205/Microsoft.SqlTools.ServiceLayer-rhel-x64-net6.0.tar.gz

RUN apt-get update && apt-get install -y curl

RUN curl -SL $sqltools -o Microsoft.SqlTools.ServiceLayer.tar.gz \
    && mkdir Microsoft.SqlTools.ServiceLayer \
    && tar -xzf Microsoft.SqlTools.ServiceLayer.tar.gz -C Microsoft.SqlTools.ServiceLayer \
    && mv Microsoft.SqlTools.ServiceLayer /opt/Microsoft.SqlTools.ServiceLayer \
    && sed -i 's/utf-8/utf-16/g' /usr/local/lib/python3.10/dist-packages/mssqlscripter/main.py

ENV MSSQLTOOLSSERVICE_PATH=/opt/Microsoft.SqlTools.ServiceLayer

[Khuram-Wani]

There is another workaround for this. The problem is that mssql-scripter bundles a rather old version of https://github.com/microsoft/sqltoolsservice - however, the MSSQLTOOLSSERVICE_PATH environment variable can be used to override this and provide a newer version instead. I managed to make this work on Ubuntu 21.04 with this version of sqltoolsservice - just download and extract the Microsoft.SqlTools.ServiceLayer-rhel-x64-netcoreapp3.1.tar.gz file and reference the extracted folder in MSSQLTOOLSSERVICE_PATH, eg:

MSSQLTOOLSSERVICE_PATH=~/path/to/extracted/sqltoolsservice-folder/ mssql-scripter -U sa -Pwhatever -d mydb --server=localhost --script-create -f ./out.sql

Note that for me, the -f out.sql was necessary - printing to stdout did not work in my case.

thanks man, it soled the problem.