Ошибка odbc sqlstate 01000

I’m trying to create an ODBC connection to SQL Server, but when i do, i get error:

Connection failed:
SQLState: '01000'
SQL Server Error: 10060
[Microsoft][ODBC_SQL Server Driver][TCP/IP Sockets] COnnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC_SQL Server Driver][TCP/IP Sockets] SQL Server does not exist or access denied.

Here’s what I’ve tried:

1. not a firewall issue: tried with firewall on SQL Server turned off, and client turned off. Also able to telnet 1433 from client and that works fine.
2. not an access issue: I can login from many different computers, including the SQL Server itself using the account i’m using on client (using SQL Server authentication)
3. i can ping the hostname and IP address. (i’ve tried both)

The only thing i can think of is that the client computer is windows server 2003, and has various roles setup:

• File Server
• Application Server
• Terminal Server
• Domain Controller
• DNS Server

I don’t know why these services are setup, but for the time being i can’t shut them off. Would it be any of these, and if so, is there a way to disable any of the aspects of them, that might be blocking SQL.

Any advise truly appreciated!

asked Nov 15, 2012 at 20:00

Found the issue. For some reason, even though SQL was not in use on the client, it was installed, and by going to All Programs -> Microsoft SQL Server -> Client Network Utility, i found that the protocal TCP/IP was using port 4717. I have no idea why that port was in use, and even further, i’m not sure why it would even matter — I was just setting up an odbc connection, and not using sql server (on the client) at all. Either way, I changed this to the typical port (1433), and like boom, ODBC connection works like charm!

By default SQL Native Client seems to look for MSSQLSERVER instance. However, if you use SQLEXPRESS all connections through that will fail.

Very strange, but try installing your SQL Server with MSSQLSERVER instance name — it should work!

answered Feb 18, 2015 at 17:23

To create a new Data source to SQL Server, do the following steps:

1. In host computer/server go to Sql server management studio —> open Security Section on left hand —> right click on Login, select New Login and then create a new account for your database which you want to connect to.

2. Check the TCP/IP Protocol is Enable. go to All programs —> Microsoft SQL server 2008 —> Configuration Tools —> open Sql server configuration manager. On the left hand select client protocols (based on your operating system 32/64 bit). On the right hand, check TCP/IP Protocol be Enabled.

3. In Remote computer/server, open Data source administrator. Control panel —> Administrative tools —> Data sources (ODBC).

4. In User DSN or System DSN , click Add button and select Sql Server driver and then press Finish.

5. Enter Name.

6. Enter Server, note that: if you want to enter host computer address, you should enter that`s IP address without «\». eg. 192.168.1.5 and press Next.

7. Select With SQL Server authentication using a login ID and password entered by the user.

8. At the bellow enter your login ID and password which you created on first step. and then click Next.

9. If shown Database is your database, click Next and then Finish.

answered May 25, 2016 at 7:31

Adding another check point to all the above answers. Valid if you are using a named instance of Sql Server ( ServerNameInstanceName)

• Make Sure UDP Port is enabled in Windows Firewall with SQL Server Default UDP Port
  Number 1434. If not present create a new incoming rule in Firewall for UDP 1434 Port and restart SQL Server Browser Services in SQL configuration Manager.

answered Jul 9, 2021 at 13:38

hi @mauricio0015,

Looks like the ODBC driver was not installed properly. The error message is from the Driver Manager, complaining that it can’t find the ODBC driver. Can you please check your odbc.ini and odbcinst.ini to make sure the ODBC driver entries are there? You should see the path of the ODBC driver in odbcinst.ini. Please make sure the path is valid and then verify your connection using isql first before attempting to connect with PHP. If isql succeeds and you’re still having connection issues, please list the entries you have in your config files and we can debug further. Thanks.

I got same problem
I use mssql on ubuntu 16.04
I removed package mssql-tools
and not can’t install
I got error
mssql-tools : Depends: msodbcsql (< 13.2.0.0) but 17.0.1.1-1 is to be installed
also php cant connect to mssql

@nicdnepr your problem looks completely different than the one above. Are you trying to install mssql-tools? If you are using msodbcsql 17 preview then you will need to manually install the mssql-tools 17 preview which is also available in the preview folder
If you have further problems with this can you please open a new issue so we can look into this further and not hijack this one.
Thanks..

To me, odbcinst.ini has the following:

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-17.0.so.1.1
UsageCount=1

But I’m getting exactly the same issue on openSUSE, running PHP CLI commands.

@neoacevedo Could you provide a repro script and the output you are getting?

good, I am just using yii migrate with the following configuration:

'db' => [
            'class' => 'yiidbConnection',
            'dsn' => "sqlsrv:Server=external-or-local-server,1433;Database=mydatabase",
            'username' => 'SA',
            'password' => 'the-password',
            'charset' => 'utf8',
            'enableQueryCache' => false
        ],

On Server I use localhost with exactly the same results as if it were Amazon or Azure RDS.
The output is just like

$ php yii migrate
PHP Fatal error:  Uncaught PDOException: SQLSTATE[01000]: [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL Server' : file not found in /home/nestor/public_html/myproject/vendor/yiisoft/yii2/db/Connection.php:660

Although in local I can’t connect via sqlcmd, on remote I can do it without problems.

@neoacevedo The latest version of the pdo_sqlsrv driver defaults to ODBC driver 17, so if you are seeing an error message saying that ‘ODBC Driver 13 for SQL Server’ is not found, you are probably using an older version of the pdo_sqlsrv driver. Please try the latest preview by running pecl install pdo_sqlsrv-5.2.0RC1 or getting the binary from the release page and let us know if the problem is fixed. Thanks!

Following this comment I uninstalled the latest pdo_sqlsrv and sqlsrv drivers and installed the 5.2.0RC1 and now the error changes to SSL Provider: [error:140A90A1:SSL routines:SSL_CTX_new:library has no ciphers]’ so I will check for any issue related to this.

Hi, I’ve been having the same problems on this using Leap 42.3. In my case I reverted back to to the previous versions of the driver and tools in zypper. ie msodbcsql.13.1.9.2-1 and mssql-tools.14.0.6.0-1 and that at least allows me to keep working. In the meantime, I’ve disabled the ms repo so that I don’t get any further updates.

I had this issue too, as the file libmsodbcsql did exist, I looked at its dependencies

root@Mespilus:/opt/microsoft/msodbcsql/lib64# readelf -d libmsodbcsql-13.1.so.9.2 | grep NEEDED
 0x0000000000000001 (NEEDED)             Shared library: [libdl.so.2]
 0x0000000000000001 (NEEDED)             Shared library: [libodbcinst.so.2]
 0x0000000000000001 (NEEDED)             Shared library: [libcrypto.so.1.0.0]
 0x0000000000000001 (NEEDED)             Shared library: [libkrb5.so.3]
 0x0000000000000001 (NEEDED)             Shared library: [libgssapi_krb5.so.2]
 0x0000000000000001 (NEEDED)             Shared library: [libcurl.so.4]
 0x0000000000000001 (NEEDED)             Shared library: [libssl.so.1.0.0]
 0x0000000000000001 (NEEDED)             Shared library: [libuuid.so.1]
 0x0000000000000001 (NEEDED)             Shared library: [libstdc++.so.6]
 0x0000000000000001 (NEEDED)             Shared library: [libm.so.6]
 0x0000000000000001 (NEEDED)             Shared library: [libgcc_s.so.1]
 0x0000000000000001 (NEEDED)             Shared library: [libpthread.so.0]
 0x0000000000000001 (NEEDED)             Shared library: [libc.so.6]

And checking if each library was present on the system. This one was missing
0x0000000000000001 (NEEDED) Shared library: [libssl.so.1.0.0]
The package build by microsoft doesn’t seem to reference/require/install the proper libssl.
It references libssl1.0.0 while debian 9 seems to have >1.0.0 installed.
Installing libssl1.0.0 from debian jessie fixed it. for me

@AnnoyingTechnology Where could I find the libssl.so.1.0.0 for Ubuntu?
I installed it via wget and dpkg. Is there a better way of doing this?

Many thanks for the fix!

I leave this scripts that worked for me.

My problem was pretty similar yours and I tested all the options such as changing the driver location, making a symbolic link, modify /etc/*.ini files, etc… nothing worked.

My problem, running python 3.6, pyodbc package in a docker container from alpine was the library libssl1.0.0

Here you will find my installation script for pyodbc Debian 8 (alpine) docker image using the driver v13

DRIVER={ODBC Driver 13 for SQL Server}

The command I run for database connection was:

import pyodbc
connection_string = 'DRIVER={ODBC Driver 13 for SQL Server};'
connection_string += 'SERVER={0};DATABASE={1};UID={2};PWD={3};'.format(host,dbname,user,pwd)
connection = pyodbc.connect(connection_string)

Hello, I have the same issue running a Debian 8 docker container with PHP7.2 and Nginx. I’ve installed the MSSQL ODBC 13.1 drivers but I still get the

SQLSTATE [01000, 0]: [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2' : file not found

As others have suggested, I ran readelf -d libmsodbcsql-13.1.so.9.2 | grep NEEDED and then ldconfig -p | grep libraryname for each of the libraries, but to my surprise, all the needed libraries are available.

My odbcinst.ini contains the right path for the libmsodbcsql file, which also has the right permissions.

My Dockerfile looks like:

FROM php:fpm

RUN apt-get update 
    && apt-get install -y --no-install-recommends vim curl libssl libc6 debconf subversion git apt-transport-https apt-utils 
    build-essential locales acl mailutils wget zip unzip 
    gnupg gnupg1 gnupg2

ENV ACCEPT_EULA=Y

# Microsoft SQL Server Prerequisites
RUN apt-get update 
    && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - 
    && curl https://packages.microsoft.com/config/debian/8/prod.list 
        > /etc/apt/sources.list.d/mssql-release.list 
    && apt-get install -y --no-install-recommends 
        locales 
        apt-transport-https 
    && echo "en_GB.UTF-8 UTF-8" > /etc/locale.gen 
    && locale-gen 
    && apt-get update 
    && apt-get -y --no-install-recommends install 
        msodbcsql 
        unixodbc-dev

RUN docker-php-ext-install mbstring pdo pdo_mysql 
    && pecl install sqlsrv pdo_sqlsrv xdebug 
    && docker-php-ext-enable sqlsrv pdo_sqlsrv xdebug

COPY php.ini /etc/php/latest/php.ini
COPY php-fpm-pool.conf /etc/php/latest/pool.d/www.conf

RUN curl -sSk https://getcomposer.org/installer | php -- --disable-tls && 
   mv composer.phar /usr/local/bin/composer

RUN groupadd dev -g 999
RUN useradd dev -g dev -d /home/dev -m

RUN rm -rf /var/lib/apt/lists/*

WORKDIR /var/www/symfony

EXPOSE 9000
CMD ["php-fpm"]

What could be the problem if the libraries exist and the path is correct?

Hey!

Did you try this line?
RUN DEBIAN_FRONTEND=noninteractive apt-get install libssl1.0.0

I think this a key problem when installing MSSql Drivers
Take a look and let me know, please

@J4VMC Anywhere after apt-get update? Just add it to after your RUN command for MSQL:

# Microsoft SQL Server Prerequisites
…
RUN DEBIAN_FRONTEND=noninteractive apt-get install libssl1.0.0

@nenetto this is what I get when I run docker-compose up -d:

E: Package 'libssl1.0.0' has no installation candidate

And my Dockerfile looks like:

FROM php:fpm

RUN apt-get update 
    && apt-get install -y --no-install-recommends vim curl libc6 debconf subversion git apt-transport-https apt-utils 
    build-essential locales acl mailutils wget zip unzip 
    gnupg gnupg1 gnupg2

ENV ACCEPT_EULA=Y

# Microsoft SQL Server Prerequisites
RUN apt-get update 
    && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - 
    && curl https://packages.microsoft.com/config/debian/8/prod.list 
        > /etc/apt/sources.list.d/mssql-release.list 
    && apt-get install -y --no-install-recommends 
        locales 
        apt-transport-https 
    && echo "en_GB.UTF-8 UTF-8" > /etc/locale.gen 
    && locale-gen 
    && apt-get update 
    && apt-get -y --no-install-recommends install 
        msodbcsql 
        unixodbc-dev

RUN DEBIAN_FRONTEND=noninteractive apt-get install libssl1.0.0

RUN docker-php-ext-install mbstring pdo pdo_mysql 
    && pecl install sqlsrv pdo_sqlsrv xdebug 
    && docker-php-ext-enable sqlsrv pdo_sqlsrv xdebug

COPY php.ini /etc/php/latest/php.ini
COPY php-fpm-pool.conf /etc/php/latest/pool.d/www.conf

RUN curl -sSk https://getcomposer.org/installer | php -- --disable-tls && 
   mv composer.phar /usr/local/bin/composer

RUN groupadd dev -g 999
RUN useradd dev -g dev -d /home/dev -m

RUN rm -rf /var/lib/apt/lists/*

WORKDIR /var/www/symfony

EXPOSE 9000
CMD ["php-fpm"]

@J4VMC ,

I check your docker image and you built it from php:fpm link here. This image is built from debian:stretch-slim link here.

This means that your image is debian based on the version 9.4 link here.

In summary, you should install following the instructions for Debian 9. However, you are installing drivers for debian 8
curl https://packages.microsoft.com/config/debian/8/prod.list -> From your Dockerfile.

So, my suggestion is the following Dockerfile for your problem

FROM php:fpm

RUN apt-get update 
    && apt-get install -y --no-install-recommends vim curl libssl libc6 debconf subversion git apt-transport-https apt-utils 
    build-essential locales acl mailutils wget zip unzip 
    gnupg gnupg1 gnupg2

ENV ACCEPT_EULA=Y

# Microsoft SQL Server Prerequisites
RUN apt-get update 
    && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - 
    && curl https://packages.microsoft.com/config/debian/9/prod.list 
        > /etc/apt/sources.list.d/mssql-release.list 
    && apt-get install -y --no-install-recommends 
        locales 
        apt-transport-https 
    && echo "en_GB.UTF-8 UTF-8" > /etc/locale.gen 
    && locale-gen 
    && apt-get update 
    && apt-get -y --no-install-recommends install 
        msodbcsql 
        unixodbc-dev

RUN echo "deb http://security.debian.org/debian-security jessie/updates main" >> /etc/apt/sources.list && apt-get update && DEBIAN_FRONTEND=noninteractive apt-get install libssl1.0.0

RUN docker-php-ext-install mbstring pdo pdo_mysql 
    && pecl install sqlsrv pdo_sqlsrv xdebug 
    && docker-php-ext-enable sqlsrv pdo_sqlsrv xdebug

COPY php.ini /etc/php/latest/php.ini
COPY php-fpm-pool.conf /etc/php/latest/pool.d/www.conf

RUN curl -sSk https://getcomposer.org/installer | php -- --disable-tls && 
   mv composer.phar /usr/local/bin/composer

RUN groupadd dev -g 999
RUN useradd dev -g dev -d /home/dev -m

RUN rm -rf /var/lib/apt/lists/*

WORKDIR /var/www/symfony

EXPOSE 9000
CMD ["php-fpm"]

@nenetto what php-fpm image can I use that is based on Debian 8? I think that will help with the issue.

I have no idea right now, take a look at dockerhub — php.

Anyway, in my last response I changed the debian repositories already. Test that image first and cross the fingers.

@nenetto still throws the E: Unable to locate package libssl error. I’ll try and build a custom image based on Ubuntu 16.04. Thanks for your help

@sprankle the name change applies to ODBC 17+
For those who want ODBC 13.1 the name remains the same

I’m trying to connect my access front-end to the SQL Server backend.

It was working when SQL Server was on my computer but now i’m trying to connect to a server

So when I create the DSN file with access

1. I chose SQL-Server driver ( I have also tried with SQL-Server native 10.0 )
2. I enter the server name that I copied from SQL Management Studio so there’s no typo there
3. I chose the NT authentication

Then I have this error

In the properties, I tried with TCP/IP with the default port 1433 and I also tried with the name pipes

I made sure that the checkbox to allow remote connection is checked

If I try to connect with management studio I got this error

I can see the server when I browse on the network

I’m trying this troubleshotting but i’m stuck with the telnet command. It says Could not open connection to host on 1433

I also tried with no port and i got the same error on port 23

Any ideas?

Thank you

asked Nov 21, 2012 at 20:24

To create a new Data source to SQL Server, do the following steps:

1. In host computer/server go to Sql server management studio —> open Security Section on left hand —> right click on Login, select New Login and then create a new account for your database which you want to connect to.

2. Check the TCP/IP Protocol is Enable. go to All programs —> Microsoft SQL server 2008 —> Configuration Tools —> open Sql server configuration manager. On the left hand select client protocols (based on your operating system 32/64 bit). On the right hand, check TCP/IP Protocol be Enabled.

3. In Remote computer/server, open Data source administrator. Control panel —> Administrative tools —> Data sources (ODBC).

4. In User DSN or System DSN , click Add button and select Sql Server driver and then press Finish.

5. Enter Name.

6. Enter Server, note that: if you want to enter host computer address, you should enter that`s IP address without «\». eg. 192.168.1.5 and press Next.

7. Select With SQL Server authentication using a login ID and password entered by the user.

8. At the bellow enter your login ID and password which you created on first step. and then click Next.

9. If shown Database is your database, click Next and then Finish.

answered May 25, 2016 at 7:45

1. Windows firewall blocks the sql server. Even if you open the 1433 port from exceptions, in the client machine it sets the connection point to dynamic port. Add also the sql server to the exceptions.

«C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBinnSqlservr.exe»

More info

1. This page helped me to solve the problem. Especially

or if you feel brave, locate the alias in the registry and delete it
there.

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerClientConnectTo

answered Jul 8, 2017 at 8:05

answered Jun 26, 2014 at 6:56

Received SQLSTATE 01000 in the following error message below:

SQL Agent — Jobs Failed: The SQL Agent Job «LiteSpeed Backup Full» has failed with the message «The job failed. The Job was invoked by User X. The last step to run was step 1 (Step1). NOTE: Failed to notify via email. — Executed as user: X. LiteSpeed(R) for SQL Server Version 6.5.0.1460 Copyright 2011 Quest Software, Inc. [SQLSTATE 01000] (Message 1) LiteSpeed for SQL Server could not open backup file: (N:BACKUP2filename.BAK). The previous system message is the reason for the failure. [SQLSTATE 42000] (Error 60405). The step failed.»

In my case this was related to permission on drive N following an SQL Server failover on an Active/Passive SQL cluster.

All SQL resources where failed over to the seconary resouce and back to the preferred node following maintenance. When the Quest LiteSpeed job then executed on the preferred node it was clear the previous permissions for SQL server user X had been lost on drive N and SQLSTATE 10100 was reported.

Simply added the permissions again to the backup destination drive and the issue was resolved.

Hope that helps someone.

Windows 2008 Enterprise

SQL Server 2008 Active/Passive cluster.

answered May 26, 2015 at 7:41