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:
- 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.
- 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)
- 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
1
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!
answered Nov 15, 2012 at 20:44
russdsrussds
8255 gold badges25 silver badges48 bronze badges
1
Is it possible that the server in question has not been configured to use named pipes?
CAUSE
The most common reason that this connection attempt failed is
that this DSN or ODBC data source attempted to make a connection using
the TCP/IP sockets Net-Library, which is Dbmssocn.dll. Because the SQL
Server is not listening for incoming connections for TCP/IP sockets
clients, the connection fails. From ISQL/w, the second error message
stated above would be returned. The OS Error is 10061, and the
function call that failed was ConnectionOpen(connect()).WORKAROUND
By default, SQL Server will listen for incoming connections
made by Named Pipes clients. Named Pipes is the default IPC mechanism
for clients to connect to a SQL Server version 4.2, 6.0, and 6.5
server. The Named Pipes DLL is Dbnmpntw.dll, and it should be located
in the WindowsSystem or WinntSystem32 directory. The TCP/IP sockets
netlib DLL is Dbmssocn.dll, and also should be in the WindowsSystem
or WinntSystem32 directory.The most common resolution to this problem
is to configure the client computer to connect using Named Pipes.
Configuring Named Pipes If the Microsoft Data Access Components (MDAC)
ODBC drivers have been installed on the computer, you can make this
change in the second step of the Create New Data Source wizard.NOTE: You can download MDAC drivers from:
http://msdn2.microsoft.com/en-us/data/aa937730.aspx To configure the
client, start the Create New Data Source wizard, click the Client
Configuration button, and perform the following steps, based on the
version of the SQL Server ODBC driver you are using:For SQL Server ODBC Driver version 3.50
Click the Net Library tab and use the drop down list box to set the default network (Net Library) to Named Pipes. On the Advanced tab, remove any advanced entries that reference the server you are connecting to. Click Done.For SQL Server ODBC Driver version 3.70
In the Network Libraries section of the Edit Network Library Configuration dialog box, select Named Pipes. Click OK.If you do not have the MDAC ODBC drivers installed, you can use the
SQL Server Client Configuration Utility tool to set the default
Net-Library to Named Pipes. You can install the client utilities from
the SQL Server CD from the i386 directory.
Source: http://support.microsoft.com/kb/195566
answered Nov 15, 2012 at 20:19
SchmitzITSchmitzIT
9,1549 gold badges65 silver badges92 bronze badges
1
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
tonysepiatonysepia
3,3103 gold badges27 silver badges45 bronze badges
To create a new Data source to SQL Server, do the following steps:
-
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.
-
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.
-
In Remote computer/server, open Data source administrator. Control panel —> Administrative tools —> Data sources (ODBC).
-
In User DSN or System DSN , click Add button and select Sql Server driver and then press Finish.
-
Enter Name.
-
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.
-
Select With SQL Server authentication using a login ID and password entered by the user.
-
At the bellow enter your login ID and password which you created on first step. and then click Next.
-
If shown Database is your database, click Next and then Finish.
answered May 25, 2016 at 7:31
VahidVahid
912 silver badges3 bronze badges
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
I need to connect to SQL Server 2016/17 using .NET client and ODBC driver (in a legacy application).
[I cannot change the code from ODBC driver to SQL driver]
After disabling TLS 1.0 on the client machine (which also runs IIS server), I get the following error:
System.Data.Odbc.OdbcException (0x80131937): ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error
ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (SECDoClientHandshake())
I can confirm that this error does NOT occur if I try to connect to SQL Server version 2014. My requirement is to connect to SQL Server 2016 or 2017. Both these version throws SSL Security error SECDoClientHandshake if TLS 1.0 is disabled.
After checking the forums, I have already tried the following solutions:
1) Upgrading Microsoft® ODBC Driver version 13 and version 17. Neither of them solved the problem.
2) Enabled — «Use FIPS-compliant algorithms for encryption, hashing, and signing».
3) Updated .NET Framework to 4.8
4) Setting: ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
None of the above solution seem to work.
I know there is a possible solution for this because SQLCMD uses ODBC driver and SQLCMD works if TLS 1.0 is disabled.
Please suggest a solution.
Regards,
Pranay
- Remove From My Forums
-
Question
-
Hello,
client computer with windows 10 was updated on 1803 version and our application not work.
Application is shared on server1 (windows server2003), data on server2 SQL server 2012 on windows server 2012.
Application is 32 bit and over odbc SQL connect to data.
Error:
Connection failed:
SQLState: ‘01000’
SQL Server Error: 2
[Microsoft][ODBC SQL Server Driver][DBNETLIBl]ConnectionOpen(Connect()).
Connection failed:
SQLState: ‘08001’
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.I create new test application that get data from SQL.
When application is run from shared network — error.
When I copy application on client local disk — work OK.
Thank You for help
Ludek
After sending many update requests on my webpage the database connection crashes and give me this error
Anyone can help ?
disable TSL1.0 and TSL1.1
Connectiong code
try {
$dbh = new PDO("sqlsrv:Server=$Serve;Database=$name;ConnectionPooling=0",$user, $password;
$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
//echo "Connection Established"
}
catch (PDOException $e) {
print "Error:" . $e->getMessage() . "<br/>";
}
NA
devpro
16.2k3 gold badges27 silver badges38 bronze badges
asked Sep 4, 2019 at 16:19
1
I had a similar problem and the solution was to restart IIS.
For this, just go to Start and open Command Prompt as administrator. Then type iisreset and hit enter. After a few seconds, everything may be up and running again.
Hope it works for you and for others with similar issue.
answered Oct 22, 2020 at 16:46
Disclaimer; This is not «the solution», just the same behaviour and how to bypass it. Very little info on the matter and I have tried for a month to find what causes this.
We have the exact same behaviour on Windows Server 2019 (also 2016), IIS w PHP 7.3 and Azure SQL. (drivers 5.3 to 5.7.1 tested, PHP 7.1 to 7.3 tested)
This seem to be a connection issue, came with windows update and the code is valid but will not be able to connect from time to time. When it first crashes no connections will be made until IIS reloads fastcgi.
What server OS is this on? (On Linux we do not seem to have this problem)
Have no idea but seem to be the driver and OS related, if we specify ODBC in the connection string it does not seem to crash but it does not handle all charsets out of the box with ODBC driver. You can fix that programatically though.
ODBC utf-8:
Unable to retrieve UTF-8 accented characters from Access via PDO_ODBC
TLS
SSL/TLS 1.2 Connection Issues — PHP/SQLSRV
answered Jan 4, 2020 at 5:01
3
we downgraded to Windows Server 2012 R2 and now it seems like the issues are gone (we run Azure and AWS instances). Azure SQL seem to be closest related to sql server 2014.
For the Windows 2012 servers I did install the PHP 7.3.7 package that came with «web platform installer» and also the SQl Server driver 5.6 for that PHP version.
Don’t think it is neccessary or needed for the SQL connections but have added curl and ssl cacert path in php.ini (it is needed for pushing data to AWS S3 though).
I also tested IIScrypto to adjust registry settings to use either TLS 1.2, all TLS, different cyphers, the solutions in the above links but no success.
The problems started with windows update or possibly a sql server update and it is intermittent making it hard to find out. Clearly related to communication but not reproducable «on demand».
We are moving the solution to a Linux platform as soon as the rest of the legacy (.asp/.aspx) scripts are converted to php.
My best bet is to use the latest php and latest sql driver from «web platform installer», also update to latest ODBC driver if you need but we are running now with the version 17 (2017.173.01.01) 2/14/2019 that came with the Windows server 2012R2 image.
answered Jan 7, 2020 at 13:11
4
Hi rosnaib11,
It was notting related with ubuntu environment, it was SQL Server setting.
On my case, TCP dynamic ports was enabled on SQL Server settings.
You need to set this port to your SQL connection string. On my case I am using Laravel, I needed to setup port 49300 to my connection and thats it. If not, it will try to use default port setting (1433)
Hope this helps…
`
‘sqlsrvconnection’ => [
'driver' => 'sqlsrv',
'host' => env('PHC_HOST', 'localhost'),
'database' => env('PHC_DATABASE_PRODUCTION', 'forge'),
'username' => env('PHC_USERNAME', 'forge'),
'password' => env('PHC_PASSWORD', ''),
'charset' => 'utf8',
'prefix' => '',
'port' => 49300
],`