AnswerBun.com

Connecting to MSSQL server using sqlcmd and odbc driver on RHEL 6 fails

Server Fault Asked on December 12, 2020

I spent the last hour trying to connect to a mssql server using sqlcmd through odbc driver.
I was talking to the db guy but he doesn’t seem to have a clue what is going on.. Maybe you can help me out to find some questions I could ask that guy..

I am using the official odbc driver from Microsoft. Aparently we are using some sort of dynamic ports.

There is no firewalls in between.

# odbcinst -q -d -n
[PostgreSQL]
[MySQL]
[ODBC Driver 11 for SQL Server]

I tried it different ways

Error code 0x2AF9:

sqlcmd -S hostnameDEV04 -U username -P password
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 11 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..
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : TCP Provider: Error code 0x2AF9.

and

Error Locating Server/Instance Specified [xFFFFFFFF].

sqlcmd -S hostname\DEV04 -U username -P password
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 11 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..
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. 

Using perl

DBI connect('DASHBOARD','username',...) failed: [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Login timeout expired (SQL-HYT00) [state was HYT00 now 08001]
[unixODBC][Microsoft][ODBC Driver 11 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. (SQL-08001)
[unixODBC][Microsoft][ODBC Driver 11 for SQL Server]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].  (SQL-08001) at ./test.pl line 4
Can't connect to : [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Login timeout expired (SQL-HYT00) [state was HYT00 now 08001]
[unixODBC][Microsoft][ODBC Driver 11 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. (SQL-08001)
[unixODBC][Microsoft][ODBC Driver 11 for SQL Server]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].  (SQL-08001) at ./test.pl line 4.

3 Answers

After reading this thread: https://github.com/Microsoft/msphpsql/issues/190

I had to find out another portnumber SQL Server was listening on by executing

USE MASTER 
GO 
xp_readerrorlog 0, 1, N'Server is listening on' 
GO

Result

2018-04-12 03:19:57.830 Server       Server is listening on [ 'any' <ipv6> 49155].                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
2018-04-12 03:19:57.830 Server       Server is listening on [ 'any' <ipv4> 49155]. 

add a Rule for the Windows Firewall on SQL Server host: - enabling specific Port 49155 (I think this can vary)

Then this command succeeded on my Ubuntu machine:

sqlcmd -Smyhost,49155 -U sa -P sa_pwd -d mydb -Q "select @@servername"

Answered by knb on December 12, 2020

Based on a blurb from here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/36b8956e-0218-44aa-b717-725fe1d601f9/error-in-sqlserver-odbc-dirver-for-linux?forum=sqldataaccess

The UDP protocol just won't work in Linux using the Microsoft provided ODBC tools and sqlcmd. The UDP portion is used initially to probe the server to find the TCP port the requested database is listening on.

The solution use at my work is to switch to pymssql which will work with named instance and the UDP port requests.

Answered by jkinney on December 12, 2020

I was hitting a similar issue. It turns out that the DEV04 part of the hostnameDEV04 is used to indicate that the client should be connecting to the server on a different port from the default. In the Microsoft world, using DEV04 automatically ensures that the client connects to the correct port... but that doesn't seem to work with the Linux sqlcmd.

Ask you SQL Server admin which port the DEV04 instance is listening on and connect using hostname,PORT

Answered by jerrykan on December 12, 2020

Add your own answers!

Related Questions

How do i mount a Raid disk in linux

2  Asked on January 25, 2021

 

AH01630: client denied by server configuration Apache

3  Asked on January 24, 2021 by koulapic

     

Postfix/Dovecot-LMTP configuration confusion

2  Asked on January 24, 2021 by user8056359

         

Cannot sign in to aws console

0  Asked on January 22, 2021 by rosencreuz

     

Router drops connection

1  Asked on January 22, 2021 by deniz-zoeteman

     

Windows 10 computer won’t update

2  Asked on January 22, 2021 by hagen-von-eitzen

     

Error while mounting partition with Live CD

1  Asked on January 21, 2021 by sat

     

logrotate not rotating the logs

4  Asked on January 21, 2021 by carmen

 

How to troubleshoot GRE tunnel issues?

1  Asked on January 21, 2021 by linux911

     

Dell PowerEdge R430 iDRAC8 snmp OIDs for hardware

1  Asked on January 20, 2021 by alex_90

 

Snort in KVM machines

1  Asked on January 20, 2021 by emilio-macias

   

check_nrpe: ssocket time out after 10 seconds

1  Asked on January 19, 2021 by suganya

       

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP