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.
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
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP