Ошибка odbc driver 17
I had upgrade my Ubuntu 19.10 to 20.04 Beta and caught this exception when trying to connect to MSSQL server. Previously it worked in Ubuntu 19.10.
SQL Server : 12.0.4100.1
PHP : 7.1.33
OS: Ubuntu 20.04 Beta
Anybody can help me, I'm really appreciate it. Thank You
The text was updated successfully, but these errors were encountered:
Razorhunter commented Apr 4, 2020
@yitam For the time being, we only got production SQL Server 2014 and we didn't plan to upgrade to the newest version. There are no other workaround for it for the time being?
yitam commented Apr 6, 2020
yitam commented Apr 6, 2020 •
I just did a brief test. I have no problem connecting to SQL Server 2017 or above from Ubuntu 20.04.
For further explanation, please check our reply to a similar issue.
But if upgrading your server is not an option for you now, this suggestion for Debian might help you.
Save a backup /etc/ssl/openssl.cnf first and then modify the file by following the steps below (disclaimer: doing this may compromise the security):
- Add openssl_conf = default_conf near the top
- Add the following at the end:
But if your SQL Server does not yet support TLSv1.2, it's highly recommended that you follow the instructions to update. Again, if you do not plan to upgrade yet, you may change the line MinProtocol = TLSv1.2 to TLSv1.0 (it's your choice but not recommended)
yitam commented Apr 20, 2020
Razorhunter commented Apr 21, 2020
@yitam Just want you to know, I did successfully connect to the MSSQL 2017 with Ubunt 20.04. Seems like you previous suggestion using MSSQL 2017 and above is a correct way for now. I had move my staging server to new VM and installed MSSQL2017 for now. The production server remains using MSSQL 2014. Tq for your help
yitam commented Apr 21, 2020
You're welcome @Razorhunter
Closing this issue now, and please feel free to reopen if necessary
ludufre commented Jun 1, 2020
I have this problem with an outdated SQL Server connecting from Oracle Linux 8.2.
The location to change MinProtocol : /etc/crypto-policies/back-ends/opensslcnf.config
esundberg commented Jun 12, 2020 •
This was helpful.
Ubuntu 20.04 PHP 7.4 using the 19.10 drivers for sqlsrv
We have a real old SQL server out there . The SQL Server show's version 10.50.2550.0, i think it's SQL Server 2008 R2. I had to use TLSv1 to connect to the server. I also had to do a "systemctl restart apache2" to get it to take affect. TLSv1.1 did not work with my MSSQL server version.
Error message: Connection failed: SQLSTATE[08001]: [Microsoft][ODBC Driver 17 for SQL Server]SSL Provider: [error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol]Database Connection Error
edit: /etc/ssl/openssl.cnf
1st line in the file added
openssl_conf = default_conf
End of file added
Not 100% sure why i had to restart apache2 for it to take effect, but I had to.
reloaded the page and it works
Razorhunter commented Jun 13, 2020
@esundberg thank you. i tried your solution and IT WORKS. . thanks a lot. very appreciate it
satarasov commented Jun 16, 2020
@esundberg thanks a lot, saved my day!
githubjeka commented Jul 28, 2020
If you use docker:
daveschafer commented Aug 13, 2020
This almost worked perfectly for my use case (docker on Ubuntu 2004 connecting to SQL Server 2014).
But I had to replace the Line 17 with this to get it working:
githubjeka commented Aug 13, 2020
MaguiTai commented Nov 6, 2020
himalayantechies commented Jan 20, 2021
This was helpful.
Ubuntu 20.04 PHP 7.4 using the 19.10 drivers for sqlsrv
We have a real old SQL server out there . The SQL Server show's version 10.50.2550.0, i think it's SQL Server 2008 R2. I had to use TLSv1 to connect to the server. I also had to do a "systemctl restart apache2" to get it to take affect. TLSv1.1 did not work with my MSSQL server version.
Error message: Connection failed: SQLSTATE[08001]: [Microsoft][ODBC Driver 17 for SQL Server]SSL Provider: [error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol]Database Connection Error
edit: /etc/ssl/openssl.cnf
1st line in the file added
openssl_conf = default_confEnd of file added
Not 100% sure why i had to restart apache2 for it to take effect, but I had to.
reloaded the page and it works
Hi,
I did that , but still I am getting the same error.
Server : Ubuntu 20.04.1 LTS
SQL Server: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)
Changed openssl.cnf
Apache2 restarted
Didnt work,
Changed MinProtocol = TLSv1.2 to TLSv1.0
Didnt work
himalayantechies commented Jan 20, 2021
FYI, @Razorhunter
I just did a brief test. I have no problem connecting to SQL Server 2017 or above from Ubuntu 20.04.
For further explanation, please check our reply to a similar issue.
But if upgrading your server is not an option for you now, this suggestion for Debian might help you.
Save a backup /etc/ssl/openssl.cnf first and then modify the file by following the steps below (disclaimer: doing this may compromise the security):
But if your SQL Server does not yet support TLSv1.2, it's highly recommended that you follow the instructions to update. Again, if you do not plan to upgrade yet, you may change the line MinProtocol = TLSv1.2 to TLSv1.0 (it's your choice but not recommended)
Hi,
I did that , but still I am getting the same error.
Server : Ubuntu 20.04.1 LTS
SQL Server: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)
Changed openssl.cnf
Apache2 restarted
Didnt work,
Changed MinProtocol = TLSv1.2 to TLSv1.0
Didnt work
Добрый день.
Пару дней назад перестало работать подключение к Microsoft Server на Linux.
С помощью isql и tsql подключается, а с sql
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 0x2746.
Подскажите пожалуйста, как решить проблему.
Ответы
Все было уже сделано, просто не понимаю, что могло поменяться за пару дней на сервере, чтобы подключение перестало работать.
Некоторые пользователи предполагают, что это могло быть после обновление пакетов (probably after some apt-get upgrade).
Avis de non-responsabilité:
Mon opinion ne peut pas coïncider avec la position officielle de Microsoft.
Bien cordialement, Andrei .
MCP
- Помечено в качестве ответа Иван Проданов Microsoft contingent staff, Moderator 19 августа 2019 г. 8:52
Все ответы
Уточните пожалуйста с каким дистрибутивом линукса возникает проблема?
P.S. Некоторые пользователи сообщают также о похожих проблемах посмотрите если у Вас схожий случай - SQL Server 2019 on Ubuntu 18.04
Avis de non-responsabilité:
Mon opinion ne peut pas coïncider avec la position officielle de Microsoft.
Bien cordialement, Andrei .
MCP
У меня два сервера и на обоих одинаковая проблема. Ubuntu 16.04 и Ubuntu 18.04. Второй день не могу понять в чем дело, буквально недавно все работало, не знаю что могло измениться.
Есть совет поставить предыдущую версию:
Все было уже сделано, просто не понимаю, что могло поменяться за пару дней на сервере, чтобы подключение перестало работать.
Наиболее распространенная проблема подключения связана с наличием двух установленных копий диспетчера драйверов UnixODBC. Поищите libodbc*.so*в /usr. Если отображается более одной версии файла, (возможно) установлено несколько диспетчеров драйверов.
libodbcinst.la
libodbcinst.so -> libodbcinst.so.2.0.0
libodbcinst.so.1 -> libodbcinst.so.2.0.0
libodbcinst.so.2 -> libodbcinst.so.2.0.0
libodbcinst.so.2.0.0
libodbc.la
libodbc.so -> libodbc.so.2.0.0
libodbc.so.1 -> libodbc.so.2
libodbc.so.2 -> libodbc.so.2.0.0
libodbc.so.2.0.0
[mssql]
Description=FreeTDS
Driver=FreeTDS
Servername=mssql
UID=sa
PWD=pppp
Port=1433
[MySQL]
Description=MySQL driver for Plesk
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc5w-plesk.so
Setup=
FileUsage=1
Driver64=
Setup64=
UsageCount=1
[FreeTDS]
Description=TDS driver (Sybase/MS SQL)
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup=
CPTimeout=
CPReuse=
[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2
UsageCount=1
[ODBC Driver 17 for SQL Server]
Server = tcp:81.169.186.154,1433
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.1.1
UsageCount=1
[ODBC]
Trace = Yes
TraceFile = /opt/odbc.log
Error: 17113, Severity: 16, State: 1.
Error 5(Access is denied.) occurred while opening file '/var/opt/mssql/data/master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.
I have installed Sql Server 2019 Developer Edition and mssql-tools on my Ubuntu 20.04 minimal. I can connect to my localhost with no issue, but when I want to remote to another sql server:
I face this error:
I has this issue once in Debian 10, and also search the net for solution, so after that I change my openssl.conf manually (su permission needed):
and add these to my file:
ess_cert_id_alg = sha1 under the [tsa_config1] heading
openssl_conf = default_conf near the top
the following at the end:
I know that MinProtocol and CipherString are normally set to TLSv1.2 and DEFAULT@SECLEVEL=2 , but as I mentioned once in my Debian 10, I edited my openssl.conf and change TLSv1.2 to TLSv1.0 and DEFAULT@SECLEVEL=2 to DEFAULT@SECLEVEL=1 and my connection fixed, but in Ubuntu 20.04 minimal not only there wasn't these lines, but also when I insert these manually again I face the same error:
my opnessl version is:
I also downgrade my openssl once to 1.0 but it didn't work either!
I couldn't find anything else so I came here to ask for help, appreciate your help.
this just led me in the right direction, as it also works the other way round. Having an older Win Machine which does not support TLS1.2 which is provided by openssl 1.1.1d in Debian buster. Downgrading to TLSv1.0 works like a charm.
1 Answer 1
The reason might be that your current openssl doesn't support / turned off some ciphers (supported by your previous installation) and the server requires them. Just compare output:
Solution: try to install a new version of openssl (>1.1.1f) manually. I upgraded from 1.1.1f to 1.1.1h and it solved my problems, no extra configuration required. I also read similar cases with 18.04 -> 20.04 and 1.1.1f which affected other guys.
The manual installation looks like:
Note: You can also avoid the first line above and manually download the source tar.gz by going to their download page.
Hope, this will help
Note if after the download and tar un-compress the directory is something like openssl-3.0.0 then change to that directory instead, run the steps above, but if openssl version fails with an error
I am unable to connect with SQL server 2008 R2 from ubuntu 18.04 and PHP 7.2 / 7.3 latest 17.2 driver.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
With ubuntu 16.04, same SQL server I am able to connect. I am assuming is something with driver.
Anyone with same problem?
The text was updated successfully, but these errors were encountered:
crisleiria commented Feb 4, 2019
I am using 17.2 version, I will get you a trace log as soon as possible.
But in teory it is possible to connect to SQL Server 2008 R2 with 17.2 driver correct?
yitam commented Feb 4, 2019
crisleiria commented Feb 5, 2019
Attached is the log file from my php application, we are using Laravel Framework.
I have tried sql profile trace, but it is useless, no requests reach to SQL server.
I have 100% sure that username and password is OK, we have the same source code running from ubuntu 16.04 and not problems on that envirement (driver version is 13).
yitam commented Feb 5, 2019
The provided link to the comment does not work, but I did skim through the comments on their issue 610.
Please enable ODBC tracing in Ubuntu 18.04 and run sqlcmd again. That is the odbc trace log I was asking for.
Can you ping the server? Login timeout may mean that you can't communicate with the server.
You might also want to try isql , like this:
isql -v -k 'Driver=ODBC Driver 17 for SQL Server;Server=;UID=;PWD='
Do make sure you escape special characters in the password. Yet, since you could connect from your Ubuntu 16.04 (assuming with the same credentials), I have ruled out this possibility.
crisleiria commented Feb 6, 2019
from isql comand I have this error:
[S1T00][unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired [08001][unixODBC][Microsoft][ODBC Driver 17 for SQL Server]MAX_PROVS: Error Locating Server/Instance Specified [xFFFFFFFF]. [08001][unixODBC][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. [ISQL]ERROR: Could not SQLDriverConnect
I have enable log trace, attached is the result from php website and from isql comand.
odbctrace_isql.txt
odbctrace.txt
I can ping sql server ip address with no problems, but it is a named instance (192.168.1.2\ABC2012). I do not have any problem on ubuntu 16.04, I dont thing this is the problem..
Thanks for the help.
yitam commented Feb 6, 2019
@crisleiria for a named instance you will need to set it up, as explained in one of the questions in FAQ
This is the documentation for your reference.
Hope this helps.
crisleiria commented Feb 6, 2019
Thanks for the info, it is strange for me to setup named odbc on ubuntu 18.04.. I have nothing configured on 16.04 and I am able to connect. the only diference is the driver version.
Anyway I have setup DNS as instructions and still same error.
Honestly I do not know what do to anymore.
This is a Vagrant virtual box machine.. maybe something on host machine?
yitam commented Feb 6, 2019
crisleiria commented Feb 6, 2019
I figured out. nothing related with ubuntu environment.
I review SQL server settings,
TCP dynamic ports where enable, I have placed correct dinamic port assign and connected successfully. I will disable dynamic ports on SQL Server (I only have one instance running).
I dont need to setup DNS on ubuntu, if set tcp port on connection string.
Other conclusion on this:
Ubuntu 18.04 is a virtual machine with a network bridge connection to the host.
Ubuntu 16.04 is a real server on the same network of SQL Server.
From Ubuntu 18.04 I am only able to connect from dynamic port assign.
From Ubuntu 16.04 I am able to connect without setting up port number.
I am start using Docker for PHP development, and I want to connect to SQL Server from Laravel Project. I am not able to setup SQL Server driver on PHP-FPM container.
on SQL query I am getting this error:
SQLSTATE[08001]: [Microsoft][ODBC Driver 17 for SQL Server]SSL Provider: [error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol] (SQL: select .
Please take in account I am trying to connect to an SQL Server 2008 R2 and upgrade server is not an option.. at least for now.
Expected behavior and actual behavior
Connection to SQL Server successfully
Repro code or steps to reproduce
this is the DockerFile script to install SQL Server driver on PHP-FPM container
The text was updated successfully, but these errors were encountered:
crisleiria commented Nov 7, 2019
This is an development enviroment on an save local network, so I dont mind to downgrade TLS version, at least for now.
But when I build PHP-FPM container with this code I have an error. Maybe the order on the script is not correct. I honestly dont know.
Note:
I have 2 other PCs running vagrant with Homestead machine, I have installed SQL Server driver manually and connect to the same SQL Server and never found this issue..
I will investigate TLS configuration on vagrant homestead client machine.
david-puglielli commented Nov 8, 2019
I have not been able to get your Dockerfile to work, so I can't reproduce the issue. I'm also not sure I'm using Laradock correctly - I assume you are running docker-compose up -d nginx mssql rather than docker-compose up -d nginx mysql ? Could you provide more detail, along with the Dockerfile you are using?
crisleiria commented Nov 9, 2019
Hi @david-puglielli there is a very good question. ;) I was not clear, sorry for that.
Laradock is very complex project with a lot of diferent container setup mainly for Laravel developers. On this case it is nginx container that leverages also php-fpm and workspace containers (where docker file has the mssql script).
It happens that my Laravel web application connects to mysql and mssql. mysql container was irrelevant for this case.
Assuming that you have laradoc setup (follow docs or videos on Youtube).
To consume mssql you need to edit .env file on laradoc folder and set this 2 variables to true:
WORKSPACE_INSTALL_MSSQL=true
PHP_FPM_INSTALL_MSSQL=true
this will install mssql requirement on php-fpm and workspace containers.
docker files can be found on [laradock folder]/php-fpm and [laradock folder]/workspace
If you search for SQL Server you will see batch script to install.
I have attached both docker files
dockerFiles.zip
My main question is: What should I change to not use TLS on connection, or to overpass this error..
I can also downgrade mssql driver.. I dont know..
I think this is very usefull for anyone that uses PHP development with Docker and connects to mssql server. Docker is becoming very popular.
Читайте также: